Window analytic functions

The analytic functions include window aggregation, reporting aggregation, lag and lead, first and last, ranking, and row count families. The following table describes the analytic function keywords.
Table 1. Analytic function keywords
Syntax Description
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.