Overview¶
Table 1 lists the window functions supported by DLI.
Syntax | Value Type | Description |
|---|---|---|
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(col) | Data type of the argument | Returns the value of the first data record in a column in a result set. |
last_value(col) | Data type of the argument | Returns the value of the last data record from a column. |
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 (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() | 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() | 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() over (order by col_1[,col_2 ...]) | INT | Assigns a unique number to each row. |