Syntax for Window Functions ​

This section is an overview of the syntax supported for window functions. For more details, see the individual function descriptions.

Function Definition ​

function_name ([ expression ]) OVER { window_name | ( window_definition ) }

Some window functions support special syntax after the expression. See the individual function descriptions.

The window_name is a reusable window definition that is defined in the WINDOW clause of the query.

Window Definition ​

The window definition may consist of a PARTITION BY clause, an ORDER BY clause, and a frame clause. At a minimum, the OVER clause must have parentheses: OVER() without any parameters is valid syntax.

[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

Frame Clause ​

A window frame is a subset of rows in a partition (or the whole data set if no PARTITION BY clause is defined). The specific value that a window function returns for a given row depends on an evaluation of all the rows in the frame. For example, the frame may refer to a moving set of three rows, defined as rows between 1 preceding and 1 following, where the "current row" being evaluated is in the middle of the frame.

The frame_clause supports RANGE and ROWS modes with different options. If no frame clause is specified, the default frame for all window functions is as follows:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

which is equivalent to RANGE UNBOUNDED PRECEDING.

Range-Based Frames ​

The RANGE options are as follows:

RANGE UNBOUNDED PRECEDING
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

CURRENT ROW in RANGE mode refers to the first or last peer row in the partition. A peer is a repeating value in the ORDER BY column for the window function. (Note that in ROWS mode, CURRENT ROW simply means the current row.)

Rows-Based Frames ​

The ROWS options are as follows:

ROWS frame_start
ROWS BETWEEN frame_start AND frame_end

where frame_start is one of the following:

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING

and frame_end is one of the following:

value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

and value is an integer that defines a number of rows that precede or follow the current row.

In ROWS mode, CURRENT ROW simply means the current row.

Parent topic:Window Functions