With GROUP BY you can find out how much each customer spent, but you lose the detail: each group is collapsed into one row. Window functions do the same calculation without merging the rows. Every order is still there, and next to it you can show its customer’s total, its category’s average or its position in a ranking.
A function becomes a window function when you add OVER:
function(column) OVER (
[PARTITION BY columns]
[ORDER BY columns]
)
- Empty
OVER (): the window is the whole result. PARTITION BY: splits the rows into groups and computes the function in each one separately.ORDER BY: sets an order within each group. It’s required by ranking functions,LAGandLEADand running totals.
OVER (): a total next to each row
The shop’s total sales next to each order, and the percentage each one represents:
| product | totalPrice | totalSales | percentage |
|---|---|---|---|
| Xiaomi Mi 11 | 4304.25 | 17251.60 | 24.9 |
| Echo DOT 4 | 2999.50 | 17251.60 | 17.4 |
| MacBook Pro M1 | 2449.50 | 17251.60 | 14.2 |
| Play Station 5 | 2199.80 | 17251.60 | 12.8 |
| Echo DOT 3 | 1999.50 | 17251.60 | 11.6 |
| Balay Oven | 1399.10 | 17251.60 | 8.1 |
| Xbox series X | 999.98 | 17251.60 | 5.8 |
| Nintendo Switch | 899.97 | 17251.60 | 5.2 |
With GROUP BY this would need a subquery for the total and another query to combine it with each row.
PARTITION BY: calculating per group
Now the total and the number of orders of each customer, while still seeing every order:
| name | product | totalPrice | customerOrders | customerSpend |
|---|---|---|---|---|
| Helen | Echo DOT 4 | 2999.50 | 2 | 4999.00 |
| Helen | Echo DOT 3 | 1999.50 | 2 | 4999.00 |
| Janice | MacBook Pro M1 | 2449.50 | 1 | 2449.50 |
| Kimberly | Nintendo Switch | 899.97 | 1 | 899.97 |
| Mavin | Play Station 5 | 2199.80 | 2 | 3199.78 |
| Mavin | Xbox series X | 999.98 | 2 | 3199.78 |
| Peter | Xiaomi Mi 11 | 4304.25 | 1 | 4304.25 |
Helen and Mavin have two rows each, and both show their total spend. Compare it with the grouped equivalent, which only returns one row per customer:
| name | customerOrders | customerSpend |
|---|---|---|
| Helen | 2 | 4999.00 |
| Janice | 1 | 2449.50 |
| Kimberly | 1 | 899.97 |
| Mavin | 2 | 3199.78 |
| Peter | 1 | 4304.25 |
Comparing each row with its group
Having the group’s value next to each row lets you calculate with it. For example, how far each product’s price is from the average of its description:
| productDescription | product | price | averagePrice | difference |
|---|---|---|---|---|
| Alexa Speaker | Echo DOT 3 | 39.99 | 49.99 | -10.00 |
| Alexa Speaker | Echo DOT 4 | 59.99 | 49.99 | 10.00 |
| Nintendo Game Console | Nintendo Switch | 299.99 | 299.99 | 0.00 |
| Sony Game Console | Play Station 5 | 549.95 | 549.95 | 0.00 |
| Xbox Game Console | Xbox series X | 499.99 | 499.99 | 0.00 |
The two Alexa speakers share a description, so their average is that of both ($49.99) and each one is $10 above or below it. Each console has a different description, so it’s its own group and the difference is 0.
Which functions support OVER
| Type | Functions |
|---|---|
| Aggregate | SUM, COUNT, AVG, MIN, MAX |
| Ranking | ROW_NUMBER, RANK, DENSE_RANK, NTILE |
| Offset | LAG, LEAD, FIRST_VALUE, LAST_VALUE |
Where they can be used
Window functions are computed after WHERE, GROUP BY and HAVING, just before ORDER BY. Therefore:
- They can be used in
SELECTandORDER BY. - They can’t be used in
WHERE. To filter on their result, compute them in a CTE and filter outside, as you’ll see in the next lesson.
Practice
- Add a column to the first example with the most expensive order in the whole shop (
MAX ... OVER ()). - For each order, work out what percentage it represents of its customer’s total spend.
- What happens if you remove the
INNER JOINfrom the second example and use onlyOrders? How does the order without a customer show up?
