On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing aggregate function information as a scalar value in a query result.

OLAP-specification |--+-| ordered-OLAP-specification |-+---------------------------| +-| numbering-specification |----+ '-| aggregation-specification |--' ordered-OLAP-specification |--+-| lag-function |--+--OVER----------------------------------> +-| lead-function |-+ +-RANK ()-----------+ '-DENSE_RANK ()-----' >--(--+-----------------------------+---------------------------> '-| window-partition-clause |-' >--| window-order-clause |--)-----------------------------------| lag-function |--LAG--(--expression-------------------------------------------> >--+-------------------------------------------------------------+--)--| '-,--offset--+----------------------------------------------+-' '-,--default-value--+------------------------+-' '-,--+-'RESPECT NULLS'-+-' '-'IGNORE NULLS'--' lead-function |--LEAD--(--expression------------------------------------------> >--+-------------------------------------------------------------+--)--| '-,--offset--+----------------------------------------------+-' '-,--default-value--+------------------------+-' '-,--+-'RESPECT NULLS'-+-' '-'IGNORE NULLS'--' window-partition-clause .-,-----------------------. V | |--PARTITION BY----partitioning-expression-+--------------------| window-order-clause .-,--------------------------------------------. V | |--ORDER BY----+-sort-key-expression--+-| asc-option |--+-+-+---| | '-| desc-option |-' | '-ORDER OF--table-designator---------------' asc-option .-ASC-. .-NULLS LAST--. |--+-----+--+-------------+-------------------------------------| '-NULLS FIRST-' desc-option .-NULLS FIRST-. |--DESC--+-------------+----------------------------------------| '-NULLS LAST--' numbering-specification |--ROW_NUMBER ()--OVER--(--+-----------------------------+------> '-| window-partition-clause |-' >--+-------------------------+--)-------------------------------| '-| window-order-clause |-' aggregation-specification (1) |--+-column-function----------+--OVER---------------------------> '-| OLAP-column-function |-' >--(--+-----------------------------+---------------------------> '-| window-partition-clause |-' .-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING----------------------. >--+--------------------------------------------------------------------------------+--> | .-RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW-. | '-| window-order-clause |--+---------------------------------------------------+-' '-| window-aggregation-group-clause |---------------' >--)------------------------------------------------------------| OLAP-column-function |--+-| first-value-function |-----+-----------------------------| +-| last-value-function |------+ '-| ratio-to-report-function |-' first-value-function |--FIRST_VALUE--(--expression--+------------------------+--)----| '-,--+-'RESPECT NULLS'-+-' '-'IGNORE NULLS'--' last-value-function |--LAST_VALUE--(--expression--+------------------------+--)-----| '-,--+-'RESPECT NULLS'-+-' '-'IGNORE NULLS'--' ratio-to-report-function |--RATIO_TO_REPORT--(--expression--)----------------------------| window-aggregation-group-clause |--+-ROWS--+--+-| group-start |---+-----------------------------| '-RANGE-' +-| group-between |-+ '-| group-end |-----' group-start |--+-UNBOUNDED PRECEDING----------+-----------------------------| +-unsigned-constant--PRECEDING-+ '-CURRENT ROW------------------' group-between |--BETWEEN--| group-bound1 |--AND--| group-bound2 |-------------| group-bound1 |--+-UNBOUNDED PRECEDING----------+-----------------------------| +-unsigned-constant--PRECEDING-+ +-unsigned-constant--FOLLOWING-+ '-CURRENT ROW------------------' group-bound2 |--+-UNBOUNDED FOLLOWING----------+-----------------------------| +-unsigned-constant--PRECEDING-+ +-unsigned-constant--FOLLOWING-+ '-CURRENT ROW------------------' group-end |--+-UNBOUNDED FOLLOWING----------+-----------------------------| '-unsigned-constant--FOLLOWING-'

Notes:

- ARRAY_AGG is not supported as an aggregate function in
`aggregation-specification`(SQLSTATE 42887).

An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used within an argument to an XMLQUERY or XMLEXISTS expression (SQLSTATE 42903). An OLAP function cannot be used as an argument of an aggregate function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP function.

When specifying an OLAP function, a window is specified that defines the rows over which the function is applied, and in what order. When used with an aggregate function, the applicable rows can be further refined, relative to the current row, as either a range or a number of rows preceding and following the current row. For example, within a partition by month, an average can be calculated over the previous three month period.

The ranking function computes the ordinal rank of a row within the window. Rows that are not distinct with respect to the ordering within their window are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values.

If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus, if two or more rows are not distinct with respect to the ordering, then there will be one or more gaps in the sequential rank numbering.

If DENSE_RANK (or DENSERANK) is specified, the rank of a row is defined as 1 plus the number of preceding rows that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering.

The ROW_NUMBER (or ROWNUMBER) function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order, as returned by the subselect (not according to any ORDER BY clause in the select-statement).

If the FETCH FIRST n ROWS ONLY clause is used along with the ROW_NUMBER function, the row numbers might not be displayed in order. The FETCH FIRST clause is applied after the result set (including any ROW_NUMBER assignments) is generated; therefore, if the row number order is not the same as the order of the result set, some assigned numbers might be missing from the sequence.

The data type of the result of RANK, DENSE_RANK or ROW_NUMBER is BIGINT. The result cannot be null.

The
LAG function returns the expression value for the row at `offset` rows
before the current row. The `offset` must be a positive integer constant (SQLSTATE 42815).
An `offset` value of 0 means the current row. If
a window-partition-clause is specified, `offset` means `offset` rows
before the current row and within the current partition. If `offset` is
not specified, the value 1 is used. If `default-value` (which
can be an expression) is specified, it will be returned if the
offset goes beyond the scope of the current partition. Otherwise,
the null value is returned. If 'IGNORE NULLS' is specified, all rows
where the expression value for the row is the null value are not considered
in the calculation. If 'IGNORE NULLS' is specified and all rows are
null, `default-value` (or the null value if `default-value` was
not specified) is returned.

The
LEAD function returns the expression value for the row at `offset` rows
after the current row. The `offset` must be a positive integer constant (SQLSTATE 42815).
An `offset` value of 0 means the current row. If
a window-partition-clause is specified, `offset` means `offset` rows
after the current row and within the current partition. If `offset` is
not specified, the value 1 is used. If `default-value` (which
can be an expression) is specified, it will be returned if the
offset goes beyond the scope of the current partition. Otherwise,
the null value is returned. If 'IGNORE NULLS' is specified, all rows
where the expression value for the row is the null value are not considered
in the calculation. If 'IGNORE NULLS' is specified and all rows are
null, `default-value` (or the null value if `default-value` was
not specified) is returned.

The FIRST_VALUE function returns the expression value for the first row in an OLAP window. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all values in the OLAP window are null, FIRST_VALUE returns the null value.

The LAST_VALUE function returns the expression value for the last row in an OLAP window. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all values in the OLAP window are null, LAST_VALUE returns the null value.

The data type of the result of FIRST_VALUE, LAG, LAST_VALUE, and LEAD is the data type of the expression. The result can be null.

The
RATIO_TO_REPORT function returns the ratio of an argument to the sum
of the arguments in an OLAP partition. For example, the following
functions are equivalent:

```
RATIO_TO_REPORT(expression) OVER (...)
CAST(expression AS DECFLOAT(34)) / SUM(expression) OVER(...)
```

The
division is always performed using DECFLOAT(34). The result data type
is DECFLOAT(34). If the argument can be null, the result can be null;
if the argument is null, the result is the null value.- PARTITION BY (partitioning-expression,...)
- Defines the partition within which the function is applied. A partitioning-expression is an expression that is used in defining the partitioning of the result set. Each column-name that is referenced in a partitioning-expression must unambiguously reference a column of the result table of the subselect that contains the OLAP specification (SQLSTATE 42702 or 42703). A partitioning-expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822), or any function or query that is not deterministic or that has an external action (SQLSTATE 42845).
- window-order-clause
- ORDER BY (sort-key-expression,...)
- Defines the ordering of rows within a partition that determines the value of the OLAP function or the meaning of the ROW values in the window-aggregation-group-clause (it does not define the ordering of the query result set).
- sort-key-expression
- An expression used in defining the ordering of the rows within a window partition. Each column name referenced in a sort-key-expression must unambiguously reference a column of the result set of the subselect, including the OLAP function (SQLSTATE 42702 or 42703). A sort-key-expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822), or any function or query that is not deterministic or that has an external action (SQLSTATE 42845). This clause is required for the RANK and DENSE_RANK functions (SQLSTATE 42601).
- ASC
- Uses the values of the sort-key-expression in ascending order.
- DESC
- Uses the values of the sort-key-expression in descending order.
- NULLS FIRST
- The window ordering considers null values
*before*all non-null values in the sort order. - NULLS LAST
- The window ordering considers null values
*after*all non-null values in the sort order. - ORDER OF table-designator
- Specifies that the same ordering used in table-designator should be applied to the result table of the subselect. There must be a table reference matching table-designator in the FROM clause of the subselect that specifies this clause (SQLSTATE 42703). The ordering that is applied is the same as if the columns of the ORDER BY clause in the nested subselect (or fullselect) were included in the outer subselect (or fullselect), and these columns were specified in place of the ORDER OF clause.

- window-aggregation-group-clause
- The aggregation group of a row R is a set of rows defined in relation
to R (in the ordering of the rows of R's partition). This clause specifies
the aggregation group. If this clause is not specified and a window-order-clause
is also not specified, the aggregation group consists of all the rows
of the window partition. This default can be specified explicitly
using RANGE (as shown) or ROWS.
If window-order-clause is specified, the default behavior is different when window-aggregation-group-clause is not specified. The window aggregation group consists of all rows of the partition of R that precede R and that are peers of R in the window ordering of the window partition defined by the window-order-clause.

- ROWS
- Indicates the aggregation group is defined by counting rows.
- RANGE
- Indicates the aggregation group is defined by an offset from a sort key.
- group-start
- Specifies the starting point for the aggregation group. The aggregation group end is the current row. Specification of the group-start clause is equivalent to a group-between clause of the form "BETWEEN group-start AND CURRENT ROW".
- group-between
- Specifies the aggregation group start and end based on either ROWS or RANGE.
- group-end
- Specifies the ending point for the aggregation group. The aggregation group start is the current row. Specification of the group-end clause is equivalent to a group-between clause of the form "BETWEEN CURRENT ROW AND group-end".
- UNBOUNDED PRECEDING
- Includes the entire partition preceding the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.
- UNBOUNDED FOLLOWING
- Includes the entire partition following the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.
- CURRENT ROW
- Specifies the start or end of the aggregation group based on the current row. If ROWS is specified, the current row is the aggregation group boundary. If RANGE is specified, the aggregation group boundary includes the set of rows with the same values for the sort-key-expressions as the current row. This clause cannot be specified in group-bound2 if group-bound1 specifies value FOLLOWING.
- unsigned-constant PRECEDING
- Specifies either the range or number of rows preceding the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or unsigned-constant FOLLOWING.
- unsigned-constant FOLLOWING
- Specifies either the range or number of rows following the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

- Display the ranking of employees, in order by surname, according
to their total salary (based on salary plus bonus) that have a total
salary more than $30,000.
**SELECT**EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS**AS**TOTAL_SALARY,**RANK() OVER (ORDER BY**SALARY+BONUS**DESC) AS**RANK_SALARY**FROM**EMPLOYEE**WHERE**SALARY+BONUS > 30000**ORDER BY**LASTNAMENote that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:

or**ORDER BY**RANK_SALARY**ORDER BY RANK() OVER (ORDER BY**SALARY+BONUS**DESC)** - Rank the departments according to their average total salary.
**SELECT**WORKDEPT, AVG(SALARY+BONUS)**AS**AVG_TOTAL_SALARY,**RANK() OVER (ORDER BY AVG(**SALARY+BONUS**) DESC) AS**RANK_AVG_SAL**FROM**EMPLOYEE**GROUP BY**WORKDEPT**ORDER BY**RANK_AVG_SAL - Rank the employees within a department according to their education
level. Having multiple employees with the same rank in the department
should not increase the next ranking value.
**SELECT**WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL,**DENSE_RANK() OVER (PARTITION BY**WORKDEPT**ORDER BY**EDLEVEL**DESC) AS**RANK_EDLEVEL**FROM**EMPLOYEE**ORDER BY**WORKDEPT, LASTNAME - Provide row numbers in the result of a query.
**SELECT ROW_NUMBER() OVER (ORDER BY**WORKDEPT, LASTNAME**) AS**NUMBER, LASTNAME, SALARY**FROM**EMPLOYEE**ORDER BY**WORKDEPT, LASTNAME - List the top five wage earners.

Note that a nested table expression was used to first compute the result, including the rankings, before the rank could be used in the WHERE clause. A common table expression could also have been used.**SELECT**EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY**FROM (SELECT**EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS**AS**TOTAL_SALARY,**RANK() OVER (ORDER BY**SALARY+BONUS**DESC) AS**RANK_SALARY**FROM**EMPLOYEE**) AS**RANKED_EMPLOYEE**WHERE**RANK_SALARY < 6**ORDER BY**RANK_SALARY - For each department, list employee salaries and show how much
less each person makes compared to the employee in that department
with the next highest salary.
**SELECT**EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY,**LEAD(**SALARY, 1**) OVER (PARTITION BY**WORKDEPT**ORDER BY**SALARY**)**- SALARY**AS**DELTA_SALARY**FROM**EMPLOYEE**ORDER BY**WORKDEPT, SALARY - Calculate an employee's salary relative to the salary of the employee
who was first hired for the same type of job.
**SELECT**JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY,**FIRST_VALUE(**SALARY**) OVER (PARTITION BY**JOB**ORDER BY**HIREDATE)**AS**FIRST_SALARY, SALARY -**FIRST_VALUE(**SALARY**) OVER (PARTITION BY**JOB**ORDER BY**HIREDATE**) AS**DELTA_SALARY**FROM**EMPLOYEE**ORDER BY**JOB, HIREDATE - Calculate the average close price for stock XYZ during the month
of January, 2006. If a stock doesn't trade on a given day, its close
price in the DAILYSTOCKDATA table is the null value. Instead of returning
the null value for days that a stock doesn't trade, use the COALESCE
function and LAG function to return the close price for the most recent
day the stock was traded. Limit the search for a previous not-null
close value to one month before January 1st, 2006.
**WITH V1(**SYMBOL, TRADINGDATE, CLOSEPRICE**) AS ( SELECT**SYMBOL, TRADINGDATE,**COALESCE(**CLOSEPRICE,**LAG(**CLOSEPRICE, 1,**CAST(NULL AS DECIMAL(8,2)), 'IGNORE NULLS') OVER (PARTITION BY**SYMBOL**ORDER BY**TRADINGDATE**) ) FROM**DAILYSTOCKDATA**WHERE**SYMBOL**=**'XYZ'**AND**TRADINGDATE**BETWEEN**'2005-12-01'**AND**'2006-01-31'**) SELECT**SYMBOL**, AVG(**CLOSEPRICE**) AS AVG FROM V1 WHERE**TRADINGDATE**BETWEEN**'2006-01-01'**AND**'2006-01-31'**GROUP BY**SYMBOL - Calculate the 30-day moving average for stocks ABC and XYZ during
the year 2005.
**WITH V1(**SYMBOL, TRADINGDATE, MOVINGAVG30DAY**) AS ( SELECT**SYMBOL, TRADINGDATE,**AVG(**CLOSEPRICE**) OVER (PARTITION BY**SYMBOL**ORDER BY**TRADINGDATE**ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) FROM**DAILYSTOCKDATA**WHERE**SYMBOL**IN ('ABC', 'XYZ') AND**TRADINGDATE**BETWEEN DATE(**'2005-01-01'**) - 2 MONTHS AND**'2005-12-31'**) SELECT**SYMBOL, TRADINGDATE, MOVINGAVG30DAY**FROM V1 WHERE**TRADINGDATE**BETWEEN**'2005-01-01'**AND**'2005-12-31'**ORDER BY**SYMBOL, TRADINGDATE - Use an expression to define the cursor position and query a sliding
window of 50 rows before that position.
**SELECT**DATE,**FIRST_VALUE(**CLOSEPRICE + 100**) OVER****(PARTITION BY**SYMBOL**ORDER BY**DATE**ROWS BETWEEN**50**PRECEDING AND**1**PRECEDING) AS FV****FROM**DAILYSTOCKDATA**ORDER BY**DATE - For
each employee, calculate the average salary for the set of employees
that includes those employees in the same department who have an education
level 1 lower and 1 higher than the employee.
**SELECT**WORKDEPT, EDLEVEL, SALARY,**AVG(**SALARY**)****OVER****(PARTITION BY**WORKDEPT**ORDER BY**EDLEVEL**RANGE BETWEEN**1**PRECEDING AND**1**FOLLOWING)****FROM**EMPLOYEE**ORDER BY**WORKDEPT, EDLEVEL