Window functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. They are often used for ranking, aggregating, and calculating running totals.

functions

dense_rank

Ranks the current row within its partition without gaps. In other words, if the value of any new row encountered is equal to the value of one of the previous rows then it will receive the next successive rank without any gaps in ranking.

The rank function provides the same behaviour, but with gaps in ranking.

Syntax

Alias: denseRank (case-sensitive)

dense_rank (column_name)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

For more detail on window function syntax see: Window Functions - Syntax.

Returned value

  • A number for the current row within its partition, without gaps in ranking. UInt64.

first_value

Returns the first value evaluated within its ordered frame. By default, NULL arguments are skipped, however the RESPECT NULLS modifier can be used to override this behaviour.

Syntax

first_value (column_name) [[RESPECT NULLS] | [IGNORE NULLS]]
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([PARTITION BY grouping_column] [ORDER BY sorting_column])

Alias: any.

Using the optional modifier RESPECT NULLS after first_value(column_name) will ensure that NULL arguments are not skipped.

Alias: firstValueRespectNulls

For more detail on window function syntax see: Window Functions - Syntax.

Returned value

  • The first value evaluated within its ordered frame.

lagInFrame

Returns a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame.

Syntax

lagInFrame(x[, offset[, default]])
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

For more detail on window function syntax see: Window Functions - Syntax.

### Parameters

  • x: Column name.
  • offset: Offset to apply. (U)Int*. (Optional - 1 by default).
  • default: Value to return if calculated row exceeds the boundaries of the window frame. (Optional - default value of column type when omitted).

Returned value

  • Value evaluated at the row that is at a specified physical offset before the current row within the ordered frame.

last_value

Returns the last value evaluated within its ordered frame. By default, NULL arguments are skipped, however the RESPECT NULLS modifier can be used to override this behaviour.

Syntax

last_value (column_name) [[RESPECT NULLS] | [IGNORE NULLS]]
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

Alias: anyLast.

Using the optional modifier RESPECT NULLS after first_value(column_name) will ensure that NULL arguments are not skipped.

Alias: lastValueRespectNulls

For more detail on window function syntax see: Window Functions - Syntax.

Returned value

  • The last value evaluated within its ordered frame.

leadInFrame

Returns a value evaluated at the row that is offset rows after the current row within the ordered frame.

Syntax

leadInFrame(x[, offset[, default]])
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

For more detail on window function syntax see: Window Functions - Syntax.

### Parameters

  • x: Column name.
  • offset: Offset to apply. (U)Int*. (Optional - 1 by default).
  • default: Value to return if calculated row exceeds the boundaries of the window frame. (Optional - default value of column type when omitted).

Returned value

  • value evaluated at the row that is offset rows after the current row within the ordered frame.

nth_value

Returns the first non-NULL value evaluated against the nth row (offset) in its ordered frame.

Syntax

nth_value (x, offset)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

For more detail on window function syntax see: Window Functions - Syntax.

### Parameters

  • x: Column name.
  • offset: nth row to evaluate current row against.

Returned value

  • The first non-NULL value evaluated against the nth row (offset) in its ordered frame.

percent_rank

returns the relative rank (i.e. percentile) of rows within a window partition.

Syntax

Alias: percentRank (case-sensitive)

percent_rank (column_name)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING]] | [window_name])
FROM table_name
WINDOW window_name as ([PARTITION BY grouping_column] [ORDER BY sorting_column] RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

The default and required window frame definition is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

For more detail on window function syntax see: Window Functions - Syntax.

rank

Ranks the current row within its partition with gaps. In other words, if the value of any row it encounters is equal to the value of a previous row then it will receive the same rank as that previous row. The rank of the next row is then equal to the rank of the previous row plus a gap equal to the number of times the previous rank was given.

The dense_rank function provides the same behaviour but without gaps in ranking.

Syntax

rank (column_name)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

For more detail on window function syntax see: Window Functions - Syntax.

Returned value

  • A number for the current row within its partition, including gaps. UInt64.

row_number

Numbers the current row within its partition starting from 1.

Syntax

row_number (column_name)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([[PARTITION BY grouping_column] [ORDER BY sorting_column])

For more detail on window function syntax see: Window Functions - Syntax.

Returned value

  • A number for the current row within its partition. UInt64.
Was this page helpful?
Updated