SELECT-Abfragen in ClickHouse: Unterschiede zu PostgreSQL, PREWHERE, SAMPLE

SELECT-Abfragen in ClickHouse: Wie ich mein Gehirn nach 10 Jahren PostgreSQL umtrainierte

Als ich mich nach zehn Jahren PostgreSQL mit ClickHouse hinsetzte, versuchte ich eine vertraute SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10 auszuführen. Es funktionierte, aber schnell – verdächtig schnell. Dann lernte ich PREWHERE und SAMPLE kennen – Konstrukte, die es in PostgreSQL aufgrund der zeilenbasierten Speicherung nicht gibt und nicht geben kann.

ClickHouse führt SQL nicht nur aus – es denkt es für eine spaltenorientierte Architektur neu. Im Folgenden sind alle Unterschiede aufgeführt, die meine Abfragen (und manchmal die Produktion) zum Scheitern brachten.

1. Grundlegende Syntax: Ein vertrautes Gesicht mit spaltenorientiertem Charakter

Eine einfache SELECT-Abfrage sieht vertraut aus:

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

Aber der Unterschied zeigt sich, wenn man EXPLAIN betrachtet. PostgreSQL erstellt Pläne mit Seq Scan, Index Scan, Bitmap Heap Scan. ClickHouse zeigt die Anzahl der gelesenen Granulen und Partitionen.

Wichtiger Unterschied: In PostgreSQL ist SELECT * manchmal in Ordnung (wenn man fast alle Spalten benötigt). In ClickHouse liest SELECT * alle Spalten von der Festplatte. Wenn Sie 20 von 30 Spalten nicht benötigen – listen Sie nur die benötigten auf. Wir haben auf diese Weise 70 % Festplatten-I/O eingespart.

2. PREWHERE – Eine Optimierung, die PostgreSQL beneidet

PREWHERE filtert, BEVOR Spalten entpackt und gelesen werden.

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

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

Wie es funktioniert:

1. ClickHouse liest zuerst die Spalte outcome (eine Datei auf der Festplatte)
2. Filtert Zeilen, behält nur win
3. Nur für die gefilterten Zeilen werden die restlichen Spalten gelesen
4. Dann wird amount > 1000 angewendet

Wann ClickHouse PREWHERE automatisch anwendet: Wenn Sie WHERE outcome = 'win' schreiben, kann der Optimierer leichte Bedingungen automatisch nach PREWHERE verschieben. Aber ich schreibe es bei komplexen Bedingungen immer explizit.

Was mich verbrannt hat: PREWHERE funktioniert nicht mit Spalten aus dem PRIMARY KEY. ClickHouse liest zuerst den Index. Versuchen Sie nicht zu optimieren, was bereits schnell ist.

3. SAMPLE – Ungefähre Antwort in 0,1 Sekunden

Im Geschäftsleben bekommt man manchmal Fragen wie: „Schätzen Sie das ungefähre Wettvolumen pro Stunde, plus/minus 5 %.“ Absolute Genauigkeit ist nicht erforderlich.

-- 10 % zufällige Zeilen (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;

Wie SAMPLE physisch funktioniert: ClickHouse liest nicht jede Granule vollständig, sondern jede N-te. Dies funktioniert, weil die Daten auf der Festplatte nicht gemischt sind – innerhalb einer Granule sind sie nach ORDER BY sortiert.

Meine Regeln für SAMPLE:

• Für Aggregate mit Milliarden von Zeilen – SAMPLE 0.01 reicht mit 2-3 % Genauigkeit
• Nicht für exakte Berechnungen verwenden (Finanzen, Auszahlungen)
• Funktioniert nur, wenn die Tabelle mit einem SAMPLE BY-Schlüssel (oder ORDER BY) erstellt wurde

4. FINAL – Ein Minenfeld für Anfänger

Wenn Sie ReplacingMergeTree (eine Deduplizierungs-Engine) verwenden, können Zeilen mehrere Versionen haben. FINAL zwingt ClickHouse, sie im laufenden Betrieb zusammenzuführen.

-- Langsam (aber manchmal notwendig)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

Warum ich FINAL fast nie verwende: Es erzwingt das Lesen aller Teile und deren Zusammenführung im Arbeitsspeicher. Bei einer Milliarde Zeilen wird die Abfrage den Speicher sprengen.

Alternativen zu FINAL:

• Gruppierung mit argMax (empfohlen)
• Periodisches OPTIMIZE TABLE ... FINAL im Hintergrund
• Verwenden Sie ReplacingMergeTree gar nicht

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

5. IN/NOT IN mit Unterabfragen vs. JOIN

In PostgreSQL ist JOIN oft schneller als Unterabfragen. In ClickHouse ist das Gegenteil der Fall – IN mit einer Unterabfrage gewinnt oft.

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

-- Langsamer (aber lesbarer)
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;

Warum IN schneller ist: ClickHouse wandelt die Unterabfrage in einen Satz von Konstanten im Speicher um und filtert mit spaltenorientierten Operationen. JOIN erfordert zeilenweises Abgleichen.

Wann JOIN dennoch benötigt wird:

• Mehr als zwei Tabellen
• Benötigen Spalten aus beiden Tabellen in SELECT
• Komplexe Join-Bedingungen (nicht nur Gleichheit)

6. ANY / ALL-Modifikatoren – Relikte aus frühen Tagen

Diese Modifikatoren existieren aus Kompatibilitätsgründen mit anderen DBMS. Ich verwende sie selten.

-- ANY: wie MIN für Gruppierung
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL: wie MAX
SELECT user_id, ALL(amount) AS all_amounts  -- Array aller Beträge
FROM betting.bets
GROUP BY user_id;

Aber ich bevorzuge explizite Aggregationen: min(), max(), groupArray().

7. DISTINCT und seine Leistung

SELECT DISTINCT ist in ClickHouse schneller als in PostgreSQL, aber nicht kostenlos.

-- Alle eindeutigen Sportarten
SELECT DISTINCT sport FROM betting.bets;

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

Unter der Haube: ClickHouse baut eine Hashtabelle im Speicher auf. Wenn Sie DISTINCT auf einer Spalte mit einer Milliarde eindeutigen Werten ausführen, erhalten Sie eine OOM.

Meine Tipps:

• Statt SELECT DISTINCT user_id verwenden Sie GROUP BY user_id (gleiches Ergebnis)
• Für ungefähre eindeutige Anzahl – uniq() und uniqHLL12()
• Für die häufigsten eindeutigen Werte – topK()

8. FORMAT – Ausgabe, wie der Client sie benötigt

ClickHouse kann Ergebnisse in Dutzenden von Formaten zurückgeben. Ich verwende fünf:

-- Menschenlesbar (für Konsole)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- Kompakt (Standard)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

-- JSON für API
SELECT * FROM bets LIMIT 3 FORMAT JSON;

-- Zeilenweises JSON (speicherschonend beim Parsen)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- CSV für Excel
SELECT * FROM bets LIMIT 3 FORMAT CSV;

# In der Befehlszeile können Sie das Format überschreiben
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. Top 10 Abfragen für Wettanalysen (produktionsreif)

1. Wetten nach Sportart heute

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 Spieler nach Umsatz diese Woche

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. Stündliches Wettvolumen heute

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. Gewinnrate nach Quotenbereich

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. Aktivste Stunden nach Wochentag

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. Durchschnittlicher Einsatz und Quote pro Benutzer (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. Ungefähre eindeutige Spieler pro Stunde

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. Auszahlungen und Rückerstattungen pro Tag

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. Kombiwetten vs. Einzelwetten

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. Benutzer mit verdächtigen Mustern (Betrug)

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. Häufige Fehler bei der Migration von SQL von PostgreSQL zu ClickHouse

Fehler 1: Verwendung von SELECT * in Unterabfragen
In PostgreSQL ist das in Ordnung. In ClickHouse liest es auf jeder Ebene alle Spalten.

Fehler 2: Erwarten, dass ORDER BY in einer Unterabfrage bestehen bleibt
In ClickHouse garantieren Unterabfragen keine Reihenfolge, selbst mit ORDER BY. Nur auf oberster Ebene sortieren.

Fehler 3: Korrelierte Unterabfragen
ClickHouse optimiert korrelierte Unterabfragen schlecht. Schreiben Sie sie als JOINs um oder verwenden Sie Fensterfunktionen.

-- Schlecht (langsam)
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- Gut (schnell)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

Fehler 4: Erwarten von transaktionaler Integrität
ClickHouse hat kein REPEATABLE READ. Wenn Sie während einer Abfrage Daten einfügen, könnten Sie einen Teil davon sehen.

Fehler 5: UPDATE und DELETE ohne ALTER TABLE
In ClickHouse sind dies Mutationen, asynchron und schwer. Aktualisieren Sie nicht eine Million Zeilen mit einem Befehl.

Was kommt als Nächstes

Jetzt wissen Sie, wie Sie SELECT in ClickHouse ohne Überraschungen schreiben. Im nächsten Artikel – fortgeschrittene Aggregationen und Fensterfunktionen.

---

← Vorherige: Daten in ClickHouse laden: Wie ich aufhörte, Zeile für Zeile einzufügen und die Aufnahme um das 500-fache beschleunigte
→ Nächste: ClickHouse-Aggregatfunktionen: Wie ich aufgehört habe, uniqHLL12 und quantileTDigest zu fürchten

— Editorial Team