MySQL Window Functions Tutorial

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row like GROUP BY does. Introduced in MySQL 8.0, they enable ranking, running totals, moving averages, and row comparisons -- all in a single, readable query.

The OVER Clause

Every window function uses the OVER() clause to define the window. An empty OVER()treats the entire result set as one window. Adding PARTITION BY divides rows into groups, andORDER BY defines the order within each partition.

-- Running total across all orders
SELECT
  order_id,
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

ROW_NUMBER, RANK, and DENSE_RANK

These ranking functions assign a number to each row within a partition. ROW_NUMBER() always produces unique sequential numbers. RANK() leaves gaps after ties. DENSE_RANK() does not leave gaps.

SELECT
  department,
  first_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_num
FROM employees;

Top-N per Group

A common use case is selecting the top N rows within each group. Wrap the window function in a subquery and filter by the row number.

-- Top 3 earners per department
SELECT department, first_name, salary
FROM (
  SELECT
    department, first_name, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

LAG and LEAD

LAG() accesses the previous row's value and LEAD() accesses the next row's value within the window. These are powerful for calculating differences between consecutive rows.

SELECT
  order_date,
  total,
  LAG(total, 1)  OVER (ORDER BY order_date) AS prev_total,
  total - LAG(total, 1) OVER (ORDER BY order_date) AS change
FROM orders;

Aggregate Window Functions

Standard aggregates like SUM, AVG, COUNT, MIN, and MAX can be used as window functions. This lets you show both the detail row and the aggregate side by side.

SELECT
  first_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Named Windows

When multiple window functions share the same definition, use a named window to avoid repetition.

SELECT
  first_name,
  department,
  salary,
  RANK()       OVER w AS salary_rank,
  SUM(salary)  OVER w AS running_salary
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);