Window Aggregate Functions - Ocient Documentation

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.

SQL

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 }

Argument	Data Type	Description
`arg1, arg2 [,...]`	String	The arguments of the window function. The arguments depend on the type of window function.

Parameter	Data Type	Description
`part_list`	String	Specifies the column by which the rows are partitioned for the window function.
`order_list`	String	Specifies the column by which the rows are ordered.
`row_integer`	Numeric	An integer representing the number of rows to operate from the 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.

Keyword Clause	Description
`UNBOUNDED PRECEDING`	Specifies that the window starts at the first row of the partition. This clause specifies a window starting point only.
`UNBOUNDED FOLLOWING`	Specifies that the window ends at the last row of the partition. This clause specifies a window endpoint only. For example: `RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING` This window frame starts with the current row and ends with the last row of the partition.
`row_integer PRECEDING`	The `row_integer` value specifies the number of rows or values to precede the current row. You cannot use the `PRECEDING` keyword in a `RANGE` clause.
`row_integer FOLLOWING`	The `row_integer` value specifies the number of rows or values to follow the current row. When you specify the `FOLLOWING` keyword as the window starting point, the endpoint must also use a `FOLLOWING` clause with a row value. For example: `ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING` This window frame starts with the second row following the current row and ends with the tenth row following the current row. You cannot use the `FOLLOWING` keyword in a `RANGE` clause.
`CURRENT ROW`	When you specify the `CURRENT ROW` keyword in a `ROWS` clause, the window frame starts or ends at the current row. When you specify this keyword in a `RANGE` clause, the window frame includes all rows with equal values. For example: `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` This clause means that the window function operates on a frame of three rows in size, where the first two rows precede the current row (inclusive). You can specify the current row as either a starting or an end point.

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.

You cannot use the DISTINCT keyword with window aggregation functions.

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

SQL

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

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

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

The function treats any NULL values in the input data as zero, which might cause unexpected results. Filter out NULL values before you use the window function.

Syntax

SQL

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

Argument	Data Type	Description
`expression`	Numeric	The column or expression to use for the DELTA calculation.
`degree`	Numeric	Optional. If `degree` is set to a positive integer greater than 1, the window function calculates the higher-order finite difference without using nested `DELTA` calls. If negative, the calculation is an anti-difference (i.e., discrete integration). You can specify fractional values, either positive or negative, and these values mean fractional differences or sums. The default is `1`.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

Examples Calculate Difference Values Using Basic Numbers This example shows the delta values for a table with generated numbers from one to 10.

SQL

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

Output

Text

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

SQL

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

Text

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

SQL

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

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

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

For higher-order calls, the sample points for expression must be equally spaced, or the function might return incorrect results. In other words, the DELTA executions between consecutive expression2 values must always be the same. If this is not true, use some form of interpolation to create an evenly-spaced series of samples before you use the window function.

The function treats any NULL values in the input data for either expression or expression2 arguments as zero, which might cause unexpected results. Filter out NULL values before you use the window function.

Syntax

SQL

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

Argument	Data Type	Description
`expression`	Numeric	The column or expression to use for the `DERIVATIVE` calculation compared to `expression2`.
`expression2`	Numeric	The column or expression to use for the `DERIVATIVE` calculation compared to `expression`.
`degree`	Numeric	Optional. If you set `degree` to a positive integer greater than one, the window function calculates the higher-order difference quotients (discrete derivatives) without using nested `DERIVATIVE` executions. If the `degree` argument is a negative integer, the calculation is a discrete anti-differentiation or integration. The default value is `1`.

Examples Calculate the Derivative Using Basic Numbers This example shows the derivative values for a basic table with values x and y.

SQL

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

Text

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

SQL

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

Text

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

SQL

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

Argument	Data Type	Description
`expression`	Any	The column or expression value to use for the `FIRST_VALUE` calculation.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

For marking repeated LAG calls on one or more columns, see Data Preparation.

Syntax

SQL

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

Argument	Data Type	Description
`value_expr`	Any	The column or expression to use for the `LAG` calculation.
`offset`	Numeric	Optional. This number determines how many rows back from the current row to return a value. The default value is `1`.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

SQL

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

Output

Text

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

SQL

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

Argument	Data Type	Description
`expression`	Any	The column or expression value to use for the `LAST_VALUE` calculation.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

SQL

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

Argument	Data Type	Description
`value_expr`	Any	The value to use for the `LEAD` calculation.
`offset`	Numeric	Optional. This number determines how many rows forward from the current row to return a value. The default value is `1`.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

SQL

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

Output

Text

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

SQL

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

Argument	Data Type	Description
`expr`	Any	The column or expression from which to retrieve a value.
`offset`	Numeric	A positive integer that indicates the row number, starting from the first row in the window. This integer must be positive.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

SQL

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

Text

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

SQL

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

Argument	Data Type	Description
`expr`	Any	The column or expression to use for the `PERCENTILE` calculation.
`percentile`	Float	A percentile (0 ≤ `n` ≤ 1) to determine the value to return by its position within the ordering of `expr`.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

SQL

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

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

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

SQL

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

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

|  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

SQL

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

Argument	Data Type	Description
`ratio_expression`	Numeric	A column or expression to calculate the ratio for each row compared to the total.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

|      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

SQL

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

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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.

SQL

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

Output

Text

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

SQL

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

Text

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

SQL

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

Argument	Data Type	Description
`expr`	Numeric	A column or expression to calculate the standard deviation for each row.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

|      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

SQL

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

Argument	Data Type	Description
`expr`	Numeric	A column or expression to calculate the standard deviation for each row.

Parameter	Data Type	Description
`part_list`	Any	Specifies the column by which the rows are partitioned for the window function.
`order_list`	Any	Specifies the column by which the rows are ordered for the window function.

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

SQL

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

Output

Text

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

Math Functions and Operators General SQL Syntax Generate Tables Using sys.dummy

OcientAIQ Unified Data Platform

Documentation Index

​Window Aggregate Keywords

​PARTITION BY

​ORDER BY

​ASC or DESC

​NULLS FIRST, NULLS LAST, or IGNORE NULLS

​ROWS or RANGE

​Supported Window Aggregate Functions

​CUME_DIST

​DELTA

​DENSE_RANK

​DERIVATIVE

​FIRST_VALUE

​LAG

​LAST_VALUE

​LEAD

​NTH_VALUE

​PERCENTILE

​PERCENT_RANK

​RANK

​RATIO_TO_REPORT

​ROW_NUMBER

​ZSCORE

​ZSCOREP

​Related Links

Window Aggregate Keywords

PARTITION BY

ORDER BY

ASC or DESC

NULLS FIRST, NULLS LAST, or IGNORE NULLS

ROWS or RANGE

Supported Window Aggregate Functions

CUME_DIST

DELTA

DENSE_RANK

DERIVATIVE

FIRST_VALUE

LAG

LAST_VALUE

LEAD

NTH_VALUE

PERCENTILE

PERCENT_RANK

RANK

RATIO_TO_REPORT

ROW_NUMBER

ZSCORE

ZSCOREP

Related Links