9.21. Window Functions
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
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
defined so that they give the same answer for all peer rows.
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
nth_value. You can redefine the frame by
adding a suitable frame specification (
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
nth_value. This is not implemented in
PostgreSQL: the behavior is always the
same as the standard's default, namely
Likewise, the standard's
FROM FIRST or
nth_value is not implemented: only the
FROM FIRST behavior is supported. (You can achieve
the result of
FROM LAST by reversing the
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.