Updated on 2022-07-04 GMT+08:00

Window Functions

A window function performs a calculation operation on a set of values related to the current value. A window function can be an aggregate function used in the GROUP BY clause, such as sum, max, min, count, and avg functions. The window functions also include the functions listed in Table 1. A window contains multiple rows defined by an OVER clause. A window function works on one window.

Table 1 Functions

Function

Return Type

Description

first_value(col)

Data type of the argument.

Return the value of the first data record from a column.

last_value(col)

Data type of the argument.

Return the value of the last data record from a column.

lag (col,n,DEFAULT)

Data type of the argument.

Return the value from the nth row preceding the current row. The first argument specifies the column name. The second argument specifies the nth row preceding the current row. The configuration of the second argument is optional, and the default argument value is 1 if the argument is not specified. The third argument is set to a default value. If the nth row preceding the current row is null, the default value is used. The default value of the third argument is NULL if the argument is not specified.

lead (col,n,DEFAULT)

Data type of the argument.

Return the value from the nth row following the current row. The first argument specifies the column name. The second argument specifies the nth row following the current row. The configuration of the second argument is optional, and the default argument value is 1 if the argument is not specified. The third argument is set to a default value. If the nth row following the current row is null, the default value is used. The default value of the third argument is NULL if the argument is not specified.

row_number() over (order by col_1[,col_2 ...])

INT

Assign a unique number to each row.

rank()

INT

Return the rank of a value in a set of values. When multiple values share the same rank, the next rank in the sequence is not consecutive.

cume_dist()

DOUBLE

Calculate the relative position of a value in a row.

percent_rank()

DOUBLE

Return the rank of a value from the column specified by the ORDER BY clause of the window. The return value is a decimal between 0 and 1, which is calculated using (RANK - 1)/(- 1).