OLAP window expressions

You can include On-Line Analytical Processing (OLAP) expressions in a SELECT statement to operate on subsets of the rows in the result set of a query or subquery. You can use OLAP window expressions to examine subsets of the qualifying rows for patterns, trends, or exceptions in the data.

OLAP window expressions allow application developers to compose analytic business queries more easily and efficiently. For example, moving averages and moving sums can be calculated over various intervals. Aggregations and ranks can be reset as selected column values change. Complex ratios can be expressed in simple terms.

Syntax

OLAP window expressions

1  %OLAP numbering function %OVER clause for numbering function(1)
1  %OLAP ranking function %OVER
clause for ranking functions(2)
1  %OLAP aggregation function %OVER clause for aggregation functions(3)
Notes:

OLAP window expressions are valid in these clauses of the SELECT statement:
  • The Select list of the Projection clause
  • The ORDER BY clause of the SELECT statement
  • Subquery specifications in the Projection clause

OLAP window expressions require an OLAP window function and an OVER clause.

OLAP window function
The OLAP window function identifies an operation for the database server to perform on rows in the query result set. OLAP window functions fit into the following functional categories:
  • The OLAP numbering function assigns a unique row number to each row:
    • ROW_NUMBER and ROWNUMBER, which are synonyms
  • OLAP ranking functions assign a rank to each row:
    • LAG
    • LEAD
    • RANK
    • DENSE_RANK
    • PERCENT_RANK
    • CUME_DIST
    • NTILE
  • OLAP aggregation functions aggregate row data:
    • FIRST_VALUE
    • LAST_VALUE
    • RATIO_TO_REPORT
    • AVG
    • COUNT
    • MAX
    • MIN
    • RANGE
    • STDEV
    • SUM
    • VARIANCE
OVER clause
The OVER clause defines the result set on which the specified operation is performed. The OVER clause has the following capabilities:
  • Define window partitions with the PARTITION BY clause. OLAP window partitions are subsets of the rows in the query result set that are based on the values of one or more column expressions that are listed in the PARTITION BY clause.
  • Order rows with the ORDER BY clause. If you include the PARTITION BY clause, you order results within each window partition. Otherwise, you order the whole result.
  • Define window frames with the ROWS or RANGE specification for OLAP window aggregation functions. A window frame defines a set of rows within a window partition. The aggregation function operates on the contents of the moving window frame instead of on the whole partition.

For example, the following query contains the OLAP aggregation function SUM:

SELECT c,d,
   SUM(d) OVER(
      PARTITION BY a,b
      ORDER BY c,d
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
   FROM table1;

The PARTITION BY clause creates window partitions for each set of values of columns a and b.

The ORDER BY clause orders the data within each window partition by the values of the columns c and d.

The window frame clause that starts with the ROWS keyword creates a window frame that consists of three rows: the current row, the row that precedes the current row, and the row that follows the current row. The current row is the reference point for the window frame. The following table shows how the window frame moves through the result set as each row becomes the current row in turn.

Table 1. Window frames in the result set
Row number First frame Second frame Third frame Fourth frame Fifth frame Sixth frame
1 Current® row Current row - 1
2 Current row + 1 Current row Current row - 1
3 Current row + 1 Current row Current row - 1
4 Current row + 1 Current row Current row - 1
5 Current row + 1 Current row Current row - 1
6 Current row + 1 Current row

The SUM function adds the values of d column for the three rows for each window partition that is in the scope of the query.

The following illustration shows how the query partitions, orders, and aggregates the data.


The graphic is described in surrounding text.

The query returns the values of columns c and d for the single window partition and the sum of up to three values of column d:

c      d     (sum)
1      1       3
1      2       6
1      3       7 
2      2       9
2      4       7
3      1       5

The first value in the sum column is the sum of the first and second values (1 + 2 = 3) in the d column. No preceding value for the current row exists for the first value.

The second value in the sum column is the sum of the first, second, and third values (1 + 2 + 3 = 6) in the d column.

The third value in the sum column is the sum of the second, third, and fourth values (2 + 3 + 2 = 7) in the d column.

The last value in the sum column is the sum of the fifth and sixth values (1 + 4 = 5) in the d column. No following value for the current row exists for the last value.