7.23.3 Rules for specifying the window frame (when RANGE is specified in the window frame clause)

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

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