OLAP specification

Start of change Online analytical processing (OLAP) specifications provide the ability to return ranking, row numbering, and aggregation information as a scalar value in the result of a query. An OLAP specification can be included in an expression, in a select-list, or in the ORDER BY clause of a select-statement. The query result to which the OLAP specifications is applied is the result table of the innermost subselect that includes the OLAP specification. End of change

OLAP-specification

>>-+-| ordered-OLAP-specification |----------------------------+-><
   +-| numbering-specification |-------------------------------+   
   '-| Start of changeaggregation-specificationEnd of change |-'

ordered-OLAP-specification

>>-+-RANK--(--)-------+--OVER--(--+-----------------------------+--| window-order-clause |--)-><
   '-DENSE_RANK--(--)-'           '-| window-partition-clause |-'

numbering-specification

>>-ROW_NUMBER--(--)--OVER--(--+-----------------------------+--+-------------------------+--)-><
                              '-| window-partition-clause |-'  '-| window-order-clause |-'

aggregation-specification

>>-| aggregate-function |--OVER--(--+-----------------------------+-->
                                    '-| window-partition-clause |-'   

   .-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING-------------------------.   
>--+-----------------------------------------------------------------------------------+-><
   |                          .-RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW-.    |   
   '-| window-order-clause |--+---------------------------------------------------+--)-'   
                              '-| window-aggregation-group-clause |---------------'

window-partition-clause

                 .-,-----------------------.   
                 V                         |   
>>-PARTITION BY----partitioning-expression-+-------------------><

window-order-clause

             .-,----------------------------------------------.   
             |                             .-NULLS LAST-.     |   
             V                      .-ASC--+------------+---. |   
>>-ORDER BY----sort-key-expression--+-----------------------+-+-><
                                    +-ASC NULLS FIRST-------+     
                                    |       .-NULLS FIRST-. |     
                                    +-DESC--+-------------+-+     
                                    '-DESC NULLS LAST-------'

aggregate-function

                            (1)   
>>-+-AVG function---------+------------------------------------><
   +-CORRELATION function-+       
   +-COUNT function-------+       
   +-COUNT_BIG function---+       
   +-COVARIANCE function--+       
   +-MAX function---------+       
   +-MIN function---------+       
   +-STDDEV function------+       
   +-SUM function---------+       
   '-VARIANCE function----'

Notes:

You cannot specify DISTINCT or ALL for an aggregate function that is included in an aggregation-specification.

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-bound-1 |--AND--| group-bound-2 |----------><

group-bound-1

>>-+-UNBOUNDED PRECEDING----------+----------------------------><
   +-unsigned-constant--PRECEDING-+   
   +-unsigned-constant--FOLLOWING-+   
   '-CURRENT ROW------------------'

group-bound-2

>>-+-UNBOUNDED FOLLOWING----------+----------------------------><
   +-unsigned-constant--PRECEDING-+   
   +-unsigned-constant--FOLLOWING-+   
   '-CURRENT ROW------------------'

group-end

>>-+-UNBOUNDED FOLLOWING----------+----------------------------><
   '-unsigned-constant--FOLLOWING-'

RANK, DENSE_RANK, and ROW_NUMBER are sometimes called window functions.

An OLAP specification is not valid in a WHERE, VALUES, GROUP BY, HAVING, or SET clause. An OLAP specification cannot be used as an argument of an aggregate function.

When invoking an OLAP specification, a window is specified that defines the rows over which the function is applied and in which order.

The result of a RANK, DENSE_RANK, or ROW_NUMBER specification is BIGINT. The result cannot be null.

RANK or DENSE_RANK

Specifies that the ordinal rank of a row within the specified window is computed. Rows that are not distinct with respect to the ordering within the specified window are assigned the same rank. The results of the ranking can be defined with or without gaps in the numbers that result from duplicate values.

RANK: Specifies that 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, there will be one or more gaps in the sequential rank numbering.
DENSE_RANK: Specifies that 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.

ROW_NUMBER

Specifies that a sequential row number is computed for the row that is 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 an arbitrary order, as the rows are returned (not according to any ORDER BY clause in the select-statement).

PARTITION BY (partitioning-expression,...)

Defines the partition within which the OLAP operation is applied. A partitioning-expression is an expression that is used in defining the partitioning of the result table. 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. A partitioning-expression cannot include a scalar-fullselect Start of change

an XMLQUERY or XMLEXISTS expression End of change

or any function that is not deterministic or has an external action.

ORDER BY (sort-key-expression,...)

Defines the ordering of rows within a partition that is used to determine the value of the OLAP specification. It does not define the ordering of the result table.

sort-key-expression: Specifies an expression to use in defining the ordering of the rows within a window partition. Each column name that is referenced in a sort-key-expression must unambiguously reference a column of the result table of the subselect, including the OLAP specification. A sort-key-expression cannot include a scalar fullselect, an XMLQUERY or XMLEXISTS expression, or any function that is not deterministic or that has an external action.
ASC: Specifies that the values of sort-key-expression are used in ascending order.
DESC: Specifies that the values of sort-key-expression are used in descending order.
NULLS FIRST: Specifies that the window ordering considers null values before all non-null values in the sort order.
NULLS LAST: Specifies that the window ordering considers null values after all non-null values in the sort order.

window-aggregation-group-clause

The aggregation group of a given row is a set of rows that is defined in relation to the given row (in the ordering of the rows in the partition of the given row). window-aggregation-group-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 rows of the window partition. The aggregation group of all rows of the window partition can be explicitly specified using the RANGE or ROWS clauses.

If window-order-clause is specified, but window-aggregation-group-clause is not specified, the window aggregation group consists of all rows that precede a given row of the partition of the given row or all rows that are peers of the given row in the window ordering of the window partition that is defined by the window-order-clause

ROW: Specifies that the aggregation group is defined by counting rows.
RANGE: Specifies that 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. Specifying group-start is equivalent to specifying group-between as BETWEEN group-start AND CURRENT ROW.
group-between: Specifies that 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. Specifying group-end is equivalent to specifying group-between as BETWEEN CURRENT ROW AND group-end.
UNBOUNDED PRECEDING: Specifies that the entire partition that precedes the current row is included in the aggregation group. This can be specified with either the ROWS or RANGE clauses. Including the entire partition that precedes the current row can also be specified with multiple sort-key-expressions in the window-order-clause.
UNBOUNDED FOLLOWING: Specifies that the entire partition that follows the current row is included in the aggregation group. This can be specified with either the ROWS or RANGE clauses. Including the entire partition that follows the current row can also be specified with multiple sort-key-expressions in the window-order-clause.
CURRENT ROW: Specifies that the aggregation group starts or ends 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 values specified for the sort-key-expression as the current row. This clause cannot be specified in group-bound-2 if group-bound-1 specifies unsigned-constant FOLLOWING.
unsigned-constant PRECEDING: Specifies either the range or the number of rows that precede the current row. If ROWS is specified, unsigned-constant must be zero or a positive integer that indicates a number of rows. If RANGE is specified, the data type of unsigned-constant must be comparable to the data type of the sort-key-expression of the window-order-clause. Only one sort-key-expression is allowed, and the data type of sort-key-expression must allow subtraction. This clause cannot be specified in group-bound-2 if group-bound-1 is CURRENT ROW or unsigned-constant FOLLOWING.
unsigned-constant FOLLOWING: Specifies either the range or the number of rows that follow the current row. If ROWS is specified, unsigned-constant must be zero or a positive integer that indicates a number of rows. If RANGE is specified, the data type of unsigned-constant must be comparable to the data type of the sort-key-expression of the window-order-clause. Only one sort-key-expression is allowed, and the data type of sort-key-expression must allow addition.

Notes

Start of change Using a column mask with an OLAP specification: If a column mask is used to mask the column values in the final result table and an OLAP specification is referenced in the select list that is used to derive the final result table, the column mask cannot be applied to the column that is specified in the partitioning-expression or the sort-key-expression in the OLAP specification. End of change

Syntax alternatives and synonyms: For compatibility, the keywords DENSERANK and ROWNUMBER can be used as synonyms for DENSE_RANK and ROW_NUMBER respectively.

Example 1: Display the ranking of employees that have a total salary of more than $30,000, in order by last name:

SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
  RANK() OVER(ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
  FROM EMP WHERE SALARY+BONUS > 30000
  ORDER BY LASTNAME;

If the result is to be ordered by rank, ORDER BY LASTNAME would be replaced with ORDER BY RANK_SALARY.

Example 2: 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 EMP
  GROUP BY WORKDEPT
  ORDER BY RANK_AVG_SAL;

Example 3: Rank the departments 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 EMP
  ORDER BY WORKDEPT, LASTNAME;

Example 4: Provide row numbers in the results of a query:

SELECT ROW_NUMBER() OVER(ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
  LASTNAME, SALARY
  FROM EMP
  ORDER BY WORKDEPT, LASTNAME;

Example 5: List the top five wage earners:

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 EMP) AS RANKED_EMPLOYEE
  WHERE RANK_SALARY < 6
  ORDER BY RANK_SALARY;

A nested table expression is used to first compute the result, including the ranking, before the rank can be used in the WHERE clause. A common table expression could also have been used.

Example 6: The following example is used to calculate the 30 day moving average for the stocks 'ABC' and 'XYX' during 2005:

CREATE VIEW V1 AS
	SELECT SYMBOL, TRADINGDATE,
				AVG(CLOSINGPRICE) 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;