In ClickHouse, what does uniqState return, and how do you turn stored states back into a number?
answer
- distinct counts cannot be added together
- the aggregate's guts, not its answer
- stored in a column with its own type
- a matching suffix turns it back into a number
- additive functions get a cheaper variant
basics
~20 suniqState returns an opaque intermediate aggregation state of type AggregateFunction(uniq, ...), not a count. You store those states and finalize them later with the matching -Merge function, uniqMerge, or with finalizeAggregation for a single state.
solid answer
~50 sThe `-State` combinator makes an aggregate return its **intermediate state** instead of a result: `uniqState(user_id)` yields a value of type `AggregateFunction(uniq, UInt64)` — the sketch itself, opaque binary if you print it. States are storable in a column of that type and mergeable. The `-Merge` combinator consumes states and produces the final value, so `uniqMerge(users)` under a `GROUP BY` gives the distinct count for whatever grouping you ask for; `-MergeState` merges states into a new state for chained rollups, and `finalizeAggregation(state)` finalizes one state without grouping. This is what makes pre-aggregation correct: you cannot add per-day distinct counts to get a monthly figure, but you *can* merge per-day `uniq` states. For aggregates whose partial value is already the final value — `sum`, `min`, `max`, `any` — the cheaper `SimpleAggregateFunction` type with `-SimpleState` stores a plain value instead.
code
sql · 12 linesCREATE TABLE daily_users
(
day Date,
users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY day;
INSERT INTO daily_users
SELECT toDate(ts) AS day, uniqState(user_id)
FROM events
GROUP BY day;go deeper
Recall that -State stores an intermediate value and -Merge turns it back into a result, and that the state prints as unreadable bytes if you select it raw.
Explain the AggregateFunction column type, why the function and argument types are part of it, and what finalizeAggregation does for a single state.
Argue the mergeability property in production terms: one rollup serving several grains correctly, state size versus raw data, and when SimpleAggregateFunction is the cheaper right answer.
Own the pre-aggregation strategy — which metrics are worth storing as states, the accuracy contract you are signing the business up to, and the retention split between raw and rolled-up data.
## The problem states solve Some aggregates are additive: daily `sum(revenue)` values add up to a month. Many are not. Daily distinct-user counts cannot be added — the same user appears on several days — and neither can medians, quantiles, or top-K lists. Storing the *finalized* number therefore destroys the ability to roll it up to a coarser grain, which is exactly what a pre-aggregation table exists for. ClickHouse's answer is to store the aggregate's **internal state** rather than its result. A `uniq` state is the hash sketch; a `quantile` state is the sampled/compressed distribution; a `sum` state is just a number. States of the same function are **mergeable**, and merging them gives exactly what aggregating the raw rows at that grain would have given. ## The combinators - **`-State`** — return the state instead of the result. `uniqState(user_id)` has type `AggregateFunction(uniq, UInt64)`. Selecting it in a client prints unreadable binary; that is expected, it is not a bug. - **`-Merge`** — consume states and produce the final value. `uniqMerge(state_column)` is an aggregate function whose *input* is states. - **`-MergeState`** — consume states and produce a new merged state, for building a second, coarser rollup from a finer one. - **`finalizeAggregation(state)`** — an ordinary function that converts a single state to its result without a `GROUP BY`. Its inverse, `initializeAggregation`, builds a state from a plain value. ```sql CREATE TABLE daily_users ( day Date, users AggregateFunction(uniq, UInt64) ) ENGINE = AggregatingMergeTree ORDER BY day; INSERT INTO daily_users SELECT toDate(ts) AS day, uniqState(user_id) FROM events GROUP BY day; SELECT day, uniqMerge(users) AS dau FROM daily_users GROUP BY day; -- per day SELECT uniqMerge(users) AS mau FROM daily_users; -- correct rollup ``` The last query is the payoff: one `uniqMerge` over a month of states gives the true monthly distinct count, to the accuracy of the `uniq` algorithm. Summing 30 daily counts would over-count every returning user. ## Type rules that trip people up The column type must name the **function and its argument types**: `AggregateFunction(uniq, UInt64)` accepts states from `uniqState` over a `UInt64`. A state of `uniq` cannot be merged with a state of `uniqExact` or `uniqCombined` — they are different algorithms and different types. Parametric aggregates carry their parameters into the type too, e.g. `AggregateFunction(quantiles(0.5, 0.99), Float64)`. Inserting into a state column requires a state on the right-hand side: either `uniqState(...)` from a `GROUP BY`, or `initializeAggregation('uniqState', value)` for a single value. Selecting the column without `-Merge` or `finalizeAggregation` gives you the binary blob. ## SimpleAggregateFunction For functions where merging two partial results is just applying the function again — `sum`, `min`, `max`, `any`, `anyLast`, `groupBitOr` — the full state machinery is overkill. `SimpleAggregateFunction(sum, UInt64)` stores an ordinary number, is readable directly with no `-Merge`, and is cheaper to store and merge. Use it whenever the aggregate qualifies; reserve `AggregateFunction` for the genuinely non-additive ones. The `-SimpleState` combinator produces values for those columns. ## Where the pattern appears The usual home for state columns is a rollup table fed by aggregation, often maintained automatically, with the raw table retained for a shorter window. The important property is not the plumbing but the algebra: a mergeable state means one physical rollup can serve hourly, daily and monthly questions correctly, and can be merged across shards. Because states are self-contained binary values they can also be shipped between tables or clusters and merged on arrival. ## Costs and pitfalls - **States are bigger than results.** A `uniq` state is a sketch, a `quantiles` state a compressed distribution; a rollup of states over a high-cardinality key can be larger than expected, occasionally larger than the raw data it summarizes. Check the grain before committing. - **Approximate stays approximate.** Merging `uniq` states does not make the answer exact; it makes it *consistent* with aggregating the raw rows using the same algorithm. If you need exactness, `uniqExact` states are exact but far more expensive. - **Forgetting `-Merge`** is the classic mistake: querying the state column directly returns garbage-looking bytes, or the query fails on a type mismatch. - **Nested merges must chain properly.** Building a monthly rollup from a daily one requires `-MergeState`, not `-State` over already-merged values. ## What interviewers probe The question behind the question is whether you understand *why* a distinct count cannot be summed and what ClickHouse offers instead. A strong answer states the type, names `-Merge` and `finalizeAggregation`, gives the daily-to-monthly rollup example, and mentions `SimpleAggregateFunction` as the cheaper path for additive aggregates.
- Why can uniq states be merged when daily distinct counts cannot be summed?A finalized count is a single number that has forgotten which users it saw. The state is the hash sketch itself, so merging two states unions the observed hash space and re-derives the cardinality — a returning user hashes to the same place on both days and is counted once. Summing counts double-counts everyone who appears on more than one day.
- When should you use SimpleAggregateFunction instead of AggregateFunction in ClickHouse?When the aggregate's partial result has the same type and meaning as its final result — `sum`, `min`, `max`, `any`, `anyLast`, `groupBitOr`. Then merging is just re-applying the function, so ClickHouse can store a plain value. It is smaller, merges more cheaply, and reads directly without a `-Merge` call. Keep `AggregateFunction` for non-additive aggregates such as `uniq` and `quantiles`.
- How do you build a monthly rollup from an existing daily state table?Aggregate the daily states with `-MergeState`, not `-State`: `SELECT toStartOfMonth(day), uniqMergeState(users) FROM daily_users GROUP BY 1`. That consumes states and emits a merged state of the same type, so the monthly table stays mergeable in turn. Using `-Merge` there would finalize to a number and you would lose the ability to roll up further.
saying these in an interview costs you the question
- Expects uniqState to return the distinct count
- Sums per-day distinct counts to get a monthly figure
- Thinks merging uniq states makes the result exact
- Mixes uniq and uniqExact states in one column
- Uses AggregateFunction for plain sums instead of SimpleAggregateFunction