Window OVER clause

Every windowed aggregate or window function ends with OVER (...). That parenthesized clause is what turns an ordinary aggregate into a per-row computation: it defines which rows are peers, how those peers are ordered, and (optionally) which slice of the peer set each row sees. This page documents that clause once, so individual function references can stay focused on what SUM, RANK, LEAD, and so on actually compute.

FeatureQL follows the same OVER shape you would write in standard SQL: optional PARTITION BY, optional ORDER BY, optional explicit frame (ROWS, RANGE, or GROUPS). The grammar is fixed in the parser; the behavior you feel in practice is almost always about how you combine those three knobs.

Syntax

OVER (
    [PARTITION BY expression [, expression ...]]
    [ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
    [ROWS | RANGE | GROUPS frame specification]
)

OVER (
    [PARTITION BY expression [, expression ...]]
    [ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
    [ROWS | RANGE | GROUPS frame specification]
)

NULLS FIRST and NULLS LAST on each sort key follow the same rules as in a non-window ORDER BY.

All parts inside OVER (...) are optional, but ranking and offset builtins (ROW_NUMBER, RANK, LEAD, LAG, …) need an ORDER BY inside OVER so row order inside each partition is defined.

window_frame and frame_bound

The optional window frame is the third piece of OVER (...): after optional partition and sort, you may pin exactly which rows in the partition count for the current row. In the grammar it is one windowFrame; in prose people call it the frame clause.

Shape. Pick a frame mode — ROWS, RANGE, or GROUPS — then either:

• One frame_bound — written right after the frame type (for example ROWS UNBOUNDED PRECEDING). The missing end of the interval follows normal SQL window rules (typically through CURRENT ROW when ORDER BY is present).
• BETWEEN … AND … — two frame_bound endpoints, for example ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running window from partition start through the current row) or ROWS BETWEEN 1 PRECEDING AND CURRENT ROW (this row plus the previous row in sort order).

frameBound values (each end of a frame is one of these):

• UNBOUNDED PRECEDING — first row of the partition (in ORDER BY order).
• UNBOUNDED FOLLOWING — last row of the partition.
• CURRENT ROW — the row being evaluated.
• n PRECEDING / FOLLOWING — a non-negative numeric offset from the current row (n is an expression; in practice a literal such as 1 PRECEDING).

window_frame ::=
    ( ROWS | RANGE | GROUPS ) frame_bound
  | ( ROWS | RANGE | GROUPS ) BETWEEN frame_bound AND frame_bound

frame_bound ::=
    UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | CURRENT ROW
  | <expression> PRECEDING
  | <expression> FOLLOWING

window_frame ::=
    ( ROWS | RANGE | GROUPS ) frame_bound
  | ( ROWS | RANGE | GROUPS ) BETWEEN frame_bound AND frame_bound

frame_bound ::=
    UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | CURRENT ROW
  | <expression> PRECEDING
  | <expression> FOLLOWING

frame_bound ::=
    UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | CURRENT ROW
  | <expression> PRECEDING
  | <expression> FOLLOWING

frame_bound ::=
    UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | CURRENT ROW
  | <expression> PRECEDING
  | <expression> FOLLOWING

ROWS vs RANGE vs GROUPS. ROWS counts a fixed number of physical rows before/after the current row in sort order. RANGE and GROUPS use peer groups tied to the ORDER BY key values (ties move as a block; exact peer rules match standard SQL window semantics). For a sliding “previous row plus current row” sum, ROWS BETWEEN 1 PRECEDING AND CURRENT ROW is the usual spelling.

PARTITION BY

PARTITION BY takes one or more expressions and splits the input rows into independent groups, the same way you would mentally “reset” a calculation when a key changes. Each row only sees other rows in its own partition when the window function runs.

If you omit PARTITION BY, the whole row set is a single partition.

ORDER BY inside OVER

The ORDER BY inside OVER (...) is not the same as a top-level query ORDER BY. It only sorts rows for the purpose of defining the window—running totals, ranks, LEAD/LAG direction, and frame bounds all depend on this ordering.

When you build windows inside TRANSFORM, the array of rows may have its own physical order. Adding ORDER BY at the end of the TRANSFORM subquery reorders the output array; that is separate from OVER’s internal ordering, though you often align them so UNWRAP() reads naturally.

Window frame — behavior

If you omit window_frame, the engine still applies an implicit default frame whenever that is defined for the function and context. With ORDER BY inside OVER, aggregates such as SUM typically default to a range that ends at the current row, which is why SUM(x) OVER (ORDER BY t) looks like a running total. With no ORDER BY, there is no natural progression, so the frame is usually the whole partition on every row (for example SUM(x) OVER () repeats the total).

FIRST_VALUE and LAST_VALUE are the common gotcha: under the default frame that ends at CURRENT ROW, LAST_VALUE is often just the current row’s argument, not the last row of the entire partition. To read the true last value across the partition, set an explicit frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (and keep the ORDER BY that defines “last”).

Examples

The snippets below all use the same pattern: a small ZIP(...) table, then .TRANSFORM(SELECT … ORDER BY …) so the result array has a predictable order when you UNWRAP(). The ORDER BY at the end of TRANSFORM only reorders the output rows; the ORDER BY inside OVER (...) is what the window function uses to decide neighbors, running totals, and frames.

From one big partition to grouped running sums

Start with no partition and no sort inside OVER: every row sees the entire input at once, so SUM(v) OVER () is the grand total on every line.

SELECT R := ZIP(ARRAY[1,2,3] AS i, ARRAY[10,20,30] AS v).TRANSFORM(SELECT SUM(v) OVER () ORDER BY i).UNWRAP();

Add PARTITION BY plus an ORDER BY inside OVER, and SUM becomes a running total that resets per key. Here ORDER BY rid at the end of TRANSFORM lines up the output with rid; it does not replace the window’s own ordering.

SELECT R := ZIP(ARRAY[1,2,3] AS rid, ARRAY[1,1,2] AS k, ARRAY[10,20,30] AS v).TRANSFORM(SELECT SUM(v) OVER (PARTITION BY k ORDER BY rid ASC) ORDER BY rid).UNWRAP();

If you partition but omit ORDER BY inside OVER, there is no “next step” for a running frame, so each row gets the aggregate over the whole partition (here, the same total for every row that shares a k). The second column shows the opposite case: one row per partition, so the running SUM along i is just that row’s value.

SELECT
    NO_ORDER := ZIP(ARRAY[1,2,3] AS i, ARRAY[1,1,2] AS k, ARRAY[10,20,30] AS v).TRANSFORM(SELECT SUM(v) OVER (PARTITION BY k) ORDER BY i).UNWRAP(),
    SINGLE_ROW := ZIP(ARRAY[1,2,3] AS i, ARRAY[1,2,3] AS k, ARRAY[10,20,30] AS v).TRANSFORM(SELECT SUM(v) OVER (PARTITION BY k ORDER BY i) ORDER BY i).UNWRAP()
;

Partitions can use several keys. The first query has a unique (k1, k2) on every row—no accumulation. The second repeats a pair so you see the running sum grow inside that composite group.

SELECT
    UNIQUE_GROUPS := ZIP(ARRAY[1,2,3] AS i, ARRAY[1,1,2] AS k1, ARRAY['a','b','a'] AS k2, ARRAY[10,20,30] AS v).TRANSFORM(SELECT SUM(v) OVER (PARTITION BY k1, k2 ORDER BY i) ORDER BY i).UNWRAP(),
    ACCUMULATE := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,1,1,2] AS k1, ARRAY['a','b','a','a'] AS k2, ARRAY[10,20,30,5] AS v).TRANSFORM(SELECT SUM(v) OVER (PARTITION BY k1, k2 ORDER BY i) ORDER BY i).UNWRAP()
;

Sort direction and NULL ordering

ORDER BY inside OVER may use DESC or NULLS FIRST / NULLS LAST, just like a normal sort. Descending window order changes how the running sum grows; the example then lists results in ascending i so the array reads intuitively.

SELECT R := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT SUM(v) OVER (ORDER BY i DESC) ORDER BY i).UNWRAP();

NULLS FIRST and NULLS LAST change where a NULL sort key sits relative to numbers, which directly changes ROW_NUMBER() assignments. The two columns are the same data with opposite null placement.

SELECT
    NFIRST := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[3::BIGINT, NULL::BIGINT, 1::BIGINT, 2::BIGINT] AS v).TRANSFORM(SELECT ROW_NUMBER() OVER (ORDER BY v ASC NULLS FIRST) ORDER BY i).UNWRAP(),
    NLAST := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[3::BIGINT, NULL::BIGINT, 1::BIGINT, 2::BIGINT] AS v).TRANSFORM(SELECT ROW_NUMBER() OVER (ORDER BY v ASC NULLS LAST) ORDER BY i).UNWRAP()
;

Frames: ROWS, then RANGE and GROUPS

Once ORDER BY is fixed, ROWS BETWEEN … is the easiest frame to reason about: you count physical neighbors in sort order. One playground runs four variants side by side (previous+current, current+next, centered, and the full partition on every row).

SELECT
    PREV_CUR := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT SUM(v) OVER (ORDER BY i ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY i).UNWRAP(),
    CUR_NEXT := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT SUM(v) OVER (ORDER BY i ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) ORDER BY i).UNWRAP(),
    CENTERED := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT SUM(v) OVER (ORDER BY i ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORDER BY i).UNWRAP(),
    F_FULL := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT SUM(v) OVER (ORDER BY i ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ORDER BY i).UNWRAP()
;

With RANGE, rows that tie on the ORDER BY key move together: the running sum jumps by the whole peer group when you leave that value. That is different from ROWS, where ties are still distinct rows.

SELECT R := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,2,2,3] AS v, ARRAY[10,20,20,30] AS amt).TRANSFORM(SELECT SUM(amt) OVER (ORDER BY v ASC RANGE UNBOUNDED PRECEDING) ORDER BY i).UNWRAP();

GROUPS frames reason about peer groups (again defined by the ORDER BY key). GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING sums only the current peer group; extending one group backward adds the previous group’s total.

SELECT
    CURRENT_ONLY := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,2,2,3] AS v, ARRAY[10,20,20,30] AS amt).TRANSFORM(SELECT SUM(amt) OVER (ORDER BY v ASC GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING) ORDER BY i).UNWRAP(),
    WITH_PREV := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,2,2,3] AS v, ARRAY[10,20,20,30] AS amt).TRANSFORM(SELECT SUM(amt) OVER (ORDER BY v ASC GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY i).UNWRAP()
;

Ranking, then offsets, then value pickers

RANK leaves gaps after ties; DENSE_RANK does not.

SELECT
    RANK := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[2,1,3,2] AS v).TRANSFORM(SELECT RANK() OVER (ORDER BY v ASC) ORDER BY i).UNWRAP(),
    DENSE := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[2,1,3,2] AS v).TRANSFORM(SELECT DENSE_RANK() OVER (ORDER BY v ASC) ORDER BY i).UNWRAP()
;

The following snippet isolates ROW_NUMBER and shows how a trailing ORDER BY i on TRANSFORM lists row numbers in original row id order, not in window sort order.

SELECT R := ZIP(ARRAY[1,2,3] AS i, ARRAY[3,1,2] AS v).TRANSFORM(SELECT ROW_NUMBER() OVER (ORDER BY v ASC) ORDER BY i).UNWRAP();

With PARTITION BY, ranks restart per group: identical v values tie inside each partition independently of other keys.

SELECT R := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,1,2,2] AS k, ARRAY[20,10,30,30] AS v).TRANSFORM(SELECT RANK() OVER (PARTITION BY k ORDER BY v ASC) ORDER BY i).UNWRAP();

LEAD and LAG look one row forward or back along the window’s ORDER BY.

SELECT
    LEAD := ZIP(ARRAY[1,2,3] AS i, ARRAY[10,20,30] AS v).TRANSFORM(SELECT LEAD(v) OVER (ORDER BY i ASC) ORDER BY i).UNWRAP(),
    LAG := ZIP(ARRAY[1,2,3] AS i, ARRAY[10,20,30] AS v).TRANSFORM(SELECT LAG(v) OVER (ORDER BY i ASC) ORDER BY i).UNWRAP()
;

They respect partition boundaries: the next query interleaves two keys so each partition has only two rows—there is no neighbor across the boundary, so you see NULL where a lead or lag would leave the partition.

SELECT
    LEAD := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,2,1,2] AS k, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT LEAD(v) OVER (PARTITION BY k ORDER BY i ASC) ORDER BY i).UNWRAP(),
    LAG := ZIP(ARRAY[1,2,3,4] AS i, ARRAY[1,2,1,2] AS k, ARRAY[10,20,30,40] AS v).TRANSFORM(SELECT LAG(v) OVER (PARTITION BY k ORDER BY i ASC) ORDER BY i).UNWRAP()
;

Finally, FIRST_VALUE and LAST_VALUE show the default-frame story in code: with the usual frame that ends at the current row, LAST_VALUE tracks the current row’s value; widening the frame to the whole partition makes every row read the true last value.

SELECT
    F_FIRST := ZIP(ARRAY[1,2,3] AS i, ARRAY[10,20,30] AS v).TRANSFORM(SELECT FIRST_VALUE(v) OVER (ORDER BY i ASC) ORDER BY i).UNWRAP(),
    LAST_GOTCHA := ZIP(ARRAY[1,2,3] AS i, ARRAY[10,20,30] AS v).TRANSFORM(SELECT LAST_VALUE(v) OVER (ORDER BY i ASC) ORDER BY i).UNWRAP(),
    LAST_FIXED := ZIP(ARRAY[1,2,3] AS i, ARRAY[10,20,30] AS v).TRANSFORM(SELECT LAST_VALUE(v) OVER (ORDER BY i ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ORDER BY i).UNWRAP()
;

Where to read next

• Window functions (batch analytics) — longer narrative with SUM and ARRAY_AGG over several OVER variants on one table.
• Window function reference — index of every window-capable operator and its parameters.