Updated on 2023-10-25 GMT+08:00

Overview

Table 1 lists the window functions supported by DLI.

Table 1 Window functions

Function

Syntax

Value Type

Description

cume_dist

cume_dist()

DOUBLE

Returns the cumulative distribution, which is equivalent to calculating the proportion of data in the partition that is greater than or equal to, or less than or equal to, the current row.

first_value

first_value(col)

Data type of the argument

Returns the value of the first data record in a column in a result set.

last_value

last_value(col)

Data type of the argument

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

lag

lag (col,n,DEFAULT)

Data type of the argument

Returns 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

lead (col,n,DEFAULT)

Data type of the argument

Returns 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.

percent_rank

percent_rank()

DOUBLE

Returns 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).

rank

rank()

INT

Returns 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.

row_number

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

INT

Assigns a unique number to each row.