### Get Postgres Tips and Tricks

Subscribe to get advanced Postgres how-tos.

PostgreSQL 9.6.20 Documentation | |||
---|---|---|---|

Prev | Up | Chapter 9. Functions and Operators | Next |

# 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-56. 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 normal
aggregate function (but 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 regular aggregates.

**Table 9-56. 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 as `row_number` of 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 rows - 1) |

`cume_dist()`
| double precision
| relative rank of the current row: (number of rows preceding or peer with current row) / (total rows) |

`ntile(`
| integer
| integer ranging from 1 to the argument value, dividing the partition as equally as possible |

` lag(`
| same type as
value | returns evaluated at
the row that is value
rows before the current row within the partition; if there is no such
row, instead return offset
(which must be of the same type as
default).
Both value and
offset are evaluated
with respect to the current row. If omitted,
default defaults to 1 and
offset to null
default |

` lead(`
| same type as
value | returns evaluated at
the row that is value
rows after the current row within the partition; if there is no such
row, instead return offset
(which must be of the same type as
default).
Both value and
offset are evaluated
with respect to the current row. If omitted,
default defaults to 1 and
offset to null
default |

`first_value(`
| same type as
value | returns evaluated
at the row that is the first row of the window frame
value |

`last_value(`
| same type as
value | returns evaluated
at the row that is the last row of the window frame
value |

` nth_value(`
| same type as
value | returns evaluated
at the row that is the value
row of the window frame (counting from 1); null if no such row
nth |

All of the functions listed in
Table 9-56 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 `last_value`

and
sometimes also `nth_value`

. You can redefine the frame by
adding a suitable frame specification (`RANGE` or
`ROWS`) 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.

Note:The SQL standard defines aRESPECT NULLSorIGNORE NULLSoption 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, namelyRESPECT NULLS. Likewise, the standard'sFROM FIRSTorFROM LASToption for`nth_value`

is not implemented: only the defaultFROM FIRSTbehavior is supported. (You can achieve the result ofFROM LASTby reversing theORDER BYordering.)