SummingMergeTree a AggregatingMergeTree v ClickHouse

SummingMergeTree a AggregatingMergeTree: inkrementální agregace bez bolesti

1. Proč jsou potřeba enginy pro agregaci — problém výpočtů na obrovských datech

Vraťme se k našemu online casinu. Každý den hráči uskuteční miliony sázek. Majitel dashboardu potřebuje vidět: kolik sázek každý uživatel za den provedl a na jakou částku.

V běžné databázi (PostgreSQL, MySQL) bys napsal:

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

Na tabulce se 100 miliony řádků bude takový dotaz trvat... no, chápeš — dlouho. Velmi dlouho. Protože databáze musí přečíst VŠECHNY řádky, seřadit je nebo hashovat a pak počítat agregáty.

ClickHouse je samozřejmě rychlejší — ale ani on není kouzelník. Čím více dat, tím déle trvá GROUP BY. A pokud je mnoho reportů a jsou potřeba "hned teď" — výkon se stává problémem.

Myšlenka: Co kdybychom agregáty předpočítali a ukládali je? Aby na dotaz "kolik sázek provedl user_id=123 za včerejšek" byla odpovědí prostě SELECT z řádku, nikoli plnohodnotná agregace?

K tomu má ClickHouse dva speciální tabulkové enginy: SummingMergeTree a AggregatingMergeTree. Dělají těžkou práci za tebe — na pozadí, během slučování kousků dat (merge).

Analogii ze života: Představ si, že vedeš evidenci prodejů v obchodě. Každý prodej je účtenka. Pokud majitel žádá report "kolik se prodalo za dnešek", můžeš pokaždé procházet všechny účtenky. Nebo si můžeš vést notes, kam na konci dne zapíšeš souhrn: "dnes 150 prodejů za 5000 Kč." SummingMergeTree je jako automatické vedení takového notesu.

2. SummingMergeTree — automatický sumátor

Jak to funguje

SummingMergeTree je engine, který při slučování kusů dat (na pozadí) sčítá číselné hodnoty pro řádky se stejným klíčem řazení (ORDER BY).

Pravidla sčítání:

• Všechny číselné sloupce (typy: UInt*, Int*, Float*, Decimal*) se automaticky sčítají.
• Ostatní sloupce (řetězce, data, pole) se berou z prvního nalezeného řádku — to je důležité si pamatovat, může tam být něco, co nečekáš.
• Pokud sloupec není číselný, ale chceš ho nějak agregovat — SummingMergeTree není vhodný, potřebuješ AggregatingMergeTree.

Proč "Summing": protože z několika řádků se stejným klíčem se udělá jeden a čísla v něm jsou součtem čísel z původních řádků.

CREATE TABLE — rozbor na atomy

-- Vytvoříme tabulku pro denní statistiku podle uživatelů
CREATE TABLE daily_stats
(
    date         Date,                -- Den, za který je statistika
    user_id      UInt64,              -- ID hráče
    bets_count   UInt64,              -- Počet sázek za den (bude se sčítat)
    total_amount Decimal(18,2)       -- Celková částka sázek (bude se sčítat)
)
ENGINE = SummingMergeTree()           -- Engine pro automatické sčítání
ORDER BY (date, user_id)              -- Klíč seskupení: podle data a uživatele

Co je zde důležité:

• ENGINE = SummingMergeTree() — lze uvést sloupce pro sčítání v závorkách: SummingMergeTree(bets_count, total_amount). Pokud neuvedeš, sčítají se všechny číselné sloupce (kromě sloupců z ORDER BY, jejich hodnoty určují jedinečnost).

• ORDER BY (date, user_id) — právě tyto sloupce určují, které řádky se sloučí do jednoho. To znamená, že všechny řádky se stejným datem a stejným user_id se při merge sloučí do jednoho řádku, kde bets_count a total_amount jsou součty.

Co se stane, když je ORDER BY příliš široký? Když tam zahrneš například bets_count — pak každá jedinečná sázka (s jiným počtem) zůstane jako samostatný řádek. Ke sčítání nedojde, protože klíče jsou různé. Past č. 1 (vrátíme se).

Jak vkládat data

Vkládáme syrové události (každý řádek = jedna sázka):

-- Vložíme tři sázky uživatele 123 za 2025-06-01
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- jedna sázka na 100 Kč
    ('2025-06-01', 123, 1, 250.00),   -- druhá sázka na 250 Kč
    ('2025-06-01', 456, 1, 50.00);    -- jiný uživatel

-- Můžeš vkládat NEagregovaná data — engine si sám poradí

Po sloučení na pozadí (může trvat od několika sekund do hodin) se řádky s date='2025-06-01' a user_id=123 sloučí do jednoho: ('2025-06-01', 123, 2, 350.00).

Čtení bez GROUP BY — magie a její limity

Myšlenka je, že po proběhnutí sloučení lze číst data bez agregace:

-- Pokud už merge proběhl, tento dotaz vrátí jeden řádek na uživatele a den
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

Ale je tu problém. Mezi sloučeními mohou data ležet v různých kusech, kde jsou duplicitní klíče. Proto v praxi stejně píšeš s SUM:

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

Proč to funguje? Protože i když se data nesloučila, SUM vše správně sečte. A pokud se sloučila — máš v každé skupině jeden řádek a SUM prostě vrátí jeho hodnotu. Dotaz stále čte data, ale nyní jich je MÉNĚ (agregovaných řádků místo syrových).

Analogii: SummingMergeTree je jako pomocník, který předběžně slepuje stejné účtenky. Ale stejně žádáš "ukaž součet za každý den". Pokud jsou účtenky slepené — součet se shoduje s číslem v jednom řádku. Pokud ne — stejně dostaneš správný součet. Hlavní je, že nebudeš číst miliony účtenek, ale tisíce souhrnů.

3. Problém: mezi mergi je potřeba SUM v SELECT

Toto je klíčový bod, který je často nepochopen.

Naivní přístup (nesprávný):

-- V domnění, že po merge jsou data již agregovaná, začátečník píše:
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- A dostane několik řádků pro jednoho uživatele (pokud merge neproběhl)

Správný přístup (bezpečný):

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

Proč tomu tak je?

1. Vždy dostaneš správný výsledek — jak před, tak po merge.
2. Velikost dat je stejně menší než v syrové tabulce se sázkami.
3. ClickHouse takové dotazy dobře optimalizuje.

Kdy lze bez SUM? Jen pokud si jsi jistý, že potřebná data už byla sloučena. Například po vynuceném OPTIMIZE TABLE daily_stats (ale to je drahá operace, nedělej ji při každém dotazu).

4. AggregatingMergeTree — když prostý součet nestačí

SummingMergeTree umí pouze sčítat čísla. Co když potřebuješ:

• Spočítat unikátní uživatele (ne součet)?
• Najít maximum nebo minimum?
• Vypočítat průměr?
• Použít přibližné algoritmy jako uniq pro počítání unikátů?

K tomu slouží AggregatingMergeTree. Ukládá nejen hodnoty, ale stavy agregačních funkcí — speciální mezidata, která umožňují později získat konečný výsledek.

Analogii: SummingMergeTree ukládá pouze konečný součet. Zatímco AggregatingMergeTree ukládá nejen součet, ale i počítadlo (aby se pak dal vypočítat průměr), nebo hashovací tabulku unikátních hodnot (aby se pak dalo říct, kolik jich bylo). Je to jako rozdíl mezi "mám výsledek" a "mám notes, kde jsou zapsaná všechna data, ale v komprimované podobě".

CREATE TABLE s AggregateFunction

-- Vytvoříme tabulku pro agregovanou statistiku dashboardu
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- Hodina události
    sport_type     String,                                 -- Druh sportu (fotbal, basketbal...)
    total_bets     AggregateFunction(sum, UInt64),         -- Součet počtu sázek
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- Součet peněz
    unique_users   AggregateFunction(uniq, UInt64),        -- Unikátní hráči (přibližně)
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- Průměrná výše sázky
    max_bet        AggregateFunction(max, Decimal(18,2))   -- Maximální sázka
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

Rozbor neznámého:

• AggregateFunction(sum, UInt64) — typ sloupce, který ukládá stav agregační funkce sum pro data typu UInt64. Není to číslo, ale vnitřní struktura ClickHouse.
• Proč ne prostě UInt64? Protože pro některé funkce (uniq, avg) je potřeba ukládat více dat než jen výsledek. avg ukládá součet i počítadlo. uniq ukládá hashovací tabulku.
• Při sloučení dvou řádků se stejným ORDER BY (stejná hodina a druh sportu) — stavy agregačních funkcí se spojí. Pro sum je to prosté sečtení mezisoučtů. Pro uniq — spojení dvou hashovacích tabulek unikátních hodnot.

Vkládání dat pomocí INSERT SELECT s funkcemi state

Do sloupce AggregateFunction nemůžeš vložit běžnou hodnotu. Je třeba použít speciální funkce *State, které vytvoří stav z nezpracované hodnoty.

-- Vložíme agregovaná data ze syrové tabulky sázek
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- Zaokrouhlíme čas na hodinu
    sport_type,
    sumState(bets_count) AS total_bets,                    -- Stav součtu
    sumState(amount) AS total_amount,                      -- Stav součtu peněz
    uniqState(user_id) AS unique_users,                    -- Stav pro unikátní
    avgState(amount) AS avg_bet_amount,                    -- Stav pro průměr
    maxState(amount) AS max_bet                            -- Stav pro maximum
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

Co se zde děje:

• toStartOfHour(event_time) — funkce ClickHouse, která z data-času vyřízne hodinu: 2025-06-01 12:34:56 → 2025-06-01 12:00:00.
• sumState(amount) — místo SUM(amount) píšeš sumState(amount). Výsledkem je stav agregační funkce, typ AggregateFunction(sum, ...).
• Povinný GROUP BY v insert dotazu! Protože agreguješ data ze syrové tabulky do skupin (hodina+sport) a pak každou skupinu vložíš jako jeden řádek do dashboard_hourly.

Čtení pomocí Merge funkcí

Pro čtení dat použij funkce *Merge:

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- Ze stavu → číslo
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- Unikátních uživatelů
    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;   -- Seskupení je stále potřeba (pokud se data nesloučila)

Proč znovu GROUP BY? Stejný důvod jako u SummingMergeTree: mezi sloučeními může být několik řádků se stejným ORDER BY. GROUP BY s *Merge dá správný výsledek v jakémkoli stavu.

5. Vzor použití s materializovanými pohledy

Nejvýkonnější způsob použití AggregatingMergeTree je ve spojení s materializovaným pohledem (Materialized View). Vkládáš syrová data do běžné tabulky a pohled je automaticky agreguje a ukládá do agregační tabulky.

Analogii: Je to jako nastavit dopravní pás: syrové účtenky padají do jedné krabice a automatický třídič každou minutu sbírá souhrny po dnech a ukládá je do jiné krabice. Analytici se dívají jen do druhé krabice — rychle a bez GROUP BY za běhu.

Kompletní příklad: hodinová statistika pro dashboard operátorů

Krok 1: Syrová tabulka — sem se budou vkládat události (sázky)

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: Agregační tabulka — sem se bude ukládat hotová statistika

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: Materializovaný pohled — most mezi 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ždý řádek = jedna sázka
    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 se nyní stane?

1. Vkládáš řádky do raw_bets běžnými INSERT.
2. ClickHouse automaticky (téměř okamžitě) je prožene materializovaným pohledem.
3. Pohled agreguje data pouze z vložené dávky a vloží výsledky do bets_hourly_agg.
4. V bets_hourly_agg se mohou dočasně nashromáždit několik řádků se stejným (hour, sport_type) — ale při sloučení na pozadí se spojí.

Čtení pro 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;

Tento dotaz bude číst pouze agregovaná data, která zabírají tisíckrát méně místa než syrové sázky.

6. Reálný příklad: dashboard sportovních událostí

Představ si, že jsi operátor sázkové kanceláře. Na dashboardu je třeba zobrazovat:

• Pro každý zápas (fotbal, Liga mistrů, "Real" vs "Bayern")
• Kolik sázek bylo provedeno za posledních 5 minut
• Celkovou částku všech sázek
• Počet unikátních hráčů
• Průměrnou sázku

Syrová data: 5000 sázek za sekundu. Ukládat vše a pokaždé znovu agregovat — šílenství.

Řešení:

-- Tabulka pro agregáty podle zápasů s intervalem 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);

-- Materializovaný pohled
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;

Nyní dashboard dotazuje match_stats_5min — a dostává odpověď za milisekundy místo sekund.

7. Kdy NEPOUŽÍVAT SummingMergeTree a AggregatingMergeTree

Kdy je vhodný SummingMergeTree:

• Potřebuješ pouze sčítat číselné hodnoty.
• Jsi ochoten smířit se s tím, že mezi sloučeními je třeba použít GROUP BY s SUM.
• Klíč seskupení není příliš vysoké kardinality (např. ne miliarda unikátních uživatelů — i když to je v pořádku, jen dat bude hodně).

Kdy NENÍ vhodný SummingMergeTree:

• Potřebuješ počítat unikátní uživatele (uniq, count(DISTINCT)) — zde jen AggregatingMergeTree.
• Potřebuješ jiné agregáty: avg, min, max — také jen AggregatingMergeTree.
• Očekáváš, že data budou vždy ležet v již agregované podobě — tak to nefunguje.
• Tvá data se aktualizují (nejen vkládají) — tyto enginy nejsou pro update-sémantiku.

Kdy je vhodný AggregatingMergeTree:

• Potřebuješ různé typy agregací (součty, unikátní, průměry, maxima).
• Jsi ochoten psát INSERT s *State a SELECT s *Merge.
• Používáš materializované pohledy pro automatickou agregaci.
• Objem syrových dat je obrovský a agregátů je o několik řádů méně.

Kdy NENÍ vhodný AggregatingMergeTree:

• Nejsi ochoten vysvětlovat týmu, co je AggregateFunction a jak s ním pracovat. Vstupní náročnost je vyšší.
• Potřebuješ přesnou unikátnost, nikoli přibližnou (uniq je pravděpodobnostní struktura, chyba ~2 %). Pro přesnou použij groupBitmap nebo počítej v jiném systému.
• Objem dat je malý (miliony řádků) — jednodušší je obyčejný GROUP BY.
• Často měníš schéma agregace (přidáváš nové metriky) — přetvářet materializovaný pohled je bolestivé.

8. Srovnání s běžným MergeTree + GROUP BY

Charakteristika	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
Rychlost vkládání	Maximální	Vysoká	Střední (kvůli state)
Rychlost čtení (velký rozsah)	Nízká (čte vše)	Vysoká (čte agregáty)	Vysoká
Rychlost čtení (bodový dotaz)	Střední	Vysoká	Vysoká
Zabrané místo	Maximum	Minimum (agregáty)	O něco více (stavy)
Složitost kódu	Nízká	Nízká (prostá tabulka)	Vysoká (*State, *Merge)
Flexibilita agregací	Libovolné	Pouze součty	Libovolné (přes AggregateFunction)

9. Typické pasti

Past č. 1: ORDER BY neobsahuje dostatek polí

-- ŠPATNĚ: pouze datum, bez user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- Při merge se sloučí VŠECHNY řádky za jeden den do jednoho
-- Ztratíš detail podle uživatelů

Správně: Zahrň do ORDER BY všechna pole, podle kterých chceš agregovat.

Past č. 2: Zapomněl jsi GROUP BY v SELECT

-- ŠPATNĚ: bez GROUP BY, i když se data nesloučila
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- Pokud existují dva řádky se stejným date, ale různým user_id — dostaneš chybu
-- ClickHouse neví, který user_id zobrazit

Správně: Vždy seskupuj podle stejných polí jako v ORDER BY.

Past č. 3: Nestochastický uniq

uniq v ClickHouse je pravděpodobnostní funkce. Chyba ~2-3 %. Pokud potřebuješ přesnou unikátnost, použij uniqExact nebo groupBitmap.

Past č. 4: Aktualizace starých dat

SummingMergeTree a AggregatingMergeTree nemají rády aktualizace. Pokud potřebuješ opravit sázku, která byla včera — jednodušší je vložit nový řádek s opačným znaménkem (přes CollapsingMergeTree).

10. Co dál

Teď, když jsi zvládl agregační enginy, následující témata:

• Jak vybrat správný engine pro svůj úkol — srovnání všech *MergeTree.
• Materializované pohledy do detailů — jak ladit, jak aktualizovat schéma.
• Ladění slučování na pozadí — aby se agregáty slučovaly rychleji.

Shrnutí: SummingMergeTree a AggregatingMergeTree jsou nástroje pro ty, kteří nechtějí, aby jejich dashboard zpomaloval na terabajtech dat. Vyžadují o něco více pochopení na začátku, ale mnohonásobně se vyplatí při reálném zatížení. Hlavní pravidlo: vždy používej GROUP BY a agregační funkce při čtení — a pak jsi v bezpečí před i po sloučení.

---

← Předchozí: ReplacingMergeTree: Jak porazit duplikáty v ClickHouse bez bolesti
→ Další: CollapsingMergeTree: Jak aktualizovat agregáty bez UPDATE v ClickHouse

— Editorial Team