PostgreSQL Window Functions – Complete List with Examples

Window functions compute a value for each row from related rows defined by OVER (PARTITION BY … ORDER BY …), without collapsing rows like GROUP BY. A WINDOW w AS (…) clause lets several functions share one definition.

Name	Description	Since
`ROW_NUMBER()`	Number the rows of each window partition 1, 2, 3, … in ORDER BY order. Ties get different numbers.	8.4
`RANK()`	Rank rows by ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4).	8.4
`DENSE_RANK()`	Rank rows by ORDER BY, giving ties the same rank with no gaps after them (1, 2, 2, 3).	8.4
`PERCENT_RANK()`	Return a row's relative rank from 0 to 1: (rank - 1) / (rows in partition - 1).	8.4
`CUME_DIST()`	Return the share of rows ordered at or before the current row (including ties), from just above 0 to 1.	8.4
`NTILE()`	Split the rows of a window into N groups of nearly equal size and return each row's group number.	8.4
`LAG()`	Return a value from a previous row in the window, or a default when there isn't one. Ideal for comparing with the last period.	8.4
`LEAD()`	Return a value from a following row in the window, or a default when there isn't one.	8.4
`FIRST_VALUE()`	Return a value from the first row of the window frame, such as each department's highest salary.	8.4
`LAST_VALUE()`	Return a value from the last row of the window frame. Widen the frame or it returns the current row.	8.4
`NTH_VALUE()`	Return a value from the nth row of the window frame, or NULL if the frame has fewer rows.	8.4

Name

Description

Since

ROW_NUMBER()

Number the rows of each window partition 1, 2, 3, … in ORDER BY order. Ties get different numbers.

8.4

RANK()

Rank rows by ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4).

8.4

DENSE_RANK()

Rank rows by ORDER BY, giving ties the same rank with no gaps after them (1, 2, 2, 3).

8.4

PERCENT_RANK()

Return a row's relative rank from 0 to 1: (rank - 1) / (rows in partition - 1).

8.4

CUME_DIST()

Return the share of rows ordered at or before the current row (including ties), from just above 0 to 1.

8.4

NTILE()

Split the rows of a window into N groups of nearly equal size and return each row's group number.

8.4

LAG()

Return a value from a previous row in the window, or a default when there isn't one. Ideal for comparing with the last period.

8.4

LEAD()

Return a value from a following row in the window, or a default when there isn't one.

8.4

FIRST_VALUE()

Return a value from the first row of the window frame, such as each department's highest salary.

8.4

LAST_VALUE()

Return a value from the last row of the window frame. Widen the frame or it returns the current row.

8.4

NTH_VALUE()

Return a value from the nth row of the window frame, or NULL if the frame has fewer rows.

8.4