Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 35 ยท Querying and Functions

Aggregate Functions and Combinators

Learn ClickHouse aggregates: count, sum, uniq and uniqExact, quantile, argMax, topK, any, and the combinators -If, -Array, -OrDefault, -State and -Merge.

Aggregation is what ClickHouse does best, and its function library goes well past count, sum and avg. There are fast approximate aggregates for distinct counts and quantiles, "pick the value at the max" functions that replace self-joins, and combinators: suffixes you attach to any aggregate to change how it behaves. Learn a dozen functions and four suffixes and you can express most analytical questions in a single pass over the data.

ClickHouse cannot run on this site. The ClickHouse functions are read-only. The runnable blocks use SQLite with the standard equivalents (COUNT(DISTINCT ...), SUM(CASE ...), window functions) so you can see what each function computes. Approximate functions have no direct SQLite twin and are explained rather than run.

The basics

SQL
SELECT
    count()                  AS rows,            -- same as count(*)
    count(user_id)           AS non_null_users,
    sum(amount), avg(amount), min(amount), max(amount),
    any(country)             AS some_country,    -- an arbitrary value (fast)
    anyLast(country)         AS last_seen_country,
    sumIf(amount, status = 'paid') AS paid
FROM orders;

any(x) returns the first value it meets, which is not well defined; it is great to carry along a column that is constant within the group, and wrong when you need a specific row.

Distinct counts: uniq vs uniqExact

Function Accuracy Speed and memory
uniq(x) Approximate (usually within about 1-2 percent) Very fast, small memory
uniqCombined(x) Approximate, often more accurate at all sizes Fast
uniqHLL12(x) Approximate (HyperLogLog) Smallest memory
uniqExact(x) Exact Memory grows with distinct values
count(DISTINCT x) Exact (uses uniqExact by default) Same as above

For dashboards, uniq is usually the right choice. Use uniqExact for billing or auditing.

SQL Stand-in: exact distinct count per page (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Quantiles and percentiles

SQL
SELECT
    quantile(0.5)(ms)             AS median_approx,
    quantile(0.95)(ms)            AS p95_approx,
    quantiles(0.5, 0.9, 0.99)(ms) AS several,        -- one pass, array result
    quantileExact(0.95)(ms)       AS p95_exact,
    median(ms)
FROM requests;

quantile uses a bounded sample (reservoir) so it is fast and memory-light; quantileExact sorts everything; quantileTDigest and quantileTiming are alternatives tuned for latencies. Always give the quantiles function several levels at once: it costs about the same as one.

argMax, argMin and topK

argMax(value, key) returns the value from the row where key is largest. It is the standard way to get "the latest X per group" without a window function or join:

SQL
SELECT user_id,
       argMax(plan, updated_at)   AS current_plan,
       argMin(plan, updated_at)   AS first_plan,
       max(updated_at)            AS last_update
FROM user_history
GROUP BY user_id;

topK(3)(page) returns the 3 most frequent values (approximately) as an array; topKWeighted(3)(page, weight) weights them. groupArray, groupUniqArray, sumMap, maxMap and histogram(5)(x) round out the list of "collecting" aggregates.

SQL Stand-in: argMax per user with a window function (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Combinators: suffixes that change an aggregate

A combinator is a suffix added to any aggregate function name. They compose.

Suffix Effect Example
-If Aggregate only rows where a condition is true countIf(status >= 500), avgIf(ms, country = 'PT')
-Array Apply the aggregate to the elements of array arguments sumArray(scores)
-ForEach Aggregate array elements position by position sumForEach(arr)
-OrDefault / -OrNull Return a default/NULL when there are no rows maxOrNull(ms)
-Distinct Over distinct values sumDistinct(x)
-Map Aggregate per key of a map sumMap(m)
-State / -Merge Store and combine partial states uniqState, uniqMerge
-Resample Aggregate in range buckets rarely used

-If is the star: it replaces SUM(CASE WHEN ...) and lets one scan answer many questions at once.

SQL
SELECT
    toDate(ts)                                  AS day,
    count()                                     AS requests,
    countIf(status >= 500)                      AS errors,
    round(100 * errors / requests, 2)           AS error_pct,
    quantileIf(0.95)(ms, status = 200)          AS p95_ok,
    uniqIf(user_id, country = 'PT')             AS pt_visitors
FROM requests
GROUP BY day
ORDER BY day;

Note that errors and requests, aliases defined earlier in the same SELECT, are reused in the next expression. One pass over the table computes five metrics.

SQL Stand-in: -If as SUM(CASE ...) (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

State, Merge and rollups

-State returns an opaque partial state that can be stored in an AggregateFunction column and merged later with -Merge, as shown in summing and aggregating engines. Materialized views use exactly this to keep dashboards instant (see the next chapter).

GROUP BY options

SQL
GROUP BY day, country WITH TOTALS             -- extra totals block
GROUP BY ROLLUP(day, country)                 -- subtotals
GROUP BY GROUPING SETS ((day), (country), ())

Memory use grows with the number of groups. For very high-cardinality GROUP BY, setting max_bytes_before_external_group_by lets aggregation spill to disk, and optimize_aggregation_in_order helps when the group columns match the table's sort key.

Common beginner mistakes

  • Using uniqExact on a billion distinct values and running out of memory.
  • Using any when the specific row matters (use argMax).
  • Averaging percentages or percentiles across groups (combine states instead).
  • Writing SUM(CASE ...) everywhere when -If is shorter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function returns the plan from the row with the latest updated_at in each group?

    argMax(value, key) returns the value in the row where key is maximal.
  2. 2. What does countIf(status >= 500) do?

    The -If combinator aggregates only the rows where the condition holds.
  3. 3. When should you prefer uniqExact over uniq?

    uniq is approximate and fast; uniqExact is precise but uses more memory.
  4. 4. What do the -State and -Merge combinators do together?

    They make pre-aggregation possible for any aggregate function.

Summary

  • ClickHouse has fast approximate aggregates (uniq, quantile, topK) and exact ones (uniqExact, quantileExact).
  • argMax and argMin fetch the value at the extreme of a key, replacing self-joins.
  • Combinators (-If, -Array, -OrDefault, -State, -Merge) extend every aggregate.
  • One scan with several -If aggregates answers many questions at once.

Next: Joins, subqueries and dictionaries