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

*Tercera entrega de la serie **[ClickHouse desde cero a pro](/search?tag=clickhouse-desde-cero)**. Tiempo de lectura estimado: 12 minutos.*

Ya tienes ClickHouse funcionando (entrega [I](/post/clickhouse-desde-cero-i-instalacion-y-primeros-pasos)) y sabes diseñar tablas con tipos adecuados y `MergeTree` bien pensado (entrega [II](/post/clickhouse-desde-cero-ii-tipos-de-datos-y-mergetree)). 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](/post/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:

```sql
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:

```sql
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

```sql
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

```sql
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"

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
[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

```sql
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:

```sql
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

```sql
-- 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:

```sql
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.

```sql
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:

```sql
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:

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

Luego en consultas:

```sql
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:

```sql
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`:

```sql
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ó:

```sql
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:

```sql
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](/post/clickhouse-desde-cero-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](/post/clickhouse-desde-cero-v-produccion-replicacion-y-clusters)** — operar ClickHouse cuando el negocio depende de él.

¿Te perdiste los fundamentos? Vuelve a [I: instalación y primeros pasos](/post/clickhouse-desde-cero-i-instalacion-y-primeros-pasos) o a [II: tipos de datos y MergeTree](/post/clickhouse-desde-cero-ii-tipos-de-datos-y-mergetree).