PostgreSQL Summary Stats and Window Functions

OVER()

Turns a regular function into a window function by defining the set of rows (the window) it operates on.

ROW_NUMBER()

Assigns a unique sequential number to each row.

ORDER BY (in OVER)

Controls the order in which rows are considered by the window function.

PARTITION BY

Divides rows into separate groups so the window function calculates independently within each group.

ROWS / RANGE

Defines a specific frame of rows (e.g., a moving window) for the window function to consider.

LAG()

Retrieves a value from a previous row relative to the current row.

LEAD()

Retrieves a value from a following row relative to the current row.

FIRST_VALUE()

Returns the value from the first row in the window.

LAST_VALUE()

Returns the value from the last row in the current window frame (not necessarily the last row overall).

RANK()

Assigns a rank to each row based on ORDER BY; tied rows share a rank and the next rank is skipped (gaps).

DENSE_RANK()

Assigns a rank to each row based on ORDER BY; tied rows share a rank and no ranks are skipped (no gaps).

NTILE(n)

Divides rows into n roughly equal groups/bins based on ORDER BY.

SUM() OVER()

Calculates a total across a window of rows without collapsing them.

AVG() OVER()

Calculates an average across a window of rows without collapsing them.

MIN() OVER()

Finds the smallest value within a window of rows without collapsing them.

MAX() OVER()

Finds the largest value within a window of rows without collapsing them.

COUNT() OVER()

Counts rows within a window without collapsing them.

Running total

A cumulative calculation that includes rows from the beginning of the window through the current row.

Moving calculation

A calculation that uses only a limited, defined set of nearby rows around the current row (not the whole partition).

Window frame

The specific subset of rows within a window used for the calculation at the current row.

ROWS BETWEEN

Syntax that defines the frame's boundaries using physical row positions relative to the current row.

UNBOUNDED PRECEDING

Frame boundary meaning the start of the partition.

n PRECEDING

Frame boundary meaning n rows before the current row.

CURRENT ROW

Frame boundary meaning the current row.

n FOLLOWING

Frame boundary meaning n rows after the current row.

UNBOUNDED FOLLOWING

Frame boundary meaning the end of the partition.

Default window frame

When ORDER BY is used without an explicit frame

Pivoting

Transforms a table from long/vertical format into wide/horizontal format, turning unique column values into new columns.

CROSSTAB()

PostgreSQL function (from the tablefunc extension) that pivots row data into columns.

ROLLUP()

GROUP BY subclause that generates hierarchical subtotals plus a grand total, based on column order.

CUBE()

GROUP BY subclause that generates all possible combinations of group-level subtotals plus a grand total.

COALESCE()

Returns the first non-NULL value from a list of values; used to replace NULLs with a fallback value.

STRING_AGG()

Combines multiple row values from a column into a single delimited string.