Window functions provide the ability to perform calculations across sets of rows that are related to the current query row. See Section 3.5 for an introduction to this feature, and Section 4.2.8 for syntax details.
The built-in window functions are listed in Table 9.57.
Note that these functions must be invoked using window function
syntax, i.e., an
OVER clause is
In addition to these functions, any built-in or user-defined
general-purpose or statistical aggregate (i.e., not ordered-set
or hypothetical-set aggregates) can be used as a window function;
see Section 9.20 for a list
of the built-in aggregates. Aggregate functions act as window
functions only when an
follows the call; otherwise they act as non-window aggregates and
return a single row for the entire set.
Table 9.57. General-Purpose Window Functions
||number of the current row within its partition, counting from 1|
||rank of the current row with gaps; same as
||rank of the current row without gaps; this function counts peer groups|
||relative rank of the current row: (
||cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows|
||integer ranging from 1 to the argument value, dividing the partition as equally as possible|
All of the functions listed in Table 9.57
depend on the sort ordering specified by the
ORDER BY clause of the associated window
definition. Rows that are not distinct when considering only the
ORDER BY columns are said to be
peers. The four ranking functions
cume_dist) are defined
so that they give the same answer for all peer rows.
nth_value consider only the rows within the
frame”, which by default contains the rows from the
start of the partition through the last peer of the current row.
This is likely to give unhelpful results for
last_value and sometimes also
nth_value. You can redefine the frame by adding
a suitable frame specification (
GROUPS) to the
OVER clause. See Section 4.2.8 for
more information about frame specifications.
When an aggregate function is used as a window function, it
aggregates over the rows within the current row's window frame.
An aggregate used with
ORDER BY and
the default window frame definition produces a “running sum” type of
behavior, which may or may not be what's wanted. To obtain
aggregation over the whole partition, omit
ORDER BY or use
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other
frame specifications can be used to obtain other effects.
The SQL standard defines a
nth_value. This is not implemented in
PostgreSQL: the behavior is
always the same as the standard's default, namely
RESPECT NULLS. Likewise, the standard's
FROM FIRST or
FROM LAST option for
nth_value is not implemented: only the
FROM FIRST behavior is
supported. (You can achieve the result of
FROM LAST by reversing the
ORDER BY ordering.)
cume_dist computes the fraction
of partition rows that are less than or equal to the current row
and its peers, while
computes the fraction of partition rows that are less than the
current row, assuming the current row does not exist in the
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.