9. Aggregation, combinators, and states

Aggregation is what ClickHouse is for. This chapter is how it executes, the combinator system that makes a hundred functions into a thousand, and the memory behaviour that decides whether your query finishes.

How GROUP BY runs

SELECT country, count(), avg(duration) FROM page_views GROUP BY country;

Each reading thread builds its own hash table mapping group key to aggregate state. No locks, no sharing.

   thread 0:  { UK: (c=812K, sum=1.1e9), FR: (c=204K, sum=…) }
   thread 1:  { UK: (c=799K, sum=1.0e9), DE: (c=311K, sum=…) }
   thread 2:  { UK: (c=821K, sum=1.1e9), FR: (c=198K, sum=…) }
        │
        ▼  merge
   final:     { UK: (c=2.43M, …), FR: (c=402K, …), DE: (c=311K, …) }

Merging works because each entry is an aggregate state that combines associatively. A count state is an integer you add. An avg state is (sum, count) — two numbers you add. A uniq state is a HyperLogLog sketch, and two sketches merge into one.

This is the same idea as chapter 4’s AggregatingMergeTree, and it is worth seeing that they are the same mechanism: a partial result that knows how to combine with another partial result.

ClickHouse chooses among several hash table implementations based on the key: a plain array for small integer keys, specialised tables for fixed-width keys, string hashing for strings. A LowCardinality key becomes integer hashing, which is a large part of why chapter 2 recommends it.

Memory

The hash tables live in RAM. Group by something with 5 million distinct values and you hold 5 million entries per thread until the merge.

max_memory_usage                     10 GiB   per query, per server
max_bytes_before_external_group_by   0        0 = fail rather than spill
max_bytes_ratio_before_external_group_by      newer, ratio-based variant

With the default of 0, a GROUP BY that exceeds max_memory_usage fails:

DB::Exception: Memory limit (for query) exceeded: would use 10.05 GiB

Enable spilling and it writes partial results to disk and merges them:

SET max_bytes_before_external_group_by = '4G';   -- typically half of max_memory_usage
SET max_bytes_before_external_sort     = '4G';

Set it to roughly half your memory limit, so there is room for the merge phase itself.

Spilling keeps a query alive. It is much slower. Treat it as a safety net, not a plan — a query that routinely spills is telling you to pre-aggregate it (chapter 11).

Combinators — the multiplier

This is one of ClickHouse’s best ideas and it is unfamiliar to people from other databases. Any aggregate function can take a suffix that changes how it behaves.

-If — aggregate conditionally

SELECT
    countIf(status = 200)                       AS ok,
    countIf(status >= 500)                      AS errors,
    avgIf(duration, status = 200)               AS avg_ok_ms,
    uniqIf(user_id, country = 'UK')             AS uk_users,
    sumIf(revenue, device = 'mobile')           AS mobile_revenue
FROM page_views;

One pass over the data producing five different conditional aggregates. Doing this with subqueries or CASE WHEN in another database means either several scans or a more awkward query. This single feature removes a lot of SQL from an analytics codebase.

-Array — aggregate over array elements

SELECT sumArray(values), uniqArray(tags) FROM events;

Equivalent to ARRAY JOIN followed by the aggregate, without materialising the expanded rows.

-State and -Merge — partial aggregation

Covered in chapter 4 and central to chapter 11:

-- write a partial state
SELECT uniqState(user_id) FROM page_views GROUP BY date;

-- combine states later
SELECT uniqMerge(state) FROM daily_uniques GROUP BY month;

The key property: uniqMerge over daily states gives the correct monthly unique count. You cannot sum daily uniques — a user visiting on two days counts once per month — but you can merge the sketches. This is why aggregate states exist and why they are worth the conceptual overhead.

-Merge versus -MergeState

-Merge finalises to a value. -MergeState merges states and produces another state, for a further level of rollup. Hourly states → daily states → monthly states, each computed from the level below.

The others

   -OrNull       return NULL instead of a default for empty input
   -OrDefault    return the default for empty input
   -Resample     aggregate into buckets of a key range
   -Distinct     deduplicate arguments first: sumDistinct(x)
   -ForEach      apply to each position of an array across rows
   -SimpleState  a lighter state for SimpleAggregateFunction

They compose: uniqIfMergeState, avgArrayIf, sumForEachIf. The naming is machine-like and it means you rarely need a subquery.

Approximate aggregation

Exact distinct counts over billions of rows need memory proportional to cardinality. ClickHouse offers a family of approximations, and choosing correctly matters.

The uniq family

Function	Mechanism	Accuracy	Memory
uniqExact(x)	a hash set	exact	proportional to cardinality — can be huge
uniq(x)	adaptive sampling / HLL	~1–2% error	small, constant-ish
uniqCombined(x)	array → hash set → HLL	~0.5% error	small
uniqCombined64(x)	same, 64-bit hash	~0.5%	small — use for >4 billion
uniqHLL12(x)	plain HyperLogLog	~1.6%	very small, fastest
uniqTheta(x)	Theta sketch	~1%	supports set operations

uniqCombined is the right default. It is accurate enough for any dashboard, uses bounded memory, and merges cleanly as a state.

Use uniqExact only when the number must be exactly right — billing, compliance — and only when you know the cardinality is manageable. On a high-cardinality column across billions of rows it is the classic cause of a memory-limit failure.

Note that uniqCombined uses a 32-bit hash by default, which starts colliding meaningfully above a few billion distinct values. Above that, use uniqCombined64.

Quantiles

   quantile(0.95)(duration)          reservoir sampling — fast, approximate
   quantileExact(0.95)(duration)     exact — sorts everything, memory-hungry
   quantileTDigest(0.95)(duration)   t-digest — very accurate at the tails
   quantileTiming(0.95)(duration)    optimised for latency values in ms
   quantilesTDigest(0.5,0.9,0.99)(x) several at once, one pass

quantileTiming is purpose-built for response-time distributions — it uses a fixed bucketing scheme tuned for millisecond values and is both fast and accurate in that range. Use it for latency SLOs.

quantileTDigest is the general answer when you need accuracy at p99 and p999, where reservoir sampling is weakest.

Ask for several at once with the plural form — one pass instead of several.

topK

SELECT topK(10)(url) FROM page_views;

Approximate most-frequent values using a space-saving algorithm, in bounded memory. It is not guaranteed exact, which for a “top pages” widget is entirely acceptable. topKWeighted weights by a second column.

The functions worth knowing

Beyond the standard set:

-- argMax / argMin: the value of x at the row with the max y
SELECT country, argMax(url, duration) AS slowest_url FROM page_views GROUP BY country;

-- any / anyLast: an arbitrary value — cheap, useful in GROUP BY
SELECT user_id, any(country), max(ts) FROM events GROUP BY user_id;

-- groupArray / groupUniqArray: collect values into an array
SELECT user_id, groupArray(url) FROM events GROUP BY user_id;
SELECT user_id, groupArray(10)(url) FROM events GROUP BY user_id;   -- bounded!

-- sumMap: sum values per key across rows
SELECT sumMap(metrics) FROM events;

-- histogram: an automatic histogram
SELECT histogram(10)(duration) FROM page_views;

groupArray without a limit

groupArray(url) over a group with ten million rows builds a ten-million-element array in memory. It is the same trap as MongoDB’s $push: "$$ROOT". Always use the bounded form groupArray(N)(x) unless you are certain the groups are small.

Behavioural analytics functions

These exist because ClickHouse was built for web analytics, and they save an enormous amount of SQL:

-- did these events happen in this order, within 3600 seconds?
SELECT windowFunnel(3600)(ts, event = 'view', event = 'cart', event = 'purchase') AS step
FROM events GROUP BY user_id;

-- retention: of users matching condition 1, how many match 2, 3, …
SELECT sumForEach(retention(date = '2026-08-01', date = '2026-08-02', date = '2026-08-03'))
FROM events;

-- pattern matching over an event sequence
SELECT sequenceMatch('(?1).*(?2)')(ts, event = 'login', event = 'purchase') FROM events;

windowFunnel in particular turns a conversion-funnel query — normally several self-joins — into one line.

Window functions

Standard SQL window functions work:

SELECT
    date,
    revenue,
    sum(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_total,
    avg(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        AS ma7,
    revenue - lagInFrame(revenue) OVER (ORDER BY date) AS change
FROM daily_revenue
ORDER BY date;

row_number(), rank(), dense_rank(), lagInFrame, leadInFrame, and PARTITION BY all work as expected.

Window functions are not distributed-friendly in the way simple aggregates are — they generally require gathering data to one node. Apply them to an already-aggregated result, not to a raw scan of billions of rows.

Making aggregation fast

1. Reduce the rows first. A good WHERE on the ordering key beats every other optimisation.
2. LowCardinality on group keys. Integer hashing instead of string hashing.
3. Approximate where you can. uniqCombined over uniqExact.
4. -If instead of several queries. One pass.
5. Pre-aggregate what repeats. A materialised view (chapter 11) turns a 3-second dashboard query into 20 milliseconds.
6. Bound groupArray.
7. Enable spilling as a safety net, not as a plan.

What goes wrong

uniqExact on a high-cardinality column. Memory limit exceeded. Use uniqCombined.

GROUP BY on a very high-cardinality key. Grouping by user_id over 500 million users builds a colossal hash table. Ask whether you need per-user rows or a distribution.

groupArray unbounded. Memory exhaustion.

quantileExact on billions of rows. It sorts. Use quantileTDigest or quantileTiming.

Summing daily uniques to get a monthly number. Wrong, silently. Use uniqState/uniqMerge.

Window functions over a raw scan. Aggregate first.

What to take from this chapter

• Each thread builds an independent hash table of aggregate states, merged at the end — which is why aggregation parallelises so well.
• Memory is per query; enable max_bytes_before_external_group_by at about half max_memory_usage as a safety net, and treat routine spilling as a signal to pre-aggregate.
• Combinators multiply every function: -If for conditional aggregates in one pass, -Array for arrays, -State/-Merge for partial aggregation, and they compose.
• uniqCombined is the default distinct count; uniqCombined64 above a few billion; uniqExact only when exactness is required and cardinality is bounded.
• quantileTiming for latency, quantileTDigest for accurate tails; ask for several at once.
• argMax, groupArray(N), sumMap, topK, windowFunnel and retention remove a great deal of SQL.
• Bound groupArray. Unbounded collection is the aggregation footgun.

Next: joins — the honest chapter.