Window/Analytic Functions

Window functions, also known as analytic functions, are used to compute an aggregate over a group of rows (i.e. window or partition).

In this Topic:

General Window Syntax

The general syntax for a window function is:

<window_function> ( [ <argument1>, ... , <argumentN> ] )
  OVER ( [ PARTITION BY <expression_list> ] ORDER BY <expression_list> )

The OVER clause specifies the partition over which the function operates. The two components to the OVER clause are:

  • PARTITION BY sub-clause (optional) defines the partition, or how the data will be grouped before applying the analytic function.
  • ORDER BY sub-clause (required) determines the ordering of the rows input to the analytic function.

Window Syntax for Aggregate Functions

Most aggregate functions (e.g. AVG) can also be called with an OVER clause; however, only PARTITION BY sub-clauses are allowed for these functions:

<aggregate_function> ( [ <argument1>, ... , <argumentN> ] )
  OVER ( PARTITION BY <expression_list> )

In addition, the following aggregate functions can be called as cumulative window functions, which support OVER clauses with both PARTITION BY and ORDER BY sub-clauses:

See the next section for details.

Cumulative Window Functions

Cumulative window functions (COUNT, SUM, MIN / MAX) enable computing rolling values over a specified group of rows. These functions use an ORDER BY sub-clause, with an optional window frame, which specifies the rows or range of values to use for computing rolling values:

<aggregate_function> ( [ <argument1> , ... , <argumentN> ] )
  OVER ( [ PARTITION BY <expression_list> ] ORDER BY <expression_list> [ windowFrame ] )

-- where:

   windowFrame ::=

       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Window Frame Semantics

The window frame specified for a cumulative window function uses the following semantics:

  • ROWS computes the aggregate for the current row using all rows from the beginning of the partition to the current row (according to the specified order).
  • RANGE is similar to ROWS, except it only computes the aggregate for rows that have the same value (according to the specified order) as the current row.

If window frame is not specified, the default is RANGE.

Examples

Set up:

CREATE OR REPLACE TABLE rolling_example (p INT, o INT, i INT);

INSERT INTO rolling_example VALUES
    (1, 1, 10), (1, 2, 30), (1, 2, 5), (1, 3, 11), (1, 3, 120),
    (2, 1, 10000), (2, 1, 200), (2, 1, 808080), (2, 2, 33333), (2, 3, null), (2, 3, 4),
    (3, 1, null), (3, 1, null);

Return a rolling count, sum, min, and max for table rolling_example using ROWS:

SELECT p,o,i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i
FROM rolling_example
ORDER BY p,o;

+---+---+--------+---------+--------+-------+--------+
| p | o |   i    | count_i | sum_i  | min_i | max_i  |
|---+---+--------+---------+--------+-------+--------|
| 1 | 1 |     10 |       1 |     10 |    10 |     10 |
| 1 | 2 |     30 |       2 |     40 |    10 |     30 |
| 1 | 2 |      5 |       3 |     45 |     5 |     30 |
| 1 | 3 |     11 |       4 |     56 |     5 |     30 |
| 1 | 3 |    120 |       5 |    176 |     5 |    120 |
| 2 | 1 |  10000 |       1 |  10000 | 10000 |  10000 |
| 2 | 1 |    200 |       2 |  10200 |   200 |  10000 |
| 2 | 1 | 808080 |       3 | 818280 |   200 | 808080 |
| 2 | 2 |  33333 |       4 | 851613 |   200 | 808080 |
| 2 | 3 |   NULL |       4 | 851613 |   200 | 808080 |
| 2 | 3 |      4 |       5 | 851617 |     4 | 808080 |
| 3 | 1 |   NULL |       0 |   NULL |  NULL |   NULL |
| 3 | 1 |   NULL |       0 |   NULL |  NULL |   NULL |
+---+---+--------+---------+--------+-------+--------+

Return a rolling count, sum, min, and max for table rolling_example using RANGE:

SELECT p,o,i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i
FROM rolling_example
ORDER BY p,o;

+---+---+--------+---------+--------+-------+--------+
| p | o |   i    | count_i | sum_i  | min_i | max_i  |
|---+---+--------+---------+--------+-------+--------|
| 1 | 1 |     10 |       1 |     10 |    10 |     10 |
| 1 | 2 |     30 |       3 |     45 |     5 |     30 |
| 1 | 2 |      5 |       3 |     45 |     5 |     30 |
| 1 | 3 |     11 |       5 |    176 |     5 |    120 |
| 1 | 3 |    120 |       5 |    176 |     5 |    120 |
| 2 | 1 |  10000 |       3 | 818280 |   200 | 808080 |
| 2 | 1 |    200 |       3 | 818280 |   200 | 808080 |
| 2 | 1 | 808080 |       3 | 818280 |   200 | 808080 |
| 2 | 2 |  33333 |       4 | 851613 |   200 | 808080 |
| 2 | 3 |   NULL |       5 | 851617 |     4 | 808080 |
| 2 | 3 |      4 |       5 | 851617 |     4 | 808080 |
| 3 | 1 |   NULL |       0 |   NULL |  NULL |   NULL |
| 3 | 1 |   NULL |       0 |   NULL |  NULL |   NULL |
+---+---+--------+---------+--------+-------+--------+

Return the same results as the above query using the window_frame default (RANGE semantics):

SELECT p,o,i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i
FROM rolling_example
ORDER BY p,o;

+---+---+--------+---------+--------+-------+--------+
| p | o |   i    | count_i | sum_i  | min_i | max_i  |
|---+---+--------+---------+--------+-------+--------|
| 1 | 1 |     10 |       1 |     10 |    10 |     10 |
| 1 | 2 |     30 |       3 |     45 |     5 |     30 |
| 1 | 2 |      5 |       3 |     45 |     5 |     30 |
| 1 | 3 |     11 |       5 |    176 |     5 |    120 |
| 1 | 3 |    120 |       5 |    176 |     5 |    120 |
| 2 | 1 |  10000 |       3 | 818280 |   200 | 808080 |
| 2 | 1 |    200 |       3 | 818280 |   200 | 808080 |
| 2 | 1 | 808080 |       3 | 818280 |   200 | 808080 |
| 2 | 2 |  33333 |       4 | 851613 |   200 | 808080 |
| 2 | 3 |   NULL |       5 | 851617 |     4 | 808080 |
| 2 | 3 |      4 |       5 | 851617 |     4 | 808080 |
| 3 | 1 |   NULL |       0 |   NULL |  NULL |   NULL |
| 3 | 1 |   NULL |       0 |   NULL |  NULL |   NULL |
+---+---+--------+---------+--------+-------+--------+