SELECT zapytania w ClickHouse: różnice od PostgreSQL, PREWHERE, SAMPLE

Zapytania SELECT w ClickHouse: jak przestawiałem mózg po 10 latach PostgreSQL

Mój pierwszy szok: PREWHERE i SAMPLE, których nie ma w PostgreSQL

Kiedy po dziesięciu latach PostgreSQL usiadłem do ClickHouse, spróbowałem wykonać znajome SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10. Wszystko działało, ale szybko – podejrzanie szybko. Potem dowiedziałem się o PREWHERE i SAMPLE – konstrukcjach, których w PostgreSQL nie ma i nie może być ze względu na przechowywanie wierszowe.

ClickHouse nie tylko wykonuje SQL – on go przemyśla na nowo dla architektury kolumnowej. Poniżej – wszystkie różnice, na których łamałem zapytania (i czasami produkcję).

1. Podstawowa składnia: znajoma twarz z kolumnowym charakterem

Podstawowo SELECT wygląda znajomo:

-- Najprostsze zapytanie
SELECT user_id, amount, odds 
FROM betting.bets 
WHERE created_at >= today() - 7 
ORDER BY amount DESC 
LIMIT 100;

Ale różnica zaczyna się, gdy spojrzysz na EXPLAIN. PostgreSQL buduje plany z Seq Scan, Index Scan, Bitmap Heap Scan. ClickHouse pokazuje liczbę przeczytanych granul i partycji.

Główna różnica: W PostgreSQL SELECT * jest czasami normalny (jeśli potrzebujesz prawie wszystkich kolumn). W ClickHouse SELECT * czyta wszystkie kolumny z dysku. Jeśli nie potrzebujesz 20 z 30 kolumn – wymieniaj tylko potrzebne. My na tym oszczędzaliśmy 70% dyskowego I/O.

2. PREWHERE – optymalizacja, której zazdrości PostgreSQL

PREWHERE to filtrowanie PRZED rozpakowaniem i odczytaniem kolumn.

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

-- Z PREWHERE (szybciej)
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

Jak to działa:

1. ClickHouse najpierw czyta kolumnę outcome (jeden plik na dysku)
2. Filtruje wiersze, zostawiając tylko win
3. Tylko dla przefiltrowanych wierszy czyta pozostałe kolumny
4. Potem stosuje amount > 1000

Kiedy ClickHouse sam stosuje PREWHERE: Jeśli napisałeś WHERE outcome = 'win', optymalizator może sam wynieść lekkie warunki do PREWHERE. Ale ja zawsze piszę jawnie dla złożonych warunków.

Na czym się sparzyłem: PREWHERE nie działa z kolumnami z PRIMARY KEY. ClickHouse i tak najpierw czyta indeks. Nie próbuj optymalizować tego, co już jest szybkie.

3. SAMPLE – przybliżona odpowiedź w 0.1 sekundy

W biznesie zdarzają się pytania: „Oszacuj przybliżoną liczbę zakładów na godzinę, plus-minus 5%”. Do tego nie potrzeba absolutnej dokładności.

-- 10% losowych wierszy (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 działa fizycznie: ClickHouse czyta nie każdą granulę w całości, ale co N-tą. Działa to, ponieważ dane na dysku nie są wymieszane – w obrębie granuli są posortowane według ORDER BY.

Moje zasady dla SAMPLE:

• Dla agregatów z miliardami wierszy – SAMPLE 0.01 wystarcza z dokładnością 2-3%
• Nie używaj do dokładnych obliczeń (finanse, wypłaty)
• Działa tylko jeśli tabela została utworzona z kluczem SAMPLE BY (lub ORDER BY)

4. FINAL – siedlisko pułapek dla początkujących

Jeśli używasz ReplacingMergeTree (silnik do deduplikacji), wiersze mogą mieć wiele wersji. FINAL zmusza ClickHouse do scalenia ich na bieżąco.

-- Wolno (ale czasem potrzebne)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

Dlaczego prawie nigdy nie używam FINAL: Zmusza do odczytu wszystkich części i scalania ich w pamięci. Jeśli masz miliard wierszy, zapytanie padnie z powodu braku pamięci.

Alternatywy dla FINAL:

• Grupowanie z argMax (polecam)
• Okresowe OPTIMIZE TABLE ... FINAL w tle
• Nie używanie ReplacingMergeTree w ogóle

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

5. IN/NOT IN z podzapytaniami vs JOIN

W PostgreSQL JOIN jest często szybszy od podzapytań. W ClickHouse odwrotnie – IN z podzapytaniem często wygrywa.

-- Szybko w ClickHouse
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- Wolniej (ale czytelniej)
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;

Dlaczego IN jest szybsze: ClickHouse zamienia podzapytanie w zestaw stałych w pamięci i filtruje operacjami kolumnowymi. JOIN wymaga porównywania wiersz po wierszu.

Kiedy JOIN jest jednak potrzebny:

• Więcej niż dwie tabele
• Potrzebne kolumny z obu tabel w SELECT
• Złożone warunki łączenia (nie tylko równość)

6. Modyfikatory ANY / ALL – relikty z początków

Te modyfikatory są potrzebne dla zgodności z innymi SZBD. Używam ich rzadko.

-- ANY: jak MIN dla grupowania
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL: jak MAX
SELECT user_id, ALL(amount) AS all_amounts  -- tablica wszystkich kwot
FROM betting.bets
GROUP BY user_id;

Ale wolę jawne agregacje: min(), max(), groupArray().

7. DISTINCT i jego wydajność

SELECT DISTINCT w ClickHouse działa szybciej niż w PostgreSQL, ale nie za darmo.

-- Wszystkie unikalne sporty
SELECT DISTINCT sport FROM betting.bets;

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

Co pod maską: ClickHouse buduje tablicę mieszającą w pamięci. Jeśli robisz DISTINCT na kolumnie z miliardem unikalnych wartości – dostaniesz OOM.

Moje lifehacki:

• Zamiast SELECT DISTINCT user_id używaj GROUP BY user_id (to samo)
• Do przybliżonej liczby unikalnych – uniq() i uniqHLL12()
• Do topki unikalnych – topK()

8. FORMAT – wyświetlamy jak potrzebuje klient

ClickHouse potrafi zwracać wynik w dziesiątkach formatów. Używam pięciu:

-- Czytelny dla człowieka (dla konsoli)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- Kompaktowy (domyślnie)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

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

-- Wierszowy JSON (oszczędność pamięci przy parsowaniu)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- CSV dla Excela
SELECT * FROM bets LIMIT 3 FORMAT CSV;

# W wierszu poleceń można nadpisać format
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. Top 10 zapytań do analityki zakładów (działają na produkcji)

1. Zakłady według sportów dzisiaj

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 graczy według obrotu w tygodniu

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. Godzinowy wolumen zakładów dzisiaj

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. Wskaźnik wygranych według przedziałów kursów

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. Najbardziej aktywne godziny według dni tygodnia

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. Średni zakład i kurs według użytkownika (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. Przybliżona liczba unikalnych graczy na godzinę

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. Wypłaty i zwroty według dni

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. Kupony łączone vs zakłady pojedyncze

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żytkownicy z podejrzanym wzorcem (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. Typowe błędy przenoszenia SQL z PostgreSQL na ClickHouse

Błąd 1: Używanie SELECT * w podzapytaniach
W PostgreSQL to normalne. W ClickHouse czyta wszystkie kolumny na każdym poziomie.

Błąd 2: Oczekiwanie, że ORDER BY w podzapytaniu zostanie zachowany
W ClickHouse podzapytania nie gwarantują kolejności, nawet z ORDER BY. Sortuj tylko na najwyższym poziomie.

Błąd 3: Zagnieżdżone zapytania skorelowane
ClickHouse słabo optymalizuje skorelowane podzapytania. Przepisuj na JOIN lub używaj funkcji okienkowych.

-- Źle (wolno)
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- Dobrze (szybko)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

Błąd 4: Oczekiwanie integralności transakcyjnej
W ClickHouse nie ma REPEATABLE READ. Jeśli wstawiasz dane podczas zapytania – możesz zobaczyć ich część.

Błąd 5: UPDATE i DELETE bez ALTER TABLE
W ClickHouse to mutacje, asynchroniczne i ciężkie. Nie aktualizuj miliona wierszy jedną komendą.

Co dalej

Teraz wiesz, jak pisać SELECT w ClickHouse, żeby się nie dziwić. W następnym artykule – zaawansowane agregacje i funkcje okienkowe.

---

← Poprzedni: Ładowanie danych do ClickHouse: jak przestałem wstawiać pojedyncze wiersze i przyspieszyłem przyjmowanie 500 razy
→ Następny: Funkcje agregujące ClickHouse: jak przestałem bać się uniqHLL12 i quantileTDigest

— Editorial Team