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