Window Framing
The window frame of an analytic function comprises a set of rows relative to the row that is currently being evaluated by the function. After the analytic function processes that row and its window frame, Vertica advances the current row and adjusts the frame boundaries accordingly. If the OVER
clause also specifies a partition, Vertica also checks that frame boundaries do not cross partition boundaries. This process repeats until the function evaluates the last row of the last partition.
Specifying a Window Frame
You specify a window frame in the analytic function's OVER
clause, as follows:
{ ROWS | RANGE } { BETWEEN start‑point AND end‑point } | start‑pointstart‑point / end‑point ={ UNBOUNDED {PRECEDING | FOLLOWING}
| CURRENT ROW
| constant-value {PRECEDING | FOLLOWING}
}
start‑point and end‑point specify the window frame's offset from the current row. Keywords ROWS
and RANGE
specify whether the offset is physical or logical. If you specify only a start point, Vertica creates a window from that point to the current row.
For syntax details, see Window Frame Clause.
Requirements
In order to specify a window frame, the OVER
must also specify a window order (ORDER BY)
clause. If the OVER
clause includes a window order clause but omits specifying a window frame, the function creates a default frame that extends from the first row in the current partition to the current row. This is equivalent to the following clause:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Window Aggregate Functions
Analytic functions that support window frames are called window aggregates. They return information such as moving averages and cumulative results. To use the following functions as window (analytic) aggregates, instead of basic aggregates, the OVER
clause must specify a window order clause and, optionally, a window frame clause. If the OVER
clause omits specifying a window frame, the function creates a default window frame as described earlier.
The following analytic functions support window frames:
Functions FIRST_VALUE
and LAST_VALUE
also support window frames, but they are only analytic functions with no aggregate counterpart. EXPONENTIAL_MOVING_AVERAGE
, LAG
, and LEAD
analytic functions do not support window frames.
A window aggregate with an empty OVER
clause creates no window frame. The function is used as a reporting function, where all input is treated as a single partition.