Window Functions in MySQL 8.0 with Practical Examples

For years, every running total or "previous row" report I wrote in MySQL was a hack: user variables with undefined evaluation order, correlated subqueries scanning the table once per row, or just doing it in the application. I'm not sad to see those go. MySQL 8.0 finally has standard window functions, and reporting queries get both simpler and faster.

Below are the ones I use most, on a small orders table. You need MySQL 8.0.11 or later; they don't exist in 5.7 or in MariaDB before 10.2. Here's the table to practice on:

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  created_at DATE NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  INDEX (customer_id, created_at)
);
INSERT INTO orders (customer_id, created_at, total) VALUES
 (1,'2018-09-01',120.00),(1,'2018-09-03',80.00),(1,'2018-09-10',45.50),
 (2,'2018-09-02',300.00),(2,'2018-09-15',150.00),
 (3,'2018-09-05',20.00),(3,'2018-09-06',20.00),(3,'2018-09-20',60.00);

Ranking within groups

The classic "top N per group" question. ROW_NUMBER() numbers rows within each partition; RANK() and DENSE_RANK() handle ties differently:

SELECT customer_id, created_at, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
       RANK()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS drnk
FROM orders;

For customer 3, the two 20.00 orders get rn 2 and 3, rnk 2 and 2 (then 4 for the next), and drnk 2 and 2 (then 3). To return only the largest order per customer, wrap it:

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC, id) AS rn
  FROM orders o
) t WHERE rn = 1;

Always add a tie-breaker (id here) to ORDER BY inside the window when you rely on ROW_NUMBER(). I forgot it once and got different "top" rows on different runs, which was fun to debug.

Running totals and moving averages

SELECT customer_id, created_at, total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at, id) AS running_total,
       AVG(total) OVER (PARTITION BY customer_id ORDER BY created_at, id
                        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_last_2
FROM orders;

The frame clause is where most mistakes happen. When you specify ORDER BY but no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all peer rows with the same ORDER BY value. If two orders share a date, both are included in the running total of the first one. Adding id to the ORDER BY, or using ROWS instead of RANGE, makes it row-by-row.

Previous and next rows

LAG() and LEAD() read a value from another row in the partition without a self-join:

SELECT customer_id, created_at, total,
       LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_order,
       DATEDIFF(created_at, LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)) AS days_since_prev,
       total - LAG(total, 1, 0) OVER (PARTITION BY customer_id ORDER BY created_at) AS delta
FROM orders;

The third argument to LAG is the default for the first row in the partition, so delta for a customer's first order equals the full amount instead of NULL.

Named windows and percentiles

When several functions share the same partition and order, define the window once:

SELECT customer_id, created_at, total,
       FIRST_VALUE(total) OVER w AS first_total,
       LAST_VALUE(total)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_total,
       NTILE(4) OVER (ORDER BY total) AS quartile,
       PERCENT_RANK() OVER (ORDER BY total) AS pct_rank
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);

Note LAST_VALUE: with the default frame it returns the current row, because the frame ends at CURRENT ROW. Extending the frame to UNBOUNDED FOLLOWING is what makes it return the last row of the partition. That one catches almost everyone once.

Is it really faster?

Run EXPLAIN on the running-total query. In the Extra column you should not see Using temporary; Using filesort twice for a single window if the index (customer_id, created_at) matches the PARTITION BY and ORDER BY; MySQL can then read the rows in order. EXPLAIN FORMAT=JSON shows the window step explicitly under windowing, including whether a temporary buffer was needed.

Compare against the old approach to see the difference:

SELECT o.*, (SELECT SUM(total) FROM orders p WHERE p.customer_id = o.customer_id AND p.id <= o.id) AS running_total
FROM orders o;

On a few million rows, the correlated subquery is quadratic; the window version reads each partition once.

Errors and limits

• ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.: window functions are evaluated after WHERE, GROUP BY and HAVING, so they cannot appear in those clauses. Wrap the query in a derived table or a CTE (WITH t AS (...) SELECT ... FROM t WHERE rn = 1).
• ERROR 3583 (HY000): Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type: RANGE frames with an offset need a single numeric or date column; use ROWS for everything else.
• Window functions cannot be used in UPDATE or DELETE directly; compute the row numbers in a CTE and join.
• MariaDB 10.2+ supports most of the same functions but not the WINDOW clause syntax in every version; check before copying queries between the two.

Use ROWS when you mean rows, and always add a tie-breaker. Do that and window functions behave.