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 function, like |
|
An expression or column reference to partition by |
|
An expression or column reference to order by |