Window functions

Window functions compute a single result for each row over a set of rows that are related to the current row (the “window”).

For details about the syntax of window function calls, see Window function calls.

General-purpose window functions

row_number()

The row_number() function assigns a unique sequential integer to each row within a partition of a result set. The numbering starts at 1 for the first row in each partition and increments by 1 for each subsequent row.

row_number() can be used to turn non-unique rows into unique rows. This could be used to eliminate duplicate rows.

The syntax of row_number() is:

row_number() → integer

NOTE

We recommend using row_number() only for top-N pattern queries. For details about this pattern, see Top-N by group.

rank()

rank() returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.

The syntax of rank() is:

rank() → integer

dense_rank()

dense_rank() returns the rank of the current row, without gaps; that is, if some rows share the same rank, the row next to them is assigned the next consecutive rank.

The syntax of dense_rank() is:

dense_rank() → integer

lag() and lead()

lag() allows you to access the value of a previous row in the result set. You can specify the number of rows to look back.

The syntax of lag() is:

lag ( value anycompatible [, offset const integer] ) → anycompatible

lead() is similar to lag(), but it allows you to access the value of a subsequent row in the result set.

The syntax of lead() is:

lead ( value anycompatible [, offset const integer] ) → anycompatible

first_value() and last_value()

The first_value() function returns the value of the first row in the current window frame.

The syntax of first_value() is:

first_value ( value anyelement ) → anyelement

last_value() returns the value of the last row in the current window frame.

The syntax of last_value() is:

last_value ( value anyelement ) → anyelement

Aggregate window functions

All aggregate functions, including builtin ones such as sum() and min(), user-defined ones and AGGREGATE:-prefixed scalar functions, can be used as window functions.

For the complete list of builtin aggregate functions and their usage, see Aggregate functions.