A window function is marked by the OVER clause, which defines the set of rows (the "window") the function operates across — optionally partitioned into groups with PARTITION BY, similar in spirit to GROUP BY but without collapsing rows. Common window functions include ROW_NUMBER (a unique sequential number per row), RANK (ties share a rank, leaving a gap afterward), DENSE_RANK (ties share a rank, no gap), and running aggregates like SUM() OVER (ORDER BY ...) for a running total.
PARTITION BY: grouping without collapsing
Every original row survives — unlike GROUP BY, which would collapse each department into one row, PARTITION BY just tells the aggregate which rows to average within, per row.
SELECT
name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;
-- EVERY employee row is kept, each showing their own department's averageROW_NUMBER, RANK, DENSE_RANK
These three differ only in how they handle ties — a common source of confusion until seen side by side.
SELECT
name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, -- always unique: 1,2,3,4
RANK() OVER (ORDER BY score DESC) AS rank_num, -- ties share a rank, then GAP: 1,2,2,4
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num -- ties share a rank, NO gap: 1,2,2,3
FROM scores;
-- aliased as rank_num / dense_rank_num, not rank / dense_rank: RANK and DENSE_RANK
-- became reserved words in MySQL 8.0+, so using them unquoted as a column alias is a syntax error thereRunning totals and moving calculations
Adding ORDER BY inside OVER turns an aggregate into a running calculation — each row sees the aggregate computed up to and including itself, in the specified order. One pitfall: ORDER BY alone defaults to a RANGE frame, which treats rows that tie on the ORDER BY value as peers and gives every row in the tie the same total (the sum through the whole peer group, not strictly "through this row"). An explicit ROWS frame avoids that by always accumulating row by row regardless of ties.
SELECT
order_date, amount,
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;
-- each row's running_total is the sum of every amount up to and including this row
-- (without the explicit ROWS frame, two orders sharing the same order_date would both show
-- the combined total for that date, not two distinct running values)