Window Aggregate Functions

Window functions operate on a set of rows and return a single value for each row from the underlying query. Like aggregate functions, window functions operate on a set of rows, but they do not reduce the number of rows returned by the query.

Unlike regular aggregate functions, window functions do not group rows into a single output row. Instead, the rows retain their separate identities, and the window function produces aggregate calculations that access more than just the current row in the query result.

The term window describes the set of rows on which the window function operates. A window function returns a value from the rows in a window.

The database uses the ORDER BY and PARTITION BY clauses to define the scope of the window.

Syntax

Window Aggregate functions follow this syntax with window_function representing any of the functions listed in Supported Window Aggregate Functions.

window_function( arg1, arg2 [,...] [ IGNORE NULLS ] )
   OVER (
      [ PARTITION BY part_list ]
      [ ORDER BY order_list [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]
      [ <ROWS_or_RANGE_clause> ] )
<ROWS_or_RANGE_clause> ::=
{ ROWS | RANGE }
   { <window_frame_preceding>
   | BETWEEN { <window_frame_preceding> | <window_frame_following> }
   AND { <window_frame_preceding> | <window_frame_following> }
     }
<window_frame_preceding> ::=
{ UNBOUNDED PRECEDING | row_integer PRECEDING | CURRENT ROW }
<window_frame_following> ::=
{ UNBOUNDED FOLLOWING | row_integer FOLLOWING | CURRENT ROW }

Window Aggregate Keywords

Window aggregate functions use these clauses and keywords to designate the parameters for the query and output.

PARTITION BY

This clause defines a set of rows within a result set. PARTITION BY operates similarly to a GROUP BY clause, except it does not reduce the number of rows in the result set.

ORDER BY

Sorts the result set in ascending or descending order based on one or more specified columns.

An ORDER BY clause must be included on all window functions to ensure your query results are deterministic. Some window functions require an ORDER BY clause. See the syntax for each function for specifics. For details, see ORDER BY.

ASC or DESC

Specifies whether the values in the specified column should be sorted in ASC ascending or DESC descending order. ASC is the default sort order.

NULLS FIRST, NULLS LAST, or IGNORE NULLS

Specifies if the database sorts NULL rows first NULLS FIRST, last NULLS LAST, or whether the database ignores NULLs IGNORE NULLS. The default is NULLS LAST.

ROWS or RANGE

Limit the rows within the partition by specifying start and end points within the partition. Specify a range of rows with respect to the current row by logical association using RANGE or physical association using ROWS.

The RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. The system defines preceding or following rows based on the ordering in the ORDER BY clause. If you do not specify this clause, the default is ascending order.

The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row.

Specifying a window frame by the RANGE keyword with the current row CURRENT ROW keyword includes all rows with the same values as the current row in the ORDER BY clause.

ROWS and RANGE clauses can include these options.

Supported Window Aggregate Functions

These functions are supported for window frames. Some examples use the sys.dummy virtual table to populate data. For details, see Generate Tables Using sys.dummy.

CUME_DIST 

Returns a positive value less than one that represents a percentage of values that are less than or equal to the current value in the group. 

Syntax

CUME_DIST ()
   OVER ( [ PARTITION BY part_list ] ORDER BY order_list )

Example

This example shows the cumulative distribution values for a table with generated numbers from one to 10.

SELECT x, CUME_DIST()
    OVER (ORDER BY x) AS d
    FROM (SELECT c1 AS x FROM sys.dummy10);

Output

| "x" | "d" |
|-----|-----|
|   1 | 0.1 |
|   2 | 0.2 |
|   3 | 0.3 |
|   4 | 0.4 |
|   5 | 0.5 |
|   6 | 0.6 |
|   7 | 0.7 |
|   8 | 0.8 |
|   9 | 0.9 |
|  10 | 1.0 |

DELTA

Computes the finite difference between successive values of expression under the specified ordering. This is a backward difference, meaning the delta value for a specified row is the difference between the value of that row and the previous row. 

The function requires two rows of input data to calculate a first-degree difference, three rows of input data to calculate a second-degree difference, and so on.

However, rather than returning NULL for the first few rows, the function returns the correct values, such that using DELTA of degree n followed by DELTA of degree -n cancel each other out to return the initial result (other than floating point error accumulation). In general, using this window function over larger result sets with large degrees worsens floating-point error accumulation.

Integer degree values in the range [-3, 3] are generally stable up to approximately 5 million rows. Fractional degree values in the range [-3, 3] are generally stable up to approximately 1 million rows. The stable degree range is wider over smaller result sets and narrower over larger result sets. For example, fractional and integer degrees are stable over the range [-4, 4] up to approximately 80,000 rows.

Syntax

DELTA ( expression [, degree ])
   OVER (
      [ PARTITION BY part_list ]
      ORDER BY order_list )

Examples

Calculate Difference Values Using****Basic Numbers

This example shows the delta values for a table with generated numbers from one to 10.

SELECT y, DELTA(y, 1)
    OVER (ORDER BY y) AS d
    FROM (SELECT DOUBLE(c1 * c1) AS y FROM sys.dummy10);

Output

| "y" | "d" |
|-----|-----|
| 1   | 1   |
| 4   | 3   |
| 9   | 5   |
| 16  | 7   |
| 25  | 9   |
| 36  | 11  |
| 49  | 13  |
| 64  | 15  |
| 81  | 17  |
| 100 | 19  |

Calculate Difference Values Using Partitioned Sales Data

This example uses the DELTA function with a table of regional sales data to find the difference in sales by region and year.

SELECT
    region,
    year,
    sales_amount,
    DELTA(sales_amount) OVER (PARTITION BY region ORDER BY year) AS sales_delta
FROM
    regional_sales
ORDER BY
    region,
    year;

Output

| "region" | "year" | "sales_amount" | "sales_delta" |
|----------|--------|----------------|---------------|
| North    |   2018 |          12000 |         12000 |
| North    |   2019 |          15000 |          3000 |
| North    |   2020 |          17000 |          2000 |
| North    |   2021 |          16000 |         -1000 |
| North    |   2022 |          18000 |          2000 |
| South    |   2018 |          10000 |         10000 |
| South    |   2019 |          14000 |          4000 |
| South    |   2020 |          15000 |          1000 |
| South    |   2021 |          17000 |          2000 |
| South    |   2022 |          16000 |         -1000 |

DENSE_RANK

Assigns a rank to each row in the result set with equal values having the same number. There are no gaps between ranks. 

Syntax

DENSE_RANK ()
   OVER ( [ PARTITION BY part_list ] ORDER BY order_list )

Example

This example generates the rank for a series of ten random numbers. Use the RAND function to generate the random numbers.

WITH RandomNumbers AS (
    SELECT RAND() AS num
    FROM sys.dummy10
)
SELECT num, DENSE_RANK()
    OVER (ORDER BY num) AS n
    FROM RandomNumbers;

Output

|         num          |  n  |
| -------------------- | --- |
| 0.021744504292815926 | 1   |
| 0.11531389663905277  | 2   |
| 0.17818297230968436  | 3   |
| 0.3009914646699445   | 4   |
| 0.366713904443239    | 5   |
| 0.7801256114813641   | 6   |
| 0.8735727062571587   | 7   |
| 0.8969752860565523   | 8   |
| 0.9679736914744647   | 9   |
| 0.9910291449541664   | 10  |

DERIVATIVE

The function calculates the difference between successive values of two numeric expressions, representing a backward difference (e.g., the difference between the current and previous row). It can be considered as computing the derivative of one expression with respect to another.

The function must use an ORDER BY clause, and it cannot use a PARTITION BY clause.

DERIVATIVE requires two arguments, expression and expression2, which must be included in the ORDER BY clause.

The function computes the difference in expression values between consecutive rows and divides this by the difference in expression2 values over the same rows. If expression represents some measured value and expression2 is time, the result is the rate of change (derivative) of the value with respect to time.

The function can compute higher-degree derivatives (second, third, etc.). To calculate the nth-degree derivative, it uses a backward difference formula that compares the current row to the previous n rows.

For example:

• A second-degree derivative requires three rows of data: the current row, the previous row, and the one before the previous row.
• A third-degree derivative would require four rows, and so on.

Unlike some functions that return NULL for rows that lack sufficient preceding data, the DERIVATIVE function is designed to return correct values even for initial rows. This behavior ensures that if you apply a derivative of degree n followed by its inverse (derivative of degree -n), you recover the original values with minimal error (except for floating point inaccuracies).

In general, using this function over larger result sets with larger degrees worsens the floating-point error. Integer degree values in the range of three are generally stable up to approximately 5 million rows. Fractional degree values in the range of three are generally stable up to approximately 1 million rows. The stable degree range is wider over smaller result sets and narrower over larger result sets. For example, fractional and integer degrees are stable over the range [-4, 4] up to approximately 80,000 rows. 

 Syntax

DERIVATIVE ( expression [, degree ] )
   OVER ( ORDER BY expression2 )

Examples

Calculate the Derivative Using Basic Numbers

This example shows the derivative values for a basic table with values x and y.

SELECT x, y, DERIVATIVE(y, 1)
    OVER (ORDER BY x) AS d
    FROM (SELECT DOUBLE(2 * x + 1) AS y, x
        FROM (SELECT c1/2.0 AS x FROM sys.dummy10));

Output

| "x" | "y"  | "d" |
|-----|------|-----|
| 0.5 |  2.0 | 4.0 |
| 1.0 |  3.0 | 2.0 |
| 1.5 |  4.0 | 2.0 |
| 2.0 |  5.0 | 2.0 |
| 2.5 |  6.0 | 2.0 |
| 3.0 |  7.0 | 2.0 |
| 3.5 |  8.0 | 2.0 |
| 4.0 |  9.0 | 2.0 |
| 4.5 | 10.0 | 2.0 |
| 5.0 | 11.0 | 2.0 |

Calculate the Antiderivative Using a Negative Degree

This example uses a negative degree to produce an antiderivative.

SELECT x, y, DERIVATIVE(y, -1)
    OVER (ORDER BY x) AS d
    FROM (SELECT DOUBLE(2 * x + 1) AS y, x
        FROM (SELECT c1/2.0 AS x FROM sys.dummy10));

Output

| "x" | "y"  | "d"  |
|-----|------|------|
| 0.5 |  2.0 |  1.0 |
| 1.0 |  3.0 |  2.5 |
| 1.5 |  4.0 |  4.5 |
| 2.0 |  5.0 |  7.0 |
| 2.5 |  6.0 | 10.0 |
| 3.0 |  7.0 | 13.5 |
| 3.5 |  8.0 | 17.5 |
| 4.0 |  9.0 | 22.0 |
| 4.5 | 10.0 | 27.0 |
| 5.0 | 11.0 | 32.5 |

FIRST_VALUE

Returns the first value in the ordered result set. 

Syntax

FIRST_VALUE ( expression )
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Example

This example uses a table with generated numbers from one to 10. Of this group, the function determines the first value from the ordering.

SELECT y, FIRST_VALUE(y)
    OVER (ORDER BY y) AS first_value
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "first_value" |
|-----|---------------|
|   1 |             1 |
|   2 |             1 |
|   3 |             1 |
|   4 |             1 |
|   5 |             1 |
|   6 |             1 |
|   7 |             1 |
|   8 |             1 |
|   9 |             1 |
|  10 |             1 |

LAG

Returns a row backward from the current row (one row back unless you specify a different number value).

Syntax

LAG (value_expr [, offset ])
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Examples

Calculate Lag Using Basic Numbers

This example uses a table with generated numbers from one to 10. Of this group, the LAG function returns the last value for the specified column y.

SELECT y, LAG(y)
    OVER (ORDER BY y) AS wf
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "wf" |
|-----|------|
|   1 |      |
|   2 |    1 |
|   3 |    2 |
|   4 |    3 |
|   5 |    4 |
|   6 |    5 |
|   7 |    6 |
|   8 |    7 |
|   9 |    8 |
|  10 |    9 |

Calculate Lag Using an Offset

In this example, the offset value 5 changes the number of rows back from the current row from which to return a value.

SELECT y, LAG(y, 5)
    OVER (ORDER BY y) AS wf
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "wf" |
|-----|------|
|   1 |      |
|   2 |      |
|   3 |      |
|   4 |      |
|   5 |      |
|   6 |    1 |
|   7 |    2 |
|   8 |    3 |
|   9 |    4 |
|  10 |    5 |

LAST_VALUE

Returns the last value in the ordered result set. 

Syntax

LAST_VALUE ( expression )
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Example

This example uses a table with generated numbers from one to 10. Of this group, the function determines the last value from the ordering.

SELECT y, LAST_VALUE(y)
    OVER (ORDER BY y) AS last_value
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "last_value" |
|-----|--------------|
|   1 |            1 |
|   2 |            2 |
|   3 |            3 |
|   4 |            4 |
|   5 |            5 |
|   6 |            6 |
|   7 |            7 |
|   8 |            8 |
|   9 |            9 |
|  10 |           10 |

LEAD

Returns a row forward from the current row (one row forward unless you specify a different number value).

Syntax

LEAD (value_expr [, offset ])
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Examples

Calculate the Lead Value Using Basic Numbers

This example uses a table with generated numbers from one to 10. Of this group, the LEAD function returns the last value for the specified column y.

SELECT y, LEAD(y)
    OVER (ORDER BY y) AS lead_value
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "lead_value" |
|-----|--------------|
|   1 |            2 |
|   2 |            3 |
|   3 |            4 |
|   4 |            5 |
|   5 |            6 |
|   6 |            7 |
|   7 |            8 |
|   8 |            9 |
|   9 |           10 |
|  10 |              |

Calculate the Lead Value Using an Offset

In this example, using a table of generated numbers with values from one to 10, specify the offset value 5 in the LEAD function to change the number of rows forward from the current row to return a value.

SELECT y, LEAD(y, 5)
    OVER (ORDER BY y) AS lead_value
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "lead_value" |
|-----|--------------|
|   1 |            6 |
|   2 |            7 |
|   3 |            8 |
|   4 |            9 |
|   5 |           10 |
|   6 |              |
|   7 |              |
|   8 |              |
|   9 |              |
|  10 |              |

NTH_VALUE

Returns the n-th value in the ordered result set. 

Syntax

NTH_VALUE (expr, offset)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Examples

Calculate the Fifth Value Using Bounded Rows

This example uses a table with generated numbers from one to 10. Of this group, the NTH_VALUE function returns the fifth value for the specified column y.

For the first four rows, the window contains fewer than five rows, so the function returns NULL because the fifth value is not yet available.

SELECT y, NTH_VALUE(y, 5)
    OVER (ORDER BY y) AS nth
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "nth" |
|-----|-------|
|   1 |       |
|   2 |       |
|   3 |       |
|   4 |       |
|   5 |     5 |
|   6 |     5 |
|   7 |     5 |
|   8 |     5 |
|   9 |     5 |
|  10 |     5 |

Calculate the Fifth Value Using Unbounded Rows

To include future rows in the window frame, you can modify the window frame as shown in this example.

SELECT y, NTH_VALUE(y, 5)
	OVER ( ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS nth
FROM
	(SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "nth" |
|-----|-------|
|   1 |     5 |
|   2 |     5 |
|   3 |     5 |
|   4 |     5 |
|   5 |     5 |
|   6 |     5 |
|   7 |     5 |
|   8 |     5 |
|   9 |     5 |
|  10 |     5 |

PERCENTILE

Returns the value corresponding to the specified percentile (0 ≤ n ≤ 1) within the group.

The set of values is treated as a continuous distribution, so it is possible that the computed value cannot appear in the result set. 

Syntax

PERCENTILE (expr, percentile)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Example

This example finds the value at approximately 90 percent of the range of the expression values. For the source data, use a table of generated numbers from one to 10.

The value returns 9.1 because the window column starts at 1. It would be 9 if the column started at 0.

SELECT y, PERCENTILE(y, 0.9)
    OVER (ORDER BY y) AS percentile_value
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" | "percentile_value" |
|-----|--------------------|
|   1 |                9.1 |
|   2 |                9.1 |
|   3 |                9.1 |
|   4 |                9.1 |
|   5 |                9.1 |
|   6 |                9.1 |
|   7 |                9.1 |
|   8 |                9.1 |
|   9 |                9.1 |
|  10 |                9.1 |

PERCENT_RANK

Returns a percentage value for each row representing the percentage of values less than the current group, excluding the highest value. The highest value in a group is always 1. 

Syntax

PERCENT_RANK ()
    OVER ( [ PARTITION BY part_list ] ORDER BY order_list )

Example

This example generates the percent rank of the table with ten generated numbers from one to 10.

SELECT y, PERCENT_RANK()
    OVER (ORDER BY y) AS percent_rank
    FROM (SELECT c1 AS y FROM sys.dummy10);

Output

| "y" |   "percent_rank"   |
|-----|--------------------|
|   1 |                0.0 |
|   2 | 0.1111111111111111 |
|   3 | 0.2222222222222222 |
|   4 | 0.3333333333333333 |
|   5 | 0.4444444444444444 |
|   6 | 0.5555555555555556 |
|   7 | 0.6666666666666666 |
|   8 | 0.7777777777777778 |
|   9 | 0.8888888888888888 |
|  10 |                1.0 |

RANK

Assigns a rank number to each row in the result set with equal values having the same number. There can be gaps between ranks. Results are similar to the ranking used in sporting events. 

Syntax

RANK ()
    OVER ( [ PARTITION BY part_list ] ORDER BY order_list )

Example

This example ranks a series of ten random numbers. Use the RAND function to generate the random numbers.

WITH RandomNumbers AS (
    SELECT RAND() AS num
    FROM sys.dummy10
)
SELECT num, RANK() OVER (ORDER BY num) AS n
FROM RandomNumbers;

Output

|  num                |  n  |
| ------------------- | --- |
| 0.0003210869634629  | 1   |
| 0.03250125317575761 | 2   |
| 0.09413438896602266 | 3   |
| 0.130728439355569   | 4   |
| 0.376078282337459   | 5   |
| 0.5049826734614208  | 6   |
| 0.5642048500743658  | 7   |
| 0.5989180494879046  | 8   |
| 0.9077625673606013  | 9   |
| 0.9428422123895421  | 10  |

RATIO_TO_REPORT

Computes the ratio of a value to the sum of the set of values. For example, the RATIO_TO_REPORT function calculates the ratio of the salary of an employee relative to the total salaries in the department where the employee belongs.  

Syntax

RATIO_TO_REPORT ( ratio_expression )
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Example

This example shows a ratio of each salary of an individual employee to the total salaries.

SELECT name, salaries, RATIO_TO_REPORT(salaries)
    OVER (ORDER BY salaries) AS ratio
    FROM salaries;

Output

|      name       |  salaries |        ratio        |
|-----------------|-----------|---------------------|
| Vasquez, Gustav |   70000.0 | 0.08484848484848485 |
| Ho, Chen        |   75000.0 | 0.09090909090909091 |
| Kiwi, Marie     |  110000.0 | 0.13333333333333333 |
| Smith, Roberts  |  140000.0 |  0.1696969696969697 |
| Porter, Polly   |  180000.0 | 0.21818181818181817 |
| Svenson, Olaf   |  250000.0 | 0.30303030303030304 |

ROW_NUMBER

Assigns a unique number to each row in the result set. 

Syntax

ROW_NUMBER ()
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Examples

Determine the Unique Number of Each Row in a Table

This example assigns a row number to a basic table of fruit prices based on the price order.

SELECT fruit, price, ROW_NUMBER()
OVER (ORDER BY price) AS row_no
    FROM fruits;

Output

|  "fruit"   | "price" |"row_no"|
|------------|---------|--------|
| banana     |     0.5 |      1 |
| kiwi       |    0.75 |      2 |
| orange     |     1.5 |      3 |
| mango      |    1.75 |      4 |
| apple      |    1.99 |      5 |
| strawberry |    3.99 |      6 |

Determine the Unique Number of Each Row****Using a Partitioned Table

This example uses a table of sales data for two regions. The ROW_NUMBER function orders the sales team members by their region and sales amounts.

SELECT
    salesperson,
    region,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY region) AS total_sales_by_region,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank_in_region
FROM
    sales;

Output

| "salesperson" | "region" | "sales_amount" | "total_sales_by_region" | "sales_rank_in_region" |
|---------------|----------|----------------|-------------------------|------------------------|
| Eve           | North    |           2500 |                    5000 |                      1 |
| Bob           | North    |           1500 |                    5000 |                      2 |
| Alice         | North    |           1000 |                    5000 |                      3 |
| Dave          | South    |           3000 |                    5000 |                      1 |
| Carol         | South    |           2000 |                    5000 |                      2 |

ZSCORE

Calculates the z-score of the sample based on the standard deviation (similar to the STDDEV aggregate function).

The standard deviation assumes that the data is a sample of a larger set, meaning it corrects for bias.

If your data set is comprehensive and represents an entire group, use the ZSCOREP function instead.

Syntax

ZSCORE (expr)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Example

This example calculates the standard deviation in a range of salaries.

SELECT name, salaries, ZSCORE(salaries)
OVER (ORDER BY salaries) AS std_dev
    FROM salaries;

Output

|      name       | salaries |      std_dev       |
|-----------------|----------|--------------------|
| Vasquez, Gustav |  70000.0 |                    |
| Ho, Chen        |  75000.0 | 0.7071076862849652 |
| Kiwi, Marie     | 110000.0 | 1.1470789011835154 |
| Smith, Roberts  | 140000.0 | 1.2593337909146212 |
| Porter, Polly   | 180000.0 | 1.4100478781464227 |
| Svenson, Olaf   | 250000.0 |  1.634473657490547 |

ZSCOREP

Calculates the z-score of the sample based on the population standard deviation (similar to the STDEVP aggregate function).

The population standard deviation assumes that the data contains the entire population, meaning there is no correction for bias. Use it when your data set is comprehensive and represents the entire group (i.e. population) you are analyzing.

If your data set is a sample of a larger set, use the ZSCORE function.

Syntax

ZSCOREP (expr)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )

Example

This example calculates the population standard deviation in a range of salaries.

SELECT name, salaries, ZSCOREP(salaries)
OVER (ORDER BY salaries) AS std_devp
    FROM salaries;

Output

|      name       | salaries |      std_devp      |
|-----------------|----------|--------------------|
| Vasquez, Gustav |  70000.0 |                    |
| Ho, Chen        |  75000.0 | 1.0000012800024576 |
| Kiwi, Marie     | 110000.0 | 1.4048790013060097 |
| Smith, Roberts  | 140000.0 | 1.4541534063682966 |
| Porter, Polly   | 180000.0 | 1.5764814535323706 |
| Svenson, Olaf   | 250000.0 | 1.7904761837110903 |

Related Links

Math Functions and Operators

General SQL Syntax

Generate Tables Using sys.dummy