Window Functions¶

A window function produces one result for each input row, using that row and other related rows. Unlike a grouped aggregate, it does not collapse those rows into a single output row. For example, a windowed SUM can attach a running total to every row.

Partitions, Peer Groups, and Frames¶

Three concepts determine which rows a window function can use:

• Partition: rows with the same values for all partitioning expressions. Each partition is processed independently; a frame never crosses a partition boundary. Null partitioning values are considered equal. Without partitioning expressions, the entire input is one partition.
• Peer group: rows within a partition that share the same values for all ordering expressions. Two null ordering values are considered equal. Ordering arranges rows within a partition, but does not break ties between peers. Without ordering expressions, all rows in a partition are peers.
• Frame: the subset of a partition selected by lower and upper bounds relative to the current row, which is the row whose result is being calculated. The frame can change as the current row changes. Whether a bound counts rows or measures a distance in ordering values depends on the bound mode.

For example, partition by team and order by score ascending:

Rows B and C are peers. Row F has the same score, but belongs to a different partition. For current row C, these frames give different results for SUM(amount):

Preceding(n) and Following(n) are shorthand here for bounds with an offset_expr evaluating to n. The ROWS examples assume B appears before C; ordering only by score does not guarantee that order among peers. Adding a tie-breaking ordering expression makes the row order deterministic, but also changes which rows are peers.

The RANGE Following(10) example includes B even though it is displayed before C: the CurrentRow lower bound starts at the beginning of their shared peer group.

RANGE frames use peer groups, but peer groups are not a bound mode of their own: Substrait currently defines ROWS and RANGE, not a mode that counts peer groups.

Window Bindings¶

A window binding inherits the aggregate binding properties and adds the following:

In a consistent partition window operation, partition_expressions and sorts are shared by all functions. Each function still has its own bound mode and frame bounds. The same rules below apply to both representations.

Frame Bounds¶

Both bounds are inclusive, and only rows in the current partition can belong to the frame.

A frame need not contain the current row. If the lower bound is after the upper bound, the frame is empty. The function’s empty-frame semantics apply, such as SUM yielding null and COUNT(*) yielding zero.

Bound Modes¶

ROWS¶

BOUNDS_TYPE_ROWS counts rows in the declared ordering:

• CurrentRow means only the current row, even when it has peers.
• Preceding and Following offset expressions must have type int64 and evaluate to a non-negative value n. This specifies an inclusive frame boundary n rows before the current row for Preceding, or after it for Following.
• Without ordering, no row order is guaranteed. With ordering, the relative order of peers is still not guaranteed.

In the example above, one preceding row means B and one following row means D when C is current and the displayed order is used. These offsets count individual rows, not groups of rows sharing a score.

RANGE¶

BOUNDS_TYPE_RANGE measures distances in ordering values, rather than counting rows:

• CurrentRow means the first row of the current peer group for a lower bound and the last row of that group for an upper bound.
• If either bound is Preceding or Following, there must be exactly one ordering expression, and it must not use SORT_DIRECTION_CLUSTERED or a custom comparison function.
• An offset must be a non-negative distance with a compatible type.
• When the current row’s ordering value is null, an offset bound is equivalent to CurrentRow.

The single-ordering-expression restriction does not apply when both bounds are CurrentRow or Unbounded (or omitted). With multiple ordering expressions, peer equality uses all of them.

For an offset d and current ordering value v, compute the boundary in the direction of the declared ordering:

A boundary value need not occur in the input. For example, with ascending scores, current score 20, and bounds Preceding(7) through CurrentRow, the frame includes scores from 13 through 20, including every peer at score 20.

Offset Type Compatibility¶

Let T be the ordering expression’s type and D the offset expression’s type. D must be compatible with T: add(T, D) -> T and subtract(T, D) -> T must be defined.

For example, the standard extensions allow:

Offset Expressions¶

The following rules apply to offset_expr in both Preceding and Following, in either bound mode:

• Field references resolve against the input relation schema. The expression must behave as if evaluated once per input row, and must not contain window or aggregate functions.
• A null or negative result is invalid. Use the opposite bound direction, not a negative offset, to move the other way.
• A statically-known zero offset must be represented as CurrentRow. If a non-literal expression evaluates to zero for a row, the bound is equivalent to CurrentRow for that row.

The strictly positive integer offset field is deprecated in favor of offset_expr and will be removed in a future release. Producers must set at least one. Because offset has no field presence, an offset of 0 counts as unset, and consumers must reject a Preceding or Following bound with no offset_expr and an offset of 0.

When offset_expr is present, consumers must use it and ignore offset. Producers must write offset_expr and must also write offset whenever an exact legacy representation exists; otherwise, they must write only offset_expr. An exact legacy representation requires one fixed, strictly positive int64 offset that is semantically equivalent for every input row; literal syntax is not required.

Function Signatures¶

Window function signatures inherit all properties of aggregate functions and add:

Window Type describes the function’s data needs, not its bound mode. Aggregate functions such as AVG, COUNT, MAX, MIN, and SUM can be used as window functions with Window Type STREAMING.