TopolearnCombinators are how ClickHouse expresses complex analytics in one pass, and -State/-Merge underpin pre-aggregation.
ClickHouse has well over a hundred aggregate functions. A few you will use constantly:
quantile(0.95)(latency)(approximate, fast) andquantileExact;quantiles(0.5, 0.9, 0.99)(x)computes several at once. Note the two pairs of parentheses: parameters, then arguments.argMax(val, ts)/argMin: the value of one column at the max or min of another.topK(5)(url): approximately most frequent values;groupArray(x)collects values into an array;groupUniqArraydistinct ones.any(x): some value from the group, cheap when you know it's constant.
Combinators are suffixes that change how any aggregate works:
-If:sumIf(amount, paid),uniqIf(user, country = 'FR').-Array: aggregate the elements of array arguments,sumArray(arr).-Distinct:sumDistinct(x).-OrNull/-OrDefault: control the result for empty input.-Statereturns the intermediate state instead of the final value (uniqState(user_id)), and-Mergefinishes states (uniqMerge(state)). States can be stored in a table and merged later, which is how incremental rollups work.
Combinators chain: uniqStateIf(user_id, country = 'DE') is a valid function.
Example
SELECT
quantiles(0.5, 0.99)(number) AS p50_p99,
argMax(number % 7, number) AS last_mod7,
topK(2)(if(number < 600, 'a', if(number < 900, 'b', 'c'))) AS common,
sumIf(number, number % 2 = 0) AS even_sum,
sumArray([number, 1]) AS arr_sum
FROM numbers(1000);
-- [499.5,989.01] | 5 | ['a','b'] | 249500 | 500500
-- -State / -Merge: compute partial states, finish them later
WITH parts AS (
SELECT number % 2 AS half, uniqState(number % 100) AS st
FROM numbers(1000) GROUP BY half
)
SELECT uniqMerge(st) FROM parts; -- 100Remember
- Parametric aggregates take parameters first: quantile(0.95)(x)
- Combinator suffixes: -If, -Array, -Distinct, -OrNull, -State, -Merge
- -State values can be stored and merged later for rollups
Watch out for
- Summing pre-computed averages or distinct counts instead of merging -State values.