Window functions

A window function operates on a group (“window”) of related rows.

Each time a window function is called, it is passed a row (the current row in the window) and the window of rows that contain the current row. The window function returns one output row for each input row. The output depends on the individual row passed to the function and the values of the other rows in the window passed to the function.

Some window functions are order-sensitive. There are two main types of order-sensitive window functions:

  • Rank-related window functions.

  • Window functions with explicit window frames.

Rank-related functions list information based on the “rank” of a row. For example, if you rank stores in descending order by profit per year, the store with the most profit will be ranked 1; the second-most profitable store will be ranked 2, etc.

Window functions with explicit window frames allow you to perform rolling operations, such as calculating a running total or a moving average, on a subset of the rows in the window.

Users who are not familiar with window functions in general might want to read the conceptual material in Using window functions.

Overview

What is a window?

A window is a group of related rows. For example, a window might be defined based on timestamps, with all rows in the same month grouped in the same window. Or a window might be defined based on location, with all rows from a particular city grouped in the same window.

A window can consist of zero, one, or multiple rows. Typically, a window contains multiple rows.

What is a window function?

A window function is any function that operates over a window of rows.

A window function is generally passed two parameters:

  • A row. More precisely, a window function is passed 0 or more expressions. In almost all cases, at least one of those expressions references a column in that row. (Most window functions require at least one column or expression, but a few window functions, such as some rank-related functions, do not required an explicit column or expression.)

  • A window of related rows that includes that row. The window can be the entire table, or a subset of the rows in the table.

    For non-window functions, all arguments are usually passed explicitly to the function, for example:

    MY_FUNCTION(argument1, argument2, ...)
    Copy

    Window functions behave differently; although the current row is passed as an argument in the normal way, the window is passed through a separate clause, called an OVER clause. See Window function syntax and usage.

What is a window frame?

A window frame is a set of rows in a window partition. You can use window frames to compute values over specified subsets of rows. Window frames are defined in the ORDER BY clause that belongs to the OVER clause.

Types of window frames

Snowflake supports two types of window frames:

Row-based:

A sequence of rows belongs to the frame, based on a physical offset from the current row. For example, 5 PRECEDING means the five rows preceding the current row. The offset must be a number.

Range-based:

A logical range of rows belongs to the frame, given an offset from the ORDER BY value for the current row. For example, 5 PRECEDING means rows with ORDER BY values that have the ORDER BY value of the current row, plus or minus a maximum of 5 (plus for DESC order, minus for ASC order). The offset value may be a number or an interval.

You can define window frames explicitly or implicitly:

  • For explicit frame syntax, see the windowFrameClause under Window function syntax and usage. You can define named boundaries: from the beginning of the partition to the current row; from the current row to the end of the partition; or completely “unbounded” end to end. Alternatively, you can use explicit offsets (inclusive) that are relative to the current row in the partition.

  • Implicit frames are used by default when the OVER clause does not include a windowFrameClause. The default frame depends on the function in question. See also Usage notes for window frames.

For more information about window frames, see Range-based versus row-based window frames.

Window function syntax and usage

<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )
Copy

Where:

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
Copy

Where:

windowFrameClause ::=

{
    { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
Copy

Parameters

OVER( [ windowDefinition ] )

Specifies that the function is being used as a window function and specifies the window over which the function operates. The OVER clause must contain parentheses, but they may be empty, depending on the requirements of the function in question. An empty OVER clause has no partitions and an implied default window frame.

PARTITION BY expr1

Groups rows into partitions, by product, city, or year, for example. Input rows are grouped by partitions, then the function is is computed over each partition. The PARTITION BY clause is optional; you can analyze a set of rows as a single partition.

ORDER BY expr2

Orders rows within each partition, or within the entire set of rows if no partition is specified. This ORDER BY clause is distinct from the ORDER BY clause that controls the order of all the rows that are returned in the final result of a query. Although the ORDER BY clause is optional for some window functions, it is required for others. For example, ranking window functions such as RANK and NTILE require their input data to be in a meaningful order.

The ORDER BY clause for a window function follows rules similar to those for the main ORDER BY clause in a query, with respect to ASC/DESC (ascending/descending) order and NULL handling. For details, see ORDER BY.

Different functions handle the ORDER BY clause in different ways:

  • Some window functions require an ORDER BY clause.

  • Some window functions prohibit an ORDER BY clause.

  • Some window functions use an ORDER BY clause if one is present, but do not require it.

  • Some window functions apply an implicit window frame to the ORDER BY clause. (For more information, see Usage notes for window frames.)

{ ROWS | RANGE }

Specifies the type or mode of window frame, which defines either a physical number of rows (ROWS) or a logically computed set of rows (RANGE). See Range-based versus row-based window frames.

Both types of frame specify starting and ending points with BETWEEN, using either implicit named boundaries or explicit offset values. A named boundary is defined with the keywords CURRENT ROW, UNBOUNDED PRECEDING, and UNBOUNDED FOLLOWING. Explicit offsets are defined with numbers or intervals (n PRECEDING or n FOLLOWING).

{ RANGE BETWEEN n PRECEDING | n FOLLOWING }

Specifies a range-based window frame with explicit offsets.

Snowflake logo in black (no text) Preview Feature — Open

Available to all accounts.

RANGE BETWEEN window frames with explicit offsets must have only one ORDER BY expression. The following data types are supported for that expression:

  • DATE, TIMESTAMP, TIMESTAMP_LTZ , TIMESTAMP_NTZ (DATETIME) , TIMESTAMP_TZ

  • NUMBER, including INT, FLOAT, and so on

TIME and other Snowflake data types are not supported when this type of window frame is used. For other window frames, other data types, such as VARCHAR, can be used in the ORDER BY expression.

For RANGE BETWEEN window frames, n must be an unsigned constant (a positive numeric value, including 0) or a positive INTERVAL literal:

  • If expr2 is a numeric data type, n must be an unsigned constant.

  • If expr2 is a TIMESTAMP data type, n must be an INTERVAL literal. For example: INTERVAL '12 hours' or INTERVAL '3 days'.

  • If expr2 is a DATE data type, n can be an unsigned constant or an INTERVAL literal, but the start and end of the frame must use the same data type for the n value.

When the ORDER BY expression is ascending (ASC), the syntax n FOLLOWING means “rows with values greater than (or later than) x,” and n PRECEDING means “rows with values less than (or earlier than) x,” where x is the ORDER BY value for the current row. When the ORDER BY expression is descending (DESC), the opposite is true. (The offsets 0 PRECEDING and 0 FOLLOWING are equivalent to CURRENT ROW.)

Range-based versus row-based window frames

A range-based window frame consists of a logically computed set of rows rather than a physical number of rows as would be expressed in a row-based frame. A range-based window frame requires an ORDER BY column or expression and a RANGE BETWEEN specification. The logical boundary of the window frame depends on the ORDER BY value (a numeric constant or interval literal) for the current row.

For example, a time-series table named heavy_weather is defined as follows:

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
Copy

Sample rows in this table look like this:

+-------------------------+--------+-------+-------------+
| START_TIME              | PRECIP | CITY  | COUNTY      |
|-------------------------+--------+-------+-------------|
| 2021-12-30 11:23:00.000 |   0.12 | Lebec | Los Angeles |
| 2021-12-30 11:43:00.000 |   0.98 | Lebec | Los Angeles |
| 2021-12-30 13:53:00.000 |   0.23 | Lebec | Los Angeles |
| 2021-12-30 14:53:00.000 |   0.13 | Lebec | Los Angeles |
| 2021-12-30 15:15:00.000 |   0.29 | Lebec | Los Angeles |
| 2021-12-30 17:53:00.000 |   0.10 | Lebec | Los Angeles |
| 2021-12-30 18:53:00.000 |   0.09 | Lebec | Los Angeles |
| 2021-12-30 19:53:00.000 |   0.07 | Lebec | Los Angeles |
| 2021-12-30 20:53:00.000 |   0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+

Assume that a query computes a 3-hour moving average (AVG) over the precip (precipitation) column, using a window frame ordered by start_time:

AVG(precip)
  OVER(ORDER BY start_time
    RANGE BETWEEN CURRENT ROW AND INTERVAL '3 hours' FOLLOWING)
Copy

Given the sample rows above, when the current row is 2021-12-30 11:23:00.000 (the first sample row), only the next two rows fall inside the frame (2021-12-30 11:43:00.000 and 2021-12-30 13:53:00.000). Subsequent timestamps are greater than 3 hours later.

However, if you change the window frame to a 1-day interval, all of the sample rows that follow the current row fall inside the frame because they all have timestamps on the same date (2021-12-30):

RANGE BETWEEN CURRENT ROW AND INTERVAL '1 day' FOLLOWING
Copy

If you were to change this syntax from RANGE BETWEEN to ROWS BETWEEN, the frame would have to specify fixed boundaries, which represent an exact number of rows: the current row plus the following exact ordered number of rows, such as 1, 3, or 10 rows, regardless of the values returned by the ORDER BY expression.

See also RANGE BETWEEN example with explicit numeric offsets.

RANGE BETWEEN limitations

  • The COUNT, SUM, MIN, MAX, and AVG window functions support the RANGE BETWEEN syntax with explicit offsets.

  • DISTINCT versions of these functions do not support this syntax.

  • Other window functions do not support this syntax.

  • Only one input argument is supported for the COUNT window function when this syntax is used.

  • You cannot specify a frame that results in a logical reversal of the frame start and end positions. For example, the following frames return errors because the ending row of the frame precedes the starting row:

    ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING and 4 PRECEDING
ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING and 2 PRECEDING
    Copy

RANGE BETWEEN behavior when the ORDER BY expression contains NULL values

Note the following behavior when a RANGE BETWEEN window frame is used and the ORDER BY column contains NULL values:

  • When the ORDER BY clause specifies NULLS FIRST, rows with NULL in the ORDER BY column are included in UNBOUNDED PRECEDING frames.

  • When the ORDER BY clause specifies NULLS LAST, rows with NULL in the ORDER BY column are included in UNBOUNDED FOLLOWING frames.

  • Rows with NULL in the ORDER BY column are included in an explicit-offset frame boundary only when the ORDER BY value of the current row is NULL.

See RANGE BETWEEN examples with NULL values in the ORDER BY clause.

Usage notes for window frames

  • All window functions support window frames. However, support for different window frame syntax varies by function. If no window frame is specified, the default depends on the function:

    • For non-rank-related functions (such as COUNT, MAX, MIN, and SUM), the default is the following window frame (in accordance with the ANSI standard):

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      Copy

    • For rank-related functions (such as FIRST_VALUE, LAST_VALUE, NTH_VALUE), the default is the entire window:

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      Copy

      Note that this behavior does not comply with the ANSI standard.

      Note

      For clarity, Snowflake recommends avoiding implicit window frames. If your query uses a window frame, define an explicit window frame.

  • Window frames require the data in the window to be in a known order. Therefore, the ORDER BY clause inside the OVER clause is required for window frame syntax, even though that ORDER BY clause is generally optional.

  • For window frames with named start and end boundaries:

    • ROWS computes the result 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 result for rows that have the same value as the current row (according to the specified ORDER BY subclause).

  • For window frames with explicit offsets:

    • 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 functions that ignore NULL values

Some functions ignore NULL values. For example, AVG calculates the average of values 1, 5, and NULL to be 3, based on the following formula:

(1 + 5) / 2 = 3

In both the numerator and the denominator, only the non-NULL values are used.

If all of the values passed to the function are NULL, then the function returns NULL.

Some window functions can be passed more than one column. For example:

SELECT COUNT(col1, col2) FROM table1;
Copy

In these instances, the function ignores a row if any individual column is NULL.

For example, in the following query, COUNT returns 1, not 4, because three of the four rows contain at least one NULL value in the selected columns:

Create a table and populate it with values:

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.
Copy

Query the table:

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+
Copy

Similarly, if SUM is called with an expression that references two or more columns, and if one or more of those columns is NULL, then the expression evaluates to NULL, and the row is ignored:

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+
Copy

Note that this behavior differs from the behavior of GROUP BY, which does not discard rows when some columns are NULL:

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+
Copy

See also RANGE BETWEEN behavior when the ORDER BY expression contains NULL values.

General tips

  • The syntax shows all subclauses of the OVER clause as optional for window functions. This is by design (that is, you can use OVER without any subclauses inside the parentheses). This can be useful in specific scenarios (for example, turning off parallel processing).

  • PARTITION BY is not always compatible with GROUP BY.

  • If a query uses more than one window function, it typically should partition each function’s input data set the same way. For example, the first statement below is more likely to be correct than the second statement below:

    SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;

SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
    Copy

    The error message SQL compilation error: ... is not a valid group by expression is often a sign that different columns in the SELECT statement’s “project” clauses are not partitioned the same way and therefore might produce different numbers of rows.

Examples

Additional examples can be found in Using window functions.

Introductory example

Suppose that you own a chain of stores. The following query shows the percentage of the total chain’s profit generated by each store. The query uses the RATIO_TO_REPORT function, which takes a value (for example net_profit) from the current row and divides it by the sum of the corresponding values (net_profit) from all the other rows:

Create and load the table:

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;
Copy

Query the table:

SELECT branch_ID,
       net_profit,
       100 * RATIO_TO_REPORT(net_profit) OVER () AS percent_of_chain_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+------------+-------------------------+
| BRANCH_ID | NET_PROFIT | PERCENT_OF_CHAIN_PROFIT |
|-----------+------------+-------------------------|
|         1 |   10000.00 |             22.72727300 |
|         2 |   15000.00 |             34.09090900 |
|         3 |   10000.00 |             22.72727300 |
|         4 |    9000.00 |             20.45454500 |
+-----------+------------+-------------------------+
Copy

Cumulative window frame examples

Create and populate a table with values:

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

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (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);
Copy

Run a query that uses a cumulative window frame and show the output. 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,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_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 | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
Copy

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,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_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 | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Copy

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,
    AVG(i)   OVER (PARTITION BY p ORDER BY o) avg_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 | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
Copy

Sliding window frame examples

Create and populate a table with values:

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');
Copy

Return the min values for two columns (numeric and string) across sliding windows before, after, and encompassing the current row:

select p, o, i AS i_col,
    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_COL | 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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Copy

Return the max values for two columns (numeric and string) across sliding windows before, after, and encompassing the current row:

SELECT p, o, i AS i_col,
    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_COL | 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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
Copy

Return the sum of a number column across sliding windows before, after, and encompassing the current row:

SELECT p, o, r AS r_col,
    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_COL | 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 |
+-----+----+-------+-------------+-------------+-------------+
Copy

RANGE BETWEEN example with explicit numeric offsets

The following example uses RANGE BETWEEN syntax with explicit numeric offsets. To run this example, follow these instructions: Create and load the menu_items table. For a similar examples that use INTERVAL offsets, see Using windowed aggregations for rolling calculations.

The following query computes the average cost of goods sold for categories of menu items available from a food truck. The window function does not partition the results; therefore, the averages are computed across the complete result set, subject to a range-based frame.

The boundary of the frame is the cost of goods value in the current row, plus two (the first row = 0.50 + 2.00, for example). Rows qualify for the frame when they fall within this two-dollar range.

SELECT menu_category, menu_cogs_usd,
    AVG(menu_cogs_usd)
      OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  GROUP BY menu_category, menu_cogs_usd
  ORDER BY menu_category, menu_cogs_usd;
Copy
+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage      |          0.50 |  1.18333 |
| Beverage      |          0.65 |  1.37857 |
| Beverage      |          0.75 |  1.50000 |
| Dessert       |          0.50 |  1.18333 |
| Dessert       |          1.00 |  1.87500 |
| Dessert       |          1.25 |  2.05000 |
| Dessert       |          2.50 |  3.16666 |
| Dessert       |          3.00 |  3.50000 |
| Snack         |          1.25 |  2.05000 |
| Snack         |          2.25 |  2.93750 |
| Snack         |          4.00 |  4.00000 |
+---------------+---------------+----------+

For example, the avg_cogs value for the first row is 1.1833. This is computed as the sum of all the menu_cogs_usd values that fall between 0.50 and 2.50, divided by the count of those rows:

(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333

The second to last row has an avg_cogs value of 2.93750. This is computed as the sum of all the menu_cogs_usd values that fall between 2.25 and 4.25, divided by the count of those rows:

(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750

The last row returns 4.0 for both the avg_cogs and menu_cogs_usd. This result is accurate because only this row belongs to the frame; 4.0 is the maximum menu_cogs_usd value in the entire result, so it becomes a single-row frame. It has no “following” rows.

Note that this query has an ORDER BY clause for the window function and an ORDER BY clause for the final results of the query. The final ORDER BY output does not influence the calculation of the window function results. The ordered result set for computing the function is an intermediate result set that the final query does not display.

RANGE BETWEEN examples with NULL values in the ORDER BY clause

The nulls table contains five rows, and two have NULL values in the c1 column. Create and load the table as follows:

CREATE OR REPLACE TABLE nulls(c1 int, c2 int);

INSERT INTO nulls VALUES
  (1,10),
  (2,20),
  (3,30),
  (NULL,20),
  (NULL,50);
Copy

When NULLS LAST is specified, and the window frame uses explicit offsets, rows with NULL in c1 are included in the frame only when the ORDER BY value of the current row is NULL. The following query returns a sum of 50 when row 3 is the current row. The following NULL row is not included in the frame.

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
  FROM nulls;
Copy
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |     30 |
|             2 | 20 |     60 |
|             3 | 30 |     50 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

When NULLS LAST is specified, and the window frame uses UNBOUNDED FOLLOWING, rows with NULL in c1 are included in the frame. The following query returns a sum of 120 when row 3 is the current row. Both following NULL rows are included in the frame.

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
  FROM nulls;
Copy
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |    130 |
|             2 | 20 |    130 |
|             3 | 30 |    120 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

Create and load the heavy_weather table

To create and insert rows into the heavy_weather table, which is used in some window function examples, run this script.

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));

INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;
Copy
Language: English