Movatterモバイル変換

9.22. Window Functions
Prev	Up	Chapter 9. Functions and Operators	Home	Next

9.22. Window Functions#

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row. SeeSection 3.5 for an introduction to this feature, andSection 4.2.8 for syntax details.

The built-in window functions are listed inTable 9.65. Note that these functionsmust be invoked using window function syntax, i.e., anOVER clause is required.

In addition to these functions, any built-in or user-defined ordinary aggregate (i.e., not ordered-set or hypothetical-set aggregates) can be used as a window function; seeSection 9.21 for a list of the built-in aggregates. Aggregate functions act as window functions only when anOVER clause follows the call; otherwise they act as plain aggregates and return a single row for the entire set.

Table 9.65. General-Purpose Window Functions

Function Description
`row_number` () →`bigint` Returns the number of the current row within its partition, counting from 1.
`rank` () →`bigint` Returns the rank of the current row, with gaps; that is, the`row_number` of the first row in its peer group.
`dense_rank` () →`bigint` Returns the rank of the current row, without gaps; this function effectively counts peer groups.
`percent_rank` () →`double precision` Returns the relative rank of the current row, that is (`rank` - 1) / (total partition rows - 1). The value thus ranges from 0 to 1 inclusive.
`cume_dist` () →`double precision` Returns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows). The value thus ranges from 1/`N` to 1.
`ntile` (`num_buckets``integer` ) →`integer` Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.
`lag` (`value``anycompatible` [,`offset``integer` [,`default``anycompatible`]] ) →`anycompatible` Returns`value` evaluated at the row that is`offset` rows before the current row within the partition; if there is no such row, instead returns`default` (which must be of a type compatible with`value`). Both`offset` and`default` are evaluated with respect to the current row. If omitted,`offset` defaults to 1 and`default` to`NULL`.
`lead` (`value``anycompatible` [,`offset``integer` [,`default``anycompatible`]] ) →`anycompatible` Returns`value` evaluated at the row that is`offset` rows after the current row within the partition; if there is no such row, instead returns`default` (which must be of a type compatible with`value`). Both`offset` and`default` are evaluated with respect to the current row. If omitted,`offset` defaults to 1 and`default` to`NULL`.
`first_value` (`value``anyelement` ) →`anyelement` Returns`value` evaluated at the row that is the first row of the window frame.
`last_value` (`value``anyelement` ) →`anyelement` Returns`value` evaluated at the row that is the last row of the window frame.
`nth_value` (`value``anyelement`,`n``integer` ) →`anyelement` Returns`value` evaluated at the row that is the`n`'th row of the window frame (counting from 1); returns`NULL` if there is no such row.

Function

Description

row_number () →bigint

Returns the number of the current row within its partition, counting from 1.

rank () →bigint

Returns the rank of the current row, with gaps; that is, therow_number of the first row in its peer group.

dense_rank () →bigint

Returns the rank of the current row, without gaps; this function effectively counts peer groups.

percent_rank () →double precision

Returns the relative rank of the current row, that is (rank - 1) / (total partition rows - 1). The value thus ranges from 0 to 1 inclusive.

cume_dist () →double precision

Returns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows). The value thus ranges from 1/N to 1.

ntile (num_bucketsinteger ) →integer

Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.

lag (valueanycompatible [,offsetinteger [,defaultanycompatible]] ) →anycompatible

Returnsvalue evaluated at the row that isoffset rows before the current row within the partition; if there is no such row, instead returnsdefault (which must be of a type compatible withvalue). Bothoffset anddefault are evaluated with respect to the current row. If omitted,offset defaults to 1 anddefault toNULL.

lead (valueanycompatible [,offsetinteger [,defaultanycompatible]] ) →anycompatible

Returnsvalue evaluated at the row that isoffset rows after the current row within the partition; if there is no such row, instead returnsdefault (which must be of a type compatible withvalue). Bothoffset anddefault are evaluated with respect to the current row. If omitted,offset defaults to 1 anddefault toNULL.

first_value (valueanyelement ) →anyelement

Returnsvalue evaluated at the row that is the first row of the window frame.

last_value (valueanyelement ) →anyelement

Returnsvalue evaluated at the row that is the last row of the window frame.

nth_value (valueanyelement,ninteger ) →anyelement

Returnsvalue evaluated at the row that is then'th row of the window frame (counting from 1); returnsNULL if there is no such row.

All of the functions listed inTable 9.65 depend on the sort ordering specified by theORDER BY clause of the associated window definition. Rows that are not distinct when considering only theORDER BY columns are said to bepeers. The four ranking functions (includingcume_dist) are defined so that they give the same answer for all rows of a peer group.

Note thatfirst_value,last_value, andnth_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 forlast_value and sometimes alsonth_value. You can redefine the frame by adding a suitable frame specification (RANGE,ROWS orGROUPS) to theOVER clause. SeeSection 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 withORDER 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, omitORDER BY or useROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.

Note

The SQL standard defines aRESPECT NULLS orIGNORE NULLS option forlead,lag,first_value,last_value, andnth_value. This is not implemented inPostgres Pro: the behavior is always the same as the standard's default, namelyRESPECT NULLS. Likewise, the standard'sFROM FIRST orFROM LAST option fornth_value is not implemented: only the defaultFROM FIRST behavior is supported. (You can achieve the result ofFROM LAST by reversing theORDER BY ordering.)

Prev	Up	Next
9.21. Aggregate Functions	Home	9.23. Merge Support Functions

pdf epub