Window functions

group by collapses. A window does not

group by gives you one row per group and throws the detail away. A window function computes across a set of rows and still returns every row — so you can print a line and its share of the total, a customer and their rank, a month and the running total beside it.

the same aggregate, twice

select style, sum(price) from doors group by style;   -- 4 rows

select name, style, price,
       sum(price) over (partition by style) as style_total
from   doors;                                         -- 10 rows, each with its group's total

The anatomy of over()

partition by is the window's version of group by. order by inside over has nothing to do with the query's own order by — it decides what "previous" and "running" mean. Leave everything out and the window is the whole result set.

three parts, all optional

function() over (
  partition by <what splits the rows into independent sets>
  order by     <what order the function sees them in>
  rows between <which rows around the current one count>
)

Ranking

three numberings that differ only on ties

row_number() over (order by spent desc)   -- 1 2 3 4 — arbitrary on a tie
rank()       over (order by spent desc)   -- 1 2 2 4 — ties share, then a gap
dense_rank() over (order by spent desc)   -- 1 2 2 3 — ties share, no gap
ntile(4)     over (order by spent desc)   -- which quarter of the list

"The top one in each group" is row_number

Number the rows inside each partition, then keep number 1 — in a subquery or CTE, because a window function cannot go in where (it is computed after the filtering).

Top per group — the pattern worth memorising

the best-selling door of each style

with per_door as (
  select   d.style, d.name,
           sum(i.qty * i.unit_price) as revenue
  from     doors d
  join     order_items i on i.door_id = d.id
  group by d.id, d.style, d.name
),
ranked as (
  select *, row_number() over (partition by style order by revenue desc) as rn
  from   per_door
)
select style, name, round(revenue, 2) as revenue
from   ranked
where  rn = 1
order by style;

-- flush Orsenne · glazed Calastra · panel Vesper · pivot Arclith

Running totals and frames

the frame decides which rows are in the sum

sum(rev) over (order by month)                                       -- default frame: up to here
sum(rev) over (order by month rows between unbounded preceding and current row)  -- the same, spelled out
avg(rev) over (order by month rows between 2 preceding and current row)          -- 3-month moving average
sum(rev) over ()                                                     -- the grand total, on every row

The default frame is RANGE, not ROWS

With order by month and duplicate months, the default range frame includes every row of the current month — so a running total jumps. When you mean "the rows up to this one", say rows.

Looking at the neighbours

lag and lead are how month-over-month, day-over-day and "time since the previous event" are written. Doing it with a self join works and is four times the code.

lag, lead, first_value, last_value

select month,
       revenue,
       lag(revenue)  over (order by month)            as last_month,
       revenue - lag(revenue) over (order by month)   as change,
       first_value(revenue) over (order by month)     as january
from   monthly;

-- 2024-02 is 3660, and 4700 down on January

Where you can use one

select and order by only

-- illegal: the filter runs before the window is computed
select name from doors where row_number() over (order by price) <= 3;

-- legal: compute it, then filter in the outer query
select name from (
  select name, row_number() over (order by price desc) as rn from doors
) where rn <= 3;

Dialect

Window functions are in PostgreSQL, SQLite 3.25+, MySQL 8, SQL Server and Oracle. MySQL 5.7 has none of this, which is why so much old code does it with variables and self joins.