Window Functions

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.

Syntax

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

Aggregate Functions

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

OVER() clause can also be used with specific window functions.

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

results matching ""

    No results matching ""