ClickHouse-Aggregatfunktionen: uniqHLL12, quantileTDigest, topK

ClickHouse-Aggregatfunktionen: Wie ich aufgehört habe, uniqHLL12 und quantileTDigest zu fürchten

In der Wettanalyse mussten wir eindeutige Spieler pro Stunde zählen. In PostgreSQL hätte ich COUNT(DISTINCT user_id) geschrieben und einen Kaffee geholt. In ClickHouse dauerte dieselbe Abfrage bei 500 Millionen Zeilen 30 Sekunden. Aber das Geschäft benötigte ein Dashboard, das sich alle 5 Sekunden aktualisiert.

Da entdeckte ich uniqHLL12() — approximatives Zählen mit 1-2% Fehler, aber in 0,2 Sekunden. Wir stellten darauf um, und das Dashboard lief. Der Direktor bemerkte den Unterschied in den Zahlen nicht, aber er bemerkte die Geschwindigkeit.

ClickHouse bietet nicht nur Standard-Mathematik, sondern auch Dutzende spezialisierter Aggregationen, von denen PostgreSQL nie geträumt hat. Unten ist alles, was ich in echten Projekten verwende.

1. Standard-Aggregate: Vertraut, aber schneller

-- Gesamtstatistik für alle Wetten
SELECT 
    count() AS total_bets,                    -- Anzahl der Wetten
    sum(amount) AS total_staked,              -- Gesamtwetteinsatz
    avg(amount) AS avg_bet,                   -- Durchschnittswette
    min(created_at) AS first_bet_time,        -- erste Wette
    max(created_at) AS last_bet_time,         -- letzte Wette
    max(amount) - min(amount) AS range        -- Spanne
FROM betting.bets
WHERE created_at >= today() - 7;

Unterschied zu PostgreSQL: count(*) und count() funktionieren gleich. Aber count(DISTINCT user_id) ist langsam — verwenden Sie spezialisierte Funktionen.

2. Eindeutige Benutzer: Genauigkeit vs. Geschwindigkeit

ClickHouse hat drei Ansätze zum Zählen eindeutiger Werte:

-- Genau, aber langsam (30 Sekunden bei 500 Millionen Zeilen)
SELECT count(DISTINCT user_id) FROM betting.bets;

-- Genau, aber ohne syntaktischen Zucker
SELECT uniqExact(user_id) FROM betting.bets;

-- Approximativ, schnell (0,2 Sekunden, 1-2% Fehler)
SELECT uniq(user_id) FROM betting.bets;

-- HyperLogLog mit kontrolliertem Fehler (wir verwenden dies)
SELECT uniqHLL12(user_id) FROM betting.bets;

-- Noch schneller, aber größerer Fehler
SELECT uniqCombined(user_id) FROM betting.bets;

Wann was verwenden:

Funktion	Fehler	Geschwindigkeit	Wo ich es anwende
uniqExact()	0%	Langsam	Steuerberichte, exakte Auszahlungen
uniqHLL12()	1-2%	Sehr schnell	Dashboards, Trends, KPIs
uniq()	2-4%	Schnell	Echtzeit-Analysen
uniqCombined()	5-8%	Sofort	Explorative Analysen, Schätzungen

Reales Produktionsbeispiel: Um eindeutige Spieler pro Stunde in einem Live-Dashboard zu zählen, verwenden wir uniqHLL12(user_id). 99% Genauigkeit ist akzeptabel, und das Dashboard aktualisiert sich alle 3 Sekunden statt 30.

3. Quantile: Wettverteilung ohne Histogramme

Die Frage „Wie viele Spieler setzen weniger als 100 Rubel und wie viele mehr als 1000?“ betrifft Quantile.

-- 50. Perzentil (Median)
SELECT quantile(0.5)(amount) FROM betting.bets;

-- 90. Perzentil (90% der Wetten liegen unter diesem Betrag)
SELECT quantile(0.9)(amount) FROM betting.bets;

-- Mehrere Quantile auf einmal
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;

-- Approximatives Quantil (10x schneller)
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;

-- Genau, aber langsam (In-Memory-Sortierung)
SELECT quantileExact(0.9)(amount) FROM betting.bets;

Wo ich auf die Nase gefallen bin: quantileExact() bei 1 Milliarde Zeilen frisst den gesamten Speicher. Wir wechselten zu quantileTDigest() — 0,5% Fehler, 100 MB Speicher statt 8 GB.

Realer Anwendungsfall: Schwellenwerte für Betrugserkennung bestimmen. Wenn 99% der Wetten unter 50.000 Rubel liegen und ein Spieler 500.000 setzt — zur Überprüfung senden.

4. groupArray: Werte in einem Array sammeln

Manchmal muss man nicht aggregieren, sondern alle Werte erhalten.

-- Alle Wetten eines Benutzers pro Tag in einem 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  -- Arbeiten mit Array
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;

Fortgeschrittener Fall: Gleitende Summen und Durchschnitte.

-- Gleitende Summe der letzten 3 Wetten pro Benutzer
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;

Wann dies wirklich nötig ist: Analyse von Wettsequenzen — ein Bot setzt identische Beträge hintereinander, ein Live-Spieler variiert.

5. topK: Keine Notwendigkeit, exakte Werte zu zählen

Frage: „Was sind die 5 beliebtesten Sportarten?“ GROUP BY sport ORDER BY count() DESC LIMIT 5 funktioniert, aber bei 1 Milliarde Zeilen baut es eine Hash-Tabelle für eine Million eindeutige Werte auf.

-- Approximative Top (schnell)
SELECT topK(5)(sport) FROM betting.bets;

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

-- Genau, aber langsam
SELECT sport, count() AS cnt 
FROM betting.bets 
GROUP BY sport 
ORDER BY cnt DESC 
LIMIT 5;

Geschwindigkeitsunterschied: Bei 10 Milliarden Zeilen führt topK in 0,5 Sekunden aus, exaktes GROUP BY in 15 Sekunden. Für ein automatisch aktualisierendes Dashboard ist die Wahl offensichtlich.

6. -If-Kombinatoren: Bedingte Aggregation ohne Unterabfragen

Statt SUM(CASE WHEN ...) schreiben Sie sumIf() — es liest sich besser und läuft schneller.

-- Gewinnende und verlierende Wetten in einer Zeile
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,
    -- Gewinnrate über Bedingung
    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;

Andere -If-Funktionen: avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().

7. AggregateFunction-Typ und -State/-Merge-Kombinatoren: Für materialisierte Ansichten

ClickHouses mächtigstes Werkzeug: Sie können nicht Daten, sondern ZWISCHENZUSTÄNDE der Aggregation speichern.

-- Tabelle mit Aggregatzuständen
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);

-- Einfügen mit -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;

-- Ergebnis mit -Merge abrufen
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;

Wo ich dies verwende: Materialisierte Ansichten für stündliche Aggregation. Statt jedes Mal 2 Milliarden Zeilen neu zu berechnen, speichere ich Zustände und führe sie einfach zusammen.

8. Andere Kombinatoren: -OrDefault, -OrNull, -Array

-- -OrDefault: gibt Standardwert statt NULL zurück
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0;  -- 0, nicht NULL

-- -OrNull: gibt NULL zurück, wenn keine Zeilen
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0;  -- NULL

-- -Array: Aggregation über Array-Elemente
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- glätten: [1,2,3,4,5,6]

9. runningAccumulate: Kumulative Summen (Fensterfunktionen auf Steroiden)

ClickHouse unterstützt Fensterfunktionen, aber runningAccumulate ist eine ältere (und manchmal schnellere) Form.

-- Kumulative Summe der Wetten pro Tag
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;

Warum ich manchmal Fensterfunktionen bevorzuge: runningAccumulate erfordert strenge Reihenfolge und unterstützt kein PARTITION BY. Jetzt schreibe ich sum(amount) OVER (ORDER BY day).

10. Praktische Beispiele: GGR, Kohorten, rollierende Retention

Beispiel 1: Täglicher GGR (Bruttospielertrag)

GGR = Gesamteinsätze - Gesamtauszahlungen.

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;

Beispiel 2: Kohortenanalyse (Spielerbindung nach Tag)

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;

Beispiel 3: Rollierende 7-Tage-Retention

SELECT 
    toDate(created_at) AS day,
    uniqHLL12(user_id) AS dau,
    -- Benutzer, die auch vor 7 Tagen aktiv waren
    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;

Häufige Fehler bei der Arbeit mit Aggregationen

Fehler 1: COUNT(DISTINCT col) bei Milliarden von Zeilen.
Lösung: uniqHLL12(col) oder uniqExact(col), wenn Genauigkeit erforderlich ist.

Fehler 2: GROUP BY auf einer hochkardinalen Spalte (user_id) ohne Filter.
Lösung: immer WHERE oder HAVING mit einer Grenze hinzufügen.

Fehler 3: quantileExact() bei großen Datenmengen.
Lösung: quantileTDigest() oder quantile(0.9) ohne Exact.

Fehler 4: arrayJoin innerhalb einer Aggregation ohne Verständnis verwenden.
Lösung: Denken Sie daran, dass arrayJoin Zeilen multipliziert. Besser Daten denormalisieren.

Was kommt als Nächstes

Aggregationen sind das Herz der Analytik. Nächster Artikel — über Fensterfunktionen und statistische Tests in ClickHouse.

---

← Vorherige: SELECT-Abfragen in ClickHouse: Wie ich mein Gehirn nach 10 Jahren PostgreSQL umtrainierte
→ Nächste: ClickHouse-Konfiguration: Wie ich die Produktion eingerichtet habe und mir nicht ins Bein geschossen habe

— Editorial Team