Operaciones básicas - series temporales - ClickHouse Documentation

ClickHouse ofrece varios métodos para trabajar con series temporales, que permiten agregar, agrupar y analizar puntos de datos en distintos períodos de tiempo. Esta sección abarca las operaciones fundamentales que se usan habitualmente al trabajar con datos temporales. Entre las operaciones más comunes se incluyen agrupar datos por intervalos de tiempo, gestionar huecos en las series temporales y calcular cambios entre distintos períodos de tiempo. Estas operaciones pueden realizarse con sintaxis SQL estándar combinada con las funciones temporales integradas de ClickHouse. Vamos a explorar las capacidades de consulta para series temporales de ClickHouse con el conjunto de datos Wikistat (datos de vistas de páginas de Wikipedia):

CREATE TABLE wikistat
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time);

Vamos a llenar esta tabla con 1.000 millones de registros:

INSERT INTO wikistat 
SELECT *
FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') 
LIMIT 1e9;

Agregación por intervalos de tiempo

Lo más habitual es agregar los datos por períodos; por ejemplo, obtener la cantidad total de hits de cada día:

SELECT
    toDate(time) AS date,
    sum(hits) AS hits
FROM wikistat
GROUP BY ALL
ORDER BY date ASC
LIMIT 5;

┌───────date─┬─────hits─┐
│ 2015-05-01 │ 25524369 │
│ 2015-05-02 │ 25608105 │
│ 2015-05-03 │ 28567101 │
│ 2015-05-04 │ 29229944 │
│ 2015-05-05 │ 29383573 │
└────────────┴──────────┘

Aquí hemos usado la función toDate(), que convierte la hora especificada en un tipo de fecha. Como alternativa, podemos agrupar en intervalos de una hora y filtrar por una fecha concreta:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits) AS hits    
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY hour ASC
LIMIT 5;

┌────────────────hour─┬───hits─┐
│ 2015-07-01 00:00:00 │ 656676 │
│ 2015-07-01 01:00:00 │ 768837 │
│ 2015-07-01 02:00:00 │ 862311 │
│ 2015-07-01 03:00:00 │ 829261 │
│ 2015-07-01 04:00:00 │ 749365 │
└─────────────────────┴────────┘

La función toStartOfHour() que se utiliza aquí convierte la hora indicada al inicio de la hora. También puedes agrupar por año, trimestre, mes o día.

Intervalos de agrupación personalizados

Incluso podemos agrupar por intervalos arbitrarios, por ejemplo, de 5 minutos, usando la función toStartOfInterval(). Supongamos que queremos agrupar por intervalos de 4 horas. Podemos especificar el intervalo de agrupación mediante la cláusula INTERVAL:

SELECT
    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;

O bien, podemos usar la función toIntervalHour()

SELECT
    toStartOfInterval(time, toIntervalHour(4)) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;

En cualquier caso, obtenemos los siguientes resultados:

┌────────────interval─┬────hits─┐
│ 2015-07-01 00:00:00 │ 3117085 │
│ 2015-07-01 04:00:00 │ 2928396 │
│ 2015-07-01 08:00:00 │ 2679775 │
│ 2015-07-01 12:00:00 │ 2461324 │
│ 2015-07-01 16:00:00 │ 2823199 │
│ 2015-07-01 20:00:00 │ 2984758 │
└─────────────────────┴─────────┘

Rellenar grupos vacíos

En muchos casos trabajamos con datos dispersos y con algunos intervalos ausentes. Esto da lugar a intervalos vacíos. Veamos el siguiente ejemplo, en el que agrupamos los datos en intervalos de 1 hora. Esto generará las siguientes estadísticas, con valores faltantes en algunas horas:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC;

┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │ <- valores ausentes
│ 2015-07-01 02:00:00 │         1 │ <- valores ausentes
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │ <- valores ausentes
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘

ClickHouse proporciona el modificador WITH FILL para solucionar esto. Esto rellenará con ceros todas las horas vacías, para que podamos entender mejor la distribución a lo largo del tiempo:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC WITH FILL STEP toIntervalHour(1);

┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │
│ 2015-07-01 01:00:00 │         0 │ <- nuevo valor
│ 2015-07-01 02:00:00 │         1 │
│ 2015-07-01 03:00:00 │         0 │ <- nuevo valor
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │
│ 2015-07-01 10:00:00 │         0 │ <- nuevo valor
│ 2015-07-01 11:00:00 │         0 │ <- nuevo valor
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘

Ventanas de tiempo deslizantes

A veces, no queremos trabajar con el inicio de los intervalos (como el comienzo de un día o de una hora), sino con ventanas de tiempo. Supongamos que queremos conocer el total de hits de una ventana, no en función de los días, sino de un período de 24 horas desplazado desde las 6 p. m. Podemos usar la función date_diff() para calcular la diferencia entre una hora de referencia y la hora de cada registro. En este caso, la columna day representará la diferencia en días (p. ej., hace 1 día, hace 2 días, etc.):

SELECT    
    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,
    sum(hits),
FROM wikistat
GROUP BY ALL
ORDER BY day ASC
LIMIT 5;

┌─day─┬─sum(hits)─┐
│   0 │  25524369 │
│   1 │  25608105 │
│   2 │  28567101 │
│   3 │  29229944 │
│   4 │  29383573 │
└─────┴───────────┘

​Agregación por intervalos de tiempo

​Intervalos de agrupación personalizados

​Rellenar grupos vacíos

​Ventanas de tiempo deslizantes

Agregación por intervalos de tiempo

Intervalos de agrupación personalizados

Rellenar grupos vacíos

Ventanas de tiempo deslizantes