Window functions: the same rows, with context

Group by collapses, a window does not

A GROUP BY turns forty orders into one row per customer. A window function computes across the same forty while leaving all forty in place: sum(total) OVER (PARTITION BY customer_id) adds a column to each order saying what that customer has spent in total. The rows stay; the context is added.

OVER is what makes a function a window function, and its two parts answer two questions. PARTITION BY asks "which rows count as the same group as me" — the window's version of GROUP BY. ORDER BY inside the window asks "in what order do I look back", and it is what makes running totals and comparisons with the previous row possible.

Ranking, three ways

ROW_NUMBER() numbers the rows 1, 2, 3 with no ties allowed. RANK() gives tied rows the same number and then skips — 1, 1, 3 — which is how competition results work. DENSE_RANK() gives tied rows the same number without a gap: 1, 1, 2. All three need an ORDER BY inside the window, because there is no ranking without a rule.

The most useful pattern in this chapter is the top-N-per-group: number the rows inside each group, keep the first three — WHERE place <= 3 — using a CTE to name the numbered result first, because a window function cannot be tested in the WHERE that produces it.

Looking back a row

LAG(total) reaches back one row inside the window and LEAD(total) forward one, both following the window's ORDER BY. total - LAG(total) OVER (ORDER BY sold_on) is the change since the previous sale — a comparison that would otherwise take a self-join and a headache.

A window with an ORDER BY has a default frame: from the start of the partition up to the current row, which is exactly a running total. The frame can be said out loud — ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — which turns the same sum into a moving window of three rows, and is the beginning of every moving average you have ever seen in a chart.

Where in the query the window runs

Window functions are evaluated after FROM, WHERE, GROUP BY and HAVING, and before the final ORDER BY and LIMIT. That position explains the rule from the last chapter: a window cannot be tested in the WHERE that produces its rows, because the value does not exist yet. A CTE is the usual way to put a new FROM between the computation and the filter.

The same position means a window sees the rows left after grouping, not the original table. If a query groups by customer, a window over that result ranks customers; if it does not group, the window ranks individual orders. Reading a query with both is a matter of tracking which rows exist at each stage.

A frame is a window inside the window

With an ORDER BY inside OVER, the default frame runs from the start of the partition to the current row — the running total everyone expects. But the default is RANGE, not ROWS: tied rows are treated as peers and all included together. That is why a running total can jump by two values at once when two rows share the same sort key.

Writing ROWS BETWEEN 2 PRECEDING AND CURRENT ROW counts physical rows, ties or not, and turns the same sum into a three-row moving window. Writing RANGE explicitly is a way of saying "include every peer". Neither is more correct; the query has to decide whether a tie is one position or several.

Try these next

• Change a running total from the default frame to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and compare the ties.
• Give lag(total, 1, 0) a default and see how the first row changes.
• Add ntile(4) over the sales and count how many rows land in each quartile.

Ranks, running totals, and the row before

-- Each sale, with its place inside the region and a running total.
SELECT region,
       rep,
       total,
       row_number() OVER (PARTITION BY region ORDER BY total DESC) AS place,
       sum(total) OVER (PARTITION BY region ORDER BY total DESC) AS running
FROM sales
ORDER BY region, place;

-- Ties are where the three rank functions disagree.
SELECT rep,
       total,
       rank()       OVER (ORDER BY total DESC) AS rank,
       dense_rank() OVER (ORDER BY total DESC) AS dense
FROM sales
ORDER BY total DESC;

-- The change since the previous sale.
SELECT sold_on,
       total,
       total - lag(total) OVER (ORDER BY sold_on) AS change
FROM sales
ORDER BY sold_on;

-- A moving window of three rows: the start of a moving average.
SELECT sold_on,
       total,
       round(avg(total) OVER (ORDER BY sold_on
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg
FROM sales
ORDER BY sold_on;