SELECT dotazy v ClickHouse: odlišnosti od PostgreSQL, PREWHERE, SAMPLE

SELECT dotazy v ClickHouse: jak jsem přeučoval mozek po 10 letech PostgreSQL

Můj první šok: PREWHERE a SAMPLE, které v PostgreSQL nejsou

Když jsem po deseti letech PostgreSQL usedl k ClickHouse, zkusil jsem provést známý SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10. Vše fungovalo, ale rychle – podezřele rychle. Pak jsem se dozvěděl o PREWHERE a SAMPLE – konstrukcích, které v PostgreSQL nejsou a ani být nemohou kvůli řádkovému ukládání.

ClickHouse SQL jen neprovádí – přehodnocuje ho pro sloupcovou architekturu. Níže jsou všechny rozdíly, na kterých jsem lámal dotazy (a někdy i produkci).

1. Základní syntaxe: známá tvář se sloupcovým charakterem

Základní SELECT vypadá povědomě:

-- Nejjednodušší výběr
SELECT user_id, amount, odds 
FROM betting.bets 
WHERE created_at >= today() - 7 
ORDER BY amount DESC 
LIMIT 100;

Ale rozdíl začíná, když se podíváte na EXPLAIN. PostgreSQL vytváří plány s Seq Scan, Index Scan, Bitmap Heap Scan. ClickHouse ukazuje počet přečtených granulí a oddílů.

Hlavní rozdíl: V PostgreSQL je SELECT * někdy v pořádku (pokud potřebujete téměř všechny sloupce). V ClickHouse SELECT * čte všechny sloupce z disku. Pokud nepotřebujete 20 z 30 sloupců – vypisujte jen potřebné. Tím jsme šetřili 70% diskového I/O.

2. PREWHERE – optimalizace, které PostgreSQL závidí

PREWHERE je filtrování PŘED tím, než jsou sloupce rozbaleny a přečteny.

-- Bez PREWHERE (pomalejší)
SELECT user_id, amount, odds, sport
FROM betting.bets
WHERE outcome = 'win' AND amount > 1000;

-- S PREWHERE (rychlejší)
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

Jak to funguje:

1. ClickHouse nejprve přečte sloupec outcome (jeden soubor na disku)
2. Filtruje řádky, ponechá pouze win
3. Teprve pro filtrované řádky přečte ostatní sloupce
4. Poté aplikuje amount > 1000

Kdy ClickHouse sám použije PREWHERE: Pokud napíšete WHERE outcome = 'win', optimalizátor může sám přesunout lehké podmínky do PREWHERE. Ale já vždy píšu explicitně pro složité podmínky.

Na čem jsem se spálil: PREWHERE nefunguje se sloupci z PRIMARY KEY. ClickHouse stejně nejprve čte index. Nesnažte se optimalizovat to, co je už rychlé.

3. SAMPLE – přibližná odpověď za 0.1 sekundy

V byznysu se objevují otázky: "Odhadni přibližný objem sázek za hodinu, plus minus 5%." K tomu není potřeba absolutní přesnost.

-- 10% náhodných řádků (SAMPLE 0.1)
SELECT 
    toHour(created_at) AS hour,
    count() * 10 AS estimated_total_bets
FROM betting.bets
SAMPLE 0.1
WHERE created_at >= now() - INTERVAL 1 HOUR
GROUP BY hour;

Jak SAMPLE fyzicky funguje: ClickHouse nečte každou granuli celou, ale každou N-tou. To funguje, protože data na disku nejsou promíchaná – v rámci granule jsou seřazena podle ORDER BY.

Moje pravidla pro SAMPLE:

• Pro agregace s miliardami řádků – SAMPLE 0.01 stačí s přesností 2-3%
• Nepoužívejte pro přesné výpočty (finance, výplaty)
• Funguje pouze pokud je tabulka vytvořena s klíčem SAMPLE BY (nebo ORDER BY)

4. FINAL – líheň hrábí pro začátečníky

Pokud používáte ReplacingMergeTree (engine pro deduplikaci), řádky mohou mít několik verzí. FINAL donutí ClickHouse je sloučit za běhu.

-- Pomalu (ale někdy nutné)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

Proč FINAL téměř nikdy nepoužívám: Nutí číst všechny části a slučovat je v paměti. Pokud máte miliardu řádků, dotaz spadne kvůli paměti.

Alternativy k FINAL:

• Agregace s argMax (doporučuji)
• Pravidelný OPTIMIZE TABLE ... FINAL na pozadí
• Nepoužívat ReplacingMergeTree vůbec

-- Místo FINAL
SELECT user_id, argMax(amount, version) AS last_amount
FROM betting.bets_replacing
GROUP BY user_id;

5. IN/NOT IN s poddotazy vs JOIN

V PostgreSQL je JOIN často rychlejší než poddotazy. V ClickHouse je to naopak – IN s poddotazem často vítězí.

-- Rychlé v ClickHouse
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- Pomalejší (ale čitelnější)
SELECT b.user_id, sum(b.amount)
FROM betting.bets b
JOIN betting.fraud_users f ON b.user_id = f.user_id
GROUP BY b.user_id;

Proč je IN rychlejší: ClickHouse převede poddotaz na sadu konstant v paměti a filtruje sloupcovými operacemi. JOIN vyžaduje řádkové porovnávání.

Kdy je JOIN přesto potřeba:

• Více než dvě tabulky
• Potřebujete sloupce z obou tabulek v SELECT
• Složité podmínky spojení (nejen rovnost)

6. ANY / ALL modifikátory – pozůstatky z dávných dob

Tyto modifikátory jsou potřebné pro kompatibilitu s jinými DBMS. Používám je zřídka.

-- ANY: jako MIN pro agregaci
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL: jako MAX
SELECT user_id, ALL(amount) AS all_amounts  -- pole všech částek
FROM betting.bets
GROUP BY user_id;

Ale preferuji explicitní agregace: min(), max(), groupArray().

7. DISTINCT a jeho výkon

SELECT DISTINCT v ClickHouse funguje rychleji než v PostgreSQL, ale není zadarmo.

-- Všechny unikátní sporty
SELECT DISTINCT sport FROM betting.bets;

-- DISTINCT s ORDER BY
SELECT DISTINCT user_id, created_at
FROM betting.bets
ORDER BY created_at DESC
LIMIT 100;

Co je pod kapotou: ClickHouse vytváří hash tabulku v paměti. Pokud děláte DISTINCT na sloupci s miliardou unikátních hodnot – dostanete OOM.

Moje lifehacky:

• Místo SELECT DISTINCT user_id použijte GROUP BY user_id (stejné)
• Pro přibližný počet unikátních – uniq() a uniqHLL12()
• Pro top unikátních – topK()

8. FORMAT – výstup podle potřeb klienta

ClickHouse umí vracet výsledek v desítkách formátů. Používám pět:

-- Čitelný pro člověka (pro konzoli)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- Kompaktní (výchozí)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

-- JSON pro API
SELECT * FROM bets LIMIT 3 FORMAT JSON;

-- Řádkový JSON (úspora paměti při parsování)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- CSV pro Excel
SELECT * FROM bets LIMIT 3 FORMAT CSV;

# V příkazovém řádku lze formát přepsat
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. Top 10 dotazů pro betting analytiku (fungují v produkci)

1. Sázky podle sportů za dnešek

SELECT 
    sport,
    count() AS bets,
    sum(amount) AS total_staked,
    round(avg(odds), 2) AS avg_odds
FROM betting.bets
WHERE created_at >= today()
GROUP BY sport
ORDER BY total_staked DESC;

2. Top 10 hráčů podle obratu za týden

SELECT 
    user_id,
    count() AS bets,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_won,
    round(total_won / total_staked, 4) AS roi
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
ORDER BY total_staked DESC
LIMIT 10;

3. Hodinový objem sázek za dnešek

SELECT 
    toHour(created_at) AS hour,
    count() AS bets,
    sum(amount) AS volume
FROM betting.bets
WHERE created_at >= today()
GROUP BY hour
ORDER BY hour;

4. Win rate podle rozsahů kurzů

SELECT 
    CASE 
        WHEN odds < 1.5 THEN '1.00-1.49'
        WHEN odds < 2.0 THEN '1.50-1.99'
        WHEN odds < 3.0 THEN '2.00-2.99'
        ELSE '3.00+'
    END AS odds_range,
    count() AS total_bets,
    countIf(outcome = 'win') AS wins,
    round(wins / total_bets, 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY odds_range
ORDER BY odds_range;

5. Nejaktivnější hodiny podle dnů v týdnu

SELECT 
    toDayOfWeek(created_at) AS dow,
    toHour(created_at) AS hour,
    count() AS bets
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY dow, hour
ORDER BY dow, hour;

6. Průměrná sázka a kurz podle uživatelů (LTV)

SELECT 
    user_id,
    avg(amount) AS avg_bet,
    avg(odds) AS avg_odds,
    count() AS total_bets,
    now() - max(created_at) AS hours_since_last_bet
FROM betting.bets
GROUP BY user_id
HAVING total_bets > 100
ORDER BY avg_bet DESC
LIMIT 50;

7. Přibližný odhad unikátních hráčů za hodinu

SELECT 
    toStartOfHour(created_at) AS hour,
    uniq(user_id) AS unique_users_approx,
    uniqExact(user_id) AS unique_users_exact
FROM betting.bets
WHERE created_at >= today() - 1
GROUP BY hour
ORDER BY hour;

8. Výplaty a vrácení podle dnů

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS staked,
    sumIf(amount * odds, outcome = 'win') AS paid,
    sumIf(amount, outcome = 'void') AS refunded,
    round((paid + refunded) / staked, 4) AS net_hold_pct
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

9. Kombinované sázky vs jednotlivé

SELECT 
    bet_type,
    count() AS bets,
    avg(amount) AS avg_stake,
    avg(odds) AS avg_odds,
    avgIf(amount * odds, outcome = 'win') AS avg_payout
FROM betting.bets
GROUP BY bet_type;

10. Uživatelé s podezřelým vzorem (fraud)

SELECT 
    user_id,
    count() AS bets_5min,
    stddevPop(amount) AS stake_variance,
    stddevPop(odds) AS odds_variance
FROM betting.bets
WHERE created_at >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING bets_5min > 30 AND stake_variance < 1 AND odds_variance < 0.1;

10. Typické chyby při přenosu SQL z PostgreSQL do ClickHouse

Chyba 1: Použití SELECT * v poddotazech
V PostgreSQL je to normální. V ClickHouse čte všechny sloupce na každé úrovni.

Chyba 2: Očekávání, že ORDER BY v poddotazu zůstane zachován
V ClickHouse poddotazy nezaručují pořadí, ani s ORDER BY. Řaďte pouze na nejvyšší úrovni.

Chyba 3: Vnořené dotazy s korelací
ClickHouse špatně optimalizuje korelované poddotazy. Přepište na JOIN nebo použijte okenní funkce.

-- Špatně (pomalu)
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- Dobře (rychle)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

Chyba 4: Očekávání transakční integrity
V ClickHouse není REPEATABLE READ. Pokud vkládáte data během dotazu – můžete vidět jejich část.

Chyba 5: UPDATE a DELETE bez ALTER TABLE
V ClickHouse jsou to mutace, asynchronní a těžké. Neaktualizujte milion řádků jedním příkazem.

Co dál

Teď víte, jak psát SELECT v ClickHouse, abyste se nedivili. V příštím článku – pokročilé agregace a okenní funkce.

---

← Předchozí: Načítání dat do ClickHouse: jak jsem přestal vkládat po jednom řádku a zrychlil příjem 500krát
→ Další: Agregační funkce ClickHouse: jak jsem se přestal bát uniqHLL12 a quantileTDigest

— Editorial Team