How do you rank inside each SQL group?
Ranking inside each group needs PARTITION BY, a part of a window function that splits the rows into groups before it calculates. A moving average needs a frame, which says how many rows around the current one to use.
With these two ideas you can find the top two in every class, or smooth a noisy week of figures.
By the end of this lesson
Note
I can rank inside each group and average over a moving window.
Key words
| Word | What it means |
|---|---|
| Partition | one group of rows that a window function treats on its own |
| Frame | the rows around the current one that a calculation uses |
| Moving average | the average of the last few rows, recalculated for each row |
| Subquery | a query written inside brackets in another query |
| row_number() | gives each row in a partition its own position number |
The cells students fill in are left blank in this preview.
PARTITION BY restarts the calculation for each group
Add partition by class inside over, and the database splits the rows into one group for each class. It ranks inside each group on its own.
The first place in class 3A and the first place in class 3B both get 1. Without a partition, the whole school would be ranked as one list.
Try it: rank inside each class
-- Rank the students inside each class, on test 4.
select class, student, mark,
rank() over (partition by class order by mark desc) as pos
from marks
where test_no = 4
order by class, pos;
Top two per group need a subquery
Say you want only the first two places in each class. It is tempting to write where pos <= 2 beside the window function. That fails, because WHERE runs before the windows are calculated.
So put the ranking in a subquery, in brackets after from. The outer query then keeps the rows where pos is 2 or less.
Try it: the best two marks per class
-- The two best marks in each class, with the test they came from.
select class, student, test_no, mark
from (
select class, student, test_no, mark,
row_number() over (partition by class order by mark desc) as pos
from marks
)
where pos <= 2
order by class, pos;
A common mistake
Warning
Many people think a window function can go in where. It cannot: the rows are chosen before any window is calculated. Calculate the rank in a subquery, then filter it outside. DuckDB also has a shortcut, qualify, but the subquery works everywhere.
A frame picks nearby rows to use
A frame is a small moving window of rows. rows between 2 preceding and current row means: the two rows before this one, and this one.
Averaging over that frame gives a moving average. The first rows have fewer than three rows behind them, so the frame is shorter there.
Worked example: Asha's three test moving average
| Test | Mark | Rows in the frame | Moving average |
|---|---|---|---|
| 1 | 62 | 62 | 62.0 |
| 2 | 70 | 62, 70 | 66.0 |
| 3 | 66 | 62, 70, 66 | 66.0 |
| 4 | 78 | 70, 66, 78 | 71.3 |
The cells students fill in are left blank in this preview.
Try it: Asha's moving average
-- Asha's average over her last three tests.
select student, test_no, mark,
round(avg(mark) over (
partition by student
order by test_no
rows between 2 preceding and current row), 1) as moving
from marks
where student = 'Asha'
order by test_no;
-- Change 2 preceding to 1 preceding for a two test average.
Going further
Change the frame to rows between 1 preceding and 1 following. It averages the row before, the row itself and the row after. Run it for Ben and say what is different at the first and last test.
Then try qualify row_number() over (...) <= 2 instead of the subquery.