SQL Window Functions
hop
and slide
. Streaming windows
allow aggregating over records with a time bound, while SQL window functions
allow you to reference other rows without aggregating.
A window function call always contains an OVER clause directly following the
window function’s name and argument(s). This is what syntactically distinguishes
it from a normal function or non-window aggregate. The OVER clause determines
exactly how the rows of the query are split up for processing by the window
function. The PARTITION BY clause within OVER divides the rows into groups, or
partitions, that share the same values of the PARTITION BY expression(s). For
each row, the window function is computed across the rows that fall into the
same partition as the current row. The previous example showed how to count the
average of a column per partition.
You can also control the order in which rows are processed by window functions
using ORDER BY within OVER. (The window ORDER BY does not even have to match the
order in which the rows are output.)
Note that currently window functions must contain a streaming window in the
PARTITION BY.
row_number
rank
dense_rank
ntile
cume_dist
percent_rank
lag
lead
first_value
last_value
nth_value