PARTITION BY
PARTITION BY splits rows into independent windows so the function restarts per group, without reducing the result to one row each.
func(args) OVER (PARTITION BY expr [, expr] ... [ORDER BY ...] [frame_clause])PARTITION BY divides the rows into independent windows, and the window function restarts for each one. Rows in different partitions never see each other: a running total resets, a ROW_NUMBER starts again at 1, a SUM covers only the current row's group. It takes a list of expressions, not just column names, so PARTITION BY region, DATE_FORMAT(sold_at, '%Y-%m') is fine.
Not the same as GROUP BY
Both take a list of expressions and both put equal values together, but GROUP BY is a reduction and PARTITION BY is not. GROUP BY customer_id returns one row per customer; OVER (PARTITION BY customer_id) returns every original row, each carrying its customer's figure. They also run at different times — grouping first, windowing after — which is why a window function can legitimately aggregate over the output of a GROUP BY in the same query.
Naming a window
When several columns share a specification, define it once in a WINDOW clause and refer to it by name. That is more than typing saved: the MySQL 8.4 optimizer makes no attempt to merge identical OVER clauses, and the manual names a named window as the way to get them evaluated as one window. An OVER clause may add properties to a named window — OVER (w ORDER BY x) where w defines only a partition — but it can never replace a property the named window already sets.
Example
SELECT
s.region,
s.rep_id,
s.amount_cents,
-- Each of these restarts at every region boundary. A rep's row never
-- sees a number from another region.
SUM(s.amount_cents) OVER w AS region_cents,
AVG(s.amount_cents) OVER w AS region_avg_cents,
COUNT(*) OVER w AS region_sales,
MAX(s.amount_cents) OVER w AS region_best_cents
FROM sales AS s
WHERE s.sold_at >= '2026-01-01'
WINDOW w AS (PARTITION BY s.region) -- defined once, used four times
ORDER BY s.region, s.amount_cents DESC;When to use it
- Rank sales reps within each region rather than across the whole company.
- Reset a running total at the start of each account, month or session.
- Give every line item its order's total and line count.
- Reuse one window specification across several computed columns.
More examples
Named window and the longhand it replaces
The two queries return the same rows, so treat the WINDOW clause as the default way to write anything with more than one window column. It also means a change to the partitioning is made in one place rather than three.
-- Longhand: the same specification typed three times.
SELECT region, rep_id, amount_cents,
SUM(amount_cents) OVER (PARTITION BY region ORDER BY rep_id) AS running,
COUNT(*) OVER (PARTITION BY region ORDER BY rep_id) AS seen,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY rep_id) AS rn
FROM sales;
-- Shorthand: name it once. Identical results, and the optimizer can now
-- evaluate all three functions over a single window instead of three.
SELECT region, rep_id, amount_cents,
SUM(amount_cents) OVER w AS running,
COUNT(*) OVER w AS seen,
ROW_NUMBER() OVER w AS rn
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY rep_id);Adding to a named window, and what is rejected
Use this when most columns share a partition but one or two need their own ordering. It keeps a single source of truth for the partitioning while letting individual functions sort differently.
SELECT region, rep_id, amount_cents,
-- Allowed: w defines only a partition, so OVER adds an ordering.
RANK() OVER (w ORDER BY amount_cents DESC) AS rank_in_region,
-- The bare name reuses w exactly as defined: unordered, whole partition.
SUM(amount_cents) OVER w AS region_cents
FROM sales
WINDOW w AS (PARTITION BY region);
-- Rejected: OVER (w PARTITION BY rep_id)
-- An OVER clause may only add properties to a named window, never replace
-- one it already defines. Forward and backward references between named
-- windows are allowed, but cycles are not.Partitioning on an expression
Monthly or weekly buckets rarely exist as a stored column. Partitioning on a derived expression avoids adding one, though if the query runs often a generated column with an index on it is the faster shape.
-- One window per (region, calendar month). Any expression works, not only columns.
SELECT
s.region,
DATE_FORMAT(s.sold_at, '%Y-%m') AS ym,
s.amount_cents,
SUM(s.amount_cents) OVER (
PARTITION BY s.region, DATE_FORMAT(s.sold_at, '%Y-%m')
) AS region_month_cents,
ROUND(100 * s.amount_cents / SUM(s.amount_cents) OVER (
PARTITION BY s.region, DATE_FORMAT(s.sold_at, '%Y-%m')
), 2) AS pct_of_region_month
FROM sales AS s
ORDER BY s.region, ym, s.amount_cents DESC;
Discussion