SQL Reference ยท Window Functions
SQL OVER() clause
Reference for the SQL OVER() clause: turn aggregate and ranking functions into window functions with PARTITION BY, ORDER BY and frames, without collapsing rows.
The OVER clause turns a function into a window function. A normal aggregate such as SUM() collapses a group of rows into one row. With OVER, the same function is calculated across a set of related rows (the window), but every input row stays in the result and gets its own value.
Inside the parentheses you can describe the window with up to three parts: PARTITION BY splits rows into independent groups, ORDER BY sorts the rows inside each group, and a frame clause narrows the rows that count for the current row. All three are optional. An empty OVER () means "the whole result set".
SELECT first_name, salary, AVG(salary) OVER () AS company_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.
Syntax
function_name(arguments) OVER ( [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ] [ { ROWS | RANGE | GROUPS } frame_extent ] ) function_name(arguments) OVER window_name -- refers to a WINDOW clause
| Part | Description |
|---|---|
function_name |
An aggregate (SUM, AVG, COUNT, MIN, MAX) or a window-only function (ROW_NUMBER, RANK, LAG, ...). |
PARTITION BY |
Splits rows into groups. The function restarts in each partition. See PARTITION BY. |
ORDER BY |
Orders rows inside each partition. Required for ranking and offset functions, and it changes the default frame of aggregates. |
| frame clause | Chooses which rows around the current row are included. See window frames. |
window_name |
A name defined in a WINDOW clause. |
Returns: one value per input row, of the function's usual type.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Window functions | 3.25+ | 8.4+ | 8.0+ | 2005+ (frames since 2012) | 8i+ |
GROUPS frame unit |
3.28+ | 11+ | No | No | No |
EXCLUDE in frames |
3.28+ | 11+ | No | No | No |
NULLS FIRST / LAST |
3.30+ | Yes | No | No | Yes |
FILTER (WHERE ...) on aggregates |
3.30+ | Yes | No | No | No |
Named WINDOW clause |
Yes | Yes | Yes | 2022+ | Not in older releases |
-- SQL Server before 2022: no WINDOW clause, so repeat the definition SELECT salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees;
Usage notes
- Window functions run after
WHERE,GROUP BYandHAVING. They see the rows that survived filtering and grouping. You cannot use one inWHERE,GROUP BYorHAVING; wrap the query in a subquery or CTE and filter the outer one. - They may appear only in
SELECTandORDER BY. That is where the standard allows them. ORDER BYinsideOVERis independent of the finalORDER BY. The one inOVERdefines the calculation order; the outer one sorts the output.- An aggregate with
ORDER BYinOVERbecomes cumulative. Without a frame clause, the default is from the partition start up to the current row, including peers with equal sort values. That makesSUM(x) OVER (ORDER BY d)a running total. - Without
ORDER BYthe frame is the whole partition, so each row gets the same value. - Window functions do not change the row count. If you need one row per group, use
GROUP BYinstead. - Several windows in one query are fine. The database may sort once per distinct window, so reuse the same definition when you can.
- Ties in the
ORDER BYmakeROW_NUMBER()arbitrary among the equal rows. Add a unique column as a tie-breaker.