Window Functions

Window functions return a value for each row they operate on in a window. A window is defined using the OVER() clause.

The OVER() clause can take the following optional arguments:

  • PARTITION BY: Defines multiple window partitions.
  • ORDER BY: Orders rows within each partition.

Syntax

window_function (expression) OVER (
   [ PARTITION BY expressionlist ]
   [ ORDER BY fieldlist ] ) 

Aggregate Functions

The OVER() clause can be used with regular aggregate functions such as:

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM

Example

select 
   product_id, 
   branch, 
   amount, 
   SUM(amount) OVER (partition by branch order by amount DESC) as total_branch_amount
from transactions
product_id branch amount total_branch_amount
Product1 A 30 56
Product2 A 24 56
Product3 A 2 56
Product3 B 45 58
Product2 B 10 58
Product1 B 3 58

Analytic Functions

The OVER() clause can be used with the functions below:

Function Return Type Description
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 ORDER BY clause.
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.