7.23.3 Rules for specifying the window frame (when RANGE is specified in the window frame clause)
When RANGE is specified in the window frame clause, the upper and lower boundaries of the window frame are determined as follows.
- Organization of this subsection
(1) Upper boundary of the window frame
(a) When the window frame start boundary is UNBOUNDED PRECEDING
The first row of the window frame (the upper boundary) will be the first row of the window (partition).
(b) When the window frame start boundary is CURRENT ROW
The first row of the window frame (the upper boundary) will be the first row with the same sort key value as the current row.
(c) When the window frame start boundary is window-frame-value-specification PRECEDING or window-frame-value-specification FOLLOWING
The upper boundary of the window frame is determined based on the value of the sort key specified in the window order clause.
- ■ When the sort key value of the current row is the null value
-
The first row of the window frame (the upper boundary) will be same, regardless of whether window-frame-value-specification PRECEDING or window-frame-value-specification FOLLOWING is specified for the window frame start boundary.
The first row of the window frame (the upper boundary) will be the topmost row among the upper rows whose sort key is the null value.
- ■ When the sort key value of the current row is not the null value
-
The upper boundary of the window frame is determined as follows:
-
When the window frame boundary is window-frame-value-specification PRECEDING
If the sort order specification is ASC (ascending), the first row of the window frame (the upper boundary) will be the first row whose sort key is greater than or equal to the value from Formula A.
When the sort order specification is DESC (descending), the first row of the window frame (the upper boundary) will be the first row whose sort key is less than or equal to the value from Formula A.
Formula A:
• When the sort order specification is ASC (ascending): the sort key value of the current row - window-frame-value-specification
• When the sort order specification is DESC (descending): the sort key value of the current row + window-frame-value-specification
If the value from Formula A is a value that cannot be represented in the data type of the result, the window frame is determined using the maximum or minimum value that can be represented by the data type of the result.
-
When the window frame boundary is window-frame-value-specification FOLLOWING
If the sort order specification is ASC (ascending), the first row of the window frame (the upper boundary) will be the first row whose sort key is greater than or equal to the value from Formula B.
When the sort order specification is DESC (descending), the first row of the window frame (the upper boundary) will be the first row whose sort key is less than or equal to the value from Formula B.
Formula B:
• When the sort order specification is ASC (ascending): the sort key value of the current row + window-frame-value-specification
• When the sort order specification is DESC (descending): the sort key value of the current row - window-frame-value-specification
If the value from Formula B is a value that cannot be represented in the data type of the result, the window frame is determined using the maximum or minimum value that can be represented by the data type of the result.
-
(2) The lower boundary of the window frame
(a) When the window frame end boundary is UNBOUNDED FOLLOWING
The last row of the window frame (the lower boundary) will be the last row of the window (partition).
(b) When the window frame end boundary is CURRENT ROW
The last row of the window frame (the lower boundary) will be the last row with the same sort key value as the current row.
(c) When the window frame end boundary is window-frame-value-specification PRECEDING or window-frame-value-specification FOLLOWING
The lower boundary of the window frame is determined based on the value of the sort key specified in the window order clause.
- ■ When the sort key value of the current row is the null value
-
The last row of the window frame (the lower boundary) will be the same, regardless of whether window-frame-value-specification PRECEDING or window-frame-value-specification FOLLOWING is specified for the window frame end boundary.
The last row of the window frame (the lower boundary) will be the last row whose sort key is the null value.
- ■ When the sort key value of the current row is not the null value
-
The lower boundary of the window frame is determined as follows:
-
When the window frame boundary is window-frame-value-specification PRECEDING
If the sort order specification is ASC (ascending), the last row of the window frame (the lower boundary) will be the last row whose sort key is less than or equal to the value from Formula A.
When the sort order specification is DESC (descending), the last row of the window frame (the lower boundary) will be the last row whose sort key is greater than or equal to the value from Formula A.
Formula A:
• When the sort order specification is ASC (ascending): the sort key value of the current row - window-frame-value-specification
• When the sort order specification is DESC (descending): the sort key value of the current row + window-frame-value-specification
If the value from Formula A is a value that cannot be represented in the data type of the result, the window frame is determined using the maximum or minimum value that can be represented by the data type of the result.
-
When the window frame boundary is window-frame-value-specification FOLLOWING
If the sort order specification is ASC (ascending), the last row of the window frame (the lower boundary) will be the last row whose sort key is less than or equal to the value from Formula B.
If the sort order specification is DESC (descending), the last row of the window frame (the lower boundary) will be the last row whose sort key is greater than or equal to the value from Formula B.
Formula B:
• When the sort order specification is ASC (ascending): the sort key value of the current row + window-frame-value-specification
• When the sort order specification is DESC (descending): the sort key value of the current row - window-frame-value-specification
If the value from Formula B is a value that cannot be represented in the data type of the result, the window frame is determined using the maximum or minimum value that can be represented by the data type of the result.
-