SummingMergeTree et AggregatingMergeTree dans ClickHouse

SummingMergeTree et AggregatingMergeTree : Agrégation incrémentale sans douleur

1. Pourquoi les moteurs d'agrégation sont nécessaires — Le problème des calculs sur des données massives

Revenons à notre casino en ligne. Chaque jour, les joueurs placent des millions de paris. Le propriétaire du tableau de bord a besoin de voir : combien de paris chaque utilisateur a effectués par jour et pour quel montant total.

Dans une base de données classique (PostgreSQL, MySQL), on écrirait :

SELECT user_id, date, COUNT(*), SUM(amount)
FROM bets
GROUP BY user_id, date

Sur une table de 100 millions de lignes, une telle requête prendrait... eh bien, vous voyez — beaucoup de temps. Très longtemps. Parce que la base de données doit lire TOUTES les lignes, les trier ou les hacher, puis calculer les agrégats.

ClickHouse est plus rapide, bien sûr, mais ce n'est pas non plus de la magie. Plus il y a de données, plus GROUP BY prend du temps. Et s'il y a beaucoup de rapports et qu'ils sont nécessaires « tout de suite » — les performances deviennent un problème.

Idée : Et si nous pré-calculions les agrégats et les stockions ? Ainsi, la requête « combien de paris user_id=123 a-t-il placés hier » serait simplement un SELECT sur une ligne, pas une agrégation complète ?

Pour cela, ClickHouse dispose de deux moteurs de table spéciaux : SummingMergeTree et AggregatingMergeTree. Ils font le travail lourd pour vous — en arrière-plan, lors des fusions de parties de données.

Analogie réelle : Imaginez que vous tenez un registre des ventes dans un magasin. Chaque vente est un ticket de caisse. Si le propriétaire demande un rapport « combien avons-nous vendu aujourd'hui », vous pourriez parcourir tous les tickets à chaque fois. Ou vous pourriez tenir un carnet où vous notez le total à la fin de la journée : « aujourd'hui 150 ventes pour 5000 roubles. » SummingMergeTree est comme tenir ce carnet automatiquement.

2. SummingMergeTree — Additionneur automatique

Comment ça marche

SummingMergeTree est un moteur qui, lors des fusions de parties de données (en arrière-plan), additionne les valeurs numériques pour les lignes ayant la même clé de tri (ORDER BY).

Règles d'addition :

• Toutes les colonnes numériques (types : UInt*, Int*, Float*, Decimal*) sont automatiquement additionnées.
• Les autres colonnes (chaînes, dates, tableaux) sont prises à partir de la première ligne rencontrée — c'est important à retenir, cela peut ne pas correspondre à ce que vous attendez.
• Si une colonne n'est pas numérique mais que vous souhaitez l'agréger d'une manière ou d'une autre — SummingMergeTree ne convient pas, il faut AggregatingMergeTree.

Pourquoi « Summing » : parce que plusieurs lignes avec la même clé sont fusionnées en une seule, et les nombres dans celle-ci sont la somme des nombres des lignes d'origine.

CREATE TABLE — Analyse détaillée

-- Créer une table pour les statistiques quotidiennes par utilisateur
CREATE TABLE daily_stats
(
    date         Date,                -- Jour des statistiques
    user_id      UInt64,              -- Identifiant du joueur
    bets_count   UInt64,              -- Nombre de paris par jour (sera additionné)
    total_amount Decimal(18,2)       -- Montant total des paris (sera additionné)
)
ENGINE = SummingMergeTree()           -- Moteur pour l'addition automatique
ORDER BY (date, user_id)              -- Clé de regroupement : par date et utilisateur

Ce qui est important ici :

• ENGINE = SummingMergeTree() — vous pouvez spécifier les colonnes à additionner entre parenthèses : SummingMergeTree(bets_count, total_amount). Si non spécifié, toutes les colonnes numériques sont additionnées (sauf celles de ORDER BY, leurs valeurs déterminent l'unicité).

• ORDER BY (date, user_id) — ces colonnes déterminent quelles lignes seront fusionnées en une seule. C'est-à-dire que toutes les lignes avec la même date et le même user_id seront réduites à une seule ligne lors de la fusion, où bets_count et total_amount sont des sommes.

Que se passe-t-il si ORDER BY est trop large ? Si vous incluez, par exemple, bets_count, alors chaque pari unique (avec un nombre différent) restera une ligne séparée. Aucune addition n'aura lieu car les clés sont différentes. Piège n°1 (nous y reviendrons).

Comment insérer des données

Insérez des événements bruts (chaque ligne est un pari) :

-- Insérer trois paris pour l'utilisateur 123 le 2025-06-01
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- un pari de 100 roubles
    ('2025-06-01', 123, 1, 250.00),   -- deuxième pari de 250 roubles
    ('2025-06-01', 456, 1, 50.00);    -- un autre utilisateur

-- Vous pouvez insérer des données NON agrégées — le moteur s'en chargera

Après une fusion en arrière-plan (peut prendre de quelques secondes à quelques heures), les lignes avec date='2025-06-01' et user_id=123 fusionneront en une seule : ('2025-06-01', 123, 2, 350.00).

Lecture sans GROUP BY — Magie et ses limites

L'idée est qu'après la fusion, vous pouvez lire les données sans agrégation :

-- Si la fusion a déjà eu lieu, cette requête renvoie une ligne par utilisateur par jour
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

Mais il y a un problème. Entre les fusions, les données peuvent résider dans différentes parties avec des clés en double. Par conséquent, en pratique, vous écrivez toujours avec SUM :

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
WHERE date = '2025-06-01'
GROUP BY date, user_id;

Pourquoi cela fonctionne-t-il ? Parce que même si les données n'ont pas fusionné, SUM additionnera tout correctement. Et si elles ont fusionné, vous avez une ligne par groupe, et SUM renvoie simplement sa valeur. La requête lit toujours les données, mais maintenant il y en a MOINS (lignes agrégées au lieu de lignes brutes).

Analogie : SummingMergeTree est comme un assistant qui pré-colle des tickets identiques. Mais vous demandez toujours « afficher le total pour chaque jour. » Si les tickets sont déjà collés, le total correspond au nombre dans une ligne. Sinon, vous obtenez toujours le total correct. L'essentiel est que vous ne lisez pas des millions de tickets, mais des milliers de résumés.

3. Le problème : Entre les fusions, vous avez besoin de SUM dans SELECT

C'est un point clé souvent mal compris.

Approche naïve (incorrecte) :

-- Pensant qu'après la fusion les données sont déjà agrégées, un débutant écrit :
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- Et obtient plusieurs lignes pour un même utilisateur (si la fusion n'a pas eu lieu)

Approche correcte (sûre) :

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
GROUP BY date, user_id;

Pourquoi ?

1. Vous obtenez toujours le résultat correct — avant et après la fusion.
2. La taille des données est toujours plus petite que dans la table de paris brute.
3. ClickHouse optimise bien ces requêtes.

Quand pouvez-vous omettre SUM ? Uniquement si vous êtes absolument sûr que les données nécessaires ont déjà été fusionnées. Par exemple, après un OPTIMIZE TABLE daily_stats forcé (mais c'est une opération coûteuse, ne le faites pas pour chaque petite chose).

4. AggregatingMergeTree — Quand la simple addition ne suffit pas

SummingMergeTree ne peut qu'additionner des nombres. Mais que faire si vous avez besoin de :

• Compter les utilisateurs uniques (pas la somme) ?
• Trouver le maximum ou le minimum ?
• Calculer la moyenne ?
• Utiliser des algorithmes approximatifs comme uniq pour compter les valeurs uniques ?

Pour cela, il existe AggregatingMergeTree. Il stocke non seulement des valeurs, mais des états de fonctions d'agrégation — des données intermédiaires spéciales qui permettent d'obtenir le résultat final plus tard.

Analogie : SummingMergeTree stocke uniquement la somme finale. Mais AggregatingMergeTree stocke non seulement la somme mais aussi un compteur (pour calculer ensuite la moyenne), ou une table de hachage des valeurs uniques (pour dire combien il y en avait). C'est comme la différence entre « j'ai le total » et « j'ai un carnet où toutes les données sont enregistrées, mais sous forme compressée. »

CREATE TABLE avec AggregateFunction

-- Créer une table pour les statistiques agrégées du tableau de bord
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- Heure de l'événement
    sport_type     String,                                 -- Type de sport (football, basketball...)
    total_bets     AggregateFunction(sum, UInt64),         -- Somme du nombre de paris
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- Somme d'argent
    unique_users   AggregateFunction(uniq, UInt64),        -- Joueurs uniques (approximatif)
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- Taille moyenne du pari
    max_bet        AggregateFunction(max, Decimal(18,2))   -- Pari maximum
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

Analyse des éléments inhabituels :

• AggregateFunction(sum, UInt64) — un type de colonne qui stocke l'état de la fonction d'agrégation sum pour des données de type UInt64. Ce n'est pas un nombre, mais une structure interne de ClickHouse.
• Pourquoi pas simplement UInt64 ? Parce que pour certaines fonctions (uniq, avg), vous devez stocker plus de données que le simple résultat final. avg stocke à la fois la somme et le compteur. uniq stocke une table de hachage.
• Lors de la fusion de deux lignes avec le même ORDER BY (même heure et même type de sport) — les états des fonctions d'agrégation sont combinés. Pour sum, c'est simplement l'addition des totaux intermédiaires. Pour uniq, c'est la fusion de deux tables de hachage de valeurs uniques.

Insertion de données via INSERT SELECT avec les fonctions State

Vous ne pouvez pas insérer une valeur ordinaire dans une colonne AggregateFunction. Vous devez utiliser des fonctions *State spéciales qui créent un état à partir d'une valeur brute.

-- Insérer des données agrégées à partir de la table de paris brute
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- Arrondir l'heure à l'heure
    sport_type,
    sumState(bets_count) AS total_bets,                    -- État de la somme
    sumState(amount) AS total_amount,                      -- État de la somme d'argent
    uniqState(user_id) AS unique_users,                    -- État pour les uniques
    avgState(amount) AS avg_bet_amount,                    -- État pour la moyenne
    maxState(amount) AS max_bet                            -- État pour le maximum
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

Que se passe-t-il ici :

• toStartOfHour(event_time) — une fonction ClickHouse qui tronque l'heure à l'heure : 2025-06-01 12:34:56 → 2025-06-01 12:00:00.
• sumState(amount) — au lieu de SUM(amount), vous écrivez sumState(amount). Le résultat est un état de la fonction d'agrégation, de type AggregateFunction(sum, ...).
• GROUP BY est obligatoire dans la requête d'insertion ! Parce que vous agrégez les données de la table brute en groupes (heure+sport), puis insérez chaque groupe comme une ligne dans dashboard_hourly.

Lecture via les fonctions Merge

Pour lire les données, utilisez les fonctions *Merge :

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- De l'état → nombre
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- Utilisateurs uniques
    avgMerge(avg_bet_amount) AS avg_bet_amount,
    maxMerge(max_bet) AS max_bet
FROM dashboard_hourly
WHERE event_hour >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;   -- Le GROUP BY est toujours nécessaire (si les données n'ont pas fusionné)

Pourquoi GROUP BY à nouveau ? Même raison qu'avec SummingMergeTree : entre les fusions, il peut y avoir plusieurs lignes avec le même ORDER BY. GROUP BY avec *Merge donne le résultat correct dans n'importe quel état.

5. Modèle d'utilisation avec les vues matérialisées

La façon la plus puissante d'utiliser AggregatingMergeTree est en combinaison avec une vue matérialisée. Vous insérez des données brutes dans une table normale, et la vue les agrège automatiquement et les stocke dans la table d'agrégation.

Analogie : C'est comme installer un tapis roulant : les tickets bruts vont dans une boîte, et un trieur automatique toutes les minutes collecte les totaux quotidiens et les met dans une autre boîte. Les analystes ne regardent que la deuxième boîte — rapide et sans GROUP BY à la volée.

Exemple complet : Statistiques horaires pour le tableau de bord de l'opérateur

Étape 1 : Table brute — les événements (paris) seront insérés ici

CREATE TABLE raw_bets
(
    event_time DateTime,
    sport_type String,
    user_id UInt64,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY event_time;

Étape 2 : Table d'agrégation — les statistiques prêtes seront stockées ici

CREATE TABLE bets_hourly_agg
(
    hour DateTime,
    sport_type String,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    avg_bet AggregateFunction(avg, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_type);

Étape 3 : Vue matérialisée — le pont entre elles

CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
    toStartOfHour(event_time) AS hour,
    sport_type,
    sumState(1) AS total_bets,                    -- Chaque ligne est un pari
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;

Que se passe-t-il maintenant ?

1. Vous insérez des lignes dans raw_bets avec un INSERT normal.
2. ClickHouse les exécute automatiquement (presque instantanément) à travers la vue matérialisée.
3. La vue agrège les données uniquement à partir du lot inséré et insère les résultats dans bets_hourly_agg.
4. Dans bets_hourly_agg, plusieurs lignes avec le même (hour, sport_type) peuvent temporairement s'accumuler — mais elles fusionneront lors des fusions en arrière-plan.

Lecture pour le tableau de bord :

SELECT
    hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,
    avgMerge(avg_bet) AS avg_bet
FROM bets_hourly_agg
WHERE hour >= today() - 7
GROUP BY hour, sport_type;

Cette requête lira uniquement des données agrégées, qui prennent des milliers de fois moins de place que les paris bruts.

6. Exemple réel : Tableau de bord des événements sportifs

Imaginez que vous êtes un opérateur de bookmaker. Sur le tableau de bord, vous devez afficher :

• Pour chaque match (football, Ligue des champions, « Real » contre « Bayern »)
• Combien de paris ont été placés dans les 5 dernières minutes
• Montant total de tous les paris
• Nombre de joueurs uniques
• Pari moyen

Données brutes : 5000 paris par seconde. Tout stocker et agréger à chaque fois est une folie.

Solution :

-- Table pour les agrégats par match avec intervalles de 5 minutes
CREATE TABLE match_stats_5min
(
    match_id String,
    interval_5min DateTime,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    max_bet AggregateFunction(max, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (match_id, interval_5min);

-- Vue matérialisée
CREATE MATERIALIZED VIEW match_stats_mv TO match_stats_5min AS
SELECT
    match_id,
    toStartOfFiveMinute(event_time) AS interval_5min,
    sumState(1) AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    maxState(amount) AS max_bet
FROM raw_bets
GROUP BY match_id, interval_5min;

Maintenant, le tableau de bord interroge match_stats_5min — et obtient des réponses en millisecondes au lieu de secondes.

7. Quand NE PAS utiliser SummingMergeTree et AggregatingMergeTree

Quand SummingMergeTree est adapté :

• Vous avez seulement besoin d'additionner des valeurs numériques.
• Vous acceptez d'utiliser GROUP BY avec SUM entre les fusions.
• La clé de regroupement n'a pas une cardinalité trop élevée (par exemple, pas un milliard d'utilisateurs uniques — même si c'est acceptable, cela donne juste plus de données).

Quand SummingMergeTree n'est PAS adapté :

• Vous devez compter les utilisateurs uniques (uniq, count(DISTINCT)) — seul AggregatingMergeTree fonctionne.
• Vous avez besoin d'autres agrégats : avg, min, max — encore une fois, seul AggregatingMergeTree.
• Vous vous attendez à ce que les données soient toujours dans un état déjà agrégé — ce n'est pas comme ça que ça marche.
• Vos données sont mises à jour (pas seulement insérées) — ces moteurs ne sont pas conçus pour la sémantique de mise à jour.

Quand AggregatingMergeTree est adapté :

• Vous avez besoin de différents types d'agrégations (sommes, uniques, moyennes, maximums).
• Vous êtes prêt à écrire des INSERT avec *State et des SELECT avec *Merge.
• Vous utilisez des vues matérialisées pour l'agrégation automatique.
• Le volume de données brutes est énorme et les agrégats sont des ordres de grandeur plus petits.

Quand AggregatingMergeTree n'est PAS adapté :

• Vous n'êtes pas prêt à expliquer à l'équipe ce qu'est AggregateFunction et comment l'utiliser. La courbe d'apprentissage est plus élevée.
• Vous avez besoin d'une unicité exacte, pas approximative (uniq est une structure probabiliste, erreur ~2%). Pour une unicité exacte, utilisez groupBitmap ou comptez dans un autre système.
• Le volume de données est faible (millions de lignes) — un GROUP BY normal est plus simple.
• Vous modifiez fréquemment le schéma d'agrégation (ajoutez de nouvelles métriques) — recréer la vue matérialisée est pénible.

8. Comparaison avec MergeTree normal + GROUP BY

Caractéristique	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
Vitesse d'insertion	Maximale	Élevée	Moyenne (à cause de l'état)
Vitesse de lecture (grande plage)	Faible (lit tout)	Élevée (lit les agrégats)	Élevée
Vitesse de lecture (point)	Moyenne	Élevée	Élevée
Espace de stockage	Maximum	Minimum (agrégats)	Légèrement plus (états)
Complexité du code	Faible	Faible (juste une table)	Élevée (*State, *Merge)
Flexibilité d'agrégation	Toute	Uniquement les sommes	Toute (via AggregateFunction)

9. Pièges courants

Piège n°1 : ORDER BY n'inclut pas assez de champs

-- MAUVAIS : seulement la date, sans user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- Lors de la fusion, TOUTES les lignes pour un même jour seront réduites à une seule
-- Vous perdez le détail au niveau de l'utilisateur

Correct : Incluez dans ORDER BY tous les champs par lesquels vous voulez agréger.

Piège n°2 : Oubli du GROUP BY dans SELECT

-- MAUVAIS : sans GROUP BY, même si les données n'ont pas fusionné
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- S'il y a deux lignes avec la même date mais des user_id différents — vous obtiendrez une erreur
-- ClickHouse ne sait pas quel user_id afficher

Correct : Groupez toujours par les mêmes champs que dans ORDER BY.

Piège n°3 : uniq non stochastique

uniq dans ClickHouse est une fonction probabiliste. Erreur ~2-3%. Si vous avez besoin d'une unicité exacte, utilisez uniqExact ou groupBitmap.

Piège n°4 : Mise à jour de données anciennes

SummingMergeTree et AggregatingMergeTree n'aiment pas les mises à jour. Si vous devez corriger un pari d'hier, il est plus facile d'insérer une nouvelle ligne avec le signe opposé (via CollapsingMergeTree).

10. Prochaines étapes

Maintenant que vous maîtrisez les moteurs d'agrégation, les sujets suivants :

• Comment choisir le bon moteur pour votre tâche — comparaison de tous les moteurs *MergeTree.
• Vues matérialisées en détail — comment déboguer, comment mettre à jour le schéma.
• Réglage des fusions en arrière-plan — pour que les agrégats se réduisent plus rapidement.

En résumé : SummingMergeTree et AggregatingMergeTree sont des outils pour ceux qui ne veulent pas que leur tableau de bord rame sur des téraoctets de données. Ils nécessident un peu plus de compréhension au départ, mais ils rapportent plusieurs fois leur investissement sous des charges réelles. La règle principale : utilisez toujours GROUP BY et les fonctions d'agrégation lors de la lecture — alors vous êtes en sécurité avant et après les fusions.

---

← Précédente: ReplacingMergeTree : Comment Vaincre les Doublons dans ClickHouse Sans Douleur
→ Suivante: CollapsingMergeTree : Comment mettre à jour des agrégats sans UPDATE dans ClickHouse

— Editorial Team