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, Vertica advances the current row and adjusts the window boundaries accordingly. If the OVER clause also specifies a partition, Vertica 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`	Specifies whether Vertica determines window frame dimensions as physical or logical offsets from the current row. See ROWS versus RANGE below for details.
`BETWEEN` `start-point``AND``end-point`	Specifies the window's first and last rows, where `start-point` and `end-point` can be one of the following (discussed in detail below): `UNBOUNDED {PRECEDING \| FOLLOWING}` `CURRENT ROW` `constant-value` `{PRECEDING \| FOLLOWING}` `start-point` must resolve to a row or value that is less than or equal to `end-point`.
`UNBOUNDED PRECEDING`	Specifies that the window frame extends to the current partition's first row.
`start-point`	If `ROWS` or `RANGE` specifies only a start point, Vertica 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.
`UNBOUNDED FOLLOWING`	Specifies that the window frame extends to the current partition's last row.
`CURRENT ROW`	Specifies the current row or value as the window's start or end point.
`constant-value` `{PRECEDING \| FOLLOWING}`	Specifies a constant value or expression that evaluates to a constant value. The value specifies a physical or logical offset from the current row, depending on whether you specify `ROWS` or `RANGE`. Other dependencies also pertain, depending whether you specify `ROWS` and `RANGE`. See ROWS versus RANGE below for details.

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 specifies the window's start-point and end-point as a number of rows relative to the current row. If start-point and end-point are expressed as constant values, the value must evaluate to a positive integer.
RANGE specifies the window as 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.

Use of ROWS or RANGE imposes specific requirements on setting the window's start and end points as constant values. 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.