PostgreSQL 8.4.22 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | Next |
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.
The built-in window functions are listed in Table 9-44. Note that these functions must be invoked using window function syntax; that is an OVER clause is required.
In addition to these functions, any built-in or user-defined aggregate function can be used as a window function (see Section 9.18 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 regular aggregates.
Table 9-44. General-Purpose Window Functions
All of the functions listed in Table 9-44 depend on the sort ordering specified by the ORDER BY clause of the associated window definition. Rows that are not distinct in the ORDER BY ordering are said to be peers; the four ranking functions are defined so that they give the same answer for any two 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 nth_value
and
particularly last_value
. You can
redefine the frame as being the whole partition by adding
ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING to the OVER
clause. See Section 4.2.8
for more information.
When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. 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.
Note: The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for
lead
,lag
,first_value
,last_value
, andnth_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 fornth_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.)