Window functions
A window function is written name(args) OVER (PARTITION BY ... ORDER BY ... frame) and returns one value for every input row, computed from the rows around it. WINDOW w AS (...) names a window that several functions share.
| Name | Arguments | Description |
|---|---|---|
row_number | The position of the row in its partition, from 1, in the order of the window. | |
rank | The position of the row's peer group, with a gap after a tie: two rows that tie for 1 are followed by 3. | |
dense_rank | The ordinal of the row's peer group, without a gap after a tie. | |
ntile | n | The bucket, from 1 to n, that the row falls in when the partition is cut into n buckets of equal size. |
percent_rank | (rank - 1) / (rows in the partition - 1), and 0 for a partition of one row. | |
cume_dist | The number of rows up to the end of the row's peer group, divided by the rows in the partition. | |
lag | x, offset, default | The value of x in the row offset positions before this one in the partition, or default, or NULL. With an explicit frame it reads within the frame. The offset is 1 when left out. |
lead | x, offset, default | The value of x in the row offset positions after this one in the partition, or default, or NULL. With an explicit frame it reads within the frame. The offset is 1 when left out. |
lagInFrame | x, offset, default | As lag, but always within the frame, also the default frame. |
leadInFrame | x, offset, default | As lead, but always within the frame, also the default frame. |
first_value | x | The value of x in the first row of the frame. |
last_value | x | The value of x in the last row of the frame. Under the default frame that is the last row of the current peer group. |
nth_value | x, n | The value of x in the n-th row of the frame, counted from 1, or NULL when the frame is shorter. |
Aggregates as windows
sum, avg, count, min and max, and the other aggregate functions that have an aggregate state, such as uniq, groupArray, argMax and quantile, also run as window functions and aggregate the rows of the frame. A combinator suffix such as -If or -State is not accepted in a window.
Frames
Without a frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when the window has an ORDER BY, so it ends with the current row's peer group, and the whole partition when it has none. An explicit frame is ROWS or RANGE BETWEEN a start and an end, each of UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING. A RANGE frame with an offset needs a single numeric ORDER BY column. percent_rank and cume_dist take no frame other than the whole partition. A negative offset in lag or lead is an error.