Skip to content
Coordinator DB
View as Markdown
View as Markdown

Window functions ​

Window functions calculate a value for every selected row using related rows from the same partition. Unlike a GROUP BY aggregate, a window calculation retains the individual input rows.

Use window functions for running totals, moving averages, rankings and comparisons with a preceding or following observation.

Syntax ​

text
function(arguments) OVER (
    [PARTITION BY expression, ...]
    [ORDER BY expression [ASC | DESC], ...]
    [ROWS | RANGE | GROUPS frame_bounds [EXCLUDE exclusion]]
)

A named WINDOW definition can be reused by several expressions:

sql
CREATE TABLE sales (region TEXT, day INTEGER, amount INTEGER);
INSERT INTO sales VALUES ('East', 1, 10), ('East', 2, 20), ('West', 1, 50);
sql
SELECT region, day, amount,
       sum(amount) OVER daily AS running_total,
       row_number() OVER daily AS position
FROM sales
WINDOW daily AS (PARTITION BY region ORDER BY day)
ORDER BY region, day
FORMAT csv;
text
"East",1,10,10,1
"East",2,20,30,2
"West",1,50,50,1

Partitions and ordering ​

PARTITION BY groups rows for independent calculations. Without it, all selected rows belong to one partition.

The ORDER BY inside OVER determines positions for the calculation. It does not sort the final query result; use an outer ORDER BY for that. Rows equal on every window ordering expression are called peers. Ranking functions use peer groups to handle ties.

Window frames ​

A frame selects a subset of a partition around the current row. Aggregate windows and first_value, last_value and nth_value read the frame. Ranking functions and lag/lead use the partition regardless of frame bounds.

With an ordering clause and no explicit frame, the frame runs from the beginning of the partition through the current peer group. Without ordering, the default frame covers the whole partition.

Unit Meaning of an offset
ROWS A number of physical rows before or after the current row.
GROUPS A number of peer groups before or after the current peer group.
RANGE A distance from the current ordering value; an offset frame requires one ordering expression.

Bounds can use UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING and UNBOUNDED FOLLOWING, subject to their valid start/end ordering. ROWS and GROUPS offsets must be nonnegative whole numbers; RANGE offsets must be nonnegative numeric values.

Moving totals ​

An explicit ROWS frame includes the current sale and at most one preceding sale:

sql
CREATE TABLE sales_2 (day INTEGER, amount INTEGER);
INSERT INTO sales_2 VALUES (1, 10), (2, 20), (3, 30);
sql
SELECT day, amount,
       sum(amount) OVER (
           ORDER BY day
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS moving_total
FROM sales_2
ORDER BY day
FORMAT csv;
text
1,10,10
2,20,30
3,30,50

Frame exclusions ​

EXCLUDE NO OTHERS keeps every selected frame row. EXCLUDE CURRENT ROW removes the current row. EXCLUDE GROUP removes its whole peer group. EXCLUDE TIES removes other peers while retaining the current row.

Aggregate windows ​

Ordinary aggregates such as sum, count and avg can be followed by OVER. Their local input and NULL rules still apply. Aggregate DISTINCT is not supported in windows. Ordered-set percentiles explicitly exclude OVER; consult the aggregate reference for restrictions on individual functions.

Function reference ​