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.