Skip to main content

Window Functions

Functions for analytical operations over data windows that must be used with the WINDOW command.

row_number()​

Returns a sequential row number for each row within a window partition.

Parameters​

This function takes no parameters.

Description​

The row_number() function assigns a unique sequential integer to each row within its window partition, starting from 1. The ordering is determined by the SORT clause in the WINDOW command. Rows with identical sort values receive different row numbers in an arbitrary but consistent order.

rank()​

Returns the rank of each row within a window partition with gaps.

Parameters​

This function takes no parameters.

Description​

The rank() function assigns a rank to each row within its window partition based on the SORT clause ordering. Rows with identical sort values receive the same rank, and subsequent ranks are skipped. For example, if two rows tie for rank 2, the next row receives rank 4 (not rank 3).

dense_rank()​

Returns the rank of each row within a window partition without gaps.

Parameters​

This function takes no parameters.

Description​

The dense_rank() function assigns a rank to each row within its window partition based on the SORT clause ordering. Rows with identical sort values receive the same rank, but subsequent ranks are not skipped. For example, if two rows tie for rank 2, the next row receives rank 3.

lag(expression, offset, ignore_nulls)​

Returns the value of an expression from a previous row within the window.

Parameters​

  • expression - Expression to evaluate from the previous row
  • offset - Integer specifying how many rows back to look
  • ignore_nulls - Boolean indicating whether to skip null values (default: true)

Description​

The lag() function retrieves the value of the specified expression from a row that is offset positions before the current row within the window partition. When ignore_nulls is true, null values are skipped when counting the offset. If there is no row at the specified offset, the function returns null.

lead(expression, offset, ignore_nulls)​

Returns the value of an expression from a subsequent row within the window.

Parameters​

  • expression - Expression to evaluate from the subsequent row
  • offset - Integer specifying how many rows ahead to look
  • ignore_nulls - Boolean indicating whether to skip null values (default: true)

Description​

The lead() function retrieves the value of the specified expression from a row that is offset positions after the current row within the window partition. When ignore_nulls is true, null values are skipped when counting the offset. If there is no row at the specified offset, the function returns null.

first_value(expression, ignore_nulls)​

Returns the first value of an expression within the window frame.

Parameters​

  • expression - Expression to evaluate
  • ignore_nulls - Boolean indicating whether to skip null values (default: true)

Description​

The first_value() function returns the value of the specified expression from the first row in the current window frame. When ignore_nulls is true, it returns the first non-null value. The window frame is determined by the WITHIN clause in the WINDOW command.

last_value(expression, ignore_nulls)​

Returns the last value of an expression within the window frame.

Parameters​

  • expression - Expression to evaluate
  • ignore_nulls - Boolean indicating whether to skip null values (default: true)

Description​

The last_value() function returns the value of the specified expression from the last row in the current window frame. When ignore_nulls is true, it returns the last non-null value. The window frame is determined by the WITHIN clause in the WINDOW command.

nth_value(expression, n, ignore_nulls)​

Returns the nth value of an expression within the window frame.

Parameters​

  • expression - Expression to evaluate
  • n - Integer specifying which value to return (1-based)
  • ignore_nulls - Boolean indicating whether to skip null values (default: true)

Description​

The nth_value() function returns the value of the specified expression from the nth row in the current window frame. When ignore_nulls is true, null values are not counted in the position. If there is no nth row, the function returns null. The position is 1-based, where 1 represents the first row.

cume_dist()​

Returns the cumulative distribution of each row within the window partition.

Parameters​

This function takes no parameters.

Description​

The cume_dist() function calculates the cumulative distribution of each row within its window partition. The result is the number of rows with values less than or equal to the current row's value, divided by the total number of rows in the partition. Values range from 0 to 1.

percent_rank()​

Returns the percentile rank of each row within the window partition.

Parameters​

This function takes no parameters.

Description​

The percent_rank() function calculates the percentile rank of each row within its window partition. The result is calculated as (rank - 1) / (total rows - 1), where rank is determined by the SORT clause ordering. Values range from 0 to 1, with 0 representing the lowest value and 1 representing the highest.