SQL Window Functions: an Aggregate for Every Row

The idea: an aggregate for every row, without collapsing the rows

GROUP BY region with SUM(amount) gives one row per region: the single rows are gone. A window function computes the same kind of value, but for every row, over a set of related rows called its window. You write it with OVER (…): SUM(amount) OVER (PARTITION BY region) keeps all 8 rows and adds a column with each row's regional total (Demo: GROUP BY collapses rows, OVER keeps them).

The page uses one small table, sales (id, region, month, amount), 4 rows in region N and 4 in region S. Two N rows share month 2, and two S rows share the amount 90, so the ties that make window functions tricky are there. Everything shown is MariaDB (window functions since 10.2); MySQL 8 has the same syntax and gives the same results.

id	region	month	amount
1	N	1	100
2	S	1	70
3	N	2	50
4	S	2	90
5	N	2	30
6	S	3	90
7	N	3	80
8	S	4	40

PARTITION BY splits, ORDER BY orders

The window of a row is described in three parts, all optional:

function(...) OVER (
    PARTITION BY region         -- which rows belong together
    ORDER BY month, id          -- their order inside the partition
    ROWS BETWEEN ... AND ...    -- the frame: which of them this row looks at
)

The engine sorts the rows by the partition keys, then the order keys, and walks them. A partition is like a group that is not collapsed: a window never looks across a partition boundary, and running values, ranks and row numbers start again in each one. Without PARTITION BY the whole result is one partition.

Frames: ROWS vs RANGE, and the default

The frame is the part of the partition an aggregate such as SUM or AVG uses for the current row. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the first row of the partition to this row": a running total, N 100, 150, 180, 260 (Demo: running total with a ROWS frame). ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING is a 3-row moving average; at a partition edge the frame simply has 2 rows (Demo: moving average). MariaDB gives AVG of an integer column 4 decimals, so the values read 75.0000, 60.0000, 53.3333, 55.0000.

ROWS counts physical rows. RANGE works on the value of the ORDER BY key, so rows with the same value, the peers of the current row, are in or out together. That matters because of the defaults:

OVER (…) has	frame used
no ORDER BY	the whole partition
ORDER BY, no frame clause	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: up to the last peer
a frame clause	exactly that frame

So SUM(amount) OVER (PARTITION BY region ORDER BY month) is not a row-by-row running total: ids 3 and 5 are both in month 2, so both get 100 + 50 + 30 = 180 (Demo: default frame is RANGE).

For a true running total write ROWS and make the ORDER BY unique (here month, id). MariaDB supports ROWS and RANGE frames; a RANGE frame with a numeric offset (RANGE BETWEEN 10 PRECEDING …) needs exactly one numeric ORDER BY key.

ROW_NUMBER, RANK, DENSE_RANK

Ranking functions take no argument and ignore the frame; they only use the order. They agree until there is a tie (Demo: ROW_NUMBER vs RANK vs DENSE_RANK on a tie). For region S ordered by amount DESC:

id	amount	ROW_NUMBER	RANK	DENSE_RANK
4	90	1	1	1
6	90	2	1	1
2	70	3	3	2
8	40	4	4	3

ROW_NUMBER numbers rows 1, 2, 3, … and which of two peers comes first is not defined unless ORDER BY breaks the tie. RANK gives peers the same number and then skips (1, 1, 3). DENSE_RANK does not skip (1, 1, 2). MariaDB also has NTILE(n), PERCENT_RANK() and CUME_DIST(). A WINDOW w AS (…) clause names a window so several functions can share it.

LAG, LEAD and the other value functions

LAG(amount) reads the amount of the row before, LEAD(amount) the row after, in the partition's order. Past the edge of a partition there is no such row, so the result is NULL, or the third argument: LAG(amount, 1, 0). They also ignore the frame (Demo: LAG and LEAD). amount - LAG(amount) OVER w is the usual month-over-month change.

FIRST_VALUE, LAST_VALUE and NTH_VALUE do use the frame, and the default frame makes LAST_VALUE return the current row's last peer, not the partition's last row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the last row.

Where a window function may appear

Window functions are computed after FROM, WHERE, GROUP BY and HAVING, just before the final ORDER BY and LIMIT (see How a SELECT Runs). So they are allowed only in the SELECT list and ORDER BY. In WHERE, MariaDB refuses the query before reading a row:

ERROR 4015 (HY000): Window function is allowed only in SELECT list and ORDER BY clause

To filter on a rank, compute it one level down and filter outside, where it is an ordinary column. This is the standard "top N per group" query (Demo: not in WHERE: top 1 per region with a CTE):

WITH ranked AS (
  SELECT id, region, month, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS rn
  FROM sales)
SELECT * FROM ranked WHERE rn = 1;     -- id 1 (N, 100) and id 4 (S, 90)

Use RANK() … WHERE rnk = 1 instead to keep both S rows with 90. Because window functions run after GROUP BY, they can also take aggregates as input: RANK() OVER (ORDER BY SUM(amount) DESC) ranks the groups.

How MariaDB runs them

MariaDB puts the rows that reach the window step into a temporary table, sorts it by PARTITION BY + ORDER BY (one filesort per distinct window; windows that share a sort order share the sort), then scans it once per sort with cursors that mark the frame's start and end. For a moving frame it adds the row that enters and removes the row that leaves instead of summing the frame again. EXPLAIN FORMAT=JSON shows this as a window_functions_computation block with its filesort keys. How joins, temporary tables and filesort work in general is on How MySQL Executes JOIN, GROUP BY and ORDER BY.

What the page leaves out

RANGE frames with numeric offsets and the INTERVAL forms, NTILE, PERCENT_RANK, CUME_DIST, FIRST_VALUE / LAST_VALUE / NTH_VALUE on the canvas, aggregate window functions such as COUNT, MIN, MAX (same pattern as SUM), DISTINCT inside a window aggregate (MariaDB doesn't allow it), and NULLs in the order key. Features from the SQL standard that MariaDB does not implement, such as GROUPS frames, frame EXCLUDE and FILTER (WHERE …), are not shown. The canvas output keeps the window's sort order; a real query without ORDER BY may return rows in any order.

References

Window Functions Overview (MariaDB Knowledge Base)

Window Frames (MariaDB Knowledge Base)

Error 4015: Window function is allowed only in SELECT list and ORDER BY clause (MariaDB)

Window Function Concepts and Syntax (MySQL 8.4 Reference Manual)