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 |