Analytic / Window Functions

Analytic functions are used to compute an aggregate over a group of rows, often referred to as a window; hence, they are also known as window functions.

In addition, as the category name implies, most general aggregate functions (AVG, MEDIAN, etc.) can also be called as window functions.

Note

Certain analytic and aggregate functions also allow a frame to be specified for the window, which enables performing rolling operations on a subset of the rows in the window.

In this Topic:

Windows

List of Analytic / Window Functions

The following table lists the analytic functions and indicates whether the function also supports a window frame:

Function Name Supports Window Frame Notes
CUME_DIST    
DENSE_RANK    
FIRST_VALUE  
LAG    
LAST_VALUE  
LEAD    
NTH_VALUE  
NTILE    
PERCENT_RANK    
RANK    
ROW_NUMBER    
WIDTH_BUCKET   Not an actual analytic function (i.e. does not utilize a window in its syntax), but performs similar operations.

Window Syntax

<analytic_function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> )
<aggregate_function> ( <arguments> ) OVER ( PARTITION BY <expr> )

Note

The analytic and aggregate functions that support window frames utilize a modified/enhanced syntax. For more details, see Window Frames (in this topic).

Window Usage Notes

  • The OVER clause specifies the window over which the function operates. The clause consists of one (or both) of the following components:

    • PARTITION BY expr: Subclause that defines the partition, if any, for the window (i.e. how the data will be grouped before applying the function).
    • ORDER BY expr: Subclause that determines the ordering of the rows in the window. For more details about additional supported options (sort order, ordering of NULL values, etc.), see the ORDER BY query construct.
  • For analytic functions:

    • PARTITION BY is optional.
    • ORDER BY is required.
  • For aggregate functions:

    • PARTITION BY is required.
    • ORDER BY is not supported, unless a window frame is specified. For more details, see the next section.

Window Frames

Window frames enable computing values over a specified group of rows within the window. Window frames are specified as an additional subclause in the ORDER BY subclause.

Types of Window Frames

Snowflake supports two types of window frames:

Cumulative:Enables computing rolling values from the beginning of the window to the current row or from the current row to the end of the window.
Sliding:Enables computing rolling values between any two rows (inclusive) in the window, relative to the current row.

Tip

Sliding window frames provide more granular control for computing rolling values, but are typically slower than cumulative window frames.

List of Functions that Support Window Frames

The following functions support window frames:

Category Function
Analytic FIRST_VALUE
  LAST_VALUE
  NTH_VALUE
Aggregate COUNT
  MIN / MAX
  SUM

Window Frame Syntax

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] ] )

Where:

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }
slidingFrame ::=
    {
       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
    }

Window Frame Usage Notes

  • For cumulative window frames:

    • ROWS computes the aggregate for the current row using all rows from the beginning or end of the partition to the current row (according to the specified ORDER BY subclause).
    • RANGE is similar to ROWS, except it only computes the aggregate for rows that have the same value as the current row (according to the specified ORDER BY subclause).
  • For sliding window frames:

    • ROWS is inclusive and is always relative to the current row.
    • RANGE is not supported.
    • If the specified number of preceding or following ROWS extends beyond the window limits, Snowflake treats the value as NULL.
  • If no window frame is specified, the default is different depending on the function:

    • For analytic functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE), the default is the entire window:

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      Note that this deviates from the ANSI standard.

    • For aggregate functions (COUNT, MIN / MAX, SUM), the default is the following cumulative window frame (in accordance with the ANSI standard):

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Examples: Cumulative Window Frames

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

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

-- Return a cumulative count, sum, min, and max, for rows in the specified window for the table:
SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
FROM example_cumulative
ORDER BY p,o;

+-----+---+--------+------------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------|
| 100 | 1 |     10 |                1 |             10 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+

-- Return a cumulative count, sum, min, and max by range for rows in the specified window for the table:
SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Range_Pre
FROM example_cumulative
ORDER BY p,o;

+-----+---+--------+-------------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------|
| 100 | 1 |     10 |                 1 |              10 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+

-- Return the same results as the above query by using the default window frame semantics
-- (i.e. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):
SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
FROM example_cumulative
ORDER BY p,o;

+-----+---+--------+-------------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------|
| 100 | 1 |     10 |                 1 |              10 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+

Examples: Sliding Window Frames

CREATE TABLE example_sliding
  (p INT, o INT, i INT, r INT, s VARCHAR(100));

INSERT INTO example_sliding VALUES
  (100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
  (200,11,NULL,90,'ninety'),
  (300,12,12,30,'thirty'),
  (400,13,NULL,20,'twenty');

-- Return the min values for two columns (numeric and string) across sliding windows before, after, and encompassing
-- the current row:
select p, o, i,
       MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
       MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
       MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
       s,
       MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
       MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
       MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
FROM example_sliding
ORDER BY p, o;

+-----+----+------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O |    I | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S       | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |    1 |        NULL |           2 |           1 | seventy | NULL        | forty       | forty       |
| 100 |  2 |    2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |    3 |           1 |           5 |           2 | forty   | seventy     | fifty       | fifty       |
| 100 |  4 | NULL |           1 |           5 |           3 | ninety  | forty       | fifty       | fifty       |
| 100 |  5 |    5 |           2 |           6 |           5 | fifty   | forty       | thirty      | fifty       |
| 100 |  6 |    6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |    7 |        NULL |          10 |           7 | forty   | NULL        | n_u_l_l     | forty       |
| 200 |  8 | NULL |           7 |          10 |           7 | n_u_l_l | forty       | n_u_l_l     | forty       |
| 200 |  9 | NULL |           7 |          10 |          10 | n_u_l_l | forty       | ninety      | n_u_l_l     |
| 200 | 10 |   10 |           7 |        NULL |          10 | twenty  | forty       | ninety      | n_u_l_l     |
| 200 | 11 | NULL |          10 |        NULL |          10 | ninety  | n_u_l_l     | NULL        | ninety      |
| 300 | 12 |   12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 | NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+------+-------------+-------------+-------------+---------+-------------+-------------+-------------+


-- Return the max values for two columns (numeric and string) across sliding windows before, after, and encompassing
-- the current row:
SELECT p, o, i,
       MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
       MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
       MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
       s,
       MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
       MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
       MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
FROM example_sliding
ORDER BY p, o;

+-----+----+------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O |    I | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S       | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |    1 |        NULL |           3 |           3 | seventy | NULL        | thirty      | thirty      |
| 100 |  2 |    2 |           1 |           5 |           5 | thirty  | seventy     | ninety      | thirty      |
| 100 |  3 |    3 |           2 |           6 |           6 | forty   | thirty      | thirty      | thirty      |
| 100 |  4 | NULL |           3 |           6 |           6 | ninety  | thirty      | thirty      | thirty      |
| 100 |  5 |    5 |           3 |           6 |           6 | fifty   | thirty      | thirty      | thirty      |
| 100 |  6 |    6 |           5 |        NULL |           6 | thirty  | ninety      | NULL        | thirty      |
| 200 |  7 |    7 |        NULL |          10 |          10 | forty   | NULL        | twenty      | twenty      |
| 200 |  8 | NULL |           7 |          10 |          10 | n_u_l_l | forty       | twenty      | twenty      |
| 200 |  9 | NULL |           7 |          10 |          10 | n_u_l_l | n_u_l_l     | twenty      | twenty      |
| 200 | 10 |   10 |           7 |        NULL |          10 | twenty  | n_u_l_l     | ninety      | twenty      |
| 200 | 11 | NULL |          10 |        NULL |          10 | ninety  | twenty      | NULL        | twenty      |
| 300 | 12 |   12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 | NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

-- Return the sum of a number column across sliding windows before, after, and encompassing the current row:
SELECT p, o, r,
       SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
       sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
       sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
FROM example_sliding
ORDER BY p, o;

+-----+----+------+-------------+-------------+-------------+
|   P |  O |    R | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+------+-------------+-------------+-------------|
| 100 |  1 |   70 |        NULL |         180 |         280 |
| 100 |  2 |   30 |        NULL |         170 |         310 |
| 100 |  3 |   40 |          70 |          80 |         310 |
| 100 |  4 |   90 |         100 |          30 |         240 |
| 100 |  5 |   50 |         140 |        NULL |         210 |
| 100 |  6 |   30 |         160 |        NULL |         170 |
| 200 |  7 |   40 |        NULL |         110 |         150 |
| 200 |  8 | NULL |        NULL |         110 |         150 |
| 200 |  9 | NULL |          40 |          90 |         150 |
| 200 | 10 |   20 |          40 |        NULL |         110 |
| 200 | 11 |   90 |          40 |        NULL |         110 |
| 300 | 12 |   30 |        NULL |        NULL |          30 |
| 400 | 13 |   20 |        NULL |        NULL |          20 |
+-----+----+------+-------------+-------------+-------------+