PostgreSQL Window Functions

Window functions provide the ability to perform calculations across sets of rows that are related to
the current query row

General Purpose Window Functions

row_number ()

row_number () → bigint
Returns the number of the current row within its partition, counting from 1

postgres=# select empno, deptno, salary, row_number() over(order by salary) from emp;
 empno | deptno | salary | row_number 
-------+--------+--------+------------
    14 |      1 |   7500 |          1
    15 |      1 |   7600 |          2
    18 |      2 |   7700 |          3
    16 |      2 |   8500 |          4
    13 |      1 |   9000 |          5
    17 |      2 |   9500 |          6
    12 |      5 |  10000 |          7
    11 |      5 |  10000 |          8
    10 |      5 |  12000 |          9
(9 rows)

postgres=# select salary, deptno, row_number() over(partition by deptno order by salary) rn from emp;
 salary | deptno | rn 
--------+--------+----
   7500 |      1 |  1
   7600 |      1 |  2
   9000 |      1 |  3
   7700 |      2 |  1
   8500 |      2 |  2
   9500 |      2 |  3
  10000 |      5 |  1
  10000 |      5 |  2
  12000 |      5 |  3
(9 rows)

rank ()

rank () → bigint
Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group

postgres=# select empno, deptno, salary, rank() over(order by salary) from emp;
 empno | deptno | salary | rank 
-------+--------+--------+------
    14 |      1 |   7500 |    1
    15 |      1 |   7600 |    2
    18 |      2 |   7700 |    3
    16 |      2 |   8500 |    4
    13 |      1 |   9000 |    5
    17 |      2 |   9500 |    6
    12 |      5 |  10000 |    7
    11 |      5 |  10000 |    7
    10 |      5 |  12000 |    9
(9 rows)

postgres=# select empno, deptno, salary, rank() over(partition by deptno order by salary) from emp;
 empno | deptno | salary | rank 
-------+--------+--------+------
    14 |      1 |   7500 |    1
    15 |      1 |   7600 |    2
    13 |      1 |   9000 |    3
    18 |      2 |   7700 |    1
    16 |      2 |   8500 |    2
    17 |      2 |   9500 |    3
    12 |      5 |  10000 |    1
    11 |      5 |  10000 |    1
    10 |      5 |  12000 |    3
(9 rows)

dense_rank()

dense_rank () → bigint
Returns the rank of the current row, without gaps; this function effectively counts peer groups

postgres=# select empno, deptno, salary, dense_rank() over(order by salary) from emp;
 empno | deptno | salary | dense_rank 
-------+--------+--------+------------
    14 |      1 |   7500 |          1
    15 |      1 |   7600 |          2
    18 |      2 |   7700 |          3
    16 |      2 |   8500 |          4
    13 |      1 |   9000 |          5
    17 |      2 |   9500 |          6
    12 |      5 |  10000 |          7
    11 |      5 |  10000 |          7
    10 |      5 |  12000 |          8
(9 rows)


postgres=# select empno, deptno, salary, dense_rank() over(partition by deptno order by salary) from emp;
 empno | deptno | salary | dense_rank 
-------+--------+--------+------------
    14 |      1 |   7500 |          1
    15 |      1 |   7600 |          2
    13 |      1 |   9000 |          3
    18 |      2 |   7700 |          1
    16 |      2 |   8500 |          2
    17 |      2 |   9500 |          3
    12 |      5 |  10000 |          1
    11 |      5 |  10000 |          1
    10 |      5 |  12000 |          2
(9 rows)

percent_rank ()

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

postgres=# select empno, deptno, salary, percent_rank() over(order by salary) from emp;
 empno | deptno | salary | percent_rank 
-------+--------+--------+--------------
    14 |      1 |   7500 |            0
    15 |      1 |   7600 |        0.125
    18 |      2 |   7700 |         0.25
    16 |      2 |   8500 |        0.375
    13 |      1 |   9000 |          0.5
    17 |      2 |   9500 |        0.625
    12 |      5 |  10000 |         0.75
    11 |      5 |  10000 |         0.75
    10 |      5 |  12000 |            1
(9 rows)

cume_dist ()

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

postgres=# select empno, deptno, salary, cume_dist() over(order by salary) from emp;
 empno | deptno | salary |     cume_dist      
-------+--------+--------+--------------------
    14 |      1 |   7500 | 0.1111111111111111
    15 |      1 |   7600 | 0.2222222222222222
    18 |      2 |   7700 | 0.3333333333333333
    16 |      2 |   8500 | 0.4444444444444444
    13 |      1 |   9000 | 0.5555555555555556
    17 |      2 |   9500 | 0.6666666666666666
    12 |      5 |  10000 | 0.8888888888888888
    11 |      5 |  10000 | 0.8888888888888888
    10 |      5 |  12000 |                  1
(9 rows)

ntile()

ntile ( num_buckets integer ) → integer
Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.

postgres=# select empno, deptno, salary, ntile(salary) over(order by deptno) from emp;
 empno | deptno | salary | ntile 
-------+--------+--------+-------
    13 |      1 |   9000 |     1
    15 |      1 |   7600 |     2
    14 |      1 |   7500 |     3
    18 |      2 |   7700 |     4
    16 |      2 |   8500 |     5
    17 |      2 |   9500 |     6
    11 |      5 |  10000 |     7
    12 |      5 |  10000 |     8
    10 |      5 |  12000 |     9
(9 rows)

postgres=# select empno, deptno, salary, ntile(salary) over(partition by deptno order by deptno) from emp;
 empno | deptno | salary | ntile -------+--------+--------+-------
    13 |      1 |   9000 |     1
    15 |      1 |   7600 |     2
    14 |      1 |   7500 |     3
    18 |      2 |   7700 |     1
    16 |      2 |   8500 |     2
    17 |      2 |   9500 |     3
    11 |      5 |  10000 |     1
    12 |      5 |  10000 |     2
    10 |      5 |  12000 |     3
(9 rows)

lag()

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 . Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL

postgres=# select empno, deptno, salary, lag(salary) over(order by deptno) from emp;
 empno | deptno | salary |  lag  
-------+--------+--------+-------
    13 |      1 |   9000 |      
    15 |      1 |   7600 |  9000
    14 |      1 |   7500 |  7600
    18 |      2 |   7700 |  7500
    16 |      2 |   8500 |  7700
    17 |      2 |   9500 |  8500
    11 |      5 |  10000 |  9500
    12 |      5 |  10000 | 10000
    10 |      5 |  12000 | 10000
(9 rows)

postgres=# select empno, deptno, salary, lag(salary) over(partition by deptno order by deptno) from emp;
 empno | deptno | salary |  lag  
-------+--------+--------+-------
    13 |      1 |   9000 |      
    15 |      1 |   7600 |  9000
    14 |      1 |   7500 |  7600
    18 |      2 |   7700 |      
    16 |      2 |   8500 |  7700
    17 |      2 |   9500 |  8500
    11 |      5 |  10000 |      
    12 |      5 |  10000 | 10000
    10 |      5 |  12000 | 10000
(9 rows)

lead()

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. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL

postgres=# select empno, deptno, salary, lead(salary) over(order by deptno) from emp;
 empno | deptno | salary | lead  
-------+--------+--------+-------
    13 |      1 |   9000 |  7600
    15 |      1 |   7600 |  7500
    14 |      1 |   7500 |  7700
    18 |      2 |   7700 |  8500
    16 |      2 |   8500 |  9500
    17 |      2 |   9500 | 10000
    11 |      5 |  10000 | 10000
    12 |      5 |  10000 | 12000
    10 |      5 |  12000 |      
(9 rows)

postgres=# select empno, deptno, salary, lead(salary) over(partition by deptno order by deptno) from emp;
 empno | deptno | salary | lead  
-------+--------+--------+-------
    13 |      1 |   9000 |  7600
    15 |      1 |   7600 |  7500
    14 |      1 |   7500 |      
    18 |      2 |   7700 |  8500
    16 |      2 |   8500 |  9500
    17 |      2 |   9500 |      
    11 |      5 |  10000 | 10000
    12 |      5 |  10000 | 12000
    10 |      5 |  12000 |      
(9 rows)

first_value()

first_value ( value anyelement ) → anyelement
Returns value evaluated at the row that is the first row of the window frame

postgres=# select empno, deptno, salary, first_value(salary) over(order by deptno) from emp;
 empno | deptno | salary | first_value 
-------+--------+--------+-------------
    13 |      1 |   9000 |        9000
    15 |      1 |   7600 |        9000
    14 |      1 |   7500 |        9000
    18 |      2 |   7700 |        9000
    16 |      2 |   8500 |        9000
    17 |      2 |   9500 |        9000
    11 |      5 |  10000 |        9000
    12 |      5 |  10000 |        9000
    10 |      5 |  12000 |        9000
(9 rows)

postgres=# select empno, deptno, salary, first_value(salary) over(partition by deptno order by deptno) from emp;
 empno | deptno | salary | first_value 
-------+--------+--------+-------------
    13 |      1 |   9000 |        9000
    15 |      1 |   7600 |        9000
    14 |      1 |   7500 |        9000
    18 |      2 |   7700 |        7700
    16 |      2 |   8500 |        7700
    17 |      2 |   9500 |        7700
    11 |      5 |  10000 |       10000
    12 |      5 |  10000 |       10000
    10 |      5 |  12000 |       10000
(9 rows)

last_value()

last_value ( value anyelement ) → anyelement
Returns value evaluated at the row that is the last row of the window frame.

postgres=# select empno, deptno, salary, last_value(salary) over(order by deptno) from emp;
 empno | deptno | salary | last_value 
-------+--------+--------+------------
    13 |      1 |   9000 |       7500
    15 |      1 |   7600 |       7500
    14 |      1 |   7500 |       7500
    18 |      2 |   7700 |       9500
    16 |      2 |   8500 |       9500
    17 |      2 |   9500 |       9500
    11 |      5 |  10000 |      12000
    12 |      5 |  10000 |      12000
    10 |      5 |  12000 |      12000
(9 rows)

postgres=# select empno, deptno, salary, last_value(salary) over(partition by deptno order by deptno) from emp;
 empno | deptno | salary | last_value 
-------+--------+--------+------------
    13 |      1 |   9000 |       7500
    15 |      1 |   7600 |       7500
    14 |      1 |   7500 |       7500
    18 |      2 |   7700 |       9500
    16 |      2 |   8500 |       9500
    17 |      2 |   9500 |       9500
    11 |      5 |  10000 |      12000
    12 |      5 |  10000 |      12000
    10 |      5 |  12000 |      12000
(9 rows)

nth_value()

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

postgres=# select empno, deptno, salary, nth_value(salary,2) over(order by deptno) from emp;
 empno | deptno | salary | nth_value 
-------+--------+--------+-----------
    13 |      1 |   9000 |      7600
    15 |      1 |   7600 |      7600
    14 |      1 |   7500 |      7600
    18 |      2 |   7700 |      7600
    16 |      2 |   8500 |      7600
    17 |      2 |   9500 |      7600
    11 |      5 |  10000 |      7600
    12 |      5 |  10000 |      7600
    10 |      5 |  12000 |      7600
(9 rows)

postgres=# select empno, deptno, salary, nth_value(salary,2) over(order by deptno desc ) from emp;
 empno | deptno | salary | nth_value 
-------+--------+--------+-----------
    11 |      5 |  10000 |     12000
    10 |      5 |  12000 |     12000
    12 |      5 |  10000 |     12000
    18 |      2 |   7700 |     12000
    16 |      2 |   8500 |     12000
    17 |      2 |   9500 |     12000
    13 |      1 |   9000 |     12000
    14 |      1 |   7500 |     12000
    15 |      1 |   7600 |     12000
(9 rows)

postgres=# select empno, deptno, salary, nth_value(salary,3) over(order by deptno desc ) from emp;
 empno | deptno | salary | nth_value 
-------+--------+--------+-----------
    11 |      5 |  10000 |     10000
    10 |      5 |  12000 |     10000
    12 |      5 |  10000 |     10000
    18 |      2 |   7700 |     10000
    16 |      2 |   8500 |     10000
    17 |      2 |   9500 |     10000
    13 |      1 |   9000 |     10000
    14 |      1 |   7500 |     10000
    15 |      1 |   7600 |     10000
(9 rows)