Window Functions

This page covers functions that can be used in window queries.

Window functions are similar to aggregate functions, except that they return one row per input row, instead of aggregating all of the rows into a single result. Window functions operate over partititons in the input data, specified by an OVER() clause. Within a partition, window frames can be defined, and the window function can be applied in a given order to these window frames.

Window function calls are permitted only in the SELECT list and the ORDER BY clause of the query.

WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT ROW_NUMBER() OVER()
  FROM numbers

+--------------+ | row_number | |--------------+ | 1 | | 2 | | 3 | | 4 | +--------------+

List of functions defined in this section:

Function	Description
`CUME_DIST()`	Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).
`DENSE_RANK()`	Rank of the current row without gaps; this function counts peer groups.
`FIRST_VALUE(value)`	Returns value evaluated at the row that is the first row of the window frame.
`LAG(value [, offset [, default ]])`	Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.
`LAST_VALUE(value)`	Returns value evaluated at the row that is the last row of the window frame.
`LEAD(value [, offset [, default ]])`	Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.
`NTH_VALUE(value, nth)`	Returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.
`NTILE(num_buckets)`	Integer ranging from 1 to the argument value, dividing the partition as equally as possible.
`PERCENT_RANK()`	Relative rank of the current row: (rank - 1) / (total rows - 1).
`RANK()`	Rank of the current row with gaps; same as row_number of its first peer.
`ROW_NUMBER()`	Number of the current row within its partition, counting from 1.