Agregační funkce ClickHouse: uniqHLL12, quantileTDigest, topK

Agregační funkce ClickHouse: jak jsem se přestal bát uniqHLL12 a quantileTDigest

V sázkové analytice jsme potřebovali počítat unikátní hráče za hodinu. V PostgreSQL bych napsal COUNT(DISTINCT user_id) a šel si uvařit čaj. V ClickHouse se stejným dotazem na 500 milionech řádků to trvalo 30 sekund. Ale business vyžadoval dashboard s obnovou každých 5 sekund.

Tehdy jsem objevil uniqHLL12() — přibližný výpočet s chybou 1–2 %, ale za 0,2 sekundy. Přepnuli jsme na něj a dashboard vylétl. Ředitel si nevšiml rozdílu v číslech, ale všiml si rychlosti.

ClickHouse nabízí nejen standardní matematiku, ale i desítky specializovaných agregací, o kterých se PostgreSQL ani nezdálo. Níže je vše, co používám v reálných projektech.

1. Standardní agregáty: známé, ale rychlejší

-- Celková statistika všech sázek
SELECT 
    count() AS total_bets,                    -- počet sázek
    sum(amount) AS total_staked,              -- celkový objem sázek
    avg(amount) AS avg_bet,                   -- průměrná sázka
    min(created_at) AS first_bet_time,        -- první sázka
    max(created_at) AS last_bet_time,         -- poslední sázka
    max(amount) - min(amount) AS range        -- rozpětí
FROM betting.bets
WHERE created_at >= today() - 7;

Rozdíl oproti PostgreSQL: count(*) a count() fungují stejně. Ale count(DISTINCT user_id) je pomalé – používejte specializované funkce.

2. Unikátní uživatelé: přesnost versus rychlost

ClickHouse má tři přístupy k počítání unikátních hodnot:

-- Přesný, ale pomalý (30 sekund na 500 mil. řádků)
SELECT count(DISTINCT user_id) FROM betting.bets;

-- Přesný, ale bez syntaktického cukru
SELECT uniqExact(user_id) FROM betting.bets;

-- Přibližný, rychlý (0,2 sekundy, chyba 1–2 %)
SELECT uniq(user_id) FROM betting.bets;

-- HyperLogLog s řízenou chybou (používáme ho)
SELECT uniqHLL12(user_id) FROM betting.bets;

-- Ještě rychlejší, ale větší chyba
SELECT uniqCombined(user_id) FROM betting.bets;

Kdy co použít:

Funkce	Chyba	Rychlost	Kde používám
uniqExact()	0 %	Pomalé	Výkazy pro finanční úřad, přesné platby
uniqHLL12()	1–2 %	Velmi rychlé	Dashboardy, trendy, KPI
uniq()	2–4 %	Rychlé	Analýza v reálném čase
uniqCombined()	5–8 %	Okamžité	Průzkumná analýza, odhady

Reálný příklad z provozu: pro počítání unikátních hráčů za hodinu v live dashboardu používáme uniqHLL12(user_id). Přesnost 99 % nám vyhovuje a dashboard se obnovuje každé 3 sekundy místo 30.

3. Kvantily: rozdělení sázek bez histogramů

Otázka „kolik hráčů sází méně než 100 Kč a kolik více než 1000 Kč?“ – to jsou kvantily.

-- 50. percentil (medián)
SELECT quantile(0.5)(amount) FROM betting.bets;

-- 90. percentil (90 % sázek je nižších než tato částka)
SELECT quantile(0.9)(amount) FROM betting.bets;

-- Několik kvantilů najednou
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;

-- Přibližný kvantil (10× rychlejší)
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;

-- Přesný, ale pomalý (třídění v paměti)
SELECT quantileExact(0.9)(amount) FROM betting.bets;

Na čem jsem se spálil: quantileExact() na 1 miliardě řádků sežere všechnu paměť. Přesedlali jsme na quantileTDigest() – chyba 0,5 %, paměť 100 MB místo 8 GB.

Reálný use-case: určujeme prahy pro detekci podvodů. Pokud je 99 % sázek nižších než 50 000 Kč a hráč vsadí 500 000 Kč – posíláme ke kontrole.

4. groupArray: sbíráme hodnoty do pole

Někdy není potřeba agregovat, ale zachovat všechny hodnoty.

-- Všechny sázky uživatele za den v poli
SELECT 
    user_id,
    toDate(created_at) AS day,
    groupArray(amount) AS amounts,
    groupArray(odds) AS odds_list,
    arrayMap(x -> x * 2, amounts) AS doubled  -- práce s polem
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;

Pokročilý případ: klouzavé součty a průměry.

-- Klouzavý součet za poslední 3 sázky uživatele
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;

Kdy je to opravdu potřeba: analýza sekvence sázek – bot sází stejné částky po sobě, živý hráč různé.

5. topK: není třeba počítat přesné hodnoty

Otázka: „Kterých 5 sportů je nejoblíbenějších?“ GROUP BY sport ORDER BY count() DESC LIMIT 5 funguje, ale na 1 miliardě řádků staví hash tabulku na milion unikátních hodnot.

-- Přibližný top (rychle)
SELECT topK(5)(sport) FROM betting.bets;

-- Výsledek: ['football', 'basketball', 'tennis', 'hockey', 'mma']

-- Přesný, ale pomalý
SELECT sport, count() AS cnt 
FROM betting.bets 
GROUP BY sport 
ORDER BY cnt DESC 
LIMIT 5;

Rozdíl v rychlosti: na 10 miliardách řádků trvá topK 0,5 sekundy, přesný GROUP BY 15 sekund. Pro dashboard s automatickou obnovou je volba jasná.

6. -If kombinátory: podmíněná agregace bez poddotazů

Místo SUM(CASE WHEN ...) pište sumIf() – lépe se čte a pracuje rychleji.

-- Výherní a proherní sázky v jednom řádku
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 pomocí podmínky
    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;

Další -If funkce: avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().

7. Typ AggregateFunction a kombinátory -State/-Merge: pro materializované pohledy

Nejvýkonnější nástroj ClickHouse: můžete ukládat nikoli data, ale PRŮBĚŽNÝ STAV agregace.

-- Tabulka s agregačními stavy
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);

-- Vložení s -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;

-- Získání výsledku s -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;

Kde to používám: materializované pohledy pro hodinovou agregaci. Místo přepočítávání 2 miliard řádků pokaždé ukládám stavy a jen je slučuji.

8. Další kombinátory: -OrDefault, -OrNull, -Array

-- -OrDefault: vrací výchozí hodnotu místo NULL
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0;  -- 0, ne NULL

-- -OrNull: vrací NULL, pokud nejsou řádky
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0;  -- NULL

-- -Array: agregace podle prvků pole
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- flatten: [1,2,3,4,5,6]

9. runningAccumulate: kumulativní součty (okenní funkce na steroidech)

ClickHouse podporuje okenní funkce, ale runningAccumulate je starší (a někdy rychlejší) forma.

-- Kumulativní součet sázek podle dní
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;

Proč někdy preferuji okenní funkce: runningAccumulate vyžaduje striktní pořadí a nepodporuje PARTITION BY. Teď píšu přes sum(amount) OVER (ORDER BY day).

10. Praktické příklady: GGR, kohorty, klouzavá retence

Příklad 1: Daily GGR (Gross Gaming Revenue)

GGR = objem sázek – objem výher.

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;

Příklad 2: Cohort analysis (retence hráčů podle dní)

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;

Příklad 3: Klouzavá 7denní retence (rolling retention)

SELECT 
    toDate(created_at) AS day,
    uniqHLL12(user_id) AS dau,
    -- Uživatelé, kteří byli aktivní i před 7 dny
    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;

Typické chyby při práci s agregacemi

Chyba 1: COUNT(DISTINCT col) na miliardách řádků.
Řešení: uniqHLL12(col) nebo uniqExact(col), pokud potřebujete přesnost.

Chyba 2: GROUP BY na vysokokardinalitním sloupci (user_id) bez filtru.
Řešení: vždy přidejte WHERE nebo HAVING s limitem.

Chyba 3: quantileExact() na velkých datech.
Řešení: quantileTDigest() nebo quantile(0.9) bez Exact.

Chyba 4: Použití arrayJoin uvnitř agregace bez porozumění.
Řešení: pamatujte, že arrayJoin násobí řádky. Raději data denormalizujte.

Co dál

Agregace jsou srdcem analytiky. Další článek – o okenních funkcích a statistických testech v ClickHouse.

---

← Předchozí: SELECT dotazy v ClickHouse: jak jsem přeučoval mozek po 10 letech PostgreSQL
→ Další: Konfigurace ClickHouse: jak jsem nastavoval prod a nestřelil se do nohy

— Editorial Team