Aggregate combinators ​

Aggregate combinators are named variants of an aggregate. A suffix changes whether the function filters input rows, returns an intermediate state, or combines previously calculated states.

CoordinatorDB provides the specific variants listed on this page. Names use underscores, such as sum_if and avg_merge_state.

_if ​

Conditional aggregates process only rows whose condition is SQL true. They let one query calculate totals for several segments without separate subqueries or joins.

count_if(condition)
sum_if(value, condition)
avg_if(value, condition)

count_if counts matching rows. sum_if and avg_if use the same numeric conversion and overflow rules as sum and avg; selected NULL values are skipped. False and NULL conditions do not contribute.

If no non-NULL value contributes, count_if and sum_if return 0, while avg_if returns NULL.

The arguments are evaluated before the aggregate tests the condition. A false condition does not protect a value expression from an evaluation error.

Comparing order segments ​

CREATE TABLE orders (status String, amount UInt32);
INSERT INTO orders VALUES
    ('paid', 100), ('paid', 60), ('pending', 40), ('cancelled', 20);

SELECT count_if(status = 'paid') AS paid_orders,
       sum_if(amount, status = 'paid') AS paid_amount,
       sum_if(amount, status = 'pending') AS pending_amount,
       avg_if(amount, status = 'paid') AS average_paid_order
FROM orders
FORMAT csv;

2,160,40,80.0

Use the SQL FILTER (WHERE condition) clause for other aggregates. sum(value) FILTER (...) returns NULL when no value contributes; sum_if returns 0.

_state ​

The _state variant returns the information needed to continue an aggregation, rather than only its final answer. The state has an AggregateFunction type containing the aggregate name and its argument types.

For example, an average state retains both a total and a count. Combining these states weights each batch by its number of observations; averaging batch averages would lose that information.

finalize_aggregation(state) is a scalar function that reads the final value of one state. It does not combine several rows.

Combining average states ​

CREATE TABLE readings(batch UInt8, value Int64);
INSERT INTO readings VALUES (1, 10), (1, 20), (2, 90);

CREATE TABLE batch_averages(batch UInt8, state AggregateFunction(avg, Int64));
INSERT INTO batch_averages
SELECT batch, avg_state(value)
FROM readings
GROUP BY batch;

Read each batch's average:

SELECT batch, finalize_aggregation(state) AS average_value
FROM batch_averages
ORDER BY batch
FORMAT csv;

1,15.0
2,90.0

_merge ​

The _merge variant combines matching intermediate states and returns their final aggregate value. Combine the states from the previous example to calculate the mean of all three observations:

SELECT avg_merge(state) AS overall_average
FROM batch_averages
FORMAT csv;

40.0

_merge_state ​

The _merge_state variant combines matching states and returns another state instead of a final value. Use avg_merge_state to retain a combined average for another stage:

SELECT finalize_aggregation(avg_merge_state(state))
FROM batch_averages
FORMAT csv;

40.0

State signatures and empty input ​

States can be merged only when the aggregate name and every argument type match exactly. A mismatched signature raises TYPE_MISMATCH. Declared column and cast types are retained even for an empty input.

Empty count and sum states finalize to zero. Empty minimum and maximum states finalize to their argument type's default. An empty average state finalizes to NULL. These state results differ from ordinary sum, min and max on empty input, which return NULL.

States use CoordinatorDB's native representation. They cannot be exchanged with ClickHouse aggregate-state bytes.

_simple_state ​

_simple_state returns a scalar partial result wrapped in a SimpleAggregateFunction type:

sum_simple_state(value)
min_simple_state(value)
max_simple_state(value)

The partial value can be read directly and combined with ordinary sum, min or max; no _merge or finalization call is required. Inserting a partial result does not automatically combine table rows.

Combining partial totals ​

CREATE TABLE partials
(
    batch UInt8,
    subtotal SimpleAggregateFunction(sum, Int64)
);

INSERT INTO partials
SELECT batch, sum_simple_state(value)
FROM readings
GROUP BY batch;

SELECT sum(subtotal) AS total_value
FROM partials
FORMAT csv;

120

Use full intermediate states for averages and counts. Arbitrary suffix combinations, such as array or map combinators, are not supported.