# 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

- 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

- The OLAP numbering function assigns a unique row number to each
row:
- 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:

SELECTc,d,SUM(d)OVER(PARTITIONBYa,bORDERBYc,dROWSBETWEEN1PRECEDINGAND1FOLLOWING)FROMtable1;

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.

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 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**:

cd (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.