Funkcje agregujące ClickHouse: jak przestałem bać się uniqHLL12 i quantileTDigest
W analityce bukmacherskiej musieliśmy liczyć unikalnych graczy na godzinę. W PostgreSQL napisałbym COUNT(DISTINCT user_id) i poszedł napić się herbaty. W ClickHouse z 500 mln wierszy to samo zapytanie wykonało się w 30 sekund. Ale biznes wymagał dashboardu z odświeżaniem co 5 sekund.
Wtedy poznałem uniqHLL12() — przybliżone zliczanie z błędem 1-2%, ale w 0,2 sekundy. Przerzuciliśmy się na niego i dashboard wystartował. Dyrektor nie zauważył różnicy w liczbach, za to zauważył szybkość.
ClickHouse daje nie tylko standardową matematykę, ale i dziesiątki specjalistycznych agregacji, o których PostgreSQL nawet nie śnił. Poniżej — wszystko, czego używam w realnych projektach.
1. Standardowe agregaty: znajome, ale szybsze
-- Ogólna statystyka wszystkich zakładów
SELECT
count() AS total_bets, -- liczba zakładów
sum(amount) AS total_staked, -- łączna kwota zakładów
avg(amount) AS avg_bet, -- średni zakład
min(created_at) AS first_bet_time, -- pierwszy zakład
max(created_at) AS last_bet_time, -- ostatni zakład
max(amount) - min(amount) AS range -- rozstęp
FROM betting.bets
WHERE created_at >= today() - 7;Różnica względem PostgreSQL: count(*) i count() działają tak samo. Ale count(DISTINCT user_id) — wolne, używaj wyspecjalizowanych funkcji.
2. Unikalni użytkownicy: dokładność kontra szybkość
ClickHouse ma trzy podejścia do liczenia unikalnych wartości:
-- Dokładne, ale wolne (30 sekund na 500 mln wierszy)
SELECT count(DISTINCT user_id) FROM betting.bets;
-- Dokładne, ale bez lukru składniowego
SELECT uniqExact(user_id) FROM betting.bets;
-- Przybliżone, szybkie (0,2 sekundy, błąd 1-2%)
SELECT uniq(user_id) FROM betting.bets;
-- Hiperloglog z kontrolowanym błędem (my go używamy)
SELECT uniqHLL12(user_id) FROM betting.bets;
-- Jeszcze szybsze, ale większy błąd
SELECT uniqCombined(user_id) FROM betting.bets;Kiedy czego używać:
| Funkcja | Błąd | Szybkość | Gdzie stosuję |
|---|---|---|---|
uniqExact() |
0% | Wolno | Raporty dla skarbówki, dokładne wypłaty |
uniqHLL12() |
1-2% | Bardzo szybko | Dashboardy, trendy, KPI |
uniq() |
2-4% | Szybko | Analityka w czasie rzeczywistym |
uniqCombined() |
5-8% | Błyskawicznie | Analiza eksploracyjna, szacunki |
Realny przykład z produkcji: do liczenia unikalnych graczy na godzinę w live dashboardzie używamy uniqHLL12(user_id). Dokładność 99% nam odpowiada, a dashboard odświeża się co 3 sekundy zamiast 30.
3. Kwantyle: rozkład zakładów bez histogramów
Pytanie "ilu graczy stawia mniej niż 100 zł, a ilu więcej niż 1000 zł?" — to kwantyle.
-- 50. percentyl (mediana)
SELECT quantile(0.5)(amount) FROM betting.bets;
-- 90. percentyl (90% zakładów poniżej tej kwoty)
SELECT quantile(0.9)(amount) FROM betting.bets;
-- Kilka kwantyli naraz
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;
-- Przybliżony kwantyl (10x szybszy)
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;
-- Dokładny, ale wolny (sortowanie w pamięci)
SELECT quantileExact(0.9)(amount) FROM betting.bets;Na czym się sparzyłem: quantileExact() na 1 mld wierszy zżera całą pamięć. Przeszliśmy na quantileTDigest() — błąd 0,5%, pamięć 100 MB zamiast 8 GB.
Realny use-case: określamy progi do detekcji fraudów. Jeśli 99% zakładów jest poniżej 50 000 zł, a gracz stawia 500 000 — wysyłamy do weryfikacji.
4. groupArray: zbieramy wartości do tablicy
Czasem trzeba nie agregować, ale zachować wszystkie wartości.
-- Wszystkie zakłady użytkownika w ciągu dnia w tablicy
SELECT
user_id,
toDate(created_at) AS day,
groupArray(amount) AS amounts,
groupArray(odds) AS odds_list,
arrayMap(x -> x * 2, amounts) AS doubled -- pracujemy z tablicą
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;Zaawansowany przypadek: sumy kroczące i średnie.
-- Suma krocząca za ostatnie 3 zakłady użytkownika
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;Kiedy to jest naprawdę potrzebne: analiza sekwencji zakładów — bot stawia identyczne kwoty pod rząd, żywy gracz — różne.
5. topK: nie trzeba liczyć dokładnych wartości
Pytanie: "Jakie 5 dyscyplin sportowych jest najpopularniejszych?" GROUP BY sport ORDER BY count() DESC LIMIT 5 — działa, ale na 1 mld wierszy buduje hash-tabelę na milion unikalnych wartości.
-- Przybliżone top (szybko)
SELECT topK(5)(sport) FROM betting.bets;
-- Wynik: ['football', 'basketball', 'tennis', 'hockey', 'mma']
-- Dokładne, ale wolne
SELECT sport, count() AS cnt
FROM betting.bets
GROUP BY sport
ORDER BY cnt DESC
LIMIT 5;Różnica w szybkości: na 10 mld wierszy topK wykonuje się 0,5 sekundy, dokładny GROUP BY — 15 sekund. Dla dashboardu z auto-odświeżaniem wybór jest oczywisty.
6. Kombinatory -If: agregacja warunkowa bez podzapytań
Zamiast SUM(CASE WHEN ...) pisz sumIf() — czyta się lepiej i działa szybciej.
-- Zakłady wygrane i przegrane w jednym wierszu
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 przez warunek
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;Inne funkcje -If: avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().
7. Typ AggregateFunction i kombinatory -State/-Merge: dla widoków zmaterializowanych
Najpotężniejsze narzędzie ClickHouse: można przechowywać nie dane, ale STAN POŚREDNI agregacji.
-- Tabela ze stanami agregacji
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);
-- Wstawianie z -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;
-- Pobieranie wyniku z -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;Gdzie tego używam: widoki zmaterializowane do agregacji godzinowej. Zamiast przeliczać 2 mld wierszy za każdym razem, przechowuję stany i po prostu je merguję.
8. Inne kombinatory: -OrDefault, -OrNull, -Array
-- -OrDefault: zwraca domyślną wartość zamiast NULL
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0; -- 0, a nie NULL
-- -OrNull: zwraca NULL, jeśli brak wierszy
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0; -- NULL
-- -Array: agregacja po elementach tablicy
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- flatten: [1,2,3,4,5,6]9. runningAccumulate: sumy skumulowane (funkcje okienne na sterydach)
ClickHouse obsługuje funkcje okienne, ale runningAccumulate to starsza (i czasem szybsza) forma.
-- Skumulowana suma zakładów po dniach
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;Dlaczego czasem wolę funkcje okienne: runningAccumulate wymaga ścisłego porządku i nie obsługuje PARTITION BY. Teraz piszę przez sum(amount) OVER (ORDER BY day).
10. Praktyczne przykłady: GGR, kohorty, krocząca retencja
Przykład 1: Daily GGR (Gross Gaming Revenue)
GGR = suma zakładów - suma wypłat.
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;Przykład 2: Analiza kohort (retencja graczy po dniach)
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;Przykład 3: Krocząca 7-dniowa retencja (rolling retention)
SELECT
toDate(created_at) AS day,
uniqHLL12(user_id) AS dau,
-- Użytkownicy, którzy byli aktywni i 7 dni temu
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;Typowe błędy przy pracy z agregacjami
Błąd 1: COUNT(DISTINCT col) na miliardach wierszy.
Rozwiązanie: uniqHLL12(col) lub uniqExact(col) jeśli potrzebna dokładność.
Błąd 2: GROUP BY po wysokokardynalnej kolumnie (user_id) bez filtra.
Rozwiązanie: zawsze dodawaj WHERE lub HAVING z limitem.
Błąd 3: quantileExact() na dużych danych.
Rozwiązanie: quantileTDigest() lub quantile(0.9) bez Exact.
Błąd 4: Używanie arrayJoin wewnątrz agregacji bez zrozumienia.
Rozwiązanie: pamiętaj, że arrayJoin mnoży wiersze. Lepiej denormalizuj dane.
Co dalej
Agregacje to serce analityki. Następny artykuł — o funkcjach okiennych i testach statystycznych w ClickHouse.
← Poprzedni: Zapytania SELECT w ClickHouse: jak przestawiałem mózg po 10 latach PostgreSQL
→ Następny: Konfiguracja ClickHouse: jak skonfigurowałem prod i nie postrzeliłem się w stopę
— Editorial Team
Brak komentarzy.