Window frame clause

Specifies a window frame, which comprises a set of rows relative to the row that is currently being evaluated by an analytic function.

Specifies a window frame, which comprises a set of rows relative to the row that is currently being evaluated by an analytic function. After the function processes that row and its window, OpenText™ Analytics Database advances the current row and adjusts the window boundaries accordingly. If the OVER clause also specifies a partition, the database also checks that window boundaries do not cross partition boundaries. This process repeats until the function evaluates the last row of the last partition.

Syntax

{ ROWS | RANGE } { BETWEEN start-point AND end-point } | start-point

start-point | end-point:

  { UNBOUNDED {PRECEDING | FOLLOWING}
    | CURRENT ROW
    | constant-value  {PRECEDING | FOLLOWING}}

Arguments

ROWS | RANGE
Whether to interpret window frame dimensions as physical (ROWS) or logical (RANGE) offsets from the current row. See ROWS versus RANGE below for details.
BETWEEN start-point AND end-point
First and last rows of the window, where start-point and end-point can be one of the following:

  • UNBOUNDED {PRECEDING | FOLLOWING}: The current partition's first (PRECEDING) or last (FOLLOWING) row.

  • CURRENT ROW: The current row or value.

  • constant-value {PRECEDING | FOLLOWING}: A constant value or expression that evaluates to a constant value. This value is interpreted as either a physical or logical offset from the current row, depending on whether you use ROWS or RANGE. See ROWS versus RANGE for other restrictions.

start-point must resolve to a row or value that is less than or equal to end-point.

start-point
If ROWS or RANGE specifies only a start point, the database uses the current row as the end point and creates the window frame accordingly. In this case, start-point must resolve to a row that is less than or equal to the current row.

Requirements

In order to specify a window frame, the OVER must also specify a window order (ORDER BY) clause. If the OVER clause omits specifying a window frame, the function creates a default window that extends from the current row to the first row in the current partition. This is equivalent to the following clause:

RANGE UNBOUNDED PRECEDING AND CURRENT ROW

ROWS versus RANGE

The window frame's offset from the current row can be physical or logical:

  • ROWS (physical): the start and end points are relative to the current row. If either is a constant value, it must evaluate to a positive integer.

  • RANGE (logical): the start and end points represent a logical offset, such as time. The range value must match the window order (ORDER BY) clause data type: NUMERIC, DATE/TIME, FLOAT or INTEGER.

When setting constant values for ROWS, the constant must evaluate to a positive INTEGER.

When setting constant values for RANGE, the following requirements apply:

  • The constant must evaluate to a positive numeric value or INTERVAL literal.

  • If the constant evaluates to a NUMERIC value, the ORDER BY column type must be a NUMERIC data type.

  • If the constant evaluates to an INTERVAL DAY TO SECOND subtype, the ORDER BY column type must be one of the following: TIMESTAMP, TIME, DATE, or INTERVAL DAY TO SECOND.

  • If the constant evaluates to an INTERVAL YEAR TO MONTH, the ORDER BY column type must be one of the following: TIMESTAMP, DATE, or INTERVAL YEAR TO MONTH.

  • The window order clause can specify only one expression.

Examples

See Window framing.