MySQL – Window Functions | Love Me Some Coding

An aggregate collapses a group into one row. A window function computes across a group of rows and keeps every row. That one difference is what lets a query show each order alongside a running total, or each item alongside its rank within its product — things that are genuinely awkward without them and three lines with them.

They arrived in MySQL 8.0.

The OVER clause

SELECT id, total,
       SUM(total) OVER (ORDER BY id) AS running_total
FROM   customer_order WHERE status = 'COMPLETED' ORDER BY id LIMIT 5;

+----+-------+---------------+
| id | total | running_total |
+----+-------+---------------+
|  1 | 28.91 |         28.91 |
|  2 | 18.43 |         47.34 |
|  3 | 32.18 |         79.52 |
|  4 | 32.18 |        111.70 |
|  6 | 24.93 |        136.63 |
+----+-------+---------------+

SUM(total) with a GROUP BY would give one row. OVER turns the same aggregate into a window function: every row survives, and each carries the sum of everything up to and including itself.

OVER takes two optional pieces:

PARTITION BY — split the rows into independent groups. Like GROUP BY, except the rows are not collapsed.
ORDER BY — order the rows within each partition. This is separate from the query's own ORDER BY.

OVER (), empty, means "the whole result set" — useful for putting a grand total next to every row.

PARTITION BY

SELECT product_name, size, line_total,
       ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY line_total DESC, id) AS rn
FROM   order_item WHERE product_name IN ('Pepperoni Pizza','Pepsi') ORDER BY product_name, rn;

+-----------------+--------+------------+----+
| product_name    | size   | line_total | rn |
+-----------------+--------+------------+----+
| Pepperoni Pizza | LARGE  |      33.98 |  1 |
| Pepperoni Pizza | LARGE  |      21.24 |  2 |
| Pepperoni Pizza | LARGE  |      16.99 |  3 |
| Pepperoni Pizza | MEDIUM |      15.49 |  4 |
| Pepperoni Pizza | SMALL  |      10.99 |  5 |
| Pepsi           | LARGE  |       8.97 |  1 |
| Pepsi           | LARGE  |       5.98 |  2 |
| Pepsi           | MEDIUM |       2.49 |  3 |
+-----------------+--------+------------+----+

The numbering restarts at 1 for each product. Note the , id tiebreaker in the window's ORDER BY — without it, rows with equal line_total could be numbered either way round, and the query would return different answers on different runs. The same rule as pagination.

RANK, DENSE_RANK and ROW_NUMBER

Three ways to number rows, differing only in what they do with ties:

SELECT product_name, ROUND(SUM(line_total),2) AS revenue,
       RANK()       OVER (ORDER BY SUM(line_total) DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY SUM(line_total) DESC) AS dense,
       ROW_NUMBER() OVER (ORDER BY SUM(line_total) DESC, product_name) AS rn
FROM   order_item GROUP BY product_name ORDER BY revenue DESC, product_name LIMIT 6;

+---------------------+---------+-----+-------+----+
| product_name        | revenue | rnk | dense | rn |
+---------------------+---------+-----+-------+----+
| Pepperoni Pizza     |   98.69 |   1 |     1 |  1 |
| Meat Lovers Pizza   |   64.47 |   2 |     2 |  2 |
| Supreme Pizza       |   55.72 |   3 |     3 |  3 |
| BBQ Chicken Pizza   |   36.98 |   4 |     4 |  4 |
| Veggie Lovers Pizza |   36.98 |   4 |     4 |  5 |
| Cheese Pizza        |   35.97 |   6 |     5 |  6 |
+---------------------+---------+-----+-------+----+

Two products tie at 36.98. Watch what happens on the row after:

RANK gives both 4 and then skips to 6 — Olympic-style.
DENSE_RANK gives both 4 and continues at 5 — no gaps.
ROW_NUMBER refuses to tie, giving 4 and 5 arbitrarily unless you break the tie yourself.

Pick deliberately. "Top 3 by revenue" with RANK can return four rows; with ROW_NUMBER it returns exactly three but drops one of a genuine tie.

Also note the window functions here operate on SUM(line_total) — window functions run after GROUP BY, so they can rank aggregates.

LAG and LEAD

SELECT id, total,
       LAG(total)  OVER (ORDER BY id) AS previous,
       total - LAG(total) OVER (ORDER BY id) AS change_from_previous
FROM   customer_order WHERE status = 'COMPLETED' ORDER BY id LIMIT 5;

+----+-------+----------+----------------------+
| id | total | previous | change_from_previous |
+----+-------+----------+----------------------+
|  1 | 28.91 |     NULL |                 NULL |
|  2 | 18.43 |    28.91 |               -10.48 |
|  3 | 32.18 |    18.43 |                13.75 |
|  4 | 32.18 |    32.18 |                 0.00 |
|  6 | 24.93 |    32.18 |                -7.25 |
+----+-------+----------+----------------------+

LAG reaches backwards, LEAD forwards. The first row has nothing before it, so LAG is NULL — supply a third argument for a default, LAG(total, 1, 0). This is how you compute month-over-month growth, time between events, or "did this value change from the previous row".

Top N per group

This is the problem window functions are most often reached for. It cannot be written with GROUP BY, because you need whole rows rather than aggregates:

WITH ranked AS (
    SELECT product_name, size, line_total,
           ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY line_total DESC, id) AS rn
    FROM   order_item
)
SELECT product_name, size, line_total
FROM   ranked WHERE rn = 1
ORDER  BY line_total DESC, product_name
LIMIT  5;

+-----------------------+--------+------------+
| product_name          | size   | line_total |
+-----------------------+--------+------------+
| Pepperoni Pizza       | LARGE  |      33.98 |
| Cheese Pizza          | MEDIUM |      25.98 |
| Meat Lovers Pizza     | LARGE  |      23.49 |
| BBQ Chicken Pizza     | LARGE  |      19.99 |
| Buffalo Chicken Pizza | LARGE  |      19.99 |
+-----------------------+--------+------------+

rn <= 3 instead of = 1 gives the top three of each. The CTE is not decoration: a window function cannot appear in WHERE, because WHERE runs before the window is computed. Compute it in a CTE (or derived table) and filter outside. That error — "Window function is allowed only in SELECT and ORDER BY" — is the one everybody hits first.

Frames

Inside a partition, the frame decides which rows the function sees. Adding ORDER BY to OVER silently sets the default frame to "from the start of the partition to the current row", which is why the first example produced a running total rather than a repeated grand total.

SELECT id, total,
       SUM(total) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
       SUM(total) OVER ()                                                             AS grand_total,
       AVG(total) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS moving_avg_3
FROM   customer_order WHERE status = 'COMPLETED' ORDER BY id LIMIT 5;

The first is the default written out. The second, with no ORDER BY, sees the whole partition. The third is a three-row moving average.

One subtlety worth knowing: ROWS counts physical rows, RANGE groups tied values together. With duplicates in the ordering column they give different answers, and RANGE is the default — so if a running total looks wrong at a tie, that is why.

What to remember

Window functions keep every row; aggregates collapse them.
PARTITION BY restarts the calculation per group; ORDER BY orders within it.
RANK skips after a tie, DENSE_RANK does not, ROW_NUMBER never ties.
Always give the window's ORDER BY a unique tiebreaker.
You cannot filter on one in WHERE — wrap it in a CTE.