Window functions return a value for each row they operate on in a window. A window is defined using the
OVER() clause can take the following optional arguments:
PARTITION BY: Defines multiple window partitions.
ORDER BY: Orders rows within each partition.
window_function (expression) OVER ( [ PARTITION BY expressionlist ] [ ORDER BY fieldlist ] )
OVER() clause can be used with regular aggregate functions such as:
select product_id, branch, amount, SUM(amount) OVER (partition by branch order by amount DESC) as total_branch_amount from transactions
OVER() clause can be used with the functions below:
|CUME_DIST()||Double||Calculates the cumulative distribution of the current row within the window partition.|
|DENSE_RANK()||BIGINT||Returns the rank of the current row within its partition and ordering. Rows that are equal have the same rank.|
|LAG()||Same as input||Returns the row before the current one in a partition. If there are no rows, returns null.|
|LEAD()||Same as input||Returns the row after the current one in a partition. If there are no rows, returns null.|
|NTILE([integer] ntile)||Integer||NTILE function equally splits the rows in each partition into N ranked parts. Has to be used with an
|PERCENT_RANK()||Double||Returns the percent rank of the current row in the partition based on the order by clause.|
|RANK()||BIGINT||Returns the rank of the current row within its partition and ordering. Rows that are equal have the same rank. However, the count of tied rows is added to the next rank, instead of being incremented by just one.|
|ROW_NUMBER()||BIGINT||Returns the row number for the current row based on the order by clause within each partition.|