Accueil Découverte Tech Anti-stress
🇬🇧 English 🇪🇸 Español 🇩🇪 Deutsch 🇫🇷 Français 🇵🇱 Polski 🇨🇿 Česky 🇨🇳 中文 🇰🇷 한국어
Retour à l'accueil

Fonctions d'agrégation ClickHouse : uniqHLL12, quantileTDigest, topK

Aperçu complet des fonctions d'agrégation ClickHouse avec des exemples sur une table de paris réelle. Couvre les fonctions standard count/sum/avg, les fonctions spécialisées pour les comptages uniques (uniqExact, uniqHLL12, uniqCombined) avec un tableau de sélection par précision et vitesse, les quantiles (quantile, quantileTDigest, quantileExact) pour les distributions, groupArray et les sommes mobiles (groupArrayMovingSum), les éléments les plus fréquents via topK, les agrégats conditionnels via les combinateurs -If (sumIf, countIf, avgIf), le type AggregateFunction et les combinateurs -State/-Merge pour les vues matérialisées, les combinateurs -OrDefault/-OrNull, runningAccumulate pour les métriques cumulatives. Inclut des requêtes prêtes pour le GGR quotidien (revenu brut des jeux), l'analyse de rétention de cohorte et la rétention mobile sur 7 jours. Inclut des avertissements sur les erreurs typiques (count(DISTINCT), quantileExact sur de grandes données).

By Sudonull Editorial Team • Published • Updated

Agrégations ClickHouse : de count() à AggregateFunction avec -State/-Merge
Advertisement 728x90

Fonctions d'agrégation ClickHouse : comment j'ai cessé de craindre uniqHLL12 et quantileTDigest

Dans l'analyse des paris, nous devions compter les joueurs uniques par heure. Sous PostgreSQL, j'écrivais COUNT(DISTINCT user_id) et j'allais me chercher un café. Sous ClickHouse, avec 500 millions de lignes, la même requête s'exécutait en 30 secondes. Mais le métier exigeait un tableau de bord se rafraîchissant toutes les 5 secondes.

C'est là que j'ai découvert uniqHLL12() — un comptage approximatif avec 1 à 2 % d'erreur, mais en 0,2 seconde. Nous sommes passés à cette fonction, et le tableau de bord a décollé. Le directeur n'a pas remarqué la différence dans les chiffres, mais il a remarqué la rapidité.

ClickHouse propose non seulement des fonctions mathématiques standard, mais aussi des dizaines d'agrégations spécialisées dont PostgreSQL n'a jamais rêvé. Voici tout ce que j'utilise dans des projets réels.

Google AdInline article slot

1. Agrégats standard : familiers mais plus rapides

-- Statistiques globales pour tous les paris
SELECT 
    count() AS total_bets,                    -- nombre de paris
    sum(amount) AS total_staked,              -- montant total misé
    avg(amount) AS avg_bet,                   -- mise moyenne
    min(created_at) AS first_bet_time,        -- premier pari
    max(created_at) AS last_bet_time,         -- dernier pari
    max(amount) - min(amount) AS range        -- étendue
FROM betting.bets
WHERE created_at >= today() - 7;

Différence avec PostgreSQL : count(*) et count() fonctionnent de la même manière. Mais count(DISTINCT user_id) est lent — utilisez des fonctions spécialisées.

2. Utilisateurs uniques : précision vs rapidité

ClickHouse propose trois approches pour compter les valeurs uniques :

-- Précis mais lent (30 secondes sur 500 millions de lignes)
SELECT count(DISTINCT user_id) FROM betting.bets;

-- Précis mais sans sucre syntaxique
SELECT uniqExact(user_id) FROM betting.bets;

-- Approximatif, rapide (0,2 seconde, 1-2 % d'erreur)
SELECT uniq(user_id) FROM betting.bets;

-- HyperLogLog avec erreur contrôlée (nous utilisons celle-ci)
SELECT uniqHLL12(user_id) FROM betting.bets;

-- Encore plus rapide, mais erreur plus grande
SELECT uniqCombined(user_id) FROM betting.bets;

Quand utiliser quoi :

Google AdInline article slot
Fonction Erreur Vitesse Où je l'applique
uniqExact() 0 % Lente Rapports fiscaux, paiements exacts
uniqHLL12() 1-2 % Très rapide Tableaux de bord, tendances, KPI
uniq() 2-4 % Rapide Analyses en temps réel
uniqCombined() 5-8 % Instantanée Analyse exploratoire, estimations

Exemple concret en production : pour compter les joueurs uniques par heure dans un tableau de bord en direct, nous utilisons uniqHLL12(user_id). Une précision de 99 % est acceptable, et le tableau de bord se rafraîchit toutes les 3 secondes au lieu de 30.

3. Quantiles : distribution des mises sans histogrammes

La question « combien de joueurs misent moins de 100 roubles, et combien plus de 1000 ? » concerne les quantiles.

-- 50e percentile (médiane)
SELECT quantile(0.5)(amount) FROM betting.bets;

-- 90e percentile (90 % des mises sont inférieures à ce montant)
SELECT quantile(0.9)(amount) FROM betting.bets;

-- Plusieurs quantiles à la fois
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;

-- Quantile approximatif (10 fois plus rapide)
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;

-- Précis mais lent (tri en mémoire)
SELECT quantileExact(0.9)(amount) FROM betting.bets;

Où je me suis brûlé : quantileExact() sur 1 milliard de lignes consomme toute la mémoire. Nous sommes passés à quantileTDigest() — 0,5 % d'erreur, 100 Mo de mémoire au lieu de 8 Go.

Google AdInline article slot

Cas d'usage réel : déterminer les seuils pour la détection de fraude. Si 99 % des mises sont inférieures à 50 000 roubles et qu'un joueur mise 500 000, envoyez pour examen.

4. groupArray : collecter des valeurs dans un tableau

Parfois, vous n'avez pas besoin d'agréger mais de conserver toutes les valeurs.

-- Tous les paris d'un utilisateur par jour dans un tableau
SELECT 
    user_id,
    toDate(created_at) AS day,
    groupArray(amount) AS amounts,
    groupArray(odds) AS odds_list,
    arrayMap(x -> x * 2, amounts) AS doubled  -- travail avec le tableau
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;

Cas avancé : sommes et moyennes mobiles.

-- Somme mobile des 3 derniers paris par utilisateur
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;

Quand c'est vraiment utile : analyser les séquences de paris — un bot place des montants identiques à la suite, un joueur humain varie.

5. topK : pas besoin de compter les valeurs exactes

Question : « Quels sont les 5 sports les plus populaires ? » GROUP BY sport ORDER BY count() DESC LIMIT 5 fonctionne, mais sur 1 milliard de lignes, il construit une table de hachage pour un million de valeurs uniques.

-- Top approximatif (rapide)
SELECT topK(5)(sport) FROM betting.bets;

-- Résultat : ['football', 'basketball', 'tennis', 'hockey', 'mma']

-- Précis mais lent
SELECT sport, count() AS cnt 
FROM betting.bets 
GROUP BY sport 
ORDER BY cnt DESC 
LIMIT 5;

Différence de vitesse : sur 10 milliards de lignes, topK s'exécute en 0,5 seconde, le GROUP BY exact en 15 secondes. Pour un tableau de bord à rafraîchissement automatique, le choix est évident.

6. Combinateurs -If : agrégation conditionnelle sans sous-requêtes

Au lieu de SUM(CASE WHEN ...), écrivez sumIf() — cela se lit mieux et s'exécute plus vite.

-- Paris gagnants et perdants en une seule ligne
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,
    -- Taux de victoire 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;

Autres fonctions -If : avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().

7. Type AggregateFunction et combinateurs -State/-Merge : pour les vues matérialisées

L'outil le plus puissant de ClickHouse : vous pouvez stocker non pas les données mais les ÉTATS INTERMÉDIAIRES d'agrégation.

-- Table avec états d'agrégation
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);

-- Insertion avec -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;

-- Obtention du résultat avec -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;

Où j'utilise cela : vues matérialisées pour l'agrégation horaire. Au lieu de recalculer 2 milliards de lignes à chaque fois, je stocke les états et les fusionne simplement.

8. Autres combinateurs : -OrDefault, -OrNull, -Array

-- -OrDefault : retourne la valeur par défaut au lieu de NULL
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0;  -- 0, pas NULL

-- -OrNull : retourne NULL s'il n'y a pas de lignes
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0;  -- NULL

-- -Array : agrégation sur les éléments d'un tableau
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- aplatit : [1,2,3,4,5,6]

9. runningAccumulate : sommes cumulées (fonctions de fenêtre en version turbo)

ClickHouse prend en charge les fonctions de fenêtre, mais runningAccumulate est une forme plus ancienne (et parfois plus rapide).

-- Somme cumulée des mises par jour
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;

Pourquoi je préfère parfois les fonctions de fenêtre : runningAccumulate nécessite un ordre strict et ne prend pas en charge PARTITION BY. Maintenant j'écris sum(amount) OVER (ORDER BY day).

10. Exemples pratiques : GGR, cohortes, rétention glissante

Exemple 1 : GGR quotidien (Gross Gaming Revenue)

GGR = total des mises - total des gains.

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;

Exemple 2 : Analyse de cohorte (rétention des joueurs par jour)

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;

Exemple 3 : Rétention glissante sur 7 jours

SELECT 
    toDate(created_at) AS day,
    uniqHLL12(user_id) AS dau,
    -- Utilisateurs qui étaient également actifs il y a 7 jours
    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;

Erreurs courantes lors du travail avec les agrégations

Erreur 1 : COUNT(DISTINCT col) sur des milliards de lignes.
Solution : uniqHLL12(col) ou uniqExact(col) si la précision est nécessaire.

Erreur 2 : GROUP BY sur une colonne à haute cardinalité (user_id) sans filtre.
Solution : ajoutez toujours WHERE ou HAVING avec une limite.

Erreur 3 : quantileExact() sur de grandes données.
Solution : quantileTDigest() ou quantile(0.9) sans Exact.

Erreur 4 : Utiliser arrayJoin dans une agrégation sans comprendre.
Solution : rappelez-vous que arrayJoin multiplie les lignes. Il vaut mieux dénormaliser les données.

Prochaines étapes

Les agrégations sont le cœur de l'analyse. Prochain article — sur les fonctions de fenêtre et les tests statistiques dans ClickHouse.

← Précédente:
→ Suivante: Configuration de ClickHouse : Comment j'ai mis en place la production sans me tirer une balle dans le pied

— Editorial Team

Advertisement 728x90

Lire ensuite

Tags

#clickhouse-aggregations #hyperloglog #probabilistic-data-structures #betting-analytics #sql-aggregates

Sujets

Bases de données Analytique

FAQ

Quelle est la différence entre uniqHLL12 et count(DISTINCT) dans ClickHouse ?

count(DISTINCT) donne un résultat exact mais est lent — 30 secondes sur 500 millions de lignes. uniqHLL12() est approximatif (erreur 1-2%) mais fonctionne en 0,2 seconde. Utilisez uniqHLL12 pour les tableaux de bord et les tendances, uniqExact pour les rapports financiers.

Quelle fonction quantile utiliser dans ClickHouse ?

quantileExact est exact mais consomme de la mémoire sur de grandes données. quantileTDigest est approximatif (erreur 0,5%), mémoire 100 Mo au lieu de 8 Go. Pour la production — quantileTDigest, pour les calculs exacts — quantileExact sur des données agrégées.

Qu'est-ce que le type AggregateFunction et pourquoi -State/-Merge sont-ils nécessaires ?

AggregateFunction stocke non pas les données mais l'état intermédiaire de l'agrégation (par exemple, table de hachage pour uniq). -State crée l'état lors de l'insertion, -Merge restaure l'agrégat. Utilisé dans les vues matérialisées pour les mises à jour incrémentielles — au lieu de recalculer 2 milliards de lignes chaque minute.

Quand utiliser topK au lieu de GROUP BY avec ORDER BY LIMIT ?

topK donne un top approximatif en fractions de seconde sur 10 milliards de lignes. GROUP BY est exact mais lent (15 secondes). Pour les tableaux de bord à actualisation automatique, utilisez topK. Pour les rapports exacts où le tri est important — GROUP BY.