Recall our Bob’s Burgers example of padding records. Let’s expand on it. We now want to know for each record in our padded record set, what was the rolling weekly running sum of burgers served as well as total burgers served to date. And we might as well get the sum of burgers served overall while we’re at it.
To prep your environment, go ahead and run the following to get your padded recordset and then we’ll grill from there:
--This handy dandy 'delete if exists' is something I routinely put in for managing temp tables
DROP TABLE if exists #tmp_BobsBurgers;
--Create a temporary table to store records of Bob's Burgers of the Day
CREATE TABLE #tmp_BobsBurgers (
Burger varchar(100)
,BurgerServedDate date
,BurgerOrderQuantity int
)
;
--Inserting some burgers of the day data...YUM!
INSERT INTO #tmp_BobsBurgers (
Burger
,BurgerServedDate
,BurgerOrderQuantity
)
SELECT 'Foot Feta-ish Burger' as Burger
,'2018-01-02' as BurgerServedDate
,5 as BurgerOrderQuantity
UNION ALL
SELECT 'Tunami' as Burger
,'2018-01-05' as BurgerServedDate
,8 as BurgerOrderQuantity
UNION ALL
SELECT 'Mission A-Corn-Plished Burger - Comes with Corn Salsa' as Burger
,'2018-01-08' as BurgerServedDate
,3 as BurgerOrderQuantity
UNION ALL
SELECT 'Pepper Don''t Preach Burger' as Burger
,'2018-01-09' as BurgerServedDate
,11 as BurgerOrderQuantity
UNION ALL
SELECT 'Rest in Peas Burger' as Burger
,'2018-01-10' as BurgerServedDate
,4 as BurgerOrderQuantity
UNION ALL
SELECT 'Fig-eta Bout It Burger' as Burger
,'2018-01-12' as BurgerServedDate
,12 as BurgerOrderQuantity
UNION ALL
SELECT 'The Roquefort Files Burger' as Burger
,'2018-01-15' as BurgerServedDate
,5 as BurgerOrderQuantity
UNION ALL
SELECT 'Olive And Let Die Burger' as Burger
,'2018-01-16' as BurgerServedDate
,7 as BurgerOrderQuantity
UNION ALL
SELECT 'Krauted House Burger' as Burger
,'2018-01-25' as BurgerServedDate
,14 as BurgerOrderQuantity
UNION ALL
SELECT 'Hit Me With Your Best Shallot Burger' as Burger
,'2018-01-26' as BurgerServedDate
,10 as BurgerOrderQuantity
UNION ALL
SELECT 'Chorizo Your Own Adventure Burger' as Burger
,'2018-01-30' as BurgerServedDate
,3 as BurgerOrderQuantity
;
--Create a temporary table to hold our date values
--Note this is just a simple example. You could add lots of other fields to your date dimension like first/last of month, fiscal stuff, etc.
DROP TABLE if exists #tmp_DimDate;
CREATE TABLE #tmp_DimDate (
DateKey Integer NOT NULL
,Date date
)
;
--Adding a primary key to our new date dimension
ALTER TABLE #tmp_DimDate ADD CONSTRAINT key_DimDate PRIMARY KEY (DateKey);
--Let's declare some variables to store our begin and end dates as well as hold our looping variable
DECLARE @StartDate date = '2018-01-01'
DECLARE @EndDate date = '2018-01-31'
DECLARE @DateLoop date
--Insert just a month's worth of dates for January 2018
SET @DateLoop = @StartDate
--Time to loop
WHILE @DateLoop <= @EndDate
BEGIN
--Let's add the current loop date record to our temporary Date Dimension
INSERT INTO #tmp_DimDate (
DateKey
,Date
) VALUES (
CONVERT(VARCHAR(10), @DateLoop, 112) --DateKey as YYYYMMDD
,@DateLoop --Date
)
--Time to move/add the next date record so we need to add a day to our loop
SET @DateLoop = DATEADD(d, 1, @DateLoop)
END
;
SELECT dt.Date as BurgerServedDate
,ISNULL(bb.Burger,'No Burger of the Day') as Burger
,ISNULL(bb.BurgerOrderQuantity,0) as BurgerOrderQuantity
FROM #tmp_DimDate dt
left join #tmp_BobsBurgers bb on dt.Date = bb.BurgerServedDate
Let’s first talk briefly about windows aggregate functions. They are used to do an aggregate (like summing) from a single row perspective. The syntax goes something like this:
SELECT SUM( [ some number ] ) OVER ( PARTITION BY [some value] ORDER BY [some value] ROWS BETWEEN [ CURRENT ROW | # parameter PRECEDING | UNBOUNDED PRECEDING ] AND [ CURRENT ROW | # parameter FOLLOWING | UNBOUNDED FOLLOWING ] )
So let’s explain each argument.
- SUM: This is your aggregate function and will sum a numeric value (note, there are other aggregate functions like FIRST_VALUE, ROW_NUMBER(), etc. that we can chat about later on). In our example, we’ll want to SUM([BurgerOrderQuantity])
- OVER: This just sets up our context. What are we going to sum over?
- PARTITION BY: This is an optional argument. Sometimes you want to restart your sum (or other aggregate) if your context changes. Let’s say we wanted to sum burgers based on customers who purchased them, we’d PARTITION BY [CustomerName]. We could then see Teddy’s tab…he eats a lot of Bob’s burgers!
- ORDER BY: This one tells you how to order your set. In our case, we’ll want to ORDER BY [BurgerServedDate]
- ROWS BETWEEN: This is an optional argument. Let’s you determine number of rows backward and forward to evaluate for your set. This argument has lots of parameters and seems confusingly verbose. So let’s break it down.
- PRECEDING:
- Do you want to start the aggregate at current row? Then say
ROWS BETWEEN CURRENT ROW… - Do you want to calculate a backward rolling set? For our Bob’s example, we want to calculate weekly totals. So we’d say
ROWS BETWEEN 6 PRECEDING…
(note that I didn’t say 7…that’s because your current row is part of the set). - Do you want to go all the way back to the first in the set? Then say
ROWS BETWEEN UNBOUNDED PRECEDING…
- Do you want to start the aggregate at current row? Then say
- FOLLOWING:
- Do you want to end the aggregate at the current row? Then say
…AND CURRENT ROW - Do you want to calculate an aggregate going forward a week? Then say
…AND 6 FOLLOWING - Do you want to calculate to the end of the set? Then say
…AND UNBOUNDED FOLLOWING
- Do you want to end the aggregate at the current row? Then say
- PRECEDING:
So let’s run the following SQL to show how you’d get:
- Weekly count of burgers ordered,
- Burgers ordered to date (shown 2 ways – if you don’t put in the optional rows between argument, it defaults to a running sum where it looks at beginning of set to current row),
- And finally, total burgers ordered overall.
SELECT BurgerServedDate
,Burger
,BurgerOrderQuantity
,sum(BurgerOrderQuantity) over (order by BurgerServedDate rows between 6 preceding and current row) as WeeklyTotalBurgerOrderQuantity
,sum(BurgerOrderQuantity) over (order by BurgerServedDate) as TotalToDateBurgerOrderQuantity_1
,sum(BurgerOrderQuantity) over (order by BurgerServedDate rows between unbounded preceding and current row) as TotalToDateBurgerOrderQuantity_2
,sum(BurgerOrderQuantity) over (order by BurgerServedDate rows between unbounded preceding and unbounded following) as TotalBurgerOrderQuantity
FROM (SELECT dt.Date as BurgerServedDate
,ISNULL(bb.Burger,'No Burger of the Day') as Burger
,ISNULL(bb.BurgerOrderQuantity,0) as BurgerOrderQuantity
FROM #tmp_DimDate dt
left join #tmp_BobsBurgers bb on dt.Date = bb.BurgerServedDate
) a
ORDER BY BurgerServedDate
So row #1 – we didn’t have a burger ordered that day. So our weekly total is 0 sum(0). There are no 6 records before and we stop at current row. Our total to dates (shown both ways) are both 0 because there are no records before row #1 and we stop at current row. And our overall total is 82 because it looks at all rows back and all rows forward and sums the total (0+5+0+0+8+0+0+3+11+4+0+12+0+0+5+7+…….)
Looking at row #8, we know that in the past 7 days, 16 burgers have been ordered (5+0+0+8+0+0+3) where we start to sum with row #2. It also matches are to date orders because row #1 is 0 (0+5+0+0+8+0+0+3) but note that the to dates are including row #1…just doesn’t change our calculation yet because of the 0 value.
On row #9, weekly burgers ordered is 22 (0+0+8+0+0+3+11) where we start summing with row #3. And note that our to dates are continuing to sum going forward with a value of 27 (0+5+0+0+8+0+0+3+11).
With all this talk of running sums, I feel perhaps I need to RUN off SUM of these burgers!
Leave a Reply