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
   | 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