PostgreSQL Window Functions

PostgreSQL window functions provide computing power across the group associated with the current row. Window functions are different from aggregate functions, which perform calculations on each group and return a row for each group, window functions do not output each group to a row and combine the calculation results of each group into each row in this group.

All window functions rely on OVER clauses, which can be used to group all rows and sort rows within groups. The calling syntax of the window function is as follows:

window_function(args)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

In this Syntax:

  • The window_function(args) is the window function that needs to be called. In addition to specific window functions, you can also use aggregate functions here.
  • The PARTITION BY specifies the column name that needs to be partitioned. It is optional. Defaults to one partition for the entire result set.
  • The ORDER BY specifies the sort column name. It is optional.

PostgreSQL provides many window functions that can be used to obtain information such as rank, row number, cumulative distribution, bucket rank, etc.

  1. cume_dist

    The PostgreSQL cume_dist() function returns the cumulative distribution of the current row.

  2. dense_rank

    The PostgreSQL dense_rank() function returns the rank within the partition in which the current row is located, starting at 1, with no gap.

  3. first_value

    The PostgreSQL first_value() function returns the evaluated value from the first row of the window frame associated with the current row.

  4. lag

    The PostgreSQL lag() function returns the value from the specified row before the current row in the partition where the current row is located.

  5. last_value

    The PostgreSQL last_value() function returns the evaluated value from the last row of the window frame associated with the current row.

  6. lead

    The PostgreSQL lead() function returns the value from the specified row after the current row in the partition where the current row is located.

  7. nth_value

    The PostgreSQL nth_value() function returns the evaluated value from the specified row of the window frame associated with the current row.

  8. ntile

    The PostgreSQL ntile() function divides all rows in the partition into the specified number of buckets as evenly as possible, and returns the rank of the bucket where the current row is located.

  9. percent_rank

    The PostgreSQL percent_rank() function returns the relative rank within the partition where the current row is located, that is (rank() - 1) / (number of rows partition - 1).

  10. rank

    The PostgreSQL rank() function returns the rank within the partition in which the current row is located, starting at 1, with gaps.

  11. row_number

    The PostgreSQL row_number() function returns the row number of the current row within the partition where the current row is located, starting from 1.