Bases de datos columnares y series temporales

En resumen: Una base de datos columnar guarda cada columna por separado para que los informes sobre millones de filas lean rápido, y una base de series temporales guarda mediciones con marca de tiempo, como las de un sensor.

Problema

El informe quiere dos columnas y lee siete

La tabla sales de la lección anterior tiene siete columnas: pedido, día, ciudad, categoría, producto, cantidad e importe. El informe de ingresos por mes quiere dos de ellas, day y amount.

Una base de datos corriente guarda los datos por filas: todo sobre una venta está junto en el disco. Para leer dos columnas, lee las filas enteras y tira las otras cinco. Con mil millones de ventas son cientos de gigabytes, de los cuales hace falta poco más de una cuarta parte.

SQLbase de datos: tienda

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

▶ EjecutarRestaurar la consultaBase nueva

La idea

Cada columna por su cuenta

Una base de datos columnar (ClickHouse, BigQuery, Snowflake, DuckDB) guarda cada columna en un archivo aparte. El informe de ingresos abre solo los archivos de day y amount. Y una columna tiene juntos valores del mismo tipo (solo fechas, solo ciudades), así que se comprime mucho mejor que una fila donde están mezclados.

La compresión más sencilla es un tramo: en vez de «Sofía, Sofía, Sofía…» escribes «Sofía × 50». La columna city entera, ordenada por ciudad, son siete tramos:

SQLbase de datos: tienda

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT city, COUNT(*) AS repeated
FROM sales
GROUP BY city
ORDER BY city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT city, COUNT(*) AS repeated FROM sales GROUP BY city ORDER BY city;

▶ EjecutarRestaurar la consultaBase nueva

Solo funciona si los valores iguales están juntos. Aquí tienes cuántos tramos tiene la columna cuando las filas están en el orden de los pedidos, y cuántos cuando están ordenadas por ciudad:

SQLbase de datos: tienda

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ EjecutarRestaurar la consultaBase nueva

SQLbase de datos: tienda

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ EjecutarRestaurar la consultaBase nueva

De 128 valores quedan 39 tramos si las filas no están ordenadas, y 7 si lo están. Por eso en ClickHouse cada tabla se crea con ORDER BY. El orden de las columnas en él es el orden en el disco.

Más precisamente, para curiosos

LAG(city, 1, '-') da la ciudad de la fila anterior (y '-' para la primera fila), y cada fila en la que cambia la ciudad empieza un tramo nuevo. IS NOT en lugar de <> es porque NULL <> NULL no es verdadero, mientras que NULL IS NOT NULL es un «no» claro. Las bases de datos columnares reales usan también otros tipos de compresión: un diccionario (las ciudades pasan a ser los números 1–7), diferencias entre números vecinos y, encima, un algoritmo general (LZ4, ZSTD). Una compresión de 10 veces no es rara.

La idea

Cuándo columnar y cuándo no

Una base de datos columnar es excelente para informes: pocas columnas, muchas filas, sumas y grupos. Es mala para la caja. Un pedido nuevo significa una escritura en siete archivos separados, y un UPDATE de una fila suele significar reescribir todo un bloque de la columna. Por eso a las bases de datos columnares les gusta escribir en lotes grandes (miles de filas a la vez) y cambian pocas veces. Es la división entre OLTP y OLAP de la lección anterior, solo que en el disco.

Problema

La nevera de lácteos

La nevera con los lácteos tiene un sensor que registra la temperatura cada 15 minutos. Por encima de 8 °C el queso y la leche se estropean pronto, y la tienda tiene que saber cuándo pasó. Estos datos son una serie temporal: valores ordenados por tiempo, que solo se añaden y casi nunca se cambian. Dos días de mediciones:

SQLbase de datos: tienda

SELECT * FROM fridge ORDER BY at LIMIT 5;

SELECT * FROM fridge ORDER BY at LIMIT 5;

▶ EjecutarRestaurar la consultaBase nueva

SQLbase de datos: tienda

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

▶ EjecutarRestaurar la consultaBase nueva

Dos días de 96 mediciones deberían ser 192, pero son 184. Faltan ocho, y vas a ver dónde.

La idea

Reducir a horas

Nadie mira 35 000 puntos en un gráfico de un año. Las mediciones se agrupan por hora (o por día) con unos pocos números para cada una: la media, el valor más bajo y el más alto. Eso es downsampling (reducir la resolución). La hora son los primeros 13 caracteres del tiempo:

SQLbase de datos: tienda

SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 1) AS avg_temp,
       MAX(temp) AS max_temp
FROM fridge
WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00'
GROUP BY hour
ORDER BY hour;

SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 1) AS avg_temp, MAX(temp) AS max_temp FROM fridge WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00' GROUP BY hour ORDER BY hour;

▶ EjecutarRestaurar la consultaBase nueva

La media de las 14:00 es 8,9 °C, y la más alta es 9,7 °C. La puerta estuvo abierta.

Pruébalo

Cuándo suena la alarma

Muestra las horas en las que al menos una medición está por encima de 8 °C (las columnas hour y max_temp, la temperatura más alta de la hora), en el orden de la hora.

SQLbase de datos: tienda

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

Comprobar▶ Ejecutar

La idea

Una media móvil

Los sensores a veces dan un único valor erróneo, y la nevera la abre cada cliente durante segundos. Por eso la alarma suele mirar no una medición, sino la media de las últimas cuatro (la última hora). Se mueve con cada fila nueva: una función de ventana con ROWS BETWEEN 3 PRECEDING AND CURRENT ROW:

SQLbase de datos: tienda

SELECT *
FROM (
  SELECT at, temp,
         ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour
  FROM fridge
) AS t
WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15'
ORDER BY at;

SELECT * FROM ( SELECT at, temp, ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour FROM fridge ) AS t WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15' ORDER BY at;

▶ EjecutarRestaurar la consultaBase nueva

La media supera los 8 °C solo a las 14:45, cuando la puerta lleva una hora abierta. Una medición alta al azar la subiría en torno a 1 °C, pero no por encima de 8.

La función de ventana está en la consulta interior, y el período, fuera. WHERE se ejecuta antes que la ventana: si la condición del período está en la misma consulta, la media de las 13:30 no ve las 12:45, las 13:00 y las 13:15 y sale de una sola medición.

Pruébalo

El hueco

El sensor mide cada 15 minutos. Encuentra dónde perdió la conexión: la última medición antes del hueco y la primera después (las columnas last_before y first_after).

SQLbase de datos: tienda

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

Comprobar▶ Ejecutar

La idea

Un período de retención

Las mediciones en bruto crecen en 35 000 filas al año por sensor, y la tienda tiene cientos. Lo habitual es una tabla por hora, que se guarda mucho tiempo, y mediciones en bruto que se guardan poco (aquí, un día). La tabla por horas se hace como en el almacén de datos, con CREATE TABLE … AS SELECT:

SQLbase de datos: tienda

CREATE TABLE fridge_hourly AS
SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp,
       COUNT(*) AS readings
FROM fridge
GROUP BY hour;

SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

CREATE TABLE fridge_hourly AS SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp, COUNT(*) AS readings FROM fridge GROUP BY hour; SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

▶ EjecutarRestaurar la consultaBase nueva

Las horas 03:00 y 04:00 tampoco están en ella: no hay ni una sola medición de esas horas. El gráfico tiene que mostrar un espacio vacío, no unir las 02:00 y las 05:00 con una línea recta como si todo estuviera bien.

Ejercicio

Limpiar y un informe

1. La tabla por horas ya está hecha.

Borra las mediciones en bruto anteriores al 25 de octubre de 2026. La tabla fridge_hourly se queda entera.

SQLbase de datos: tienda

SELECT COUNT(*) FROM fridge;

SELECT COUNT(*) FROM fridge;

Comprobar▶ Ejecutar

2. Un informe del día.

Para cada día: la temperatura más baja, la más alta y la media (redondeada a 0.01) y el número de mediciones (las columnas day, min_temp, max_temp, avg_temp, readings), en el orden del día.

SQLbase de datos: tienda

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

Comprobar▶ Ejecutar

La idea

Bases de datos para series temporales

También hay bases de datos aparte para series temporales: TimescaleDB (una extensión de PostgreSQL que divide la tabla por tiempo por sí sola y reduce a horas por sí sola), InfluxDB y Prometheus (para métricas de servidores). Todas hacen lo mismo que en la lección: ordenar por tiempo, comprimir las columnas, mantener tablas reducidas y borrar los datos en bruto pasado un período. Las columnares ClickHouse y DuckDB también se las arreglan bien con ellas.

Más precisamente, para curiosos

En TimescaleDB una tabla por horas que se actualiza sola es CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', at) …, y el período de retención es SELECT add_retention_policy('fridge', INTERVAL '1 day'). SQLite no tiene nada parecido: la tabla por horas y el borrado los ejecuta el programa según un horario.

Test

Ponte a prueba

1. Un informe suma la columna amount de una tabla con 7 columnas y 100 millones de filas. ¿Cuántos de los datos lee una base de datos columnar?

Las 7 columnasSolo la primera filaSolo la columna amount (y las columnas del WHERE)

2. ¿Por qué las bases de datos columnares ordenan las filas (ORDER BY en un CREATE TABLE de ClickHouse)?

Porque no funcionan sin esoPara que se vea bonitoLos valores iguales quedan juntos y la columna se reduce a unos pocos tramos

3. El sensor mide cada 15 minutos, y el gráfico de un año tiene que ser rápido. ¿Qué se suele hacer?

Se mantiene una tabla por hora (o por día) con AVG, MIN y MAX, y las mediciones en bruto se borran pasado un períodoSe mide con menos frecuenciaEl gráfico lee todas las mediciones

4. La media móvil de la última hora está en una subconsulta, y el WHERE del período, fuera. ¿Por qué no está en la misma consulta?

Porque SQLite no lo permiteWHERE se ejecuta antes que la función de ventana, y las primeras filas del período perderían su hora anteriorNo hay diferencia

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: CREATE TABLE: crear una tabla · Claves naturales y desnormalización · Índices · OLTP y OLAP
• Continúa en: Cómo elegir una base de datos
• Ejercicios sobre el tema: Tipos de bases de datos