Window function calls

A window function, also known as an analytic function, computes values over a group of rows and returns a single result for each row. This is different from an aggregate function, which returns a single result for a group of rows.

A window function includes an OVER clause, which defines a window of rows around the row being evaluated. For each row, the window function result is computed using the selected window of rows as input, possibly doing aggregation.

With window functions you can compute moving averages, rank items, calculate cumulative sums, and perform other analyses.

Window function syntax

function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

Description

A window function computes results over a group of rows. You can use the following syntax to build a window function:

  • function_name: The function that performs a window operation. For example, the numbering function RANK() could be used here.
  • argument_list: Arguments that are specific to the function. Some functions have them, some do not.
  • OVER: Keyword required in the window function syntax preceding the OVER clause.
  • over_clause: References a window that defines a group of rows in a table upon which to use a window function.
  • window_specification: Defines the specifications for the window.
  • window_frame_clause: Defines the window frame for the window.
  • rows_range: Defines the physical rows or a logical range for a window frame.

Notes

A window function can appear as a scalar expression operand in the following places in the query:

  • The SELECT list. If the window function appears in the SELECT list, its argument list and OVER clause can't refer to aliases introduced in the same SELECT list.
  • The ORDER BY clause. If the window function appears in the ORDER BY clause of the query, its argument list can refer to SELECT list aliases.
  • The QUALIFY clause.

A window function can't refer to another window function in its argument list or its OVER clause, even indirectly through an alias.

A window function is evaluated after aggregation. For example, the GROUP BY clause and non-window aggregate functions are evaluated first. Because aggregate functions are evaluated before window functions, aggregate functions can be used as input operands to window functions.

Returns

A single result for each row in the input.

Defining the OVER clause

function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

Description

The OVER clause references a window that defines a group of rows in a table upon which to use a window function. You can provide a named_window that is defined in your query, or you can define the specifications for a new window.

Notes

If neither a named window nor window specification is provided, all input rows are included in the window for every row.

Examples using the OVER clause

These queries use window specifications:

These queries use a named window:

Defining the window specification

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
  [ window_frame_clause ]

Description

Defines the specifications for the window.

  • PARTITION BY: Breaks up the input rows into separate partitions, over which the window function is independently evaluated.
    • Multiple partition expressions are allowed in the PARTITION BY clause.
    • An expression can't contain floating point types, non-groupable types, constants, or window functions.
    • If this optional clause is not used, all rows in the input table comprise a single partition.
  • ORDER BY: Defines how rows are ordered within a partition. This clause is optional in most situations, but is required in some cases for navigation functions.
  • window_frame_clause: For aggregate analytic functions, defines the window frame within the current partition. The window frame determines what to include in the window. If this clause is used, ORDER BY is required except for fully unbounded windows.

Notes

If neither the ORDER BY clause nor window frame clause are present, the window frame includes all rows in that partition.

For aggregate analytic functions, if the ORDER BY clause is present but the window frame clause is not, the following window frame clause is used by default:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

For example, the following queries are equivalent:

SELECT book, LAST_VALUE(item)
  OVER (ORDER BY year)
FROM Library
SELECT book, LAST_VALUE(item)
  OVER (
    ORDER BY year
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library

Rules for using a named window in the window specification

If you use a named window in your window specifications, these rules apply:

  • The specifications in the named window can be extended with new specifications that you define in the window specification clause.
  • You can't have redundant definitions. If you have an ORDER BY clause in the named window and the window specification clause, an error is thrown.
  • The order of clauses matters. PARTITION BY must come first, followed by ORDER BY and window_frame_