SQL Reference ยท Window Functions
SQL PARTITION BY clause
Reference for SQL PARTITION BY inside OVER(): split rows into independent groups for window functions, how it differs from GROUP BY, NULL partitions and examples.
PARTITION BY is the part of an OVER() clause that divides the rows into independent groups called partitions. The window function is calculated separately inside each partition, so a running total, a rank or a group average starts over whenever the partition value changes.
It looks like GROUP BY, but it does not collapse rows. Every input row is kept, and each row simply sees only the rows of its own partition. If you leave PARTITION BY out, the entire result set is one partition.
SELECT first_name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS department_average FROM employees;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The last column counts only the rows left after the WHERE filter (orders of these two customers in that month, not all orders of the month), which is the filtering caveat from the usage notes below.
Syntax
function(...) OVER (PARTITION BY expression [, expression ...] [ORDER BY ...] [frame])
| Part | Description |
|---|---|
expression |
A column or any expression. Rows with equal values (all listed expressions together) belong to the same partition. |
Notes: the expressions are evaluated per row; there is no alias lookup, so repeat the expression instead of using a SELECT alias. The number of partitions equals the number of distinct value combinations.
Dialect differences
PARTITION BY is standard and works the same way everywhere window functions exist (SQLite 3.25+, PostgreSQL 8.4+, MySQL 8.0+, SQL Server, Oracle).
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
Expressions in PARTITION BY |
Yes | Yes | Yes | Yes | Yes |
NULL values form one partition |
Yes | Yes | Yes | Yes | Yes |
| Use a select-list alias | No | No | No | No | No |
-- Not portable: aliases cannot be used inside OVER SELECT substr(ordered_at, 1, 7) AS month, COUNT(*) OVER (PARTITION BY month) FROM orders; -- fails -- Portable: repeat the expression, or compute it in a subquery first SELECT month, COUNT(*) OVER (PARTITION BY month) FROM (SELECT substr(ordered_at, 1, 7) AS month FROM orders) AS m;
Usage notes
PARTITION BYis notGROUP BY.GROUP BYreturns one row per group.PARTITION BYkeeps every row and repeats the group-level result on each. You can combine them: window functions then run over the grouped rows, and they may use the grouped aggregates.NULLs are grouped together. All rows with aNULLpartition value fall into one partition, the same wayGROUP BYtreats them.- Several expressions create finer partitions:
PARTITION BY customer_id, statushas one partition per distinct pair. - Partitions are independent. Row numbers, ranks, running totals and
LAG()never cross a partition boundary. The first row of a partition has no previous row. - Frames never leave the partition. Even
UNBOUNDED PRECEDINGstops at the partition start. - Performance: the database usually sorts by the partition columns, so an index that starts with them can help. Fewer different
PARTITION BYlists in one query means fewer sorts. - Common mistake: filtering with
WHEREbefore the window changes the partitions. If you want counts over all rows but only some in the output, compute the window in a subquery and filter outside.
Related
OVER(),ROW_NUMBER(), window frames,GROUP BY- Lessons: Window functions, Ranking