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.60. Note that these functions must be invoked using window function syntax, i.e., an OVER
clause is required.
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 OVER
clause follows the call; otherwise they act as non-window aggregates and return a single row for the entire set.
Table 9.60. General-Purpose Window Functions
All of the functions listed in Table 9.60 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 (including cume_dist
) are defined so that they give the same answer for all peer rows.
Note that first_value
, last_value
, and nth_value
consider only the rows within the “window 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 (RANGE
, ROWS
or 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 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
. Other frame specifications can be used to obtain other effects.
The SQL standard defines a RESPECT NULLS
or IGNORE NULLS
option for lead
, lag
, first_value
, last_value
, and 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 default 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 percent_rank
computes the fraction of partition rows that are less than the current row, assuming the current row does not exist in the partition.
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.