SummingMergeTree und AggregatingMergeTree in ClickHouse

SummingMergeTree und AggregatingMergeTree: Schmerzlose inkrementelle Aggregation

Kehren wir zu unserem Online-Casino zurück. Jeden Tag platzieren Spieler Millionen von Wetten. Der Dashboard-Besitzer muss sehen: wie viele Wetten jeder Benutzer pro Tag abgeschlossen hat und für welchen Gesamtbetrag.

In einer regulären Datenbank (PostgreSQL, MySQL) würde man schreiben:

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

Bei einer Tabelle mit 100 Millionen Zeilen würde eine solche Abfrage ... nun, Sie verstehen – eine lange Zeit dauern. Sehr lange. Denn die Datenbank muss ALLE Zeilen lesen, sortieren oder hashen und dann die Aggregate berechnen.

ClickHouse ist zwar schneller, aber auch kein Zaubermittel. Je mehr Daten, desto länger dauert GROUP BY. Und wenn es viele Berichte gibt und diese „sofort“ benötigt werden – wird die Performance zum Problem.

Idee: Was wäre, wenn wir Aggregate vorberechnen und speichern? Sodass die Abfrage „wie viele Wetten hat user_id=123 gestern gemacht“ nur ein SELECT auf eine Zeile ist, keine vollständige Aggregation?

Dafür hat ClickHouse zwei spezielle Tabellen-Engines: SummingMergeTree und AggregatingMergeTree. Sie erledigen die schwere Arbeit für Sie – im Hintergrund, während der Zusammenführung von Datenteilen.

Analogie aus dem echten Leben: Stellen Sie sich vor, Sie führen Buch über Verkäufe in einem Geschäft. Jeder Verkauf ist ein Kassenbon. Wenn der Besitzer einen Bericht „wie viel haben wir heute verkauft“ anfordert, könnten Sie jedes Mal alle Kassenbons durchgehen. Oder Sie könnten ein Notizbuch führen, in dem Sie die Summe am Ende des Tages notieren: „heute 150 Verkäufe für 5000 Rubel.“ SummingMergeTree ist wie das automatische Führen dieses Notizbuchs.

2. SummingMergeTree – Automatischer Summierer

Wie es funktioniert

SummingMergeTree ist eine Engine, die während der Zusammenführung von Datenteilen (im Hintergrund) numerische Werte für Zeilen mit demselben Sortierschlüssel (ORDER BY) summiert.

Summierungsregeln:

• Alle numerischen Spalten (Typen: UInt*, Int*, Float*, Decimal*) werden automatisch summiert.
• Andere Spalten (Zeichenketten, Daten, Arrays) werden aus der ersten gefundenen Zeile übernommen – das ist wichtig zu beachten, es könnte nicht das sein, was Sie erwarten.
• Wenn eine Spalte nicht numerisch ist, Sie sie aber irgendwie aggregieren möchten – ist SummingMergeTree nicht geeignet, Sie benötigen AggregatingMergeTree.

Warum „Summing“: Weil mehrere Zeilen mit demselben Schlüssel zu einer zusammengefasst werden und die Zahlen darin die Summe der Zahlen aus den ursprünglichen Zeilen sind.

CREATE TABLE – Aufschlüsselung

-- Erstellen einer Tabelle für tägliche Statistiken pro Benutzer
CREATE TABLE daily_stats
(
    date         Date,                -- Tag der Statistik
    user_id      UInt64,              -- Spieler-ID
    bets_count   UInt64,              -- Anzahl der Wetten pro Tag (wird summiert)
    total_amount Decimal(18,2)       -- Gesamtwettbetrag (wird summiert)
)
ENGINE = SummingMergeTree()           -- Engine für automatische Summierung
ORDER BY (date, user_id)              -- Gruppierungsschlüssel: nach Datum und Benutzer

Was hier wichtig ist:

• ENGINE = SummingMergeTree() – Sie können in Klammern die zu summierenden Spalten angeben: SummingMergeTree(bets_count, total_amount). Wenn nicht angegeben, werden alle numerischen Spalten summiert (außer Spalten aus ORDER BY, deren Werte die Eindeutigkeit bestimmen).

• ORDER BY (date, user_id) – diese Spalten bestimmen, welche Zeilen zu einer zusammengeführt werden. Das heißt, alle Zeilen mit demselben Datum und derselben user_id werden während der Zusammenführung zu einer Zeile zusammengefasst, in der bets_count und total_amount Summen sind.

Was passiert, wenn ORDER BY zu breit ist? Wenn Sie zum Beispiel bets_count aufnehmen, bleibt jede eindeutige Wette (mit einer anderen Anzahl) eine separate Zeile. Es findet keine Summierung statt, da die Schlüssel unterschiedlich sind. Falle Nr. 1 (wir kommen darauf zurück).

Wie man Daten einfügt

Fügen Sie rohe Ereignisse ein (jede Zeile ist eine Wette):

-- Einfügen von drei Wetten für Benutzer 123 am 2025-06-01
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- eine Wette für 100 Rubel
    ('2025-06-01', 123, 1, 250.00),   -- zweite Wette für 250 Rubel
    ('2025-06-01', 456, 1, 50.00);    -- ein anderer Benutzer

-- Sie können NICHT aggregierte Daten einfügen – die Engine kümmert sich darum

Nach einer Hintergrundzusammenführung (kann Sekunden bis Stunden dauern) werden Zeilen mit date='2025-06-01' und user_id=123 zu einer zusammengeführt: ('2025-06-01', 123, 2, 350.00).

Lesen ohne GROUP BY – Magie und ihre Grenzen

Die Idee ist, dass Sie nach erfolgter Zusammenführung Daten ohne Aggregation lesen können:

-- Wenn die Zusammenführung bereits stattgefunden hat, gibt diese Abfrage eine Zeile pro Benutzer und Tag zurück
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

Aber es gibt ein Problem. Zwischen Zusammenführungen können Daten in verschiedenen Teilen mit doppelten Schlüsseln liegen. Daher schreiben Sie in der Praxis trotzdem mit SUM:

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

Warum funktioniert das? Denn selbst wenn die Daten noch nicht zusammengeführt wurden, addiert SUM alles korrekt. Und wenn sie zusammengeführt wurden, haben Sie eine Zeile pro Gruppe, und SUM gibt einfach ihren Wert zurück. Die Abfrage liest zwar immer noch Daten, aber es sind WENIGER (aggregierte Zeilen statt roher).

Analogie: SummingMergeTree ist wie ein Assistent, der identische Kassenbons vorab zusammenklebt. Aber Sie fragen trotzdem „zeige die Summe für jeden Tag“. Wenn die Kassenbons bereits verklebt sind, stimmt die Summe mit der Zahl in einer Zeile überein. Wenn nicht, erhalten Sie trotzdem die korrekte Summe. Hauptsache, Sie lesen nicht Millionen von Kassenbons, sondern Tausende von Zusammenfassungen.

3. Das Problem: Zwischen Zusammenführungen benötigen Sie SUM im SELECT

Dies ist ein entscheidender Punkt, der oft missverstanden wird.

Naiver Ansatz (falsch):

-- In der Annahme, dass die Daten nach der Zusammenführung bereits aggregiert sind, schreibt ein Anfänger:
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- Und erhält mehrere Zeilen für einen Benutzer (wenn die Zusammenführung noch nicht stattgefunden hat)

Korrekter Ansatz (sicher):

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

Warum?

1. Sie erhalten immer das korrekte Ergebnis – sowohl vor als auch nach der Zusammenführung.
2. Die Datenmenge ist immer noch kleiner als in der rohen Wettentabelle.
3. ClickHouse optimiert solche Abfragen gut.

Wann können Sie SUM weglassen? Nur wenn Sie absolut sicher sind, dass die benötigten Daten bereits zusammengeführt wurden. Zum Beispiel nach einem erzwungenen OPTIMIZE TABLE daily_stats (aber das ist eine teure Operation, führen Sie sie nicht für jede Kleinigkeit durch).

4. AggregatingMergeTree – Wenn einfache Summierung nicht ausreicht

SummingMergeTree kann nur Zahlen addieren. Aber was ist, wenn Sie benötigen:

• Eindeutige Benutzer zählen (nicht summieren)?
• Maximum oder Minimum finden?
• Durchschnitt berechnen?
• Approximative Algorithmen wie uniq zum Zählen eindeutiger Werte verwenden?

Dafür gibt es AggregatingMergeTree. Es speichert nicht nur Werte, sondern Zustände von Aggregatfunktionen – spezielle Zwischendaten, die es ermöglichen, später das endgültige Ergebnis zu erhalten.

Analogie: SummingMergeTree speichert nur die endgültige Summe. Aber AggregatingMergeTree speichert nicht nur die Summe, sondern auch einen Zähler (um später den Durchschnitt zu berechnen) oder eine Hashtabelle eindeutiger Werte (um später zu sagen, wie viele es waren). Es ist wie der Unterschied zwischen „Ich habe die Summe“ und „Ich habe ein Notizbuch, in dem alle Daten aufgezeichnet sind, aber in komprimierter Form.“

CREATE TABLE mit AggregateFunction

-- Erstellen einer Tabelle für aggregierte Dashboard-Statistiken
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- Stunde des Ereignisses
    sport_type     String,                                 -- Sportart (Fußball, Basketball...)
    total_bets     AggregateFunction(sum, UInt64),         -- Summe der Wettanzahl
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- Summe des Geldes
    unique_users   AggregateFunction(uniq, UInt64),        -- Eindeutige Spieler (approximativ)
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- Durchschnittliche Wettgröße
    max_bet        AggregateFunction(max, Decimal(18,2))   -- Maximale Wette
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

Aufschlüsselung des Unbekannten:

• AggregateFunction(sum, UInt64) – ein Spaltentyp, der den Zustand der Aggregatfunktion sum für Daten vom Typ UInt64 speichert. Es ist keine Zahl, sondern eine interne ClickHouse-Struktur.
• Warum nicht einfach UInt64? Denn für einige Funktionen (uniq, avg) müssen Sie mehr Daten speichern als nur das endgültige Ergebnis. avg speichert sowohl Summe als auch Anzahl. uniq speichert eine Hashtabelle.
• Wenn zwei Zeilen mit demselben ORDER BY (gleiche Stunde und Sportart) zusammengeführt werden – werden die Zustände der Aggregatfunktionen kombiniert. Für sum ist das einfaches Addieren von Zwischensummen. Für uniq ist es das Zusammenführen zweier Hashtabellen eindeutiger Werte.

Einfügen von Daten mittels INSERT SELECT mit State-Funktionen

Sie können keinen regulären Wert in eine AggregateFunction-Spalte einfügen. Sie müssen spezielle *State-Funktionen verwenden, die aus einem Rohwert einen Zustand erzeugen.

-- Einfügen aggregierter Daten aus der rohen Wettentabelle
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- Zeit auf Stunde runden
    sport_type,
    sumState(bets_count) AS total_bets,                    -- Zustand der Summe
    sumState(amount) AS total_amount,                      -- Zustand der Geldsumme
    uniqState(user_id) AS unique_users,                    -- Zustand für Eindeutige
    avgState(amount) AS avg_bet_amount,                    -- Zustand für Durchschnitt
    maxState(amount) AS max_bet                            -- Zustand für Maximum
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

Was passiert hier:

• toStartOfHour(event_time) – eine ClickHouse-Funktion, die die Zeit auf die Stunde kürzt: 2025-06-01 12:34:56 → 2025-06-01 12:00:00.
• sumState(amount) – statt SUM(amount) schreiben Sie sumState(amount). Das Ergebnis ist ein Zustand der Aggregatfunktion, Typ AggregateFunction(sum, ...).
• GROUP BY ist in der Einfügeabfrage obligatorisch! Denn Sie aggregieren Daten aus der rohen Tabelle in Gruppen (Stunde+Sportart) und fügen dann jede Gruppe als eine Zeile in dashboard_hourly ein.

Lesen mittels Merge-Funktionen

Zum Lesen der Daten verwenden Sie *Merge-Funktionen:

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- Vom Zustand → Zahl
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- Eindeutige Benutzer
    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;   -- Gruppierung ist immer noch nötig (wenn Daten nicht zusammengeführt)

Warum wieder GROUP BY? Aus demselben Grund wie bei SummingMergeTree: Zwischen Zusammenführungen kann es mehrere Zeilen mit demselben ORDER BY geben. GROUP BY mit *Merge liefert in jedem Zustand das korrekte Ergebnis.

5. Nutzungsmuster mit Materialisierten Ansichten

Die leistungsstärkste Art, AggregatingMergeTree zu verwenden, ist in Kombination mit einer Materialisierten Ansicht. Sie fügen rohe Daten in eine reguläre Tabelle ein, und die Ansicht aggregiert sie automatisch und speichert sie in der aggregierenden Tabelle.

Analogie: Es ist, als ob Sie ein Fließband einrichten: Rohe Kassenbons kommen in eine Kiste, und ein automatischer Sortierer sammelt jede Minute Tagessummen und legt sie in eine andere Kiste. Analysten schauen nur in die zweite Kiste – schnell und ohne GROUP BY on the fly.

Vollständiges Beispiel: Stündliche Statistiken für ein Operator-Dashboard

Schritt 1: Rohe Tabelle – Ereignisse (Wetten) werden hier eingefügt

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

Schritt 2: Aggregierende Tabelle – fertige Statistiken werden hier gespeichert

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);

Schritt 3: Materialisierte Ansicht – die Brücke zwischen ihnen

CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
    toStartOfHour(event_time) AS hour,
    sport_type,
    sumState(1) AS total_bets,                    -- Jede Zeile ist eine Wette
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;

Was passiert jetzt?

1. Sie fügen Zeilen in raw_bets mit regulärem INSERT ein.
2. ClickHouse führt sie automatisch (fast sofort) durch die materialisierte Ansicht.
3. Die Ansicht aggregiert Daten nur aus dem eingefügten Batch und fügt die Ergebnisse in bets_hourly_agg ein.
4. In bets_hourly_agg können sich vorübergehend mehrere Zeilen mit demselben (hour, sport_type) ansammeln – aber sie werden während der Hintergrundzusammenführungen zusammengeführt.

Lesen für das Dashboard:

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;

Diese Abfrage liest nur aggregierte Daten, die tausendmal weniger Speicherplatz beanspruchen als rohe Wetten.

6. Praxisbeispiel: Dashboard für Sportereignisse

Stellen Sie sich vor, Sie sind ein Buchmacher-Betreiber. Auf dem Dashboard müssen Sie anzeigen:

• Für jedes Spiel (Fußball, Champions League, „Real“ gegen „Bayern“)
• Wie viele Wetten in den letzten 5 Minuten platziert wurden
• Gesamtbetrag aller Wetten
• Anzahl der eindeutigen Spieler
• Durchschnittliche Wette

Rohdaten: 5000 Wetten pro Sekunde. Alles zu speichern und jedes Mal von Grund auf zu aggregieren ist Wahnsinn.

Lösung:

-- Tabelle für Aggregate nach Spiel mit 5-Minuten-Intervallen
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);

-- Materialisierte Ansicht
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;

Jetzt fragt das Dashboard match_stats_5min ab – und erhält Antworten in Millisekunden statt Sekunden.

7. Wann man SummingMergeTree und AggregatingMergeTree NICHT verwenden sollte

Wann SummingMergeTree geeignet ist:

• Sie müssen nur numerische Werte summieren.
• Sie sind damit einverstanden, zwischen Zusammenführungen GROUP BY mit SUM zu verwenden.
• Der Gruppierungsschlüssel hat keine zu hohe Kardinalität (z.B. nicht eine Milliarde eindeutige Benutzer – obwohl das auch in Ordnung ist, nur mehr Daten).

Wann SummingMergeTree NICHT geeignet ist:

• Sie müssen eindeutige Benutzer zählen (uniq, count(DISTINCT)) – nur AggregatingMergeTree funktioniert.
• Sie benötigen andere Aggregate: avg, min, max – wieder nur AggregatingMergeTree.
• Sie erwarten, dass Daten immer in einem bereits aggregierten Zustand vorliegen – so funktioniert es nicht.
• Ihre Daten werden aktualisiert (nicht nur eingefügt) – diese Engines sind nicht für Update-Semantiken gedacht.

Wann AggregatingMergeTree geeignet ist:

• Sie benötigen verschiedene Arten von Aggregationen (Summen, Eindeutige, Durchschnitte, Maxima).
• Sie sind bereit, INSERT mit *State und SELECT mit *Merge zu schreiben.
• Sie verwenden materialisierte Ansichten zur automatischen Aggregation.
• Das Volumen der Rohdaten ist riesig, und Aggregate sind um Größenordnungen kleiner.

Wann AggregatingMergeTree NICHT geeignet ist:

• Sie sind nicht bereit, dem Team zu erklären, was AggregateFunction ist und wie man damit arbeitet. Die Lernkurve ist höher.
• Sie benötigen exakte Eindeutigkeit, nicht approximativ (uniq ist eine probabilistische Struktur, Fehler ~2%). Für exakte verwenden Sie groupBitmap oder zählen in einem anderen System.
• Die Datenmenge ist klein (Millionen von Zeilen) – ein reguläres GROUP BY ist einfacher.
• Sie ändern häufig das Aggregationsschema (fügen neue Metriken hinzu) – das Neuerstellen der materialisierten Ansicht ist mühsam.

8. Vergleich mit regulärem MergeTree + GROUP BY

Merkmal	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
Einfügegeschwindigkeit	Maximal	Hoch	Mittel (aufgrund des Zustands)
Lesegeschwindigkeit (großer Bereich)	Niedrig (liest alles)	Hoch (liest Aggregate)	Hoch
Lesegeschwindigkeit (Punktabfrage)	Mittel	Hoch	Hoch
Speicherplatz	Maximal	Minimal (Aggregate)	Etwas mehr (Zustände)
Code-Komplexität	Niedrig	Niedrig (nur eine Tabelle)	Hoch (*State, *Merge)
Aggregationsflexibilität	Beliebig	Nur Summen	Beliebig (via AggregateFunction)

9. Häufige Fallstricke

Falle Nr. 1: ORDER BY enthält nicht genügend Felder

-- SCHLECHT: nur Datum, ohne user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- Während der Zusammenführung werden ALLE Zeilen für einen Tag zu einer zusammengefasst
-- Sie verlieren die Details auf Benutzerebene

Richtig: Nehmen Sie in ORDER BY alle Felder auf, nach denen Sie aggregieren möchten.

Falle Nr. 2: GROUP BY im SELECT vergessen

-- SCHLECHT: ohne GROUP BY, selbst wenn Daten noch nicht zusammengeführt wurden
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- Wenn es zwei Zeilen mit demselben Datum aber unterschiedlicher user_id gibt – erhalten Sie einen Fehler
-- ClickHouse weiß nicht, welche user_id angezeigt werden soll

Richtig: Gruppieren Sie immer nach denselben Feldern wie in ORDER BY.

Falle Nr. 3: Nicht-stochastisches uniq

uniq in ClickHouse ist eine probabilistische Funktion. Fehler ~2-3%. Wenn Sie exakte Eindeutigkeit benötigen, verwenden Sie uniqExact oder groupBitmap.

Falle Nr. 4: Aktualisieren alter Daten

SummingMergeTree und AggregatingMergeTree mögen keine Aktualisierungen. Wenn Sie eine Wette von gestern korrigieren müssen, ist es einfacher, eine neue Zeile mit dem entgegengesetzten Vorzeichen einzufügen (via CollapsingMergeTree).

10. Wie es weitergeht

Nachdem Sie nun die aggregierenden Engines gemeistert haben, sind die nächsten Themen:

• Wie wählt man die richtige Engine für Ihre Aufgabe – Vergleich aller *MergeTree-Engines.
• Materialisierte Ansichten im Detail – wie debuggen, wie das Schema aktualisieren.
• Optimierung von Hintergrundzusammenführungen – damit Aggregate schneller zusammengeführt werden.

Fazit: SummingMergeTree und AggregatingMergeTree sind Werkzeuge für diejenigen, die nicht wollen, dass ihr Dashboard bei Terabytes an Daten ins Stocken gerät. Sie erfordern etwas mehr Vorwissen, zahlen sich aber unter realen Arbeitslasten vielfach aus. Die Hauptregel: Verwenden Sie beim Lesen immer GROUP BY und Aggregatfunktionen – dann sind Sie vor und nach Zusammenführungen auf der sicheren Seite.

---

← Vorherige: ReplacingMergeTree: So schlagen Sie Duplikate in ClickHouse ohne Schmerzen
→ Nächste: CollapsingMergeTree: Wie man Aggregate ohne UPDATE in ClickHouse aktualisiert

— Editorial Team