SummingMergeTree y AggregatingMergeTree en ClickHouse

SummingMergeTree y AggregatingMergeTree: Agregación Incremental sin Dolor

1. Por qué se necesitan motores de agregación — El problema de los cálculos sobre datos enormes

Volvamos a nuestro casino online. Cada día, los jugadores hacen millones de apuestas. El dueño del panel necesita ver: cuántas apuestas hizo cada usuario por día y por qué monto total.

En una base de datos normal (PostgreSQL, MySQL), escribirías:

SELECT user_id, date, COUNT(*), SUM(amount)
FROM bets
GROUP BY user_id, date

En una tabla con 100 millones de filas, esa consulta tardaría... bueno, ya te imaginas — mucho tiempo. Muchísimo. Porque la base de datos tiene que leer TODAS las filas, ordenarlas o aplicar hash, y luego calcular los agregados.

ClickHouse es más rápido, claro, pero tampoco es magia. Cuantos más datos, más tarda GROUP BY. Y si hay muchos informes y se necesitan "ya mismo" — el rendimiento se vuelve un problema.

Idea: ¿Y si precalculamos los agregados y los almacenamos? De modo que la consulta "¿cuántas apuestas hizo user_id=123 ayer?" sea solo un SELECT de una fila, no una agregación completa.

Para esto, ClickHouse tiene dos motores de tabla especiales: SummingMergeTree y AggregatingMergeTree. Ellos hacen el trabajo pesado por ti — en segundo plano, durante las fusiones de partes de datos.

Analogía de la vida real: Imagina que llevas el registro de ventas en una tienda. Cada venta es un recibo. Si el dueño pide un informe "¿cuánto vendimos hoy?", podrías revisar todos los recibos cada vez. O podrías tener un cuaderno donde anotas el total al final del día: "hoy 150 ventas por 5000 rublos." SummingMergeTree es como tener ese cuaderno automáticamente.

2. SummingMergeTree — Sumador Automático

Cómo funciona

SummingMergeTree es un motor que, durante las fusiones de partes de datos (en segundo plano), suma valores numéricos para filas con la misma clave de ordenamiento (ORDER BY).

Reglas de suma:

• Todas las columnas numéricas (tipos: UInt*, Int*, Float*, Decimal*) se suman automáticamente.
• Otras columnas (cadenas, fechas, arreglos) se toman de la primera fila encontrada — esto es importante recordarlo, puede no ser lo que esperas.
• Si una columna no es numérica pero quieres agregarla de alguna manera — SummingMergeTree no es adecuado, necesitas AggregatingMergeTree.

Por qué "Summing": porque múltiples filas con la misma clave se colapsan en una, y los números en ella son la suma de los números de las filas originales.

CREATE TABLE — Desglosado

-- Crear una tabla para estadísticas diarias por usuario
CREATE TABLE daily_stats
(
    date         Date,                -- Día de la estadística
    user_id      UInt64,              -- ID del jugador
    bets_count   UInt64,              -- Número de apuestas por día (se sumará)
    total_amount Decimal(18,2)       -- Monto total de apuestas (se sumará)
)
ENGINE = SummingMergeTree()           -- Motor para suma automática
ORDER BY (date, user_id)              -- Clave de agrupación: por fecha y usuario

Qué es importante aquí:

• ENGINE = SummingMergeTree() — puedes especificar columnas a sumar entre paréntesis: SummingMergeTree(bets_count, total_amount). Si no se especifica, se suman todas las columnas numéricas (excepto las de ORDER BY, cuyos valores determinan la unicidad).

• ORDER BY (date, user_id) — estas columnas determinan qué filas se fusionarán en una. Es decir, todas las filas con la misma fecha y el mismo user_id se colapsarán en una fila durante la fusión, donde bets_count y total_amount serán sumas.

¿Qué pasa si ORDER BY es demasiado amplio? Si incluyes, por ejemplo, bets_count, entonces cada apuesta única (con un conteo diferente) seguirá siendo una fila separada. No ocurrirá ninguna suma porque las claves son diferentes. Escollo #1 (volveremos a ello).

Cómo insertar datos

Inserta eventos en bruto (cada fila es una apuesta):

-- Insertar tres apuestas para el usuario 123 el 2025-06-01
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- una apuesta por 100 rublos
    ('2025-06-01', 123, 1, 250.00),   -- segunda apuesta por 250 rublos
    ('2025-06-01', 456, 1, 50.00);    -- otro usuario

-- Puedes insertar datos NO agregados — el motor se encargará

Después de una fusión en segundo plano (puede tardar desde segundos hasta horas), las filas con date='2025-06-01' y user_id=123 se fusionarán en una: ('2025-06-01', 123, 2, 350.00).

Lectura sin GROUP BY — Magia y sus límites

La idea es que después de que ocurra la fusión, puedes leer datos sin agregación:

-- Si la fusión ya ocurrió, esta consulta devuelve una fila por usuario por día
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

Pero hay un problema. Entre fusiones, los datos pueden residir en diferentes partes con claves duplicadas. Por lo tanto, en la práctica, aún escribes con SUM:

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
WHERE date = '2025-06-01'
GROUP BY date, user_id;

¿Por qué funciona esto? Porque incluso si los datos no se han fusionado, SUM sumará todo correctamente. Y si se han fusionado, tienes una fila por grupo, y SUM simplemente devuelve su valor. La consulta sigue leyendo datos, pero ahora hay MENOS (filas agregadas en lugar de filas en bruto).

Analogía: SummingMergeTree es como un asistente que pega previamente recibos idénticos. Pero tú aún preguntas "muestra el total de cada día". Si los recibos ya están pegados, el total coincide con el número en una fila. Si no, igual obtienes el total correcto. Lo principal es que lees no millones de recibos, sino miles de resúmenes.

3. El problema: Entre fusiones necesitas SUM en SELECT

Este es un punto clave que a menudo se malinterpreta.

Enfoque ingenuo (incorrecto):

-- Pensando que después de la fusión los datos ya están agregados, un principiante escribe:
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- Y obtiene múltiples filas para un mismo usuario (si la fusión no ha ocurrido)

Enfoque correcto (seguro):

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
GROUP BY date, user_id;

¿Por qué?

1. Siempre obtienes el resultado correcto — tanto antes como después de la fusión.
2. El tamaño de los datos sigue siendo menor que en la tabla de apuestas en bruto.
3. ClickHouse optimiza bien estas consultas.

¿Cuándo puedes omitir SUM? Solo si estás absolutamente seguro de que los datos necesarios ya se han fusionado. Por ejemplo, después de un OPTIMIZE TABLE daily_stats forzado (pero esto es una operación costosa, no lo hagas para cualquier cosa).

4. AggregatingMergeTree — Cuando la simple suma no es suficiente

SummingMergeTree solo puede sumar números. Pero ¿qué pasa si necesitas:

• Contar usuarios únicos (no sumar)?
• Encontrar el máximo o mínimo?
• Calcular el promedio?
• Usar algoritmos aproximados como uniq para contar valores únicos?

Para esto existe AggregatingMergeTree. Almacena no solo valores, sino estados de funciones de agregación — datos intermedios especiales que permiten obtener el resultado final más tarde.

Analogía: SummingMergeTree almacena solo la suma final. Pero AggregatingMergeTree almacena no solo la suma sino también un contador (para luego calcular el promedio), o una tabla hash de valores únicos (para luego decir cuántos había). Es como la diferencia entre "tengo el total" y "tengo un cuaderno donde están registrados todos los datos, pero en forma comprimida".

CREATE TABLE con AggregateFunction

-- Crear una tabla para estadísticas agregadas del panel
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- Hora del evento
    sport_type     String,                                 -- Tipo de deporte (fútbol, baloncesto...)
    total_bets     AggregateFunction(sum, UInt64),         -- Suma del conteo de apuestas
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- Suma de dinero
    unique_users   AggregateFunction(uniq, UInt64),        -- Jugadores únicos (aproximado)
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- Tamaño promedio de apuesta
    max_bet        AggregateFunction(max, Decimal(18,2))   -- Apuesta máxima
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

Desglosando lo desconocido:

• AggregateFunction(sum, UInt64) — un tipo de columna que almacena el estado de la función de agregación sum para datos de tipo UInt64. No es un número, sino una estructura interna de ClickHouse.
• ¿Por qué no simplemente UInt64? Porque para algunas funciones (uniq, avg), necesitas almacenar más datos que solo el resultado final. avg almacena tanto la suma como el conteo. uniq almacena una tabla hash.
• Cuando se fusionan dos filas con el mismo ORDER BY (misma hora y tipo de deporte) — los estados de las funciones de agregación se combinan. Para sum, es simplemente sumar totales intermedios. Para uniq, es fusionar dos tablas hash de valores únicos.

Insertar datos mediante INSERT SELECT con funciones State

No puedes insertar un valor normal en una columna AggregateFunction. Necesitas usar funciones especiales *State que crean un estado a partir de un valor en bruto.

-- Insertar datos agregados desde la tabla de apuestas en bruto
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- Redondear la hora
    sport_type,
    sumState(bets_count) AS total_bets,                    -- Estado de suma
    sumState(amount) AS total_amount,                      -- Estado de suma de dinero
    uniqState(user_id) AS unique_users,                    -- Estado para únicos
    avgState(amount) AS avg_bet_amount,                    -- Estado para promedio
    maxState(amount) AS max_bet                            -- Estado para máximo
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

¿Qué está pasando aquí?

• toStartOfHour(event_time) — una función de ClickHouse que trunca la hora a la hora: 2025-06-01 12:34:56 → 2025-06-01 12:00:00.
• sumState(amount) — en lugar de SUM(amount), escribes sumState(amount). El resultado es un estado de la función de agregación, tipo AggregateFunction(sum, ...).
• GROUP BY es obligatorio en la consulta de inserción. Porque estás agregando datos de la tabla en bruto en grupos (hora+deporte), y luego insertando cada grupo como una fila en dashboard_hourly.

Lectura mediante funciones Merge

Para leer los datos, usa funciones *Merge:

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- De estado → número
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- Usuarios únicos
    avgMerge(avg_bet_amount) AS avg_bet_amount,
    maxMerge(max_bet) AS max_bet
FROM dashboard_hourly
WHERE event_hour >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;   -- Aún se necesita agrupación (si los datos no se han fusionado)

¿Por qué GROUP BY de nuevo? Misma razón que con SummingMergeTree: entre fusiones, puede haber múltiples filas con el mismo ORDER BY. GROUP BY con *Merge da el resultado correcto en cualquier estado.

5. Patrón de uso con Vistas Materializadas

La forma más potente de usar AggregatingMergeTree es en combinación con una Vista Materializada. Insertas datos en bruto en una tabla normal, y la vista los agrega automáticamente y los almacena en la tabla de agregación.

Analogía: Es como configurar una cinta transportadora: los recibos en bruto van a una caja, y un clasificador automático cada minuto recoge los totales diarios y los pone en otra caja. Los analistas solo miran la segunda caja — rápido y sin GROUP BY sobre la marcha.

Ejemplo completo: Estadísticas por hora para el panel del operador

Paso 1: Tabla en bruto — aquí se insertarán los eventos (apuestas)

CREATE TABLE raw_bets
(
    event_time DateTime,
    sport_type String,
    user_id UInt64,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY event_time;

Paso 2: Tabla de agregación — aquí se almacenarán las estadísticas listas

CREATE TABLE bets_hourly_agg
(
    hour DateTime,
    sport_type String,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    avg_bet AggregateFunction(avg, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_type);

Paso 3: Vista Materializada — el puente entre ellas

CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
    toStartOfHour(event_time) AS hour,
    sport_type,
    sumState(1) AS total_bets,                    -- Cada fila es una apuesta
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;

¿Qué sucede ahora?

1. Insertas filas en raw_bets con INSERT normal.
2. ClickHouse automáticamente (casi instantáneamente) las procesa a través de la vista materializada.
3. La vista agrega datos solo del lote insertado e inserta los resultados en bets_hourly_agg.
4. En bets_hourly_agg, pueden acumularse temporalmente múltiples filas con el mismo (hour, sport_type) — pero se fusionarán durante las fusiones en segundo plano.

Lectura para el panel:

SELECT
    hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,
    avgMerge(avg_bet) AS avg_bet
FROM bets_hourly_agg
WHERE hour >= today() - 7
GROUP BY hour, sport_type;

Esta consulta leerá solo datos agregados, que ocupan miles de veces menos espacio que las apuestas en bruto.

6. Ejemplo real: Panel de eventos deportivos

Imagina que eres un operador de casa de apuestas. En el panel, necesitas mostrar:

• Para cada partido (fútbol, Champions League, "Real" vs "Bayern")
• Cuántas apuestas se hicieron en los últimos 5 minutos
• Monto total de todas las apuestas
• Número de jugadores únicos
• Apuesta promedio

Datos en bruto: 5000 apuestas por segundo. Almacenar todo y agregar desde cero cada vez es una locura.

Solución:

-- Tabla para agregados por partido con intervalos de 5 minutos
CREATE TABLE match_stats_5min
(
    match_id String,
    interval_5min DateTime,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    max_bet AggregateFunction(max, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (match_id, interval_5min);

-- Vista Materializada
CREATE MATERIALIZED VIEW match_stats_mv TO match_stats_5min AS
SELECT
    match_id,
    toStartOfFiveMinute(event_time) AS interval_5min,
    sumState(1) AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    maxState(amount) AS max_bet
FROM raw_bets
GROUP BY match_id, interval_5min;

Ahora el panel consulta match_stats_5min — y obtiene respuestas en milisegundos en lugar de segundos.

7. Cuándo NO usar SummingMergeTree y AggregatingMergeTree

Cuándo SummingMergeTree es adecuado:

• Solo necesitas sumar valores numéricos.
• Te parece bien usar GROUP BY con SUM entre fusiones.
• La clave de agrupación no tiene una cardinalidad demasiado alta (p. ej., no mil millones de usuarios únicos — aunque eso está bien, solo más datos).

Cuándo SummingMergeTree NO es adecuado:

• Necesitas contar usuarios únicos (uniq, count(DISTINCT)) — solo funciona AggregatingMergeTree.
• Necesitas otros agregados: avg, min, max — nuevamente solo AggregatingMergeTree.
• Esperas que los datos siempre estén en un estado ya agregado — no funciona así.
• Tus datos se actualizan (no solo se insertan) — estos motores no son para semántica de actualización.

Cuándo AggregatingMergeTree es adecuado:

• Necesitas diferentes tipos de agregaciones (sumas, únicos, promedios, máximos).
• Estás dispuesto a escribir INSERT con *State y SELECT con *Merge.
• Usas vistas materializadas para agregación automática.
• El volumen de datos en bruto es enorme y los agregados son órdenes de magnitud más pequeños.

Cuándo AggregatingMergeTree NO es adecuado:

• No estás listo para explicar al equipo qué es AggregateFunction y cómo trabajar con ello. La curva de aprendizaje es más alta.
• Necesitas unicidad exacta, no aproximada (uniq es una estructura probabilística, error ~2%). Para exacto, usa groupBitmap o cuenta en otro sistema.
• El volumen de datos es pequeño (millones de filas) — un GROUP BY normal es más simple.
• Cambias con frecuencia el esquema de agregación (agregas nuevas métricas) — recrear la vista materializada es tedioso.

8. Comparación con MergeTree normal + GROUP BY

Característica	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
Velocidad de inserción	Máxima	Alta	Media (debido al estado)
Velocidad de lectura (rango grande)	Baja (lee todo)	Alta (lee agregados)	Alta
Velocidad de lectura (punto)	Media	Alta	Alta
Espacio de almacenamiento	Máximo	Mínimo (agregados)	Un poco más (estados)
Complejidad del código	Baja	Baja (solo una tabla)	Alta (*State, *Merge)
Flexibilidad de agregación	Cualquiera	Solo sumas	Cualquiera (mediante AggregateFunction)

9. Escollos comunes

Escollo #1: ORDER BY no incluye suficientes campos

-- MALO: solo fecha, sin user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- Durante la fusión, TODAS las filas de un día se colapsarán en una
-- Pierdes el detalle a nivel de usuario

Correcto: Incluye en ORDER BY todos los campos por los que quieras agregar.

Escollo #2: Olvidaste GROUP BY en SELECT

-- MALO: sin GROUP BY, incluso si los datos no se han fusionado
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- Si hay dos filas con la misma fecha pero diferente user_id — obtendrás un error
-- ClickHouse no sabe qué user_id mostrar

Correcto: Siempre agrupa por los mismos campos que en ORDER BY.

Escollo #3: uniq no estocástico

uniq en ClickHouse es una función probabilística. Error ~2-3%. Si necesitas unicidad exacta, usa uniqExact o groupBitmap.

Escollo #4: Actualizar datos antiguos

SummingMergeTree y AggregatingMergeTree no toleran bien las actualizaciones. Si necesitas corregir una apuesta de ayer, es más fácil insertar una nueva fila con el signo opuesto (mediante CollapsingMergeTree).

10. Qué sigue

Ahora que dominas los motores de agregación, los siguientes temas:

• Cómo elegir el motor adecuado para tu tarea — comparación de todos los motores *MergeTree.
• Vistas Materializadas en detalle — cómo depurar, cómo actualizar el esquema.
• Ajuste de fusiones en segundo plano — para que los agregados se colapsen más rápido.

Conclusión: SummingMergeTree y AggregatingMergeTree son herramientas para quienes no quieren que su panel se retrase con terabytes de datos. Requieren un poco más de comprensión al principio, pero se amortizan muchas veces bajo cargas de trabajo reales. La regla principal: usa siempre GROUP BY y funciones de agregación al leer — así estarás seguro antes y después de las fusiones.

---

← Anterior: ReplacingMergeTree: Cómo Vencer los Duplicados en ClickHouse Sin Dolor
→ Siguiente: CollapsingMergeTree: Cómo Actualizar Agregados Sin UPDATE en ClickHouse

— Editorial Team