For nonranking aggregate OLAP functions, you can define a window frame with a window frame clause, which specifies the beginning and end of the window relative to the current row.
<WINDOW FRAME CLAUSE> ::= <WINDOW FRAME UNIT> <WINDOW FRAME EXTENT>
This OLAP function is computed with respect to the contents of a moving frame rather than the fixed contents of the whole partition. Depending on its definition, the partition has a start row and an end row, and the window frame slides from the starting point to the end of the partition.
UNBOUNDED PRECEDING and FOLLOWING
Window frames can be defined by an unbounded aggregation group that either extends back to the beginning of the partition (UNBOUNDED PRECEDING) or extends to the end of the partition (UNBOUNDED FOLLOWING), or both.
UNBOUNDED PRECEDING includes all rows within the partition preceding the current row, which can be specified with either ROWS or RANGE. UNBOUNDED FOLLOWING includes all rows within the partition following the current row, which can be specified with either ROWS or RANGE.
The value FOLLOWING specifies either the range or number of rows following the current row. If ROWS is specified, then the value is a positive integer indicating a number of rows. If RANGE is specified, the window includes any rows that are less than the current row plus the specified numeric value. For the RANGE case, the data type of the windowed value must be comparable to the type of the sort key expression of the ORDER BY clause. There can be only one sort key expression, and the data type of the sort key expression must allow addition.
The value PRECEDING specifies either the range or number of rows preceding the current row. If ROWS is specified, then the value is a positive integer indicating a number of rows. If RANGE is specified, the window includes any rows that are less than the current row minus the specified numeric value. For the RANGE case, the data type of the windowed value must be comparable to the type of the sort key expression of the ORDER BY clause. There can be only one sort key expression, and the data type of the sort key expression must allow subtraction. This clause cannot be specified in second bound group if the first bound group is CURRENT ROW or value FOLLOWING.
The combination BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING provides an aggregate over an entire partition, without the need to construct a join to a grouped query. An aggregate over an entire partition is also known as a reporting aggregate.
CURRENT ROW concept
In physical aggregation groups, rows are included or excluded based on their position relative to the current row, by counting adjacent rows. The current row is simply a reference to the next row in a query’s intermediate results. As the current row advances, the window is reevaluated based on the new set of rows that lie within the window. There is no requirement that the current row be included in a window.
If a window frame clause is not specified, the default window frame depends on whether or not a window order clause is specified:
If the window specification contains a window order clause, the window’s start point is UNBOUNDED PRECEDING, and the end point is CURRENT ROW, thus defining a varying-size window suitable for computing cumulative values.
If the window specification does not contain a window order clause, the window’s start point is UNBOUNDED PRECEDING, and the end point is UNBOUNDED FOLLOWING, thus defining a window of fixed size, regardless of the current row.
Note: A window frame clause cannot be used with a ranking function.
You can also define a window by specifying a window frame unit that is row-based (rows specification) or value-based (range specification).
<WINDOW FRAME UNIT> ::= ROWS | RANGE
<WINDOW FRAME EXTENT> ::= <WINDOW FRAME START> | <WINDOW FRAME BETWEEN>
When a window frame extent specifies BETWEEN, it explicitly provides the beginning and end of a window frame.
If the window frame extent specifies only one of these two values then the other value defaults to CURRENT ROW.
Row-based window frames—In the example rows [1] through [5] represent a partition; each row becomes the current row as the OLAP window frame slides forward. The frame is defined as Between Current Row And 2 Following, so each frame includes a maximum of three rows and a minimum of one row. When the frame reaches the end of the partition, only the current row is included. The shaded areas indicate which rows are excluded from the frame at each step.
The window frame imposes the following rules:
When row [1] is the current row, rows [4] and [5] are excluded.
When row [2] is the current row, rows [5] and [1] are excluded.
When row [3] is the current row, rows [1] and [2] are excluded.
When row [4] is the current row, rows [1], [2], and [3] are excluded.
When row [5] is the current row, rows [1], [2], [3], and [4] are excluded.
The following diagram applies these rules to a specific set of values, showing the OLAP AVG function that would be calculated for each row. The sliding calculations produce a moving average with an interval of three rows or fewer, depending on which row is the current row:
The following example demonstrates a sliding window:
SELECT dimension, measure,
AVG(measure) OVER(partition BY dimension
ORDER BY measure
ROWS BETWEEN CURRENT ROW and 2 FOLLOWING)
AS olap_avg
FROM ...
The averages are computed as follows:
Row [1] = (10 + 50 + 100)/3
Row [2] = (50+ 100 + 120)/3
Row [3] = (100 + 120 + 500)/3
Row [4] = (120 + 500 + NULL)/3
Row [5] = (500 + NULL + NULL)/3
Similar calculations would be computed for all subsequent partitions in the result set (such as, B, C, and so on).
If there are no rows in the current window, the result is NULL, except for COUNT.