Aggregate functions

45 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.

any_heavy

Returns a majority candidate from grouped values using a bounded voting counter.

any_heavy(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 value occurring in more than half of the non-NULL observations when such a majority exists; otherwise the representative is unspecified. Empty groups return NULL. The voting state supports append and merge, while deletions require bounded replay and exceeding the configured value-byte ceiling raises RESOURCE_LIMIT.

Example

(SELECT any_heavy(x) FROM (VALUES (7), (7), (2)) AS readings(x))
-- → 7

any_if

Picks an arbitrary present observation among rows satisfying a Boolean predicate.

any_if(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],condition:bool) -> T

Returns any non-NULL value whose condition is true, without promising processing or event-time order. False and NULL predicates exclude the row before state updates; no qualifying value produces NULL. Retractable streams retain replacement candidates under a group cap, with RESOURCE_LIMIT if that cap is exceeded.

Example

(
  SELECT any_if(x, keep)
  FROM (VALUES (4, TRUE), (9, FALSE)) AS readings(x, keep)
)
-- → 4

any_last

Retrieves the most recently processed nonmissing value within a group.

any_last(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

Returns the last non-NULL value encountered in processing order, which need not be the value with the latest event timestamp. All-NULL and empty groups yield NULL, and parallel merges must preserve the declared ordering contract. Replacement history for retractions is capped; exhausting it raises RESOURCE_LIMIT.

Example

(SELECT any_last(x) FROM (VALUES (7)) AS readings(x))
-- → 7

any_value

Chooses one unspecified non-NULL representative from an aggregate group.

any_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

Returns an arbitrary accepted value with its original descriptor, skipping NULL observations and yielding NULL when none remain. It makes no guarantee about first, last, or event-time order, so merged partitions may choose different representatives. Retraction-capable streams must retain bounded replacement candidates; exceeding their allowance raises RESOURCE_LIMIT.

Example

(SELECT any_value(x) FROM (VALUES (7), (7), (NULL)) AS readings(x))
-- → 7

arg_max

Selects the payload attached to the greatest ordering key.

arg_max(value: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],key:K[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Returns a value from a row whose key is greatest, skipping rows with a NULL key or NULL value. An empty effective group returns NULL; equal winning keys leave the selected value unspecified. Retractions need capped replacement candidates rather than only the winning pair, and exceeding that history raises RESOURCE_LIMIT.

Example

(
  SELECT arg_max(label, score)
  FROM (VALUES ('sensor_a', 2), ('sensor_b', 5)) AS readings(label, score)
)
-- → 'sensor_b'

arg_max_if

Finds the winning label by highest score after conditional row selection.

arg_max_if(value: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],key:K[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6],condition:bool) -> T

Applies the Boolean condition before comparing keys and returns the value associated with the greatest surviving key. False or NULL conditions and NULL value/key pairs are discarded, with NULL returned if none survive; tied keys do not guarantee which value wins. Continuous deletions require a bounded candidate history and can raise RESOURCE_LIMIT when its capacity is exceeded.

Example

(
  SELECT arg_max_if(label, score, keep)
  FROM (
    VALUES ('sensor_a', 2, TRUE),
    ('sensor_b', 5, FALSE)
  ) AS readings(label, score, keep)
)
-- → 'sensor_a'

arg_min

Returns the associated record value at the lowest comparable key.

arg_min(value: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],key:K[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Chooses a value attached to the smallest non-NULL key after removing pairs with either field NULL. Equal minimum keys may select different representatives, so callers needing stable ties must supply a supported unique ordering key. Empty input yields NULL; candidate retention needed for stream retractions raises RESOURCE_LIMIT at its declared cap.

Example

(
  SELECT arg_min(label, score)
  FROM (VALUES ('sensor_a', 2), ('sensor_b', 5)) AS readings(label, score)
)
-- → 'sensor_a'

arg_min_if

Chooses an eligible payload whose ranking field is least.

arg_min_if(value: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],key:K[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6],condition:bool) -> T

Only rows with a true condition participate in the minimum-key selection; NULL conditions behave as false. The selected payload and key must both be non-NULL, and absent eligible pairs return NULL while ties remain unspecified. A retractable plan must reserve bounded replacement state, reporting RESOURCE_LIMIT when that allowance is exhausted.

Example

(
  SELECT arg_min_if(label, score, keep)
  FROM (
    VALUES ('sensor_a', 2, FALSE),
    ('sensor_b', 5, TRUE)
  ) AS readings(label, score, keep)
)
-- → 'sensor_b'

avg

Returns the arithmetic mean of present numbers as a double-precision value.

avg(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Sums non-NULL numeric observations and divides by their count, returning float64 for every supported numeric domain, including decimal. Empty input and groups containing only NULL return NULL, while checked arithmetic failures raise NUMERIC_OVERFLOW. Streaming count/sum state supports incremental updates; floating merge order must be fixed for bitwise replay.

Example

(SELECT avg(x) FROM (VALUES (2), (4), (NULL)) AS readings(x))
-- → 3.0

avg_if

Computes the arithmetic mean of numeric observations passing a filter.

avg_if(x:int*|uint*|float32|float64|decimal(P,S),condition:bool) -> float64

Divides the sum of non-NULL numbers with a true condition by their accepted count and returns float64, including for decimal arguments. A false or NULL condition suppresses the row; no accepted numbers return NULL. Count and sum form the streaming state, and reproducible floating results require a fixed merge order; unrepresentable arithmetic raises NUMERIC_OVERFLOW.

Example

(
  SELECT avg_if(x, keep)
  FROM (VALUES (2, TRUE), (8, FALSE), (4, TRUE)) AS readings(x, keep)
)
-- → 3.0

avg_or_null

Returns a nullable average, preserving absence when a numeric group has no observations.

avg_or_null(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Finalizes the numeric mean as float64 and explicitly returns NULL when the base aggregate accepted no non-NULL rows. This wrapper preserves normal averaging and arithmetic errors rather than replacing failures with a missing value. Streaming plans reuse the base sum/count state and fixed floating merge order; overflow raises NUMERIC_OVERFLOW.

Example

(SELECT avg_or_null(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → NULL

avg_or_zero

Uses zero for absent measurements and otherwise reports their arithmetic center.

avg_or_zero(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Computes a float64 arithmetic mean over non-NULL numbers, substituting 0.0 only when the effective group is empty. A nonempty group retains ordinary average semantics, and NUMERIC_OVERFLOW is never converted into zero. The wrapper tracks accepted-row count alongside the base streaming accumulator so retractions restore the correct empty result.

Example

(SELECT avg_or_zero(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0.0

avg_weighted

Calculates a mean scaled by nonnegative observation weights.

avg_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns sum(value × weight) divided by sum(weight), ignoring rows where either argument is NULL. Zero total weight produces NULL; negative or nonfinite weights raise INVALID_ARGUMENT, while checked arithmetic failure raises NUMERIC_OVERFLOW. Streaming updates maintain weighted sum and weight total, with merge order affecting floating rounding.

Example

(
  SELECT avg_weighted(x, weight)
  FROM (VALUES (2, 1), (8, 2)) AS readings(x, weight)
)
-- → 6.0

count

Returns the number of rows, present expressions, or distinct expression values.

count(*) -> uint64
count() -> uint64
count(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]) -> uint64
count(DISTINCT 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]) -> uint64

The star and no-argument forms count all rows, the expression form skips NULL, and DISTINCT counts each accepted value once. Every empty form returns uint64 zero; counter overflow raises NUMERIC_OVERFLOW. Ordinary streaming count needs one counter, whereas exact DISTINCT retains capped value multiplicities and raises RESOURCE_LIMIT when the admitted key limit is exceeded.

Example

(SELECT count(x) FROM (VALUES (1), (NULL), (2)) AS readings(x))
-- → 2

count_distinct

Counts different nonmissing values without probabilistic cardinality error.

unique_exact(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]) -> uint64
unique_exact(x1:T1,...,xn:Tn) -> uint64

Returns the exact number of distinct non-NULL inputs, with the variadic form counting distinct argument combinations. Empty input returns zero, and argument descriptors must support the documented equality and hashing operations or TYPE_MISMATCH is raised. Continuous plans retain multiplicities under a positive distinct/group bound and raise RESOURCE_LIMIT before exceeding it.

Example

(SELECT unique_exact(x) FROM (VALUES (1), (1), (2), (NULL)) AS readings(x))
-- → 2

count_if

Counts true predicate outcomes within the current aggregate group.

count_if(condition:bool) -> uint64

Returns the number of rows whose Boolean condition is true, excluding both false and NULL conditions. An empty group or a group without true conditions returns uint64 zero; non-Boolean predicates raise TYPE_MISMATCH. Continuous updates and retractions adjust one checked counter, with NUMERIC_OVERFLOW if the count cannot be represented.

Example

(
  SELECT count_if(keep)
  FROM (VALUES (TRUE), (FALSE), (NULL), (TRUE)) AS readings(keep)
)
-- → 2

count_or_null

Reports row cardinality while representing an empty effective group as missing.

count_or_null(*) -> uint64
count_or_null() -> uint64
count_or_null(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]) -> uint64
count_or_null(DISTINCT 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]) -> uint64

Counts every row for the star or no-argument form and only non-NULL arguments for the expression form; DISTINCT counts accepted unique values. It returns NULL when no row is accepted rather than the base count of zero. Exact DISTINCT streaming state requires a positive bound and raises RESOURCE_LIMIT on exhaustion; counter overflow raises NUMERIC_OVERFLOW.

Example

(SELECT count_or_null(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → NULL

count_or_zero

Produces a numeric tally with an explicit zero default for absent input.

count_or_zero(*) -> uint64
count_or_zero() -> uint64
count_or_zero(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]) -> uint64
count_or_zero(DISTINCT 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]) -> uint64

Preserves the base count result and makes its empty-group zero contract explicit for generated aggregate wrappers. Star and no-argument calls include rows containing NULL, whereas an expression call skips NULL and DISTINCT removes duplicates. Retractions decrement the checked count, and exceeding numeric or exact-distinct state capacity raises NUMERIC_OVERFLOW or RESOURCE_LIMIT respectively.

Example

(SELECT count_or_zero(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0

every

Tests whether all present Boolean observations are true.

every(x:bool) -> bool

Returns false if any non-NULL input is false and true when the group has accepted values that are all true. An empty or all-NULL group returns NULL, and non-Boolean input raises TYPE_MISMATCH. Counts of true and false observations provide bounded streaming state that can be updated exactly for retractions.

Example

(SELECT every(x) FROM (VALUES (TRUE), (TRUE), (NULL)) AS readings(x))
-- → true

group_bit_and

Intersects all integer bit masks, retaining flags present in every observation.

group_bit_and(x:T[int*|uint*]) -> T

Combines the non-NULL integers with bitwise AND while preserving their width and signedness. The empty identity is an all-ones value of that descriptor, so uint8 input yields 255 and int8 input yields -1. Per-bit multiplicities permit exact streaming retractions; incompatible noninteger arguments raise TYPE_MISMATCH.

Example

(
  SELECT group_bit_and(x)
  FROM (VALUES (cast(7 AS UINT8)), (cast(3 AS UINT8))) AS readings(x)
)
-- → 3

group_bit_or

Unites integer flags set by at least one member of a group.

group_bit_or(x:T[int*|uint*]) -> T

Sets each output bit when any non-NULL integer has that bit set, using the input integer descriptor for the result. NULLs are ignored, and an empty effective group returns a typed zero. A stream maintains occurrence counts for individual bits to remove flags correctly after retractions; noninteger input raises TYPE_MISMATCH.

Example

(
  SELECT group_bit_or(x)
  FROM (VALUES (cast(1 AS UINT8)), (cast(2 AS UINT8))) AS readings(x)
)
-- → 3

group_bit_xor

Combines grouped integer masks by odd occurrence parity of each flag.

group_bit_xor(x:T[int*|uint*]) -> T

Returns the exclusive-OR reduction of all non-NULL integers, with the same width and signedness as the argument. Repeated identical values cancel pairwise, and no accepted values yield typed zero. Exact per-bit state handles insertions and deletions; passing a noninteger descriptor raises TYPE_MISMATCH.

Example

(
  SELECT group_bit_xor(x)
  FROM (VALUES (cast(3 AS UINT8)), (cast(1 AS UINT8))) AS readings(x)
)
-- → 2

listagg

Joins ordered text observations into one delimiter-separated string.

listagg(x:string) WITHIN GROUP (ORDER BY key:T) -> string
listagg(x:string,delimiter:string) WITHIN GROUP (ORDER BY key:T) -> string

Concatenates non-NULL strings in the WITHIN GROUP ordering, placing the supplied delimiter between accepted values; the default delimiter is empty. Empty effective input returns NULL, and output overflow raises RESOURCE_LIMIT unless an explicit supported ON OVERFLOW TRUNCATE clause requests truncation. Ordered streaming aggregation retains text and ordering keys under the configured group and byte limits.

Example

(
  SELECT listagg(label, ',') WITHIN GROUP (ORDER BY position)
  FROM (VALUES ('b', 2), ('a', 1)) AS readings(label, position)
)
-- → 'a,b'

max

Returns the greatest non-NULL comparable value in a group.

max(x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Compares accepted values using their declared ordered descriptor and returns the greatest without changing its type. NULLs are skipped and an empty effective group yields NULL; incompatible ordering domains raise TYPE_MISMATCH. Append-only aggregation can keep one extreme, while deletions require capped replacement multiplicities and raise RESOURCE_LIMIT if they exceed capacity.

Example

(SELECT max(x) FROM (VALUES (2), (9), (NULL)) AS readings(x))
-- → 9

max_if

Reports the greatest qualifying observation after a Boolean gate.

max_if(x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6],condition:bool) -> T

Evaluates the maximum only over non-NULL values whose condition is true, treating NULL conditions as false. The result retains the comparable input descriptor and is NULL when no value qualifies; incompatible ordering types raise TYPE_MISMATCH. Append-only streams keep an extremum, while retractions require bounded candidate multiplicities and raise RESOURCE_LIMIT if those fill.

Example

(
  SELECT max_if(x, keep)
  FROM (VALUES (2, TRUE), (9, FALSE), (4, TRUE)) AS readings(x, keep)
)
-- → 4

max_or_null

Exposes a nullable upper extreme for groups without comparable members.

max_or_null(x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Returns the greatest accepted value and explicitly preserves NULL for an empty or all-NULL group. Ordering uses the argument descriptor without converting dates, strings, or numbers into a common display representation. A retractable stream retains replacement candidates under its group limit; RESOURCE_LIMIT reports exhausted history and TYPE_MISMATCH rejects unordered types.

Example

(SELECT max_or_null(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → NULL

min

Retrieves the least present value according to its declared ordering.

min(x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Finds the smallest non-NULL input and preserves the argument descriptor, including for supported strings and temporal values. No accepted observation returns NULL, and types without qualified ordering raise TYPE_MISMATCH. Streaming retractions can remove the current answer, so plans retain bounded candidate counts and report RESOURCE_LIMIT when that storage is exhausted.

Example

(SELECT min(x) FROM (VALUES (2), (9), (NULL)) AS readings(x))
-- → 2

min_if

Finds the smallest present measurement among predicate-approved rows.

min_if(x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6],condition:bool) -> T

Computes the least value after excluding rows with false or NULL conditions and skipping NULL arguments. If that filtering leaves no comparable value, the result is NULL; its type otherwise matches the input. Retracting a current minimum may need another candidate, so continuous plans enforce a state cap and raise RESOURCE_LIMIT on excess.

Example

(
  SELECT min_if(x, keep)
  FROM (VALUES (2, FALSE), (9, TRUE), (4, TRUE)) AS readings(x, keep)
)
-- → 4

min_or_null

Returns a lower bound observation while keeping an empty result nullable.

min_or_null(x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Finalizes the minimum of non-NULL ordered inputs, returning NULL if the effective group has no member. The wrapper does not insert a numeric zero, which could incorrectly become the smallest value for positive data. Replacement state for stream deletions shares the base aggregate cap and raises RESOURCE_LIMIT when full; incompatible comparison descriptors raise TYPE_MISMATCH.

Example

(SELECT min_or_null(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → NULL

single_value_or_null

Returns the sole distinct observed value, or missing when observations disagree.

single_value_or_null(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

Repeated copies of one non-NULL value yield that value, but two or more distinct values make the result NULL. An empty or all-NULL group also returns NULL, using the original argument descriptor for a successful result. Stream retractions can restore uniqueness, requiring capped candidate multiplicities whose exhaustion raises RESOURCE_LIMIT.

Example

(SELECT single_value_or_null(x) FROM (VALUES (7), (7), (NULL)) AS readings(x))
-- → 7

sum

Returns a checked total of numeric observations with signedness-aware integer widening.

sum(x:int8|int16|int32|int64) -> int64
sum(x:uint8|uint16|uint32|uint64) -> uint64
sum(x:T[int128|int256|uint128|uint256|float32|float64|decimal(P,S)]) -> T

Adds non-NULL numbers, widening signed integers up to 64 bits to int64 and unsigned integers up to 64 bits to uint64; wider integers, floats, and decimals preserve their descriptor. Empty effective input returns NULL and unrepresentable arithmetic raises NUMERIC_OVERFLOW. Incremental and retracting streams maintain an accumulator and accepted count, with a fixed merge order required for identical floating replay.

Example

(SELECT sum(x) FROM (VALUES (1), (2), (3), (NULL)) AS readings(x))
-- → 6

sum_if

Adds numeric amounts from rows whose condition evaluates true.

sum_if(x:int8|int16|int32|int64,condition:bool) -> int64
sum_if(x:uint8|uint16|uint32|uint64,condition:bool) -> uint64
sum_if(x:T[int128|int256|uint128|uint256|float32|float64|decimal(P,S)],condition:bool) -> T

Sums non-NULL arguments only when the condition is true, with NULL conditions excluded before updates. Signed and unsigned integers through 64 bits widen to int64 and uint64 respectively; larger integers, floating values, and decimals retain their descriptor, and empty qualifying input gives NULL. Streaming addition and subtraction are checked for NUMERIC_OVERFLOW, with a fixed merge order needed for reproducible floating results.

Example

(
  SELECT sum_if(x, keep)
  FROM (VALUES (2, TRUE), (8, FALSE), (4, TRUE)) AS readings(x, keep)
)
-- → 6

sum_kahan

Accumulates a floating total with compensation for lost low-order digits.

sum_kahan(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Converts accepted numbers to float64 and uses Kahan compensation to reduce rounding error during summation. NULL values do not enter the accumulator and an empty effective group returns NULL; a nonfinite checked result raises NUMERIC_OVERFLOW. The correction term is part of streaming state, and identical replay still requires a fixed merge order.

Example

(SELECT sum_kahan(x) FROM (VALUES (1), (2), (3)) AS readings(x))
-- → 6.0

sum_or_null

Keeps an absent total as NULL while applying the checked numeric widening rules.

sum_or_null(x:int8|int16|int32|int64) -> int64
sum_or_null(x:uint8|uint16|uint32|uint64) -> uint64
sum_or_null(x:T[int128|int256|uint128|uint256|float32|float64|decimal(P,S)]) -> T

Returns NULL when no non-NULL number was accepted, otherwise finalizing the normal checked sum. Integer inputs no wider than 64 bits use the corresponding signed or unsigned 64-bit result, while wider integers, decimals, and floats preserve their descriptor. Accepted count and accumulator share the base streaming state; NUMERIC_OVERFLOW remains an error rather than being converted to NULL.

Example

(SELECT sum_or_null(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → NULL

sum_or_zero

Substitutes a typed additive identity when a group contains no usable numbers.

sum_or_zero(x:int8|int16|int32|int64) -> int64
sum_or_zero(x:uint8|uint16|uint32|uint64) -> uint64
sum_or_zero(x:T[int128|int256|uint128|uint256|float32|float64|decimal(P,S)]) -> T

Adds the accepted non-NULL values and returns a result-typed zero only when the effective group is empty. Standard integer widening still applies, and a nonempty group that overflows raises NUMERIC_OVERFLOW instead of falling back to zero. Continuous plans track accepted-row count so deleting the final observation switches the result to the empty identity.

Example

(SELECT sum_or_zero(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0

sum_with_overflow

Totals integers with deliberate modular wraparound at their original bit width.

sum_with_overflow(x:T[int*|uint*]) -> T

Adds non-NULL integer inputs modulo 2 raised to their descriptor width, preserving signedness and width in the output. Empty input returns typed zero, and wraparound is the specified result rather than NUMERIC_OVERFLOW. The modular accumulator can process stream additions and retractions; noninteger arguments raise TYPE_MISMATCH.

Example

(
  SELECT sum_with_overflow(x)
  FROM (VALUES (cast(255 AS UINT8)), (cast(2 AS UINT8))) AS readings(x)
)
-- → 1

top_k

Estimates the most frequent distinct values using a bounded heavy-hitter sketch.

top_k(k:uint64)(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]) -> array(T)
top_k(k:uint64,load_factor:uint64)(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]) -> array(T)

Returns at most k likely frequent non-NULL values, with approximate rankings and unspecified ordering among ties; an empty group produces an empty array. The bind-time k and optional load_factor must be positive or INVALID_ARGUMENT is raised, and admitted sketch capacity is bounded. Append and merge are supported, while retractable streams require bounded replay and raise RESOURCE_LIMIT if that replay allowance is exceeded.

Example

(SELECT top_k(1)(x) FROM (VALUES (7), (7), (7)) AS readings(x))
-- → [7]

top_k_weighted

Ranks likely heavy hitters by accumulated unsigned multiplicities.

top_k_weighted(k:uint64)(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],weight:uint64) -> array(T)
top_k_weighted(k:uint64,load_factor:uint64)(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],weight:uint64) -> array(T)

Uses each uint64 weight as a contribution to a value’s estimated frequency and returns no more than the requested k candidates. NULL value/weight pairs are skipped and zero weights add nothing, leaving an empty array when no positive contribution exists. Nonpositive bind-time k or load_factor raises INVALID_ARGUMENT, checked weight overflow raises NUMERIC_OVERFLOW, and stream deletions require a bounded replay plan.

Example

(
  SELECT top_k_weighted(1)(x, weight)
  FROM (VALUES (7, cast(3 AS UINT64))) AS readings(x, weight)
)
-- → [7]

unique

Estimates distinct cardinality from hashed observations or combinations of columns.

unique(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]) -> uint64
unique(x1:T1,...,xn:Tn) -> uint64

Returns an approximate count of distinct accepted values, or distinct argument combinations for the variadic form. NULL arguments are skipped according to the aggregate NULL policy, and an empty effective group yields zero. Hash collisions and sketch sampling affect accuracy; the persisted hash seed supports repeatable state, while arbitrary retractions require bounded replay.

Example

(SELECT unique(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0

unique_combined

Estimates cardinality with a sketch that switches from small sets to HyperLogLog.

unique_combined(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]) -> uint64

Combines a compact exact representation for small populations with a HyperLogLog state as cardinality grows. Non-NULL observations participate and no accepted observation gives zero; persisted precision and seed define the approximation contract. Sketches support append and merge, but deletion plans need bounded replay and reject state exhaustion with RESOURCE_LIMIT.

Example

(SELECT unique_combined(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0

unique_combined64

Counts approximate distinct values through staged sets backed by 64-bit hashing.

unique_combined64(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]) -> uint64

Uses a 64-bit hash in the combined distinct-count algorithm, transitioning from small-set storage to probabilistic registers. It ignores NULL values and returns zero for empty effective input; hash collisions mean the result is still an estimate. Streaming state records precision and seed, and arbitrary removals require capped replay rather than subtracting register values.

Example

(SELECT unique_combined64(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0

unique_exact_if

Counts distinct eligible observations after testing a row predicate.

unique_exact_if(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],condition:bool) -> uint64
unique_exact_if(x1:T1,...,xn:Tn,condition:bool) -> uint64

Applies a true-only condition to each positive or negative transition before maintaining the exact distinct-value state. False or NULL conditions suppress the row, the base NULL-skipping rule still applies, and an empty accepted set has cardinality zero. Streaming use requires a positive cap on retained keys and reports RESOURCE_LIMIT if that cap is reached.

Example

(
  SELECT unique_exact_if(x, keep)
  FROM (VALUES (1, TRUE), (1, TRUE), (2, FALSE)) AS readings(x, keep)
)
-- → 1

unique_exact_or_null

Represents absent distinct-count evidence as NULL while retaining exact deduplication.

unique_exact_or_null(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]) -> uint64
unique_exact_or_null(x1:T1,...,xn:Tn) -> uint64

Computes the exact cardinality of accepted non-NULL values but returns NULL when the base aggregate has accepted none. Repeated observations of one value still produce one, and the wrapper preserves the equality and hashing requirements of the base contract. Retraction multiplicities remain subject to the declared state bound; overflow of retained keys raises RESOURCE_LIMIT.

Example

(
  SELECT unique_exact_or_null(x)
  FROM (VALUES (cast(NULL AS INT64))) AS readings(x)
)
-- → NULL

unique_exact_or_zero

Reports an exact deduplicated tally with a numeric identity for empty groups.

unique_exact_or_zero(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]) -> uint64
unique_exact_or_zero(x1:T1,...,xn:Tn) -> uint64

Maintains an exact set of accepted values and returns zero when there are no non-NULL observations. The empty-result wrapper does not suppress TYPE_MISMATCH for incompatible key descriptors or RESOURCE_LIMIT for an exceeded distinct-key cap. Streaming deletions use multiplicities to remove a key only after its final contributing occurrence disappears.

Example

(
  SELECT unique_exact_or_zero(x)
  FROM (VALUES (cast(NULL AS INT64))) AS readings(x)
)
-- → 0

unique_hll12

Estimates distinct population size using 4,096 HyperLogLog registers.

unique_hll12(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]) -> uint64

Hashes non-NULL values into a HyperLogLog sketch with 4096 registers and finalizes an approximate distinct count. Empty effective input returns zero, while persisted precision and hash seed identify the state’s accuracy and replay contract. Register merges support append-only aggregation; exact deletion of arbitrary prior values requires bounded replay and can raise RESOURCE_LIMIT.

Example

(SELECT unique_hll12(x) FROM (VALUES (cast(NULL AS INT64))) AS readings(x))
-- → 0