Table of Contents
Window Functions
Grammar
window_function
: window_function_type OVER window_name_or_specification
window_function_type
: { ROW_NUMBER | RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST } ()
| { SUM | AVG | MIN | MAX } ( COLUMN )
window_name_or_specification
: window_name
| window_specification
window_specification
: [existing_window_name]
[window_partition_clause]
[window_order_clause]
[window_frame_clause]
window_partition_clause
: PARTITION BY column_reference, ...
window_order_clause
: ORDER BY column_reference [ASC | DESC] [ NULLS { FIRST | LAST } ], ...
window_frame_clause
: { ROWS | RANGE } frame_start
| { ROWS | RANGE } BETWEEN frame_start AND frame_end
frame_start, frame_end
: UNBOUNDED PRECEDING
| uint PRECEDING
| CURRENT ROW
| uint FOLLOWING
| UNBOUNDED FOLLOWING
See also: window_specification
Description
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row as non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
Window function calls are permitted only in the SELECT list of the query.
Functions
| Function | Return Type | Description |
|---|---|---|
| row_number() | uint64 | number of the current row within its partition, counting from 1 |
| rank() | uint64 | rank of the current row with gaps; same as row_number of its first peer |
| dense_rank() | uint64 | rank of the current row without gaps; this function counts peer groups |
| percent_rank() | double | relative rank of the current row: (rank - 1) / (total partition rows - 1) |
| cume_dist() | double | cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows |
Functions, listed in the above table, depend on the sort ordering specified by the ORDER BY clause of the associated window definition.
Aggregative Functions
When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame.