Skip to main content

Window

Function NameDescription
AVGComputes the average of a set of values.
COUNTReturns the total number of records for the specified expression.
COVAR_POPReturns the population covariance for non-NULL pairs across all input values.
COVAR_SAMPReturns the sample covariance for non-NULL pairs across all input values.
CUME_DISTReturns the cumulative distribution of the current row with regard to other values within the same window partition.
DENSE_RANKReturns the rank of the current row within its partition and ordering. Rows that are equal will have the same rank.
FIRST_VALUEReturns the first value within an ordered group of a result set.
LAGReturns the row before the current one in a partition based on the ORDER BY clause without the need for a self-join. If there are no rows, this function returns NULL.
LEADReturns the row after the current one in the same result set without the need for a self-join. If there are no rows, this function returns NULL.
MAXReturns the maximum value among the non-NULL input expressions.
MINReturns the minimum value among the non-NULL input expressions.
NDVReturns an approximate distinct value number, similar to COUNT(DISTINCT col). NDV can return results faster than using the combination of COUNT and DISTINCT while using a constant amount of memory, resulting in less memory usage for columns with high cardinality.
NTILEEqually splits the rows in each partition into ranked parts specified by the integer value and starting from 1. This function requires the ORDER BY clause.
PERCENT_RANKReturns the relative rank of the current row in the partition based on the ORDER BY clause. The displayed percentage ranges from 0.0 to 1.0.
RANKReturns the rank of the current row within its partition and placement order. 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 one. The rank value starts at 1 and increases sequentially.
ROW_NUMBERReturns the row number for the current row based on the ORDER BY clause within each partition.
STDDEV_POPReturns the population standard deviation (square root of variance) of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STDDEV_SAMPReturns the sample standard deviation (square root of sample variance) of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
SUMReturns the sum of non-NULL input expressions.
VAR_POPReturns the population variance of non-NULL records.
VAR_SAMPReturns the sample variance of non-NULL records.

Window Functions

A window function performs a calculation across a set of table rows that has some relationship to the current row. This is comparable to how an aggregate function can run a calculation. The difference is that a window function does not group rows into a single output row. With a window function, the rows retain their separate identities.

For more information about window functions, see SQL Window Functions.

Syntax

Window function queries use the OVER() clause directly following the window function name and argument. The OVER() clause may use the following optional arguments:

  • PARTITION BY: A subclause that groups rows into partitions. You can specify a single expression or a comma-separated list of expressions, such as PARTITION BY column1, column3, column4.
  • ORDER BY: A subclause that specifies the order of the rows within each partition of the result set. Required if using a cumulative or sliding window frame.
  • cumulativeFrame or slidingFrame: A window frame subclause that allows the function to perform a calculation across a set of rows that has some relationship to the current row. The query does not group rows into a single output row; instead, the rows retain their separate identities. Window frames may be cumulative or sliding.
Window function syntax
window_function (expression) OVER (
[ PARTITION BY expressionlist ]
[ ORDER BY fieldlist ]
[ cumulativeFrame | slidingFrame ] )
Cumulative frame syntax
  { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
Sliding frame syntax
  ROWS BETWEEN $N { PRECEDING | FOLLOWING } AND $N { PRECEDING | FOLLOWING }
| ROWS BETWEEN UNBOUNDED PRECEDING AND $N { PRECEDING | FOLLOWING }
| ROWS BETWEEN $N { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING

Examples

The following examples use the sample table transactions and the SUM function to demonstrate window function queries with no frame, a cumulative frame, and a sliding frame. Refer to each window function's page for function-specific examples.

Sample Table transactions

product_idbranchamount
Product1A30.0
Product1B3.0
Product3B45.0
Product2A24.0
Product2B10.0
Product3A2.0
Window function example with no frame
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.0, 30.0
-- Product2, A, 24.0, 54.0
-- Product3, A, 2.0, 56.0
-- Product3, B, 45.0, 45.0
-- Product2, B, 10.0, 55.0
-- Product1, B, 3.0, 58.0
Window function example with cumulative frame
SELECT 
product_id,
branch,
amount,
SUM(amount) OVER (PARTITION BY branch ORDER BY amount DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS total_branch_amount
FROM transactions
-- product_id, branch, amount, total_branch_amount
-- Product1, A, 30.0, 56.0
-- Product2, A, 24.0, 26.0
-- Product3, A, 2.0, 2.0
-- Product3, B, 45.0, 58.0
-- Product2, B, 10.0, 13.0
-- Product1, B, 3.0, 3.0
Window function example with sliding frame
SELECT 
product_id,
branch,
amount,
SUM(amount) OVER (PARTITION BY branch ORDER BY amount DESC ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS total_branch_amount
FROM transactions
-- product_id, branch, amount, total_branch_amount
-- Product1, A, 30.0, 56.0
-- Product2, A, 24.0, 56.0
-- Product3, A, 2.0, 26.0
-- Product3, B, 45.0, 58.0
-- Product2, B, 10.0, 58.0
-- Product1, B, 3.0, 13.0