What is a window function in SQL?
A window function does a calculation across a set of rows and puts the answer beside each row. It does not squash the rows into one, the way GROUP BY does.
That lets you add a running total, a rank or a change from the day before to a table, and still see every row.
By the end of this lesson
Note
I can add a running total or a rank beside each row without losing rows.
Key words
| Word | What it means |
|---|---|
| Window function | a calculation over related rows that keeps every row |
| OVER | the keyword that turns a function into a window function |
| Running total | the sum of every value so far, in order |
| Rank | the position of a row when the rows are put in order |
| Tie | two rows with the same value in the order |
The cells students fill in are left blank in this preview.
GROUP BY merges rows, windows keep them
With GROUP BY shop, six rows of one shop become one row. The detail is gone.
A window function adds over (...) after the function. The brackets say which rows to look at. The result is a new column, and all six rows stay.
Run the query below. The last column grows as the days go by.
Try it: a running total of takings
-- A running total: every row stays, and a new column grows.
select day, amount,
sum(amount) over (order by day) as running
from takings
order by day;
-- Change sum to count and see what the column shows.
ORDER BY inside OVER orders the calculation
Inside the brackets, order by day puts the rows in day order for the calculation. A sum with an order is a running total: each row adds itself to the ones before.
Takings of 120, 95 and 140 give running totals of 120, 215 and 355.
Leave the brackets empty, over (), and the sum covers every row. Each row then shows the same total.
Worked example: building a running total
| Day | Takings | Working | Running total |
|---|---|---|---|
| 2 March | 120 | 120 | 120 |
| 3 March | 95 | 120 + 95 | 215 |
| 4 March | 140 | 215 + 140 | 355 |
| 5 March | 110 | 355 + 110 | 465 |
The cells students fill in are left blank in this preview.
Rank and lag depend on order
rank() over (order by amount desc) gives 1 to the biggest amount, 2 to the next, and so on.
lag(amount) fetches the value from the row before. Take it away from the current amount and you get the change since yesterday. The first row has no row before it, so its lag is NULL.
Try it: rank and daily change
-- Rank each day by takings, and compare it with the day before.
select day, amount,
rank() over (order by amount desc) as best,
amount - lag(amount) over (order by day) as change
from takings
order by day;
A common mistake
Warning
Many people think the order by at the end of the query sets the running total. It only sorts what you see. The order by inside over sets the calculation. Sort the days backwards at the end, and the totals do not change.
Ties: rank skips, dense_rank does not
Two rows with the same value are a tie. rank() gives them the same number, then skips ahead. Scores of 90, 85, 85 and 70 get ranks 1, 2, 2 and 4.
dense_rank() does not skip, so the same scores get 1, 2, 2 and 3.
row_number() gives every row its own number. For tied rows, which gets the lower number is not fixed.
Try it: a tie, two ways
-- Two people share 85. Compare rank and dense_rank.
select name, score,
rank() over (order by score desc) as rnk,
dense_rank() over (order by score desc) as dense
from scores
order by score desc, name;
Going further
Use sum(amount) over () to show each day's share of the week as a percentage, rounded to one decimal place. Divide the amount by the total and multiply by 100.
Say why this needs a window function and not GROUP BY.