Window functions

13 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

cume_dist

Measures the fraction of ordered partition rows at or before the current peer group.

cume_dist() -> float64

Divides the number of rows preceding or tied with the current row by the partition size. NULL sort keys participate under NULLS FIRST or NULLS LAST, and the result is non-NULL with a singleton partition returning one. General partition materialization is one-shot only; incompatible continuous use raises INVALID_ARGUMENT and admitted state limits raise RESOURCE_LIMIT.

Example

cume_dist() OVER (ORDER BY 7)
-- → 1

dense_rank

Assigns consecutive peer-group numbers without gaps after ties.

dense_rank() -> uint64

Equal ordering keys receive the same rank, and the next distinct key advances the value by exactly one. NULL keys join peer groups according to the explicit NULL ordering, while the uint64 result itself is never NULL. A total admitted ordering is required, and this one-shot window contract rejects continuous execution with INVALID_ARGUMENT.

Example

dense_rank() OVER (ORDER BY 7)
-- → 1

first_value

Reads the earliest value within the current ordered window frame.

first_value(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T

Selects the first frame row rather than the first non-NULL value, so a selected SQL NULL is preserved. An empty frame returns NULL and output retains the expression’s logical type. Deterministic selection requires sufficient ordering, and general frame evaluation is one-shot only with INVALID_ARGUMENT for incompatible continuous admission.

Example

first_value(12) OVER (
  ORDER BY 7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- → 12

lag

Returns a value from an earlier partition row with an optional missing-row default.

lag(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lag(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lag(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T

Moves backward by the nonnegative offset, defaulting to one, independently of the declared frame. A missing predecessor yields the supplied default or NULL, while an existing predecessor containing NULL keeps NULL. Offset zero reads the current row; incompatible default types raise TYPE_MISMATCH, and this one-shot contract rejects incompatible continuous execution with INVALID_ARGUMENT.

Example

lag(12, 1, 0) OVER (ORDER BY 7)
-- → 0

lag_in_frame

Reads an earlier row only when it remains inside the declared window frame.

lag_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lag_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lag_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T

Applies a backward offset of one by default and checks both partition position and current frame boundaries. A target outside the frame returns the supplied default or NULL, while a present NULL value remains NULL. TYPE_MISMATCH rejects an incompatible default; general frame-aware execution is one-shot only and incompatible streaming raises INVALID_ARGUMENT.

Example

lag_in_frame(12, 1, 0) OVER (
  ORDER BY 7 ROWS BETWEEN CURRENT ROW AND CURRENT ROW
)
-- → 0

last_value

Retrieves the value at the ending row of the active window frame.

last_value(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T

Respects the declared frame boundary, which may end at the current row rather than the partition’s last row. A selected NULL or an empty frame yields NULL; values are not implicitly skipped. The contract requires one-shot frame materialization, and incompatible streaming or invalid framing raises INVALID_ARGUMENT.

Example

last_value(12) OVER (
  ORDER BY 7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- → 12

lead

Looks ahead within an ordered partition and substitutes a default beyond its end.

lead(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lead(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lead(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T

Advances by the optional nonnegative offset, which defaults to one and is independent of frame boundaries. A missing successor returns the provided default or NULL; an existing NULL successor is not replaced. Incompatible default types raise TYPE_MISMATCH, and one-shot partition evaluation rejects continuous admission with INVALID_ARGUMENT.

Example

lead(12, 1, 99) OVER (ORDER BY 7)
-- → 99

lead_in_frame

Fetches a later value while honoring the current frame’s upper boundary.

lead_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> T
lead_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64) -> T
lead_in_frame(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],offset:uint64,default:T) -> T

Looks forward by the nonnegative offset, defaulting to one, but never crosses the active frame. The default is used only when that target row is unavailable, not when its value is NULL. Incompatible defaults raise TYPE_MISMATCH, and the one-shot window contract raises INVALID_ARGUMENT for incompatible continuous plans.

Example

lead_in_frame(12, 1, 0) OVER (
  ORDER BY 7 ROWS BETWEEN CURRENT ROW AND CURRENT ROW
)
-- → 0

nth_value

Selects a one-based position from the current frame, returning NULL if it is missing.

nth_value(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],n:uint64) -> T

Reads the nth frame row without ignoring NULL expression values, and preserves the selected logical type. A position beyond the frame length returns NULL, while zero raises INVALID_ARGUMENT. The current contract is one-shot only; invalid continuous admission raises INVALID_ARGUMENT and excessive retained frame data raises RESOURCE_LIMIT.

Example

nth_value(12, 2) OVER (
  ORDER BY 7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- → NULL

ntile

Partitions ordered rows into nearly equal numbered buckets.

ntile(buckets:uint64) -> uint64

Assigns bucket numbers from one, giving earlier buckets an extra row when division is uneven. NULL ordering keys remain rows in the distribution and do not make the result NULL; zero bucket count raises INVALID_ARGUMENT. The complete partition must be available under this one-shot contract, and excessive materialization raises RESOURCE_LIMIT.

Example

ntile(4) OVER (ORDER BY 7)
-- → 1

percent_rank

Scales a row’s gapped rank onto the partition-relative interval from zero to one.

percent_rank() -> float64

For partitions larger than one row, computes rank minus one divided by partition size minus one; a singleton returns zero. Peer and NULL-key ordering follow the window order, and the floating result is never NULL. One-shot partition evaluation is required, with INVALID_ARGUMENT for incompatible streaming and RESOURCE_LIMIT for excessive retained rows.

Example

percent_rank() OVER (ORDER BY 7)
-- → 0

rank

Numbers ordered peers by their first position, leaving gaps after tied rows.

rank() -> uint64

Returns one plus the count of rows strictly preceding the current peer group. Ties share a rank and consume positions, while NULL ordering keys are handled by NULLS FIRST or NULLS LAST without making the result NULL. The general partition contract is one-shot only; invalid continuous admission raises INVALID_ARGUMENT and excessive window state raises RESOURCE_LIMIT.

Example

rank() OVER (ORDER BY 7)
-- → 1

row_number

Gives every row a unique sequential position inside its ordered partition.

row_number() -> uint64

Counts rows from one, including rows whose ordering keys are NULL, and always returns a non-NULL uint64. Tied keys need an explicit deterministic tiebreaker when repeatable numbering matters. This contract requires a finite one-shot partition; incompatible streaming raises INVALID_ARGUMENT and materialization beyond the admitted budget raises RESOURCE_LIMIT.

Example

row_number() OVER (ORDER BY 7)
-- → 1