ALL |
Applies the analytic function to all values. This value is
the default and you do not need to specify it. |
ASC | DESC |
Specifies the ordering sequence, either ascending or descending. |
BETWEEN … AND |
Specifies starting and ending points for the window. The first
expression (before and) specifies the start; the second expression
(after and) specifies the end. |
CURRENT ROW |
As a starting point, specifies that the window begins at the
current row or value. As an ending point, specifies that the window
ends at the current row or value. |
DISTINCT |
Specifies that the function aggregates results for each unique
value. DISTINCT is not supported for the FIRST_VALUE, LAST_VALUE,
LEAD, or LAG functions. It is also not supported for STDDEV, STDDEV_POP,
STDDEV_SAMP, VARIANCE, VAR_POP, OR VAR_SAMP functions in either a
grouped or windowed aggregate. |
EXCLUDE CURRENT ROW |
Specifies excluding the current row. |
EXCLUDE GROUP |
Specifies excluding the current row and all rows that are tied
with it. Ties occur when there is a match on the order column or columns. |
EXCLUDE NO OTHERS |
Specifies not excluding any rows. This value is the default
if you specify no exclusion. |
EXCLUDE TIES |
Specifies excluding all rows that are tied with the current
row (peer rows), but retaining the current row. |
NULLS {FIRST | LAST} |
Specifies whether nulls display before or after non-null values
in the sort ordering. By default, null values sort as if they are
lower than any non-null value; that is, NULLS FIRST is the default
for DESC order, and NULLS LAST otherwise. |
ORDER BY |
Specifies how the data is ordered within the partition. You
can order the values on multiple keys, each defined by an ordering
sequence. |
OVER |
Indicates that the function operates on a query result set
that is computed after the FROM, WHERE, and HAVING clauses. Use over
to define the window of rows to include in the function. |
PARTITION BY |
Partitions the query result into groups that are based on one
or more columns. If you omit this clause, the query handles the query
result as a single partition. |
ROWS | RANGE |
Defines the window as physical rows or as a logical range.
To use range between, you must specify the order by clause. |
UNBOUNDED FOLLOWING |
Specifies that the window ends at the last row of the partition.
If there is no partition by clause, the end is the last row of the
data set. |
UNBOUNDED PRECEDING |
Specifies that the window starts at the first row of the partition.
If there is no partition by clause, the start is the first row of
the data set. |