Window functions

Window functions are functions applied over a subset (known as a window) of the rows returned by a SELECT query.

Read more about Window functions in the SQL syntax features section.

Syntax

window_expr ::=
   window_fn ( [ value_expr [, ...] ] )
      OVER (
         [ PARTITION BY value_expression [, ...] ]
         [ ORDER BY value_expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
         [ frame_clause ]
      )

window_fn ::=
   | AVG
   | COUNT
   | LAG
   | LEAD
   | MAX
   | MIN
   | RANK
   | ROW_NUMBER
   | SUM


frame_clause ::=
   { RANGE | ROWS } frame_start [ frame_exclusion ]
   | { RANGE | ROWS } BETWEEN frame_def AND frame_def [ frame_exclusion ]

frame_def ::=
   UNBOUNDED PRECEDING
   offset PRECEDING
   CURRENT ROW
   offset FOLLOWING
   UNBOUNDED FOLLOWING

offset ::=
   EXCLUDE CURRENT ROW
   | EXCLUDE GROUP
   | EXCLUDE TIES
   | EXCLUDE NO OTHERS

row_offset ::= numeric literal

Arguments

Parameter

Description

window_fn

Window function, like AVG, MIN, etc.

PARTITION BY partition_expr

An expression or column reference to partition by

ORDER BY order

An expression or column reference to order by

Supported window functions

Window function aggregations

Function

AVG

COUNT

MAX

MIN

SUM

Changed in version 2020.1: COUNT and AVG are supported in window functions from v2020.1.

Ranking functions

Function

LAG

LEAD

RANK

ROW_NUMBER

Changed in version 2020.2: LAG and LEAD are supported from v2020.2.