Window functions return a value for each row they operate on. A window is defined using the
OVER() clause. Window functions use the rows defined in this window when returning results.
OVER() clause can take the following optional arguments:
PARTITION BY: To define multiple window partitions.
ORDER BY: To order 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 also be used with specific window functions.
|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 it’s partition and ordering. Rows that are equal will 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 order by clause.|
|PERCENT_RANK()||Double||Returns the percent rankf of the current row in the partition based on the order by clause.|
|RANK()||BIGINT||Returns the rank of the current row within it’s partition and ordering. Rows that are equal will have the same rank. However, the count of tied rows will be added to the next rank, instead of incrementing by just one.|
|ROW_NUMBER()||BIGINT||Returns the row number for the current row based on the order by clause within each partition.|