examples · sql · 9 / 13 · works today

9. Window functions

works today

ROW_NUMBER(), RANK(), DENSE_RANK(), LAG() and LEAD() with OVER (PARTITION BY ... ORDER BY ...) all execute server-side through the SQL translator, alongside SUM / AVG / COUNT / MIN / MAX over a window and the value and distribution functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, PERCENT_RANK, CUME_DIST). Explicit ROWS BETWEEN / RANGE frame clauses (running totals, moving windows) execute too — on the frame-respecting functions: the aggregates plus FIRST_VALUE / LAST_VALUE / NTH_VALUE. Ranking and offset functions ignore a frame by definition, so writing one on ROW_NUMBER, RANK, LAG, LEAD or NTILE is refused with a hint rather than silently dropped.

per-group ranking

Rank each customer's orders by amount - the canonical window use - runs server-side:

SELECT id, customer_id, amount_cents,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY amount_cents DESC
       ) AS rn
  FROM shop.orders

Each row comes back with its rn computed in one pass - no client-side sorting needed.

running totals — ROWS frame

Add a ROWS BETWEEN frame to accumulate a running total per partition — the classic dashboard shape — in one server-side pass:

SELECT id, customer_id, amount_cents,
       SUM(amount_cents) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running
  FROM shop.orders

running carries the cumulative sum as the window slides — customer 1 → 14900, 18800; customer 2 → 7200, 14400, 36400.

The two frame units differ in what they accept. ROWS takes every bound — UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, UNBOUNDED FOLLOWING — so a moving window such as ROWS BETWEEN 1 PRECEDING AND CURRENT ROW works. RANGE takes peer bounds only (UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING), where CURRENT ROW spans the whole tie group; a value offset such as RANGE BETWEEN 5 PRECEDING is refused, as are the GROUPS frame unit and EXCLUDE.

what a window cannot share a SELECT with

A window function runs over a single-table SELECT. It cannot yet appear in the same SELECT as a JOIN, a GROUP BY or DISTINCT, and the OVER call has to sit in the select list - you can ORDER BY the alias it was given, but not write the call itself into a WHERE or ORDER BY expression. Do the join or the aggregate in a subquery first, then window over its output. Named windows (WINDOW w AS (...) with OVER w) are supported; chaining one named window off another is not.