Window function calls in GoogleSQL

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:
  ( [ window_specification ] )

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

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.
  • argument_list: Arguments that are specific to the function. Some functions have them, some don't.
  • 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.

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.

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:
( [ 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 define the specifications for a new window.

Notes

If no window specification is provided, all input rows are included in the window for every row.

Defining the window specification

window_specification:

  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC } ] [, ...] ]

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 isn't used, all rows in the input table comprise a single partition.
  • ORDER BY: Defines how rows are ordered within a partition.

Notes

Hints are supported on the PARTITION BY clause and the ORDER BY clause.