ClickHouse aggregate functions: uniqHLL12, quantileTDigest, topK

ClickHouse Aggregate Functions: How I Stopped Fearing uniqHLL12 and quantileTDigest

In betting analytics, we needed to count unique players per hour. In PostgreSQL, I would write COUNT(DISTINCT user_id) and go grab a coffee. In ClickHouse, with 500 million rows, the same query executed in 30 seconds. But the business required a dashboard refreshing every 5 seconds.

That's when I discovered uniqHLL12() — approximate counting with a 1-2% error, but in 0.2 seconds. We switched to it, and the dashboard took off. The director didn't notice the difference in numbers, but he did notice the speed.

ClickHouse provides not only standard math but also dozens of specialized aggregations that PostgreSQL never dreamed of. Below is everything I use in real projects.

1. Standard Aggregates: Familiar but Faster

-- Overall statistics for all bets
SELECT 
    count() AS total_bets,                    -- number of bets
    sum(amount) AS total_staked,              -- total bet amount
    avg(amount) AS avg_bet,                   -- average bet
    min(created_at) AS first_bet_time,        -- first bet
    max(created_at) AS last_bet_time,         -- last bet
    max(amount) - min(amount) AS range        -- range
FROM betting.bets
WHERE created_at >= today() - 7;

Difference from PostgreSQL: count(*) and count() work the same. But count(DISTINCT user_id) is slow — use specialized functions.

2. Unique Users: Accuracy vs. Speed

ClickHouse has three approaches to counting unique values:

-- Accurate but slow (30 seconds on 500 million rows)
SELECT count(DISTINCT user_id) FROM betting.bets;

-- Accurate but without syntactic sugar
SELECT uniqExact(user_id) FROM betting.bets;

-- Approximate, fast (0.2 seconds, 1-2% error)
SELECT uniq(user_id) FROM betting.bets;

-- HyperLogLog with controlled error (we use this)
SELECT uniqHLL12(user_id) FROM betting.bets;

-- Even faster, but larger error
SELECT uniqCombined(user_id) FROM betting.bets;

When to use what:

Function	Error	Speed	Where I apply
uniqExact()	0%	Slow	Tax reports, exact payouts
uniqHLL12()	1-2%	Very fast	Dashboards, trends, KPIs
uniq()	2-4%	Fast	Real-time analytics
uniqCombined()	5-8%	Instant	Exploratory analysis, estimates

Real production example: to count unique players per hour in a live dashboard, we use uniqHLL12(user_id). 99% accuracy is acceptable, and the dashboard refreshes every 3 seconds instead of 30.

3. Quantiles: Bet Distribution Without Histograms

The question "how many players bet less than 100 rubles, and how many more than 1000?" is about quantiles.

-- 50th percentile (median)
SELECT quantile(0.5)(amount) FROM betting.bets;

-- 90th percentile (90% of bets are below this amount)
SELECT quantile(0.9)(amount) FROM betting.bets;

-- Multiple quantiles at once
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;

-- Approximate quantile (10x faster)
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;

-- Accurate but slow (in-memory sorting)
SELECT quantileExact(0.9)(amount) FROM betting.bets;

Where I got burned: quantileExact() on 1 billion rows eats all memory. We switched to quantileTDigest() — 0.5% error, 100 MB memory instead of 8 GB.

Real use case: determining thresholds for fraud detection. If 99% of bets are under 50,000 rubles and a player bets 500,000 — send for review.

4. groupArray: Collecting Values into an Array

Sometimes you need not to aggregate but to preserve all values.

-- All bets of a user per day in an array
SELECT 
    user_id,
    toDate(created_at) AS day,
    groupArray(amount) AS amounts,
    groupArray(odds) AS odds_list,
    arrayMap(x -> x * 2, amounts) AS doubled  -- working with array
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;

Advanced case: moving sums and averages.

-- Moving sum of last 3 bets per user
SELECT 
    user_id,
    created_at,
    amount,
    groupArrayMovingSum(3)(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS moving_sum
FROM betting.bets
WHERE user_id = 1001
ORDER BY created_at;

When this is really needed: analyzing bet sequences — a bot places identical amounts in a row, a live player varies.

5. topK: No Need to Count Exact Values

Question: "What are the 5 most popular sports?" GROUP BY sport ORDER BY count() DESC LIMIT 5 works, but on 1 billion rows it builds a hash table for a million unique values.

-- Approximate top (fast)
SELECT topK(5)(sport) FROM betting.bets;

-- Result: ['football', 'basketball', 'tennis', 'hockey', 'mma']

-- Accurate but slow
SELECT sport, count() AS cnt 
FROM betting.bets 
GROUP BY sport 
ORDER BY cnt DESC 
LIMIT 5;

Speed difference: on 10 billion rows, topK executes in 0.5 seconds, exact GROUP BY in 15 seconds. For an auto-refreshing dashboard, the choice is obvious.

6. -If Combinators: Conditional Aggregation Without Subqueries

Instead of SUM(CASE WHEN ...), write sumIf() — it reads better and runs faster.

-- Winning and losing bets in one row
SELECT 
    user_id,
    countIf(outcome = 'win') AS wins,
    countIf(outcome = 'loss') AS losses,
    sumIf(amount, outcome = 'win') AS winning_stake,
    sumIf(amount, outcome = 'loss') AS losing_stake,
    avgIf(odds, outcome = 'win') AS avg_win_odds,
    -- Win rate via condition
    round(wins / (wins + losses), 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
HAVING wins + losses > 50
ORDER BY win_rate DESC
LIMIT 20;

Other -If functions: avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().

7. AggregateFunction Type and -State/-Merge Combinators: For Materialized Views

ClickHouse's most powerful tool: you can store not data but INTERMEDIATE aggregation states.

-- Table with aggregate states
CREATE TABLE betting.daily_agg
(
    day Date,
    user_id UInt64,
    total_bets AggregateFunction(count, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_sports AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, user_id);

-- Insert with -State
INSERT INTO betting.daily_agg
SELECT 
    toDate(created_at) AS day,
    user_id,
    countState() AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(sport) AS unique_sports
FROM betting.bets
GROUP BY day, user_id;

-- Get result with -Merge
SELECT 
    day,
    user_id,
    countMerge(total_bets) AS bets,
    sumMerge(total_amount) AS total_staked,
    uniqMerge(unique_sports) AS unique_sports_count
FROM betting.daily_agg
GROUP BY day, user_id;

Where I use this: materialized views for hourly aggregation. Instead of recalculating 2 billion rows each time, I store states and simply merge them.

8. Other Combinators: -OrDefault, -OrNull, -Array

-- -OrDefault: returns default instead of NULL
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0;  -- 0, not NULL

-- -OrNull: returns NULL if no rows
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0;  -- NULL

-- -Array: aggregation over array elements
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- flatten: [1,2,3,4,5,6]

9. runningAccumulate: Cumulative Sums (Window Functions on Steroids)

ClickHouse supports window functions, but runningAccumulate is an older (and sometimes faster) form.

-- Cumulative sum of bets by day
SELECT 
    toDate(created_at) AS day,
    sum(amount) AS daily_amount,
    runningAccumulate(sum(amount)) OVER (ORDER BY day) AS cumulative_amount
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY day
ORDER BY day;

Why I sometimes prefer window functions: runningAccumulate requires strict ordering and does not support PARTITION BY. Now I write sum(amount) OVER (ORDER BY day).

10. Practical Examples: GGR, Cohorts, Rolling Retention

Example 1: Daily GGR (Gross Gaming Revenue)

GGR = total bets - total payouts.

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_paid,
    total_staked - total_paid AS ggr,
    round(ggr / total_staked, 4) AS hold_percentage
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

Example 2: Cohort Analysis (Player Retention by Day)

WITH cohorts AS (
    SELECT 
        user_id,
        toDate(min(created_at)) AS cohort_day
    FROM betting.bets
    GROUP BY user_id
),
user_activity AS (
    SELECT 
        b.user_id,
        c.cohort_day,
        toDate(b.created_at) AS activity_day,
        datediff('day', c.cohort_day, activity_day) AS day_number
    FROM betting.bets b
    JOIN cohorts c ON b.user_id = c.user_id
    WHERE day_number <= 30
)
SELECT 
    cohort_day,
    day_number,
    uniqHLL12(user_id) AS active_users
FROM user_activity
GROUP BY cohort_day, day_number
ORDER BY cohort_day DESC, day_number;

Example 3: Rolling 7-Day Retention

SELECT 
    toDate(created_at) AS day,
    uniqHLL12(user_id) AS dau,
    -- Users who were active also 7 days ago
    uniqHLL12If(user_id, 
        created_at >= today() - 7 AND created_at < today() - 6
    ) AS retained_users,
    round(retained_users / uniqHLL12If(user_id, 
        created_at >= today() - 14 AND created_at < today() - 13
    ), 4) AS retention_7d
FROM betting.bets
GROUP BY day
ORDER BY day DESC;

Common Mistakes When Working with Aggregations

Mistake 1: COUNT(DISTINCT col) on billions of rows.
Solution: uniqHLL12(col) or uniqExact(col) if accuracy is needed.

Mistake 2: GROUP BY on a high-cardinality column (user_id) without a filter.
Solution: always add WHERE or HAVING with a limit.

Mistake 3: quantileExact() on large data.
Solution: quantileTDigest() or quantile(0.9) without Exact.

Mistake 4: Using arrayJoin inside aggregation without understanding.
Solution: remember that arrayJoin multiplies rows. Better to denormalize data.

What's Next

Aggregations are the heart of analytics. Next article — about window functions and statistical tests in ClickHouse.

---

← Previous: SELECT Queries in ClickHouse: How I Retrained My Brain After 10 Years of PostgreSQL
→ Next: ClickHouse Configuration: How I Set Up Production and Didn't Shoot Myself in the Foot

— Editorial Team