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 frame_start
RANGE BETWEEN frame_start AND frame_end
where frame_start is one of the following:
UNBOUNDED PRECEDING
CURRENT ROW
and frame_end is one of the following:
CURRENT ROW
UNBOUNDED FOLLOWING

CURRENT ROW in RANGE mode refers to the first (frame_start) or last (frame_end) 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.