# Aggregate functions

> 45 Aggregate functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

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.

| Function | Summary |
| --- | --- |
| [`any_heavy`](#any_heavy) | Returns a majority candidate from grouped values using a bounded voting counter. |
| [`any_if`](#any_if) | Picks an arbitrary present observation among rows satisfying a Boolean predicate. |
| [`any_last`](#any_last) | Retrieves the most recently processed nonmissing value within a group. |
| [`any_value`](#any_value) | Chooses one unspecified non-NULL representative from an aggregate group. |
| [`arg_max`](#arg_max) | Selects the payload attached to the greatest ordering key. |
| [`arg_max_if`](#arg_max_if) | Finds the winning label by highest score after conditional row selection. |
| [`arg_min`](#arg_min) | Returns the associated record value at the lowest comparable key. |
| [`arg_min_if`](#arg_min_if) | Chooses an eligible payload whose ranking field is least. |
| [`avg`](#avg) | Returns the arithmetic mean of present numbers as a double-precision value. |
| [`avg_if`](#avg_if) | Computes the arithmetic mean of numeric observations passing a filter. |
| [`avg_or_null`](#avg_or_null) | Returns a nullable average, preserving absence when a numeric group has no observations. |
| [`avg_or_zero`](#avg_or_zero) | Uses zero for absent measurements and otherwise reports their arithmetic center. |
| [`avg_weighted`](#avg_weighted) | Calculates a mean scaled by nonnegative observation weights. |
| [`count`](#count) | Returns the number of rows, present expressions, or distinct expression values. |
| [`count_distinct`](#count_distinct) | Counts different nonmissing values without probabilistic cardinality error. |
| [`count_if`](#count_if) | Counts true predicate outcomes within the current aggregate group. |
| [`count_or_null`](#count_or_null) | Reports row cardinality while representing an empty effective group as missing. |
| [`count_or_zero`](#count_or_zero) | Produces a numeric tally with an explicit zero default for absent input. |
| [`every`](#every) | Tests whether all present Boolean observations are true. |
| [`group_bit_and`](#group_bit_and) | Intersects all integer bit masks, retaining flags present in every observation. |
| [`group_bit_or`](#group_bit_or) | Unites integer flags set by at least one member of a group. |
| [`group_bit_xor`](#group_bit_xor) | Combines grouped integer masks by odd occurrence parity of each flag. |
| [`listagg`](#listagg) | Joins ordered text observations into one delimiter-separated string. |
| [`max`](#max) | Returns the greatest non-NULL comparable value in a group. |
| [`max_if`](#max_if) | Reports the greatest qualifying observation after a Boolean gate. |
| [`max_or_null`](#max_or_null) | Exposes a nullable upper extreme for groups without comparable members. |
| [`min`](#min) | Retrieves the least present value according to its declared ordering. |
| [`min_if`](#min_if) | Finds the smallest present measurement among predicate-approved rows. |
| [`min_or_null`](#min_or_null) | Returns a lower bound observation while keeping an empty result nullable. |
| [`single_value_or_null`](#single_value_or_null) | Returns the sole distinct observed value, or missing when observations disagree. |
| [`sum`](#sum) | Returns a checked total of numeric observations with signedness-aware integer widening. |
| [`sum_if`](#sum_if) | Adds numeric amounts from rows whose condition evaluates true. |
| [`sum_kahan`](#sum_kahan) | Accumulates a floating total with compensation for lost low-order digits. |
| [`sum_or_null`](#sum_or_null) | Keeps an absent total as NULL while applying the checked numeric widening rules. |
| [`sum_or_zero`](#sum_or_zero) | Substitutes a typed additive identity when a group contains no usable numbers. |
| [`sum_with_overflow`](#sum_with_overflow) | Totals integers with deliberate modular wraparound at their original bit width. |
| [`top_k`](#top_k) | Estimates the most frequent distinct values using a bounded heavy-hitter sketch. |
| [`top_k_weighted`](#top_k_weighted) | Ranks likely heavy hitters by accumulated unsigned multiplicities. |
| [`unique`](#unique) | Estimates distinct cardinality from hashed observations or combinations of columns. |
| [`unique_combined`](#unique_combined) | Estimates cardinality with a sketch that switches from small sets to HyperLogLog. |
| [`unique_combined64`](#unique_combined64) | Counts approximate distinct values through staged sets backed by 64-bit hashing. |
| [`unique_exact_if`](#unique_exact_if) | Counts distinct eligible observations after testing a row predicate. |
| [`unique_exact_or_null`](#unique_exact_or_null) | Represents absent distinct-count evidence as NULL while retaining exact deduplication. |
| [`unique_exact_or_zero`](#unique_exact_or_zero) | Reports an exact deduplicated tally with a numeric identity for empty groups. |
| [`unique_hll12`](#unique_hll12) | Estimates distinct population size using 4,096 HyperLogLog registers. |

## any_heavy

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

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Aliases | `anyHeavy` |
| Streaming | Stateful — keeps per-group state. State: majority candidate counter with declared value-byte ceiling |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `T` |
| Aliases | `anyIf` |
| Streaming | Stateful — keeps per-group state. State: one append-only selected value; retractable candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Aliases | `anyLast` |
| Streaming | Stateful — keeps per-group state. State: retractable ordered candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

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**

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

## any_value

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

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Aliases | `any` |
| Streaming | Stateful — keeps per-group state. State: one append-only selected value; retractable candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

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**

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

## arg_max

Selects the payload attached to the greatest ordering key.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `T` |
| Aliases | `argMax` |
| Streaming | Stateful — keeps per-group state. State: winning value/key plus retractable candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/ternary+generic` |
| Returns | `T` |
| Aliases | `argMaxIf` |
| Streaming | Stateful — keeps per-group state. State: winning value/key plus retractable candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `T` |
| Aliases | `argMin` |
| Streaming | Stateful — keeps per-group state. State: winning value/key plus retractable candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/ternary+generic` |
| Returns | `T` |
| Aliases | `argMinIf` |
| Streaming | Stateful — keeps per-group state. State: winning value/key plus retractable candidates under maximum_group_elements |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `float64` |
| Streaming | Stateful — keeps per-group state. State: sum and count per group under host byte admission |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary` |
| Returns | `float64` |
| Aliases | `avgIf` |
| Streaming | Stateful — keeps per-group state. State: checked sum and uint64 count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `float64` |
| Streaming | Stateful — keeps per-group state. State: checked sum and uint64 count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives NULL; base NULL policy otherwise |

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**

```sql
(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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `float64` |
| Streaming | Stateful — keeps per-group state. State: checked sum and uint64 count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives typed numeric zero; base NULL policy otherwise |

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**

```sql
(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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary` |
| Returns | `float64` |
| Streaming | Stateful — keeps per-group state. State: weighted sum weight sum and accepted count |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/0..1-ary+generic+syntax_form` |
| Returns | `uint64` |
| Streaming | Stateful — keeps per-group state. State: fixed counter for ordinary COUNT; admitted distinct-value and multiplicity set for DISTINCT |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | COUNT(\*) counts rows; COUNT(x) skips NULL; empty input 0 |

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**

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

## count_distinct

Counts different nonmissing values without probabilistic cardinality error.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/variadic+generic` |
| Returns | `uint64` |
| Aliases | `uniq`, `uniq_exact`, `uniqExact`, `unique_exact` |
| Streaming | Stateful — keeps per-group state. State: distinct canonical values and multiplicities per group capped by maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
count_if(condition:bool) -> uint64
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `uint64` |
| Aliases | `countIf` |
| Streaming | Stateful — keeps per-group state. State: uint64 count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/0..1-ary+generic+syntax_form` |
| Returns | `uint64` |
| Streaming | Stateful — keeps per-group state. State: uint64 count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives NULL; base NULL policy otherwise |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/0..1-ary+generic+syntax_form` |
| Returns | `uint64` |
| Streaming | Stateful — keeps per-group state. State: uint64 count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives typed numeric zero; base NULL policy otherwise |

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**

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

## every

Tests whether all present Boolean observations are true.

```text
every(x:bool) -> bool
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `bool` |
| Aliases | `bool_and` |
| Streaming | Stateful — keeps per-group state. State: true false and non-NULL counts per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: per-bit one/zero counts and non-NULL count for fixed input width |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: per-bit one/zero counts and non-NULL count for fixed input width |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: per-bit one/zero counts and non-NULL count for fixed input width |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/1..2-ary+generic+syntax_form` |
| Returns | `string` |
| Aliases | `group_concat`, `string_agg` |
| Streaming | Stateful — keeps per-group state. State: concatenation bytes and retractable items under declared group/byte caps |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: multiset of values and multiplicities per group capped by maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

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

## max_if

Reports the greatest qualifying observation after a Boolean gate.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `T` |
| Aliases | `maxIf` |
| Streaming | Stateful — keeps per-group state. State: retractable value multiset with declared maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: retractable value multiset with declared maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives NULL; base NULL policy otherwise |

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**

```sql
(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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: multiset of values and multiplicities per group capped by maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

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

## min_if

Finds the smallest present measurement among predicate-approved rows.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `T` |
| Aliases | `minIf` |
| Streaming | Stateful — keeps per-group state. State: retractable value multiset with declared maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: retractable value multiset with declared maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives NULL; base NULL policy otherwise |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: distinct candidate values and multiplicities under maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | skip NULL; exactly one distinct value returns it; otherwise NULL |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `int64 for signed <=64; uint64 for unsigned <=64; otherwise input descriptor` |
| Streaming | Stateful — keeps per-group state. State: sum accumulator and count per group under host byte admission |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

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

## sum_if

Adds numeric amounts from rows whose condition evaluates true.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `int64 for signed <=64; uint64 for unsigned <=64; otherwise input descriptor` |
| Aliases | `sumIf` |
| Streaming | Stateful — keeps per-group state. State: checked sum accumulator and non-NULL count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `float64` |
| Streaming | Stateful — keeps per-group state. State: compensated floating sum and non-NULL count |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `int64 for signed <=64; uint64 for unsigned <=64; otherwise input descriptor` |
| Streaming | Stateful — keeps per-group state. State: checked sum accumulator and non-NULL count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives NULL; base NULL policy otherwise |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `int64 for signed <=64; uint64 for unsigned <=64; otherwise input descriptor` |
| Streaming | Stateful — keeps per-group state. State: checked sum accumulator and non-NULL count per group |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives typed numeric zero; base NULL policy otherwise |

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**

```sql
(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.

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

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `T` |
| Streaming | Stateful — keeps per-group state. State: fixed-width modular accumulator |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

```text
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)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/1..2-ary+generic+parametric` |
| Returns | `array(T)` |
| Streaming | Stateful — keeps per-group state. State: space-saving counters limited by k\*load_factor |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
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)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/2..3-ary+generic+parametric` |
| Returns | `array(T)` |
| Streaming | Stateful — keeps per-group state. State: space-saving counters limited by k\*load_factor |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/variadic+generic` |
| Returns | `uint64` |
| Aliases | `uniq` |
| Streaming | Stateful — keeps per-group state. State: adaptive hash sample with fixed maximum sample count |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `uint64` |
| Aliases | `uniqCombined` |
| Streaming | Stateful — keeps per-group state. State: fixed precision approximate distinct sketch with persisted hash seed |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `uint64` |
| Aliases | `uniqCombined64` |
| Streaming | Stateful — keeps per-group state. State: fixed precision approximate distinct sketch with persisted hash seed |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

```sql
(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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/variadic+generic` |
| Returns | `uint64` |
| Aliases | `count_distinct_if`, `count_if_distinct` |
| Streaming | Stateful — keeps per-group state. State: distinct canonical values and multiplicities under maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL/false condition skips row; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/variadic+generic` |
| Returns | `uint64` |
| Streaming | Stateful — keeps per-group state. State: distinct canonical values and multiplicities under maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives NULL; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/variadic+generic` |
| Returns | `uint64` |
| Streaming | Stateful — keeps per-group state. State: distinct canonical values and multiplicities under maximum_group_elements |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | no non-NULL rows accepted by base gives typed numeric zero; base NULL policy otherwise |

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**

```sql
(
  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.

```text
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
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `uint64` |
| Aliases | `uniqHLL12` |
| Streaming | Stateful — keeps per-group state. State: fixed precision approximate distinct sketch with persisted hash seed |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

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**

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

---

Source: https://nyxdb.io/docs/functions/aggregate/