Window functions

Window functions are the feature that removes most of the correlated subqueries, self-joins and application-side loops from a codebase. They compute a value for each row using other rows, without collapsing the rows the way GROUP BY does: a rank within a group, the previous row's value, a running total. Every "top three per customer" and "compare each month to the last" query is one of these, and both PostgreSQL and MySQL 8 support them in full.

#OVER and PARTITION BY

SELECT o.id, o.customer_id, o.total,
       SUM(o.total) OVER (PARTITION BY o.customer_id)            AS customer_total,
       o.total / SUM(o.total) OVER (PARTITION BY o.customer_id)  AS share
FROM orders o;

OVER is what makes it a window function. PARTITION BY splits the rows into groups the function works within, like GROUP BY without the collapse: every order is still a row, and the customer total appears beside it. An empty OVER () is one partition, the whole result, which is how you get a grand total on every line.

Window functions are evaluated after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. Two consequences: they see the filtered rows only, and you cannot reference one in WHERE. The second is why top-N-per-group needs a subquery.

#Ranking

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,   -- 1 2 3 4: ties broken arbitrarily
       RANK()       OVER (ORDER BY score DESC) AS rank,      -- 1 2 2 4: ties share, next skips
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense      -- 1 2 2 3: ties share, no gap
FROM players;

ORDER BY inside OVER says what the ranking is over; it is independent of the query's own ORDER BY. ROW_NUMBER is unique and therefore breaks ties by whatever order the engine happens to produce, so make the ordering deterministic (ORDER BY score DESC, id) whenever the number will be used to pick a row. NTILE(4) assigns quartiles.

#Top-N per group

The query a correlated subquery did badly:

SELECT id, customer_id, placed_at, total
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, id DESC) AS rn
  FROM orders o
) ranked
WHERE rn <= 3
ORDER BY customer_id, rn;

One pass over orders, with an index on (customer_id, placed_at DESC) making the partition and order free. The subquery is required because rn does not exist yet when WHERE runs. Use RANK instead of ROW_NUMBER if ties should all be kept, and DENSE_RANK if "top three scores" should mean three distinct values.

#LAG and LEAD

The previous or next row's value within the partition, in the window's order:

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month)                      AS prev_revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY month)      AS change
FROM monthly_revenue;

LAG(col, n, default) looks n rows back and returns default when there is no such row; the first month's change is computed against 0 instead of NULL. LEAD looks forward. Together they replace the self-join m1 JOIN m2 ON m2.month = m1.month + 1, which also silently loses the row after any gap in the months; LAG does not care about gaps, it looks at the previous row that exists.

#Frames: ROWS, RANGE and the default that surprises

When a window has an ORDER BY, aggregates over it are computed over a frame, and the default frame is not the whole partition:

SELECT placed_at, total,
       SUM(total) OVER (ORDER BY placed_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders WHERE customer_id = 42;

The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes every row that ties with the current one on the ordering value, so two orders at the same timestamp both get the running total that includes both, and the "running" line jumps. ROWS counts physical rows and gives the strictly increasing total people expect. Say ROWS explicitly for running totals; use RANGE only when tied rows really should share a value.

Frames also give moving windows: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a seven-row moving average, and RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW (PostgreSQL 11+) is a seven-day one, which differs on days with no rows.

#Naming windows, and the cost

Several functions over the same window repeat the clause; WINDOW w AS (PARTITION BY ... ORDER BY ...) names it once and each function says OVER w. The engine sorts the rows per distinct window; five functions over one named window is one sort, five different windows is five. A window's sort is served by an index whose columns match PARTITION BY then ORDER BY, which is the index to add when EXPLAIN shows a sort on a big table.

Both engines have had window functions for years: PostgreSQL since 8.4, MySQL since 8.0. A codebase on MySQL 5.7 has none, which is why it has user variables and self-joins doing this work, and why the upgrade is worth doing.

#Under the hood: the WindowAgg node

A window function is evaluated by a WindowAgg node (PostgreSQL; MySQL's is the "window" step in EXPLAIN FORMAT=TREE) placed above the joins, filters and grouping and below the final projection and ORDER BY. Its input must arrive sorted by PARTITION BY columns then ORDER BY columns; if no index or earlier step supplies that order, the planner inserts a Sort, and that sort is usually the whole cost of the query. The node then walks the sorted stream, detects partition boundaries by comparing keys, and for each row computes every function that shares this window. Functions over different windows get their own WindowAgg and their own sort, stacked; that is why naming one window and reusing it is not a style point but a plan with one sort instead of five.

Within a partition the node keeps a buffer of rows, because a frame can look backwards and forwards. For ROW_NUMBER, LAG and LEAD the buffer is tiny: the current row and a bounded offset. For an aggregate with a frame it depends on the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is a running accumulator that never re-reads; a moving window like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW adds the new row and subtracts the departing one when the aggregate is invertible (SUM, COUNT, AVG are; MIN, MAX are not, and re-scan the frame); and a frame that reaches UNBOUNDED FOLLOWING or a whole RANGE needs the entire partition buffered before the first row can be emitted. Large partitions with wide frames spill that buffer to a tuplestore on disk, which is the plan to look for when a window query is slow and the sort is not the reason.

RANGE versus ROWS is a boundary rule in the same walk. With ROWS the frame edge is a row count. With RANGE the node must find every row whose ordering value equals the current one (peers) and include them, which is why ties share a running total, and why RANGE with an offset (INTERVAL '7 days' PRECEDING) requires the ordering column to be a single numeric or date type. GROUPS (PostgreSQL 11+) is the third mode, counting peer groups rather than rows or values.

#Walkthrough: the leaderboard that paid the same prize twice

A weekly contest paid a prize to the top three scores. The query used ROW_NUMBER.

SELECT player_id, score, rn
FROM (SELECT player_id, score,
             ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
      FROM weekly_scores WHERE week = :week) w
WHERE rn <= 3;

1. Two players tied for second. ROW_NUMBER gave them 2 and 3 in an order the engine happened to produce, which changed between the preview run and the payout run because a parallel worker delivered the rows differently. The preview showed player A in third; the payout went to player B. A support ticket, then a policy question.
2. The policy turned out to be "ties share the place and both are paid, the next place is skipped", which is RANK, not ROW_NUMBER. The query was changed to RANK() OVER (ORDER BY score DESC) <= 3, and now four players could be paid in a week with a tie for third, which finance had to be told.
3. The second bug surfaced when the query was reused for "best score per player across all weeks": ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY score DESC) with rn = 1 picked one of two equal best scores arbitrarily, and the week shown beside it flipped between runs. The tiebreaker was added: ORDER BY score DESC, week DESC.
4. EXPLAIN on the fixed query showed a Sort on (player_id, score DESC, week DESC) over 80 million rows before the WindowAgg. An index on exactly those columns turned it into an index scan feeding the window with no sort, and the nightly job went from nine minutes to forty seconds.
5. The rules the team wrote: every ROW_NUMBER has a deterministic ORDER BY ending in a unique column; RANK or DENSE_RANK whenever ties have business meaning; and the index for a hot window matches partition then order.

Non-determinism in a ranking is invisible until two runs disagree, and by then one of them has been acted on.

#Try it yourself

SELECT customer_id, placed_at, total,
       LAG(total) OVER (PARTITION BY customer_id ORDER BY placed_at) AS prev,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY placed_at ROWS UNBOUNDED PRECEDING) AS running,
       COUNT(*) OVER (PARTITION BY status) AS same_status
FROM orders;

#Misconceptions

• "Window functions run per row like a correlated subquery." They run in one WindowAgg pass over a sorted stream; the sort, not the function, is the cost.
• "The default frame is the whole partition." With ORDER BY it is RANGE ... CURRENT ROW, which includes peers; without ORDER BY it is the whole partition.
• "ROW_NUMBER with ties is stable between runs." It is whatever order the executor produced; parallel workers change it. End the ORDER BY in a unique column.
• "One OVER clause per function costs nothing extra." Each distinct window is its own sort; name and reuse them.
• "You can filter on a window function in WHERE." It is evaluated after WHERE; wrap it in a subquery or CTE.

#Going deeper

• PostgreSQL manual, "Window Functions" (tutorial) and the SELECT reference's "WINDOW Clause", including ROWS, RANGE, GROUPS and EXCLUDE.
• MySQL reference, "Window Function Frame Specification" and "Window Function Optimization".
• Markus Winand, "Modern SQL: Window Functions" on modern-sql.com, with per-engine support tables.
• Itzik Ben-Gan, T-SQL Window Functions: engine-agnostic in its treatment of frames and the WindowAgg algorithm.
• The PostgreSQL source, nodeWindowAgg.c, for the tuplestore buffer and invertible aggregates.