Aggregate Combinators

ClickHouse lets you attach a small set of suffixes — combinators — to almost any aggregate function to change its behavior. Once you know the combinators, you can often replace a subquery, a CASE expression, or a self-join with a single function call.

-If: conditional aggregation without a subquery

Appending If to any aggregate function adds a condition as its last argument — the function only considers rows where the condition is true, computed in a single pass over the data instead of one pass per condition.

SELECT
    sumIf(amount, status = 'paid')     AS paid_total,
    sumIf(amount, status = 'refunded') AS refunded_total,
    countIf(status = 'failed')         AS failed_count
FROM payments;

-Array: aggregating over array columns

Appending Array makes an aggregate function treat its argument as an array and aggregate over all the array's elements across all rows, rather than over one scalar value per row.

-- tags is Array(String) per row
SELECT sumArray(scores) AS total_score
FROM matches;

-State / -Merge: partial aggregates that combine later

This is the mechanism behind materialized views that pre-aggregate incrementally, covered later using countState() / countMerge(). The general pattern applies to any aggregate function:

• fooState() — instead of returning a final value, returns an opaque, mergeable intermediate state (stored via the AggregateFunction(foo, ...) column type).
• fooMerge() — combines many stored states back into one, and produces the actual final value.

This is what lets a table hold, say, one row of pre-aggregated "average response time per minute" per source server, and later merge those per-server states into a correct overall average — which a naive average-of-averages would get wrong.

Counting distinct values: the uniq family

ClickHouse doesn't have a -Distinct combinator — distinct counting is its own family of functions, because exact and approximate distinct counting have very different costs:

• uniqExact — exact count, implemented by holding every distinct value seen. Correct, but memory cost grows with cardinality.
• uniq — approximate count using an adaptive sampling algorithm; small, bounded memory use, small statistical error.
• uniqCombined — approximate, tuned to use even less memory than uniq for very large cardinalities, at a similar error rate.

SELECT
    uniqExact(user_id) AS exact_dau,
    uniq(user_id)      AS approx_dau
FROM events
WHERE event_date = today();

argMax / argMin: "the row where X was largest"

A frequent pattern — find the value of one column at the row where another column is at its maximum — normally needs a subquery or a window function. argMax/argMin do it in one call:

SELECT
    user_id,
    argMax(plan, updated_at) AS current_plan
FROM plan_history
GROUP BY user_id;

This returns, per user, the plan value from whichever row has the largest updated_at — effectively "latest plan per user" without a self-join or a ROW_NUMBER() OVER (...) = 1 filter.