|
The current row provides the reference point for determining the start and end points of a window. The window bounds that you specify define a window relative to the current row.
You can specify the bounds as either an exact number of rows, using the ROWS clause, or as a range of values offset from the value in the current row, using the RANGE clause. In the latter case, the size of the window can vary, depending on the values in the surrounding rows.
Following are some additional examples of window bounds you might specify.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW This window starts at the beginning of the partition, and ends with the current row. Use this construct when computing cumulative results, such as cumulative sums.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING This syntax specifies a fixed window (regardless of the current row) over the entire partition. Use this construct when you want the value of an aggregate function to be identical for each row of a partition.
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING This syntax specifies a moving window of fixed-size (three adjacent rows) that includes the current row, and the row preceding and following the current row. Use this construct when, for example, computing a three day or three month moving average. To avoid problems due to gaps in the input to the window function if the set of values is not continuous, use the RANGE clause rather than ROWS clause because a window's bounds, when based on the RANGE clause, automatically handles adjacent rows where there are gaps in the range, as well as rows with duplicate values.
With a moving window of more than one row, NULLs exist when computing the first and last row in the partition. This is because when the current row is either the very first or very last row of the partition, there are no preceding or following (respectively) rows to use in the computation. Therefore, NULL values are used instead.ROWS BETWEEN CURRENT ROW AND CURRENT ROW This syntax specifies a window of one row, the current row.
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING This syntax specifies a window of one row, the row preceding the current row. This construction makes it possible to easily compute deltas, or differences in value, between adjacent rows.
When using the RANGE clause, you define the window size based on values in the column specified in the ORDER BY clause. To use the RANGE clause, you must also specify an ORDER BY clause, the ORDER BY clause must contain only a single column, and the column must be in the number domain. The window size is then determined by comparing values in the column specified in the ORDER BY clause to the value in the current row.
For example, suppose that for the current row, the column specified in the ORDER BY clause contains the value 10. If you specify the window size to be RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING, you are specifying the size of the window to be as large as required to ensure that the first row contains a 5 in the column, and the last row in the window contains a 15 in the column. As the window moves down the partition, the size of the window may grow or shrink according to the size required to fulfill the range specification.
RANGE uses unsigned integer values. Truncation of the range expression may occur depending on the domain of the ORDER BY expression and the domain of the value specified in the RANGE clause.