Advanced20 min read

Window Functions

Analyze rows in context with OVER, PARTITION BY, and ranking window functions.

Window Function Concepts

Window functions compute values across related rows without collapsing result sets like GROUP BY. The OVER clause defines the window—PARTITION BY groups rows and ORDER BY sorts within each partition.

MySQL 8.0+ supports window functions fully. They replace many self-join and variable hacks from older MySQL versions.

SELECT
  user_id,
  total,
  SUM(total) OVER (PARTITION BY user_id) AS user_lifetime_spend,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq
FROM orders;

Ranking Functions

ROW_NUMBER assigns unique sequential ranks. RANK and DENSE_RANK handle ties differently—RANK skips numbers after ties, DENSE_RANK does not. NTILE distributes rows into buckets.

Use ROW_NUMBER to deduplicate rows by keeping rank = 1 per partition—a common pattern for latest record per group.

PERCENT_RANK and CUME_DIST support statistical reporting
Ranking requires ORDER BY in OVER for deterministic results
Combine with CTEs for readable multi-step analytics

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY price ASC) AS rn
  FROM offers
) t WHERE rn = 1;

Aggregate Window Functions

SUM, AVG, COUNT, MIN, MAX as window functions accumulate over the frame. Default frame is RANGE UNBOUNDED PRECEDING TO CURRENT ROW when ORDER BY is specified.

Running totals, moving averages, and cumulative metrics are natural window function use cases without correlated subqueries.

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue
FROM daily_sales;

Frame Clauses

ROWS BETWEEN and RANGE BETWEEN refine which rows contribute to each window. Three-row moving average uses ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

Understand difference between ROWS (physical rows) and RANGE (logical peers by sort key value) when duplicates exist in ORDER BY columns.

AVG(amount) OVER (
  ORDER BY created_at
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_day_avg

Performance Considerations

Window functions may materialize sorted partitions—ensure adequate memory (sort_buffer_size, tmp_table_size) for large partitions. Filter with WHERE before window computation using subqueries or CTEs.

Compare EXPLAIN plans against equivalent self-join solutions; window functions often simplify SQL and perform comparably with proper indexing on PARTITION BY and ORDER BY columns.