Inicio Descubrimiento Tech Antiestrés
🇬🇧 English 🇪🇸 Español 🇩🇪 Deutsch 🇫🇷 Français 🇵🇱 Polski 🇨🇿 Česky 🇨🇳 中文 🇰🇷 한국어
Volver al inicio

Funciones de agregación de ClickHouse: uniqHLL12, quantileTDigest, topK

Visión general completa de las funciones de agregación de ClickHouse con ejemplos en una tabla de apuestas real. Cubre count/sum/avg estándar, especializadas para recuentos únicos (uniqExact, uniqHLL12, uniqCombined) con una tabla de selección por precisión y velocidad, cuantiles (quantile, quantileTDigest, quantileExact) para distribuciones, groupArray y sumas móviles (groupArrayMovingSum), elementos principales mediante topK, agregados condicionales mediante combinadores -If (sumIf, countIf, avgIf), tipo AggregateFunction y combinadores -State/-Merge para vistas materializadas, combinadores -OrDefault/-OrNull, runningAccumulate para métricas acumulativas. Incluye consultas listas para GGR diario (ingresos brutos de juego), análisis de retención de cohortes y retención móvil de 7 días. Incluye advertencias sobre errores típicos (count(DISTINCT), quantileExact en datos grandes).

By Sudonull Editorial Team • Published • Updated

Agregaciones de ClickHouse: desde count() hasta AggregateFunction con -State/-Merge
Advertisement 728x90

Funciones de Agregación en ClickHouse: Cómo Dejé de Temer a uniqHLL12 y quantileTDigest

En análisis de apuestas, necesitábamos contar jugadores únicos por hora. En PostgreSQL, escribía COUNT(DISTINCT user_id) y me iba a tomar un café. En ClickHouse, con 500 millones de filas, la misma consulta se ejecutaba en 30 segundos. Pero el negocio requería un panel que se actualizara cada 5 segundos.

Fue entonces cuando descubrí uniqHLL12() — conteo aproximado con un error del 1-2%, pero en 0.2 segundos. Cambiamos a él, y el panel despegó. El director no notó la diferencia en los números, pero sí notó la velocidad.

ClickHouse no solo proporciona matemáticas estándar, sino también docenas de agregaciones especializadas que PostgreSQL nunca soñó. A continuación, todo lo que uso en proyectos reales.

Google AdInline article slot

1. Agregados Estándar: Familiares pero Más Rápidos

-- Estadísticas generales de todas las apuestas
SELECT 
    count() AS total_apuestas,                    -- número de apuestas
    sum(monto) AS total_apostado,              -- monto total apostado
    avg(monto) AS promedio_apuesta,                   -- apuesta promedio
    min(created_at) AS hora_primera_apuesta,        -- primera apuesta
    max(created_at) AS hora_ultima_apuesta,         -- última apuesta
    max(monto) - min(monto) AS rango        -- rango
FROM apuestas.apuestas
WHERE created_at >= today() - 7;

Diferencia con PostgreSQL: count(*) y count() funcionan igual. Pero count(DISTINCT user_id) es lento — usa funciones especializadas.

2. Usuarios Únicos: Precisión vs. Velocidad

ClickHouse tiene tres enfoques para contar valores únicos:

-- Preciso pero lento (30 segundos en 500 millones de filas)
SELECT count(DISTINCT user_id) FROM apuestas.apuestas;

-- Preciso pero sin azúcar sintáctico
SELECT uniqExact(user_id) FROM apuestas.apuestas;

-- Aproximado, rápido (0.2 segundos, error 1-2%)
SELECT uniq(user_id) FROM apuestas.apuestas;

-- HyperLogLog con error controlado (usamos este)
SELECT uniqHLL12(user_id) FROM apuestas.apuestas;

-- Aún más rápido, pero mayor error
SELECT uniqCombined(user_id) FROM apuestas.apuestas;

Cuándo usar qué:

Google AdInline article slot
Función Error Velocidad Dónde lo aplico
uniqExact() 0% Lenta Informes fiscales, pagos exactos
uniqHLL12() 1-2% Muy rápida Paneles, tendencias, KPIs
uniq() 2-4% Rápida Analítica en tiempo real
uniqCombined() 5-8% Instantánea Análisis exploratorio, estimaciones

Ejemplo real de producción: para contar jugadores únicos por hora en un panel en vivo, usamos uniqHLL12(user_id). La precisión del 99% es aceptable, y el panel se actualiza cada 3 segundos en lugar de 30.

3. Cuantiles: Distribución de Apuestas Sin Histogramas

La pregunta "¿cuántos jugadores apostaron menos de 100 rublos, y cuántos más de 1000?" es sobre cuantiles.

-- Percentil 50 (mediana)
SELECT quantile(0.5)(monto) FROM apuestas.apuestas;

-- Percentil 90 (el 90% de las apuestas están por debajo de este monto)
SELECT quantile(0.9)(monto) FROM apuestas.apuestas;

-- Múltiples cuantiles a la vez
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(monto) FROM apuestas.apuestas;

-- Cuantil aproximado (10x más rápido)
SELECT quantileTDigest(0.9)(monto) FROM apuestas.apuestas;

-- Preciso pero lento (ordenamiento en memoria)
SELECT quantileExact(0.9)(monto) FROM apuestas.apuestas;

Donde me quemé: quantileExact() en 1 mil millones de filas consume toda la memoria. Cambiamos a quantileTDigest() — error del 0.5%, 100 MB de memoria en lugar de 8 GB.

Google AdInline article slot

Caso de uso real: determinar umbrales para detección de fraude. Si el 99% de las apuestas son menores a 50,000 rublos y un jugador apuesta 500,000 — enviar a revisión.

4. groupArray: Recopilar Valores en un Array

A veces no necesitas agregar sino preservar todos los valores.

-- Todas las apuestas de un usuario por día en un array
SELECT 
    user_id,
    toDate(created_at) AS dia,
    groupArray(monto) AS montos,
    groupArray(cuota) AS cuotas_list,
    arrayMap(x -> x * 2, montos) AS duplicado  -- trabajando con array
FROM apuestas.apuestas
WHERE created_at >= today() - 7
GROUP BY user_id, dia
LIMIT 10;

Caso avanzado: sumas y promedios móviles.

-- Suma móvil de las últimas 3 apuestas por usuario
SELECT 
    user_id,
    created_at,
    monto,
    groupArrayMovingSum(3)(monto) OVER (PARTITION BY user_id ORDER BY created_at) AS suma_movil
FROM apuestas.apuestas
WHERE user_id = 1001
ORDER BY created_at;

Cuándo es realmente necesario: analizar secuencias de apuestas — un bot coloca montos idénticos seguidos, un jugador real varía.

5. topK: No Necesitas Contar Valores Exactos

Pregunta: "¿Cuáles son los 5 deportes más populares?" GROUP BY deporte ORDER BY count() DESC LIMIT 5 funciona, pero en 1 mil millones de filas construye una tabla hash para un millón de valores únicos.

-- Top aproximado (rápido)
SELECT topK(5)(deporte) FROM apuestas.apuestas;

-- Resultado: ['fútbol', 'baloncesto', 'tenis', 'hockey', 'mma']

-- Preciso pero lento
SELECT deporte, count() AS cnt 
FROM apuestas.apuestas 
GROUP BY deporte 
ORDER BY cnt DESC 
LIMIT 5;

Diferencia de velocidad: en 10 mil millones de filas, topK se ejecuta en 0.5 segundos, GROUP BY exacto en 15 segundos. Para un panel que se actualiza automáticamente, la elección es obvia.

6. Combinadores -If: Agregación Condicional Sin Subconsultas

En lugar de SUM(CASE WHEN ...), escribe sumIf() — se lee mejor y se ejecuta más rápido.

-- Apuestas ganadoras y perdedoras en una fila
SELECT 
    user_id,
    countIf(resultado = 'ganada') AS ganadas,
    countIf(resultado = 'perdida') AS perdidas,
    sumIf(monto, resultado = 'ganada') AS monto_ganado,
    sumIf(monto, resultado = 'perdida') AS monto_perdido,
    avgIf(cuota, resultado = 'ganada') AS promedio_cuota_ganadora,
    -- Tasa de ganancia mediante condición
    round(ganadas / (ganadas + perdidas), 4) AS tasa_ganancia
FROM apuestas.apuestas
WHERE created_at >= today() - 7
GROUP BY user_id
HAVING ganadas + perdidas > 50
ORDER BY tasa_ganancia DESC
LIMIT 20;

Otras funciones -If: avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().

7. Tipo AggregateFunction y Combinadores -State/-Merge: Para Vistas Materializadas

La herramienta más poderosa de ClickHouse: puedes almacenar no datos sino estados de agregación INTERMEDIOS.

-- Tabla con estados de agregación
CREATE TABLE apuestas.resumen_diario
(
    dia Date,
    user_id UInt64,
    total_apuestas AggregateFunction(count, UInt64),
    total_monto AggregateFunction(sum, Decimal(18,2)),
    deportes_unicos AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (dia, user_id);

-- Insertar con -State
INSERT INTO apuestas.resumen_diario
SELECT 
    toDate(created_at) AS dia,
    user_id,
    countState() AS total_apuestas,
    sumState(monto) AS total_monto,
    uniqState(deporte) AS deportes_unicos
FROM apuestas.apuestas
GROUP BY dia, user_id;

-- Obtener resultado con -Merge
SELECT 
    dia,
    user_id,
    countMerge(total_apuestas) AS apuestas,
    sumMerge(total_monto) AS total_apostado,
    uniqMerge(deportes_unicos) AS cantidad_deportes_unicos
FROM apuestas.resumen_diario
GROUP BY dia, user_id;

Dónde lo uso: vistas materializadas para agregación por hora. En lugar de recalcular 2 mil millones de filas cada vez, almaceno estados y simplemente los fusiono.

8. Otros Combinadores: -OrDefault, -OrNull, -Array

-- -OrDefault: devuelve valor por defecto en lugar de NULL
SELECT avgOrDefault(monto, 0) FROM apuestas.apuestas WHERE 1=0;  -- 0, no NULL

-- -OrNull: devuelve NULL si no hay filas
SELECT avgOrNull(monto) FROM apuestas.apuestas WHERE 1=0;  -- NULL

-- -Array: agregación sobre elementos de array
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS aplanado;
-- aplanado: [1,2,3,4,5,6]

9. runningAccumulate: Sumas Acumuladas (Funciones de Ventana Mejoradas)

ClickHouse soporta funciones de ventana, pero runningAccumulate es una forma más antigua (y a veces más rápida).

-- Suma acumulada de apuestas por día
SELECT 
    toDate(created_at) AS dia,
    sum(monto) AS monto_diario,
    runningAccumulate(sum(monto)) OVER (ORDER BY dia) AS monto_acumulado
FROM apuestas.apuestas
WHERE created_at >= today() - 30
GROUP BY dia
ORDER BY dia;

Por qué a veces prefiero funciones de ventana: runningAccumulate requiere orden estricto y no soporta PARTITION BY. Ahora escribo sum(monto) OVER (ORDER BY dia).

10. Ejemplos Prácticos: GGR, Cohortes, Retención Móvil

Ejemplo 1: GGR Diario (Ingreso Bruto de Juego)

GGR = total apuestas - total pagos.

SELECT 
    toDate(created_at) AS dia,
    sum(monto) AS total_apostado,
    sumIf(monto * cuota, resultado = 'ganada') AS total_pagado,
    total_apostado - total_pagado AS ggr,
    round(ggr / total_apostado, 4) AS porcentaje_retencion
FROM apuestas.apuestas
GROUP BY dia
ORDER BY dia DESC
LIMIT 30;

Ejemplo 2: Análisis de Cohortes (Retención de Jugadores por Día)

WITH cohortes AS (
    SELECT 
        user_id,
        toDate(min(created_at)) AS dia_cohorte
    FROM apuestas.apuestas
    GROUP BY user_id
),
actividad_usuario AS (
    SELECT 
        b.user_id,
        c.dia_cohorte,
        toDate(b.created_at) AS dia_actividad,
        datediff('day', c.dia_cohorte, dia_actividad) AS numero_dia
    FROM apuestas.apuestas b
    JOIN cohortes c ON b.user_id = c.user_id
    WHERE numero_dia <= 30
)
SELECT 
    dia_cohorte,
    numero_dia,
    uniqHLL12(user_id) AS usuarios_activos
FROM actividad_usuario
GROUP BY dia_cohorte, numero_dia
ORDER BY dia_cohorte DESC, numero_dia;

Ejemplo 3: Retención Móvil de 7 Días

SELECT 
    toDate(created_at) AS dia,
    uniqHLL12(user_id) AS dau,
    -- Usuarios que también estuvieron activos hace 7 días
    uniqHLL12If(user_id, 
        created_at >= today() - 7 AND created_at < today() - 6
    ) AS usuarios_retenidos,
    round(usuarios_retenidos / uniqHLL12If(user_id, 
        created_at >= today() - 14 AND created_at < today() - 13
    ), 4) AS retencion_7d
FROM apuestas.apuestas
GROUP BY dia
ORDER BY dia DESC;

Errores Comunes al Trabajar con Agregaciones

Error 1: COUNT(DISTINCT col) en miles de millones de filas.
Solución: uniqHLL12(col) o uniqExact(col) si se necesita precisión.

Error 2: GROUP BY en una columna de alta cardinalidad (user_id) sin filtro.
Solución: siempre añade WHERE o HAVING con un límite.

Error 3: quantileExact() en datos grandes.
Solución: quantileTDigest() o quantile(0.9) sin Exact.

Error 4: Usar arrayJoin dentro de agregación sin entender.
Solución: recuerda que arrayJoin multiplica filas. Mejor desnormalizar datos.

Qué Sigue

Las agregaciones son el corazón del análisis. Próximo artículo — sobre funciones de ventana y pruebas estadísticas en ClickHouse.

← Anterior:
→ Siguiente: Configuración de ClickHouse: Cómo configuré producción y no me pegué un tiro en el pie

— Editorial Team

Advertisement 728x90

Leer después

Etiquetas

#clickhouse-aggregations #hyperloglog #probabilistic-data-structures #betting-analytics #sql-aggregates

Temas

Bases de datos Analítica

Preguntas frecuentes

¿Cuál es la diferencia entre uniqHLL12 y count(DISTINCT) en ClickHouse?

count(DISTINCT) da un resultado exacto pero es lento: 30 segundos en 500 millones de filas. uniqHLL12() es aproximado (error 1-2%) pero funciona en 0.2 segundos. Use uniqHLL12 para paneles y tendencias, uniqExact para informes financieros.

¿Qué función de cuantil usar en ClickHouse?

quantileExact es exacto pero consume memoria en datos grandes. quantileTDigest es aproximado (error 0.5%), memoria 100 MB en lugar de 8 GB. Para producción — quantileTDigest, para cálculos exactos — quantileExact en datos agregados.

¿Qué es el tipo AggregateFunction y por qué se necesitan -State/-Merge?

AggregateFunction almacena no los datos sino el estado intermedio de la agregación (por ejemplo, tabla hash para uniq). -State crea el estado al insertar, -Merge restaura el agregado. Se usa en vistas materializadas para actualizaciones incrementales — en lugar de recalcular 2 mil millones de filas cada minuto.

¿Cuándo usar topK en lugar de GROUP BY con ORDER BY LIMIT?

topK da un top aproximado en fracciones de segundo en 10 mil millones de filas. GROUP BY es exacto pero lento (15 segundos). Para paneles de actualización automática use topK. Para informes exactos donde el orden importa — GROUP BY.