Window Function Syntax
A window function has the following format:
window_function OVER window_specification
where:
window_function
Specifies an analytical function or an aggregate function.
A select list can have more than one function, each with a different OVER clause.
OVER window_specification
Specifies a named window specification defined elsewhere in the query or a parenthesized optional partitioning definition, ordering definition, and/or frame definition:
([PARTITION BY partitioning_list ] [ORDER BY sort_specification_list] [ROWS BETWEEN frame_bound1 AND frame_bound2])
PARTITION BY partitioning_list
Specifies a list of column names or expressions.
ORDER BY sort_specification_list
Specifies a list of column names or expressions optionally qualified by the ASC or DESC sort directions.
Note: ORDER BY for an aggregate function is not supported.
ROWS BETWEEN frame_bound1 AND frame_bound2
Specifies the set of rows in the current partition over which the window function is computed.
Window functions that support frame definitions include the aggregates, FIRST_VALUE, and LAST_VALUE.
Aggregates support the following options:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
FIRST_VALUE and LAST_VALUE support the option:
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Note: ORDER BY is required with frame specifications, even for aggregate functions.
Note: DISTINCT is not allowed for COUNT, SUM, and AVG if the window specification includes an ORDER BY or a frame definition.
An empty window specification, that is, OVER(), causes the function results to be computed across all rows in the table.