9.21. Window函式

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

The built-in window functions are listed inTable 9.57. Note that these functions_must_be invoked using window function syntax, i.e., anOVERclause 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; seeSection 9.20for a list of the built-in aggregates. Aggregate functions act as window functions only when anOVERclause 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

Function	Return Type	Description
row_number()	bigint	number of the current row within its partition, counting from 1
rank()	bigint	rank of the current row with gaps; same asrow_numberof its first peer
dense_rank()	bigint	rank of the current row without gaps; this function counts peer groups
percent_rank()	double precision	relative rank of the current row: (rank- 1) / (total partition rows - 1)
cume_dist()	double precision	cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows
ntile(num_bucketsinteger)	integer	integer ranging from 1 to the argument value, dividing the partition as equally as possible
lag(valueanyelement[,offsetinteger[,defaultanyelement]])	same type asvalue	returnsvalue_evaluated at the row that isoffsetrows before the current row within the partition; if there is no such row, instead returndefault(which must be of the same type asvalue). Bothoffsetanddefaultare evaluated with respect to the current row. If omitted,offsetdefaults to 1 anddefault_to null
lead(valueanyelement[,offsetinteger[,defaultanyelement]])	same type asvalue	returnsvalue_evaluated at the row that isoffsetrows after the current row within the partition; if there is no such row, instead returndefault(which must be of the same type asvalue). Bothoffsetanddefaultare evaluated with respect to the current row. If omitted,offsetdefaults to 1 anddefault_to null
first_value(valueany)	same type asvalue	returns_value_evaluated at the row that is the first row of the window frame
last_value(valueany)	same type asvalue	returns_value_evaluated at the row that is the last row of the window frame
nth_value(valueany,nthinteger)	same type asvalue	returnsvalue_evaluated at the row that is thenth_row of the window frame (counting from 1); null if no such row

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

Note thatfirst_value,last_value, andnth_valueconsider 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_valueand sometimes alsonth_value. You can redefine the frame by adding a suitable frame specification (RANGEorROWS) to theOVERclause. SeeSection 4.2.8for 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 BYand 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 BYor useROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.

Note

The SQL standard defines aRESPECT NULLSorIGNORE NULLSoption forlead,lag,first_value,last_value, andnth_value. This is not implemented inPostgreSQL: the behavior is always the same as the standard's default, namelyRESPECT NULLS. Likewise, the standard'sFROM FIRSTorFROM LASToption fornth_valueis not implemented: only the defaultFROM FIRSTbehavior is supported. (You can achieve the result ofFROM LASTby reversing theORDER BYordering.)

cume_distcomputes the fraction of partition rows that are less than or equal to the current row and its peers, whilepercent_rankcomputes the fraction of partition rows that are less than the current row, assuming the current row does not exist in the partition.