Consultas SELECT en ClickHouse: diferencias con PostgreSQL, PREWHERE, SAMPLE

Consultas SELECT en ClickHouse: Cómo reentrené mi cerebro después de 10 años con PostgreSQL

Cuando me senté con ClickHouse después de diez años con PostgreSQL, intenté ejecutar un familiar SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10. Funcionó, pero rápido — sospechosamente rápido. Luego aprendí sobre PREWHERE y SAMPLE — construcciones que no existen y no pueden existir en PostgreSQL debido al almacenamiento basado en filas.

ClickHouse no solo ejecuta SQL — lo repiensa para una arquitectura columnar. A continuación, todas las diferencias que rompieron mis consultas (y a veces la producción).

1. Sintaxis básica: Una cara familiar con carácter columnar

Un SELECT básico se ve familiar:

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

Pero la diferencia comienza cuando miras EXPLAIN. PostgreSQL construye planes con Seq Scan, Index Scan, Bitmap Heap Scan. ClickHouse muestra el número de gránulos y particiones leídas.

Diferencia clave: En PostgreSQL, SELECT * a veces está bien (si necesitas casi todas las columnas). En ClickHouse, SELECT * lee todas las columnas del disco. Si no necesitas 20 de 30 columnas — enumera solo las que necesitas. Así ahorramos un 70% de E/S de disco.

2. PREWHERE — Una optimización que PostgreSQL envidia

PREWHERE filtra ANTES de que las columnas se desempaqueten y lean.

-- Sin PREWHERE (más lento)
SELECT user_id, amount, odds, sport
FROM betting.bets
WHERE outcome = 'win' AND amount > 1000;

-- Con PREWHERE (más rápido)
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

Cómo funciona:

1. ClickHouse primero lee la columna outcome (un archivo en disco)
2. Filtra filas, conservando solo win
3. Solo para las filas filtradas, lee las columnas restantes
4. Luego aplica amount > 1000

Cuándo ClickHouse aplica PREWHERE automáticamente: Si escribes WHERE outcome = 'win', el optimizador puede mover automáticamente condiciones ligeras a PREWHERE. Pero yo siempre lo escribo explícitamente para condiciones complejas.

Lo que me quemó: PREWHERE no funciona con columnas de PRIMARY KEY. ClickHouse aún lee el índice primero. No intentes optimizar lo que ya es rápido.

3. SAMPLE — Respuesta aproximada en 0.1 segundos

En los negocios, a veces te hacen preguntas como: "Estima el volumen aproximado de apuestas por hora, más o menos un 5%." No se necesita precisión absoluta.

-- 10% de filas aleatorias (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;

Cómo funciona SAMPLE físicamente: ClickHouse no lee cada gránulo completo, sino cada N-ésimo. Esto funciona porque los datos en disco no están mezclados — dentro de un gránulo, están ordenados por ORDER BY.

Mis reglas para SAMPLE:

• Para agregados con miles de millones de filas — SAMPLE 0.01 es suficiente con 2-3% de precisión
• No lo uses para cálculos exactos (finanzas, pagos)
• Solo funciona si la tabla se creó con una clave SAMPLE BY (o ORDER BY)

4. FINAL — Un campo minado para principiantes

Si usas ReplacingMergeTree (un motor de deduplicación), las filas pueden tener múltiples versiones. FINAL obliga a ClickHouse a fusionarlas sobre la marcha.

-- Lento (pero a veces necesario)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

Por qué casi nunca uso FINAL: Obliga a leer todas las partes y fusionarlas en memoria. Si tienes mil millones de filas, la consulta se quedará sin memoria.

Alternativas a FINAL:

• Agrupación con argMax (recomendado)
• OPTIMIZE TABLE ... FINAL periódico en segundo plano
• No uses ReplacingMergeTree en absoluto

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

5. IN/NOT IN con subconsultas vs JOIN

En PostgreSQL, JOIN suele ser más rápido que las subconsultas. En ClickHouse, ocurre lo contrario — IN con una subconsulta a menudo gana.

-- Rápido en ClickHouse
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- Más lento (pero más legible)
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;

Por qué IN es más rápido: ClickHouse convierte la subconsulta en un conjunto de constantes en memoria y filtra usando operaciones columnares. JOIN requiere coincidencia fila por fila.

Cuándo JOIN sigue siendo necesario:

• Más de dos tablas
• Necesitas columnas de ambas tablas en SELECT
• Condiciones de join complejas (no solo igualdad)

6. Modificadores ANY / ALL — Reliquias de los primeros días

Estos modificadores existen para compatibilidad con otros DBMS. Rara vez los uso.

-- ANY: como MIN para agrupación
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL: como MAX
SELECT user_id, ALL(amount) AS all_amounts  -- array de todos los montos
FROM betting.bets
GROUP BY user_id;

Pero prefiero agregaciones explícitas: min(), max(), groupArray().

7. DISTINCT y su rendimiento

SELECT DISTINCT en ClickHouse es más rápido que en PostgreSQL, pero no es gratuito.

-- Todos los deportes únicos
SELECT DISTINCT sport FROM betting.bets;

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

Internamente: ClickHouse construye una tabla hash en memoria. Si ejecutas DISTINCT en una columna con mil millones de valores únicos — obtendrás OOM.

Mis consejos:

• En lugar de SELECT DISTINCT user_id, usa GROUP BY user_id (mismo resultado)
• Para conteo aproximado de únicos — uniq() y uniqHLL12()
• Para valores únicos principales — topK()

8. FORMAT — Salida según lo que necesite el cliente

ClickHouse puede devolver resultados en docenas de formatos. Yo uso cinco:

-- Legible para humanos (para consola)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- Compacto (por defecto)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

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

-- JSON fila por fila (ahorra memoria al analizar)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

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

# En línea de comandos, puedes sobrescribir el formato
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. Top 10 consultas para análisis de apuestas (listas para producción)

1. Apuestas por deporte hoy

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 jugadores por volumen de negocio esta semana

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. Volumen de apuestas por hora hoy

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. Tasa de ganancia por rango de cuotas

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. Horas más activas por día de la semana

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. Apuesta y cuota promedio por usuario (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. Jugadores únicos aproximados por hora

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. Pagos y reembolsos por día

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. Apuestas combinadas vs simples

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. Usuarios con patrones sospechosos (fraude)

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. Errores comunes al migrar SQL de PostgreSQL a ClickHouse

Error 1: Usar SELECT * en subconsultas
En PostgreSQL está bien. En ClickHouse, lee todas las columnas en cada nivel.

Error 2: Esperar que ORDER BY en una subconsulta persista
En ClickHouse, las subconsultas no garantizan orden, incluso con ORDER BY. Solo ordena en el nivel superior.

Error 3: Subconsultas correlacionadas
ClickHouse optimiza mal las subconsultas correlacionadas. Reescíbelas como JOIN o usa funciones de ventana.

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

-- Bueno (rápido)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

Error 4: Esperar integridad transaccional
ClickHouse no tiene REPEATABLE READ. Si insertas datos durante una consulta — podrías ver parte de ellos.

Error 5: UPDATE y DELETE sin ALTER TABLE
En ClickHouse, estas son mutaciones, asíncronas y pesadas. No actualices un millón de filas con un solo comando.

Qué sigue

Ahora sabes cómo escribir SELECT en ClickHouse sin sorpresas. En el próximo artículo — agregaciones avanzadas y funciones de ventana.

---

← Anterior: Carga de datos en ClickHouse: Cómo dejé de insertar una fila a la vez y aceleré la ingesta 500 veces
→ Siguiente: Funciones de Agregación en ClickHouse: Cómo Dejé de Temer a uniqHLL12 y quantileTDigest

— Editorial Team