1. Calculating running totals and moving averages
Window functions have a number of great uses and in this lesson we are going to learn about two of them: calculating running totals and moving averages.
2. Calculating running totals
Just like in the last lesson, we will use the same motivating example, but with one extra column to represent each game in the series.
Here, we see once more six games and the number of runs scored by team. What we would like to do is calculate an ongoing tally by team of how many runs have been scored.
For our Arizona team, we want to count in order. First, we see that they scored eight runs.
Then, in the next game, they scored 6, for a total of 14.
In their third game, they scored 3 more, for a total of 17.
The Florida team has a similar output, adding up to 20. So how do we do this with T-SQL?
3. Running totals
To create the SQL query on the left, we need to have a few pieces in place.
First, we are going to need the per-game values we want to know about: team, game, and runs scored.
Then we want to know the running total of runs scored, so we will need to use the SUM() function to sum up values.
This has to be a window function because that's the way we will get both the detail records and the aggregated running total in the same query, so we will need an OVER() clause.
Inside the OVER() clause, we want to partition our running totals by team, showing totals on a per-team basis rather than for all teams.
Then, we need to give SQL Server the appropriate expression for ordering. We'll order by game in the series.
The next part of our OVER() clause is a choice between RANGE and ROWS. In a window function, the RANGE and ROWS clauses are used to define the set of rows that are included in the window. We'll explain these in more detail shortly.
In the example shown, we use RANGE to calculate a running total. For a running total, we want to go back to the beginning of our set, so we create a window between UNBOUNDED PRECEDING and the CURRENT ROW (and no further). That will give us a running total.
If we didn't want to use all the previous rows, but only wanted a window from the last two rows to the current row, we'd use RANGE BETWEEN 1 PRECEDING AND CURRENT ROW.
4. RANGE and ROWS
When creating window functions, we have two options: RANGE and ROWS.
RANGE will specify a range of results whereas ROWS focuses on the specific rows. This has a couple of follow-on implications.
RANGE will process all duplicate records at the same time, so if we have two instances of Game 1, RANGE will sum both of them together for both records.
ROWS will take duplicates one at a time, meaning the first Game 1 will have a total of that score and the second Game 1 will have as its total the sum of both.
Also, RANGE supports only two scenarios: specifically unbounded and current row. ROWS also allows us to specify a number of rows back or forward. This will help us when calculating moving averages.
5. Calculating moving averages
Let's look at the current and prior games to calculate the moving average.
For Arizona, we start with 8 runs.
Then they scored 6, so over a two-game stretch they averaged 7.
For the third game, we look at the 6 in game 2 and 3 in game 3 and get 4 because of the integer math.
Florida behaves similarly.
6. Let's practice!
Let's give running totals and moving averages a try in these exercises.