ClickHouse desde cero (III): analítica

Tercera entrega de la serie ClickHouse desde cero a pro. Tiempo de lectura estimado: 12 minutos.

Ya tienes ClickHouse funcionando (entrega I) y sabes diseñar tablas con tipos adecuados y MergeTree bien pensado (entrega II). Ahora toca la parte divertida: escribir consultas que, con PostgreSQL, no te habrías planteado ni intentar.

Este post no pretende listar todas las funciones (hay cientos), sino mostrar las familias que más usarás y las que te van a sorprender si vienes del SQL tradicional. Si aún no tienes claro por qué ClickHouse puede ser necesario en tu stack, léete ClickHouse para desarrolladores que vienen de PostgreSQL.

Funciones de fecha y tiempo

Olvida date_trunc y extract. ClickHouse tiene una familia de funciones toStart* y toXxxOf* muchísimo más granular:

toStartOfMinute(ts)
toStartOfFiveMinutes(ts)
toStartOfFifteenMinutes(ts)
toStartOfHour(ts)
toStartOfDay(ts)
toStartOfWeek(ts)          -- lunes
toStartOfMonth(ts)
toStartOfQuarter(ts)
toStartOfYear(ts)

toYear(ts), toMonth(ts), toDayOfMonth(ts), toDayOfWeek(ts)
toHour(ts), toMinute(ts), toSecond(ts)

Son funciones puras, muy baratas, y ClickHouse las conoce lo bastante bien como para aprovechar la clave de ordenación cuando filtras por ellas:

SELECT
  toStartOfHour(ts) AS hour,
  count() AS pv,
  uniq(user_id) AS uniques
FROM pageviews
WHERE ts >= today() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

Intervalos: INTERVAL N SECOND/MINUTE/HOUR/DAY/WEEK/MONTH/QUARTER/YEAR.

Funciones de agregación

Además de las clásicas (count, sum, avg, min, max), ClickHouse añade una batería impresionante:

Conteo distinto: la familia uniq

uniqExact(x)     -- exacto, usa memoria proporcional a cardinalidades
uniq(x)          -- HyperLogLog adaptativo, muy rápido, error ~0.5%
uniqHLL12(x)     -- HLL con 12 bits de precisión
uniqCombined(x)  -- híbrido, bueno para ~1M valores distintos

Para dashboards y métricas, uniq es suficiente y mucho más rápido que uniqExact. Para conteos de facturación, usa uniqExact.

Cuantiles

quantile(0.95)(latency_ms)
quantiles(0.5, 0.9, 0.95, 0.99)(latency_ms)  -- devuelve tuple

quantileTDigest(0.99)(latency_ms)       -- más memoria, más preciso
quantileTiming(0.99)(latency_ms)        -- muy rápido para valores en ms
quantileExact(0.5)(latency_ms)          -- exacto, pero carga todo en memoria

Calcular p95 y p99 en ClickHouse es una línea. En PostgreSQL, ya sabes.

Funciones de "primer/último"

any(x)          -- un valor cualquiera del grupo (no determinista)
anyLast(x)      -- el último insertado
argMin(x, by)   -- el x cuyo by es mínimo
argMax(x, by)   -- el x cuyo by es máximo

argMax es oro puro. Ejemplo: último estado conocido por usuario:

SELECT
  user_id,
  argMax(status, ts) AS last_status,
  max(ts) AS last_seen
FROM events
GROUP BY user_id;

Combinators

Aquí viene lo que no tiene PostgreSQL. Puedes sufijar cualquier función de agregación para modificar su comportamiento:

countIf(status = 'error')             -- contar condicional, sin CASE
sumIf(cost, country = 'ES')
avgIf(duration, disposition = 'ANSWERED')

uniqArray(tags)                       -- unique sobre arrays aplanados
quantileMerge(quantile_state)         -- consolidar estados intermedios

groupArray(x)                         -- agrega valores en un array
groupUniqArray(x)                     -- array de valores únicos
topK(10)(path)                        -- los 10 más frecuentes, aproximado

Con -If te ahorras el CASE WHEN en todas partes. Es idiomático:

SELECT
  toStartOfHour(ts) AS hour,
  countIf(disposition = 'ANSWERED') AS answered,
  countIf(disposition = 'BUSY') AS busy,
  countIf(disposition = 'NO ANSWER') AS no_answer,
  avgIf(duration_ms, disposition = 'ANSWERED') AS avg_answered_ms
FROM cdrs
WHERE ts >= today() - INTERVAL 1 DAY
GROUP BY hour
ORDER BY hour;

Una sola pasada por los datos, múltiples agregaciones condicionales. El planner no tiene que hacer malabares.

Arrays: ciudadanos de primera

Los arrays en ClickHouse son idiomáticos, no un añadido. Hay docenas de funciones que operan sobre ellos:

[1, 2, 3, 4]                           -- literal
arrayMap(x -> x * 2, [1,2,3])          -- lambda, devuelve [2,4,6]
arrayFilter(x -> x > 10, arr)
arraySum(arr), arrayAvg(arr)
arrayJoin(arr)                         -- "unnest": una fila por elemento
arrayEnumerate(arr)                    -- [1, 2, 3, ...]
arrayDistinct(arr)
arrayIntersect(a, b), arrayDifference(a, b)
has(arr, 'valor'), hasAny(arr, ['a','b']), hasAll(arr, ['a','b'])
length(arr), empty(arr)

arrayJoin, el unnest explosivo

SELECT
  arrayJoin(tags) AS tag,
  count() AS posts
FROM posts
GROUP BY tag
ORDER BY posts DESC;

arrayJoin multiplica la fila por cada elemento del array. Si un post tiene 5 tags, genera 5 filas. Estándar para explotar datos semiestructurados.

ARRAY JOIN

Variante de JOIN que hace lo mismo que arrayJoin pero permite mantener columnas del array original alineadas:

SELECT path, code, codes
FROM (
  SELECT path, [200, 404, 500] AS codes
  FROM urls
)
ARRAY JOIN codes AS code;

Lambdas y funciones de orden superior

-- Posts con al menos un tag que empieza por 'data'
SELECT title
FROM posts
WHERE arrayExists(t -> startsWith(t, 'data'), tags);

-- Suma de valores positivos de un array
SELECT arraySum(x -> if(x > 0, x, 0), values) FROM metrics;

Window functions

Disponibles desde ClickHouse 21.8 y estables desde 22.3. Sintaxis estándar:

SELECT
  customer_id,
  ts,
  cost_eur,
  sum(cost_eur) OVER (
    PARTITION BY customer_id
    ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  row_number() OVER (PARTITION BY customer_id ORDER BY ts) AS rn,
  lag(cost_eur, 1) OVER (PARTITION BY customer_id ORDER BY ts) AS prev_cost
FROM cdrs
WHERE toYear(ts) = 2026;

Las funciones de ventana son más lentas en ClickHouse que las agregaciones puras. Úsalas cuando realmente las necesites. Para muchos casos, una combinación de groupArray + lambda es más eficiente.

Joins: útiles, pero con cautela

ClickHouse soporta INNER, LEFT, RIGHT, FULL, CROSS, ASOF y ANY joins. La gran diferencia con PostgreSQL:

Los joins se ejecutan en memoria.
La tabla de la derecha es la que se carga entera (hash join). Pon siempre la tabla pequeña a la derecha.
Para datasets enormes, ClickHouse ofrece partial_merge y full_sorting_merge como estrategias alternativas, pero suelen ser más lentas.

SELECT
  o.customer_id,
  c.name,
  sum(o.total) AS total
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
GROUP BY o.customer_id, c.name;

Para tablas de lookup pequeñas (países, categorías, tarifas), lo idiomático es un diccionario (Dictionary), no un join. Los diccionarios se cargan en memoria una sola vez y permiten hacer lookups con funciones como dictGet('tariffs', 'rate', customer_id). Es muchísimo más rápido.

ASOF JOIN

Joina por aproximación temporal. Útil para correlacionar eventos con el estado vigente en un momento dado:

SELECT
  q.ts,
  q.symbol,
  q.price,
  t.trade_price
FROM quotes AS q
ASOF LEFT JOIN trades AS t
  ON q.symbol = t.symbol AND q.ts >= t.ts;

Para cada fila de quotes, busca el último trades con ts ≤ al de la quote. Esto en PostgreSQL es un LATERAL JOIN horrible.

Sampling

ClickHouse permite muestreo determinista si lo declaras al crear la tabla:

ENGINE = MergeTree()
ORDER BY (cityHash64(user_id), ts)
SAMPLE BY cityHash64(user_id);

Luego en consultas:

SELECT count()
FROM events
SAMPLE 0.1                  -- 10% de los datos
WHERE ts >= today() - 7;

El muestreo es coherente: la misma consulta devuelve siempre el mismo subconjunto. Para métricas aproximadas sobre datos masivos, puede reducir tiempos de consulta 10×.

WITH FILL: rellenar huecos

Una joya que no vas a volver a querer perder:

SELECT
  toStartOfHour(ts) AS hour,
  count() AS pv
FROM pageviews
WHERE ts >= today() - 1
GROUP BY hour
ORDER BY hour WITH FILL STEP INTERVAL 1 HOUR;

Rellena automáticamente las horas sin datos con 0. Esencial para dashboards: sin esto, las series temporales tienen huecos visuales cuando hay inactividad.

EXPLAIN y análisis de consultas

Antes de optimizar, mide. ClickHouse tiene varios niveles de EXPLAIN:

EXPLAIN SYNTAX    SELECT ...;      -- cómo entiende la consulta
EXPLAIN PLAN      SELECT ...;      -- plan lógico
EXPLAIN PIPELINE  SELECT ...;      -- plan de ejecución paralelo
EXPLAIN ESTIMATE  SELECT ...;      -- filas y *parts* escaneados

Y para ver lo que realmente pasó:

SELECT
  query,
  query_duration_ms,
  read_rows,
  read_bytes,
  memory_usage,
  result_rows
FROM system.query_log
WHERE event_time >= now() - INTERVAL 10 MINUTE
  AND type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 20;

system.query_log es el mejor amigo cuando intentas averiguar por qué una consulta va más lenta de lo que debería.

Un ejemplo realista

Dashboard de tráfico de un blog, una sola consulta:

SELECT
  toStartOfDay(ts) AS day,
  count() AS views,
  uniq(user_id) AS visitors,
  countIf(referrer = 'direct') AS direct_views,
  quantile(0.95)(duration_ms) AS p95_ms,
  topK(5)(path) AS top_paths
FROM pageviews
WHERE ts >= today() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day WITH FILL STEP INTERVAL 1 DAY;

Sobre 100 millones de filas, esto devuelve en decenas de milisegundos si la tabla está bien diseñada.

Por dónde seguir

IV: materialized views, projections y TTL — cómo conseguir que una consulta que escanea 100M filas se responda desde 30 filas pre-agregadas.
V: producción, replicación y clusters — operar ClickHouse cuando el negocio depende de él.

¿Te perdiste los fundamentos? Vuelve a I: instalación y primeros pasos o a II: tipos de datos y MergeTree.

ClickHouse desde cero (III): consultas analíticas en profundidad