SummingMergeTree i AggregatingMergeTree w ClickHouse

SummingMergeTree i AggregatingMergeTree: inkrementalna agregacja bez bólu

Wróćmy do naszego kasyna online. Każdego dnia gracze stawiają miliony zakładów. Właściciel dashboardu musi widzieć: ile zakładów postawił każdy użytkownik danego dnia i na jaką kwotę.

W zwykłej bazie danych (PostgreSQL, MySQL) napisałbyś:

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

Na tabeli ze 100 milionami wierszy takie zapytanie będzie wykonywane... no wiesz — długo. Bardzo długo. Bo baza danych musi przeczytać WSZYSTKIE wiersze, posortować je lub zahaszować, a potem policzyć agregaty.

ClickHouse jest oczywiście szybszy — ale też nie jest czarodziejem. Im więcej danych, tym dłużej działa GROUP BY. A jeśli raportów jest dużo i są potrzebne "od ręki" — wydajność staje się problemem.

Pomysł: A co jeśli z góry policzyć agregaty i je przechowywać? Aby na zapytanie "ile zakładów postawił user_id=123 wczoraj" odpowiedzią był po prostu SELECT z wiersza, a nie pełna agregacja?

Do tego ClickHouse ma dwa specjalne silniki tabel: SummingMergeTree i AggregatingMergeTree. Robią one ciężką pracę za ciebie — w tle, podczas scalania kawałków danych (merge).

Analogia z życia: Wyobraź sobie, że prowadzisz ewidencję sprzedaży w sklepie. Każda sprzedaż to paragon. Jeśli właściciel prosi o raport "ile sprzedaliśmy dzisiaj", możesz za każdym razem przeglądać wszystkie paragony. Albo możesz prowadzić notes, w którym na koniec dnia zapisujesz podsumowanie: "dzisiaj 150 sprzedaży na 5000 zł". SummingMergeTree to jak automatyczne prowadzenie takiego notesu.

2. SummingMergeTree — automatyczny sumator

Jak to działa

SummingMergeTree to silnik, który podczas scalania kawałków danych (w tle) sumuje wartości liczbowe dla wierszy z tym samym kluczem sortowania (ORDER BY).

Zasady sumowania:

• Wszystkie kolumny liczbowe (typy: UInt*, Int*, Float*, Decimal*) są automatycznie sumowane.
• Pozostałe kolumny (stringi, daty, tablice) są brane z pierwszego lepszego wiersza — warto o tym pamiętać, może tam być coś innego niż oczekujesz.
• Jeśli kolumna nie jest liczbowa, ale chcesz ją jakoś agregować — SummingMergeTree nie jest odpowiedni, potrzebujesz AggregatingMergeTree.

Dlaczego "Summing": ponieważ z kilku wierszy z tym samym kluczem powstaje jeden, a liczby w nim to suma liczb z oryginalnych wierszy.

CREATE TABLE — rozkładamy na czynniki pierwsze

-- Tworzymy tabelę dla dziennych statystyk według użytkowników
CREATE TABLE daily_stats
(
    date         Date,                -- Dzień, za który statystyka
    user_id      UInt64,              -- ID gracza
    bets_count   UInt64,              -- Liczba zakładów w ciągu dnia (będzie sumowana)
    total_amount Decimal(18,2)       -- Łączna kwota zakładów (będzie sumowana)
)
ENGINE = SummingMergeTree()           -- Silnik do automatycznego sumowania
ORDER BY (date, user_id)              -- Klucz grupowania: po dacie i użytkowniku

Co jest tu ważne:

• ENGINE = SummingMergeTree() — możesz podać kolumny do sumowania w nawiasach: SummingMergeTree(bets_count, total_amount). Jeśli nie podasz — sumowane są wszystkie kolumny liczbowe (oprócz kolumn z ORDER BY, ich wartości określają unikalność).

• ORDER BY (date, user_id) — właśnie te kolumny decydują, które wiersze zostaną scalone w jeden. Czyli wszystkie wiersze z tą samą datą i tym samym user_id podczas merge'a zostaną sklejone w jeden wiersz, gdzie bets_count i total_amount będą sumami.

Co się stanie, jeśli ORDER BY jest zbyt szeroki? Jeśli dodasz tam np. bets_count — wtedy każdy unikalny zakład (z różną liczbą) pozostanie osobnym wierszem. Nie będzie sumowania, bo klucze są różne. Pułapka nr 1 (wrócimy do tego).

Jak wstawiać dane

Wstawiamy surowe zdarzenia (każdy wiersz to jeden zakład):

-- Wstawiamy trzy zakłady użytkownika 123 z dnia 2025-06-01
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- jeden zakład na 100 zł
    ('2025-06-01', 123, 1, 250.00),   -- drugi zakład na 250 zł
    ('2025-06-01', 456, 1, 50.00);    -- inny użytkownik

-- Możesz wstawiać NIE zagregowane dane — silnik sam sobie poradzi

Po scaleniu w tle (może to potrwać od kilku sekund do godzin) wiersze z date='2025-06-01' i user_id=123 połączą się w jeden: ('2025-06-01', 123, 2, 350.00).

Odczyt bez GROUP BY — magia i jej ograniczenia

Pomysł jest taki, że po scaleniu można czytać dane bez agregacji:

-- Jeśli merge już nastąpił, to zapytanie zwróci jeden wiersz na użytkownika dziennie
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

Ale jest problem. Między scaleniami dane mogą leżeć w różnych kawałkach, gdzie są zduplikowane klucze. Dlatego w praktyce i tak piszesz z SUM:

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

Dlaczego to działa? Ponieważ nawet jeśli dane się nie scaliły, SUM doda wszystko poprawnie. A jeśli się scaliły — w każdej grupie masz jeden wiersz, a SUM po prostu zwróci jego wartość. Zapytanie wciąż czyta dane, ale teraz jest ich MNIEJ (zagregowanych wierszy zamiast surowych).

Analogia: SummingMergeTree to jak pomocnik, który wstępnie skleja identyczne paragony. Ale ty i tak prosisz "pokaż sumę dla każdego dnia". Jeśli paragony są już sklejone — suma zgadza się z liczbą w jednym wierszu. Jeśli nie — i tak dostajesz poprawną sumę. Najważniejsze, że czytasz nie miliony paragonów, a tysiące podsumowań.

3. Problem: między merge'ami potrzebne SUM w SELECT

To kluczowy punkt, który często jest źle rozumiany.

Naiwne podejście (nieprawidłowe):

-- Myśląc, że po merge dane są już zagregowane, nowicjusz pisze:
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- I dostaje kilka wierszy dla jednego użytkownika (jeśli merge nie nastąpił)

Prawidłowe podejście (bezpieczne):

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

Dlaczego tak?

1. Zawsze dostajesz poprawny wynik — zarówno przed, jak i po merge.
2. Rozmiar danych jest i tak mniejszy niż w surowej tabeli z zakładami.
3. ClickHouse dobrze optymalizuje takie zapytania.

Kiedy można bez SUM? Tylko jeśli masz pewność, że potrzebne dane już się scaliły. Na przykład po wymuszonym OPTIMIZE TABLE daily_stats (ale to kosztowna operacja, nie rób tego przy każdej okazji).

4. AggregatingMergeTree — gdy zwykła suma nie wystarcza

SummingMergeTree umie tylko dodawać liczby. A co, jeśli potrzebujesz:

• Policzyć unikalnych użytkowników (a nie sumę)?
• Znaleźć maksimum lub minimum?
• Obliczyć średnią?
• Użyć przybliżonych algorytmów, takich jak uniq do liczenia unikalnych?

Do tego służy AggregatingMergeTree. Przechowuje on nie tylko wartości, ale stany funkcji agregujących — specjalne dane pośrednie, które pozwalają później uzyskać końcowy wynik.

Analogia: SummingMergeTree przechowuje tylko końcową sumę. A AggregatingMergeTree przechowuje nie tylko sumę, ale też licznik (aby potem obliczyć średnią), lub tablicę haszującą unikalnych wartości (aby potem powiedzieć, ile ich było). To jak różnica między "mam wynik" a "mam notes, w którym zapisane są wszystkie dane, ale w skompresowanej formie".

CREATE TABLE z AggregateFunction

-- Tworzymy tabelę dla zagregowanych statystyk dashboardu
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- Godzina zdarzenia
    sport_type     String,                                 -- Rodzaj sportu (piłka nożna, koszykówka...)
    total_bets     AggregateFunction(sum, UInt64),         -- Suma liczby zakładów
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- Suma pieniędzy
    unique_users   AggregateFunction(uniq, UInt64),        -- Unikalni gracze (przybliżone)
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- Średnia wielkość zakładu
    max_bet        AggregateFunction(max, Decimal(18,2))   -- Maksymalny zakład
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

Rozkładamy nieznane:

• AggregateFunction(sum, UInt64) — typ kolumny, który przechowuje stan funkcji agregującej sum dla danych typu UInt64. To nie liczba, a wewnętrzna struktura ClickHouse.
• Dlaczego nie po prostu UInt64? Ponieważ dla niektórych funkcji (uniq, avg) trzeba przechowywać więcej danych niż tylko wynik. avg przechowuje zarówno sumę, jak i licznik. uniq przechowuje tablicę haszującą.
• Podczas scalania dwóch wierszy z tym samym ORDER BY (ta sama godzina i rodzaj sportu) — stany funkcji agregujących są łączone. Dla sum to po prostu dodanie pośrednich wyników. Dla uniq — połączenie dwóch tablic haszujących unikalnych wartości.

Wstawianie danych przez INSERT SELECT z funkcjami state

Nie możesz wstawić do kolumny AggregateFunction zwykłej wartości. Musisz użyć specjalnych funkcji *State, które tworzą stan z surowej wartości.

-- Wstawiamy zagregowane dane z surowej tabeli zakładów
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- Zaokrąglamy czas do godziny
    sport_type,
    sumState(bets_count) AS total_bets,                    -- Stan sumy
    sumState(amount) AS total_amount,                      -- Stan sumy pieniędzy
    uniqState(user_id) AS unique_users,                    -- Stan dla unikalnych
    avgState(amount) AS avg_bet_amount,                    -- Stan dla średniej
    maxState(amount) AS max_bet                            -- Stan dla maksimum
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

Co się tu dzieje:

• toStartOfHour(event_time) — funkcja ClickHouse, która z daty-czasu wycina godzinę: 2025-06-01 12:34:56 → 2025-06-01 12:00:00.
• sumState(amount) — zamiast SUM(amount) piszesz sumState(amount). Wynik to stan funkcji agregującej, typ AggregateFunction(sum, ...).
• Obowiązkowo GROUP BY w zapytaniu wstawiającym! Ponieważ agregujesz dane z surowej tabeli w grupy (godzina+sport), a potem każdą grupę wstawiasz jako jeden wiersz do dashboard_hourly.

Odczyt przez funkcje Merge

Aby odczytać dane, użyj funkcji *Merge:

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- Ze stanu → liczba
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- Unikalnych użytkowników
    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;   -- Grupowanie wciąż potrzebne (jeśli dane się nie scaliły)

Dlaczego znowu GROUP BY? Ten sam powód co w SummingMergeTree: między scaleniami może być kilka wierszy z tym samym ORDER BY. GROUP BY z *Merge da poprawny wynik w każdym stanie.

5. Wzorzec użycia z widokami zmaterializowanymi

Najpotężniejszy sposób użycia AggregatingMergeTree to połączenie z widokiem zmaterializowanym (Materialized View). Wstawiasz surowe dane do zwykłej tabeli, a widok automatycznie je agreguje i umieszcza w tabeli agregującej.

Analogia: To jak ustawienie taśmy produkcyjnej: surowe paragony trafiają do jednego pudełka, a automatyczny sortownik co minutę zbiera podsumowania według dni i wkłada do drugiego pudełka. Analitycy patrzą tylko na drugie pudełko — szybko i bez GROUP BY na bieżąco.

Pełny przykład: statystyka godzinowa dla dashboardu operatorów

Krok 1: Surowe dane — tutaj będą wstawiane zdarzenia (zakłady)

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

Krok 2: Tabela agregująca — tutaj będzie trafiać gotowa statystyka

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

Krok 3: Widok zmaterializowany — most między nimi

CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
    toStartOfHour(event_time) AS hour,
    sport_type,
    sumState(1) AS total_bets,                    -- Każdy wiersz to jeden zakład
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;

Co się teraz stanie?

1. Wstawiasz wiersze do raw_bets zwykłymi INSERT.
2. ClickHouse automatycznie (prawie natychmiast) przepuszcza je przez widok zmaterializowany.
3. Widok agreguje dane tylko z wstawionej porcji i wstawia wyniki do bets_hourly_agg.
4. W bets_hourly_agg mogą tymczasowo gromadzić się wiersze z tym samym (hour, sport_type) — ale podczas scalania w tle zostaną połączone.

Odczyt dla dashboardu:

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;

To zapytanie będzie czytać tylko zagregowane dane, które zajmują tysiące razy mniej miejsca niż surowe zakłady.

6. Przykład z życia: dashboard wydarzeń sportowych

Wyobraź sobie, że jesteś operatorem biura bukmacherskiego. Na dashboardzie trzeba pokazywać:

• Dla każdego meczu (piłka nożna, Liga Mistrzów, "Real" vs "Bayern")
• Ile zakładów postawiono w ciągu ostatnich 5 minut
• Sumę wszystkich zakładów
• Liczbę unikalnych graczy
• Średni zakład

Surowe dane: 5000 zakładów na sekundę. Przechowywanie wszystkiego i każdorazowe agregowanie od nowa to szaleństwo.

Rozwiązanie:

-- Tabela dla agregatów według meczów z interwałem 5 minut
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);

-- Widok zmaterializowany
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;

Teraz dashboard wykonuje zapytania do match_stats_5min — i dostaje odpowiedź w milisekundy zamiast sekund.

7. Kiedy NIE używać SummingMergeTree i AggregatingMergeTree

Kiedy SummingMergeTree jest odpowiedni:

• Potrzebujesz tylko sumować wartości liczbowe.
• Jesteś gotów pogodzić się z tym, że między scaleniami trzeba używać GROUP BY z SUM.
• Klucz grupowania nie jest zbyt wysokiej kardynalności (np. nie miliard unikalnych użytkowników — choć to też jest ok, po prostu danych będzie dużo).

Kiedy SummingMergeTree NIE jest odpowiedni:

• Potrzebujesz liczyć unikalnych użytkowników (uniq, count(DISTINCT)) — tu tylko AggregatingMergeTree.
• Potrzebujesz innych agregatów: avg, min, max — też tylko AggregatingMergeTree.
• Oczekujesz, że dane zawsze będą leżeć w już zagregowanej postaci — tak to nie działa.
• Twoje dane są aktualizowane (a nie tylko wstawiane) — te silniki nie są przeznaczone do semantyki update.

Kiedy AggregatingMergeTree jest odpowiedni:

• Potrzebujesz różnych typów agregacji (sumy, unikalne, średnie, maksima).
• Jesteś gotów pisać INSERT z *State i SELECT z *Merge.
• Używasz widoków zmaterializowanych do automatycznej agregacji.
• Wolumen surowych danych jest ogromny, a agregatów — o kilka rzędów wielkości mniej.

Kiedy AggregatingMergeTree NIE jest odpowiedni:

• Nie jesteś gotów tłumaczyć zespołowi, czym jest AggregateFunction i jak z nim pracować. Próg wejścia jest wyższy.
• Potrzebujesz dokładnej unikalności, a nie przybliżonej (uniq to struktura probabilistyczna, błąd ~2%). Do dokładnej użyj groupBitmap lub licz w innym systemie.
• Wolumen danych jest mały (miliony wierszy) — łatwiej zwykły GROUP BY.
• Często zmieniasz schemat agregacji (dodajesz nowe metryki) — odtwarzanie widoku zmaterializowanego jest bolesne.

8. Porównanie ze zwykłym MergeTree + GROUP BY

Cecha	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
Szybkość wstawiania	Maksymalna	Wysoka	Średnia (przez state)
Szybkość odczytu (duży zakres)	Niska (czyta wszystko)	Wysoka (czyta agregaty)	Wysoka
Szybkość odczytu (punktowy)	Średnia	Wysoka	Wysoka
Zajmowane miejsce	Maksimum	Minimum (agregaty)	Nieco więcej (stany)
Złożoność kodu	Niska	Niska (prosta tabela)	Wysoka (*State, *Merge)
Elastyczność agregacji	Dowolne	Tylko sumy	Dowolne (przez AggregateFunction)

9. Typowe pułapki

Pułapka nr 1: ORDER BY nie zawiera wystarczającej liczby pól

-- ŹLE: tylko data, bez user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- Podczas merge'a WSZYSTKIE wiersze z jednego dnia zostaną sklejone w jeden
-- Stracisz szczegółowość według użytkowników

Prawidłowo: Umieść w ORDER BY wszystkie pola, według których chcesz agregować.

Pułapka nr 2: Zapomniałeś GROUP BY w SELECT

-- ŹLE: bez GROUP BY, nawet jeśli dane się nie scaliły
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- Jeśli są dwa wiersze z tą samą datą, ale różnym user_id — dostaniesz błąd
-- ClickHouse nie wie, który user_id pokazać

Prawidłowo: Zawsze grupuj według tych samych pól co w ORDER BY.

Pułapka nr 3: Niestochastyczny uniq

uniq w ClickHouse to funkcja probabilistyczna. Błąd ~2-3%. Jeśli potrzebujesz dokładnej unikalności, użyj uniqExact lub groupBitmap.

Pułapka nr 4: Aktualizacja starych danych

SummingMergeTree i AggregatingMergeTree nie lubią aktualizacji. Jeśli trzeba poprawić zakład z wczoraj — łatwiej wstawić nowy wiersz z przeciwnym znakiem (przez CollapsingMergeTree).

10. Co dalej

Teraz, gdy opanowałeś silniki agregujące, kolejne tematy:

• Jak wybrać odpowiedni silnik dla swojego zadania — porównanie wszystkich *MergeTree.
• Widoki zmaterializowane w szczegółach — jak debugować, jak aktualizować schemat.
• Dostrajanie scalania w tle — aby agregaty scalały się szybciej.

Podsumowanie: SummingMergeTree i AggregatingMergeTree to narzędzia dla tych, którzy nie chcą, aby ich dashboard zwalniał na terabajtach danych. Wymagają nieco więcej zrozumienia na początku, ale wielokrotnie się zwracają przy rzeczywistych obciążeniach. Główna zasada: zawsze używaj GROUP BY i funkcji agregujących przy odczycie — wtedy jesteś bezpieczny przed i po merge'ach.

---

← Poprzedni: ReplacingMergeTree: Jak pokonać duplikaty w ClickHouse bez bólu
→ Następny: CollapsingMergeTree: Jak aktualizować agregaty bez UPDATE w ClickHouse

— Editorial Team