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.
Syntax
row_number() → integer
Example
SELECT
    row_number() OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as r,
    col1, col2
FROM t ORDER BY col1, col2;
----
 r | col1 | col2
---+------+------
 1 |    a |  100
 2 |    a |  105
 1 |    b |  103
 2 |    b |  107
 3 |    b |  107
 4 |    b |  109
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.
Syntax
rank() → integer
Example
SELECT
    rank() OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as r,
    col1, col2
FROM t ORDER BY col1, col2;
----
 r | col1 | col2
---+------+------
 1 |    a |  100
 2 |    a |  105
 1 |    b |  103
 2 |    b |  107
 2 |    b |  107
 4 |    b |  109

​
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.
Syntax
dense_rank() → integer
Example
SELECT
    dense_rank() OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as r,
    col1, col2
FROM t ORDER BY col1, col2;
----
 r | col1 | col2
---+------+------
 1 |    a |  100
 2 |    a |  105
 1 |    b |  103
 2 |    b |  107
 2 |    b |  107
 3 |    b |  109

​
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.
Syntax
lag ( value anycompatible [, offset const integer] ) → anycompatible
Example
SELECT
    col1, col2,
    lag(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as lag
FROM t ORDER BY col1, col2;
----
 col1 | col2 |  lag
------+------|------
    a |  100 | NULL
    a |  105 |  100
    b |  103 | NULL
    b |  107 |  103
    b |  107 |  107
    b |  109 |  107
lead() is similar to lag(), but it allows you to access the value of a subsequent row in the result set.
Syntax
lead ( value anycompatible [, offset const integer] ) → anycompatible
Example
SELECT
    col1, col2,
    lead(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as lead
FROM t ORDER BY col1, col2;
----
 col1 | col2 | lead
------+------|------
    a |  100 |  105
    a |  105 | NULL
    b |  103 |  107
    b |  107 |  107
    b |  107 |  109
    b |  109 | NULL

​
first_value() and last_value()

Added in v2.3.0: Support IGNORE NULLS.
The first_value() function returns the value of the first row in the current window frame. If IGNORE NULLS is present, first_value() returns the first non-null value.
Syntax
first_value ( value anyelement [ IGNORE NULLS ] ) → anyelement
Example
SELECT
    col1, col2,
    first_value(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as first
FROM t ORDER BY col1, col2;
----
 col1 | col2 | first
------+------|-------
    a |  100 |   100
    a |  105 |   100
    b |  103 |   103
    b |  107 |   103
    b |  107 |   103
    b |  109 |   103
last_value() returns the value of the last row in the current window frame. If IGNORE NULLS is present, last_value() returns the last non-null value.
Syntax
last_value ( value anyelement [ IGNORE NULLS ] ) → anyelement
Example
SELECT
    col1, col2,
    last_value(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
        -- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as last
FROM t ORDER BY col1, col2;
----
 col1 | col2 | last
------+------|------
    a |  100 |  100
    a |  105 |  105
    b |  103 |  103
    b |  107 |  107
    b |  107 |  107
    b |  109 |  109
Example
SELECT
    col1, col2,
    last_value(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last
FROM t ORDER BY col1, col2;
----
 col1 | col2 | last
------+------|------
    a |  100 |  105
    a |  105 |  105
    b |  103 |  109
    b |  107 |  109
    b |  107 |  109
    b |  109 |  109

​
Named window

Added in v2.5.0.
When multiple window functions share the same partitioning and ordering logic, repeating the same OVER clause can be error-prone. To avoid redundancy, you can define a named window in the WINDOW clause of a query and reference it by name in window function calls.
Syntax
WINDOW window_name AS ...
Example
-- Without named window
SELECT
  sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC),
  avg(salary) OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

-- With named window
SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
The WINDOW keyword is now a reserved keyword and cannot be directly used as table alias.

​
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.