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:

  • Most general aggregate functions (AVG, MEDIAN, etc.) can also be called as window functions.
  • Certain analytic and aggregate functions 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:

List of Standard Window Functions

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

Function Name Notes
CUME_DIST  
DENSE_RANK  
FIRST_VALUE Also supports window frames.
LAG  
LAST_VALUE Also supports window frames.
LEAD  
NTH_VALUE Also supports window frames.
NTILE  
PERCENT_RANK  
RANK  
ROW_NUMBER  
WIDTH_BUCKET  

Syntax for Standard Window Functions (and Aggregate Functions)

Standard Window Functions

<analytic_function> ( <argument1> [ , ... , <argumentN> ] ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> )

The OVER clause specifies the window over which the function operates. The OVER clause consists of the following two components:

PARTITION BY <expr1>
Subclause that defines the partition, if any, for the window, i.e. how the data will be grouped before applying the function.
ORDER BY <exp2>
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.

Aggregate Functions

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

For aggregate functions:

  • The PARTITION BY subclause is required.
  • The ORDER BY subclause is not supported.

Note

For the analytic and aggregate functions that support specifying a window frame for the ORDER BY subclause, these functions utilize a slightly different syntax. See the next section for more details.

Window Frames

Window frames enable computing values over a specified group of rows within the window. Snowflake supports two types of frames, cumulative and sliding:

  • Cumulative window frames enable 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 window frames enable computing rolling values between any two rows (inclusive) in the window, relative to the current row.

Note

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

The following functions support window frames:

Analytic Functions Aggregate Functions
FIRST_VALUE COUNT
LAST_VALUE MAX
NTH_VALUE MIN
  SUM

Syntax for Cumulative Window Frame

<function> ( [ <argument1> [ , ... , <argumentN> ] ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame ] )

-- where:
   cumulativeFrame ::=

        {
          { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
          { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        }

Note:

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

Syntax for Sliding Window Frame

<function> ( [ <argument1> [ , ... , <argumentN> ] ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ slidingFrame ] )

-- where:
   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 |
        }

Note:

  • RANGE is not supported for sliding window frames.
  • ROWS is inclusive and is always relative to the current row.
  • If the specified number of preceding or following ROWS extends beyond the window limits, Snowflake treats the value as NULL.

Window Frame Defaults

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, i.e.:

    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

Window Frame 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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+

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,'fourty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'fourty'),(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        | fourty      | fourty      |
| 100 |  2 |    2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |    3 |           1 |           5 |           2 | fourty  | seventy     | fifty       | fifty       |
| 100 |  4 | NULL |           1 |           5 |           3 | ninety  | fourty      | fifty       | fifty       |
| 100 |  5 |    5 |           2 |           6 |           5 | fifty   | fourty      | thirty      | fifty       |
| 100 |  6 |    6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |    7 |        NULL |          10 |           7 | fourty  | NULL        | n_u_l_l     | fourty      |
| 200 |  8 | NULL |           7 |          10 |           7 | n_u_l_l | fourty      | n_u_l_l     | fourty      |
| 200 |  9 | NULL |           7 |          10 |          10 | n_u_l_l | fourty      | ninety      | n_u_l_l     |
| 200 | 10 |   10 |           7 |        NULL |          10 | twenty  | fourty      | 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 | fourty  | 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 | fourty  | NULL        | twenty      | twenty      |
| 200 |  8 | NULL |           7 |          10 |          10 | n_u_l_l | fourty      | 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 |
+-----+----+------+-------------+-------------+-------------+