Operações básicas - séries temporais - ClickHouse Documentation

O ClickHouse oferece vários métodos para trabalhar com dados de séries temporais, permitindo agregar, agrupar e analisar pontos de dados em diferentes períodos de tempo. Esta seção aborda as operações fundamentais mais usadas ao trabalhar com dados temporais. As operações mais comuns incluem agrupar dados por intervalos de tempo, lidar com lacunas em dados de séries temporais e calcular variações entre períodos. Essas operações podem ser realizadas com a sintaxe SQL padrão combinada com as funções de tempo nativas do ClickHouse. Vamos explorar os recursos de consulta para séries temporais do ClickHouse com o conjunto de dados Wikistat (dados de visualizações de páginas da Wikipedia):

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

Vamos preencher esta tabela com 1 bilhão de registros:

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

Agregação por bucket de tempo

A necessidade mais comum é agregar os dados com base em períodos, por exemplo, obter o total de acessos de cada dia:

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 │
└────────────┴──────────┘

Usamos a função toDate() aqui, que converte o horário especificado para um tipo de data. Como alternativa, podemos agrupar em intervalos de uma hora e filtrar pela data específica:

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 │
└─────────────────────┴────────┘

A função toStartOfHour() usada aqui converte o horário especificado para o início da hora. Você também pode agrupar por ano, trimestre, mês ou dia.

Intervalos personalizados de agrupamento

Podemos até agrupar por intervalos arbitrários, por exemplo, de 5 minutos, usando a função toStartOfInterval(). Digamos que queremos agrupar em intervalos de 4 horas. Podemos especificar o intervalo de agrupamento usando a 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;

Ou podemos usar a função 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;

De qualquer forma, obtemos os resultados a seguir:

┌────────────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 │
└─────────────────────┴─────────┘

Preenchendo grupos vazios

Em muitos casos, lidamos com dados esparsos, com alguns intervalos ausentes. Isso gera buckets vazios. Vamos considerar o exemplo a seguir, em que agrupamos os dados em intervalos de 1 hora. Isso produzirá as estatísticas a seguir, com algumas horas sem valores:

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 │
└─────────────────────┴───────────┘

O ClickHouse fornece o modificador WITH FILL para lidar com isso. Ele preencherá todas as horas sem dados com zeros, para que possamos entender melhor a distribuição ao longo do tempo:

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 │ <- valor novo
│ 2015-07-01 02:00:00 │         1 │
│ 2015-07-01 03:00:00 │         0 │ <- valor novo
│ 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 │ <- valor novo
│ 2015-07-01 11:00:00 │         0 │ <- valor novo
│ 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 │
└─────────────────────┴───────────┘

Janelas de tempo móveis

Às vezes, não queremos lidar com o início dos intervalos (como o início de um dia ou de uma hora), mas com intervalos de janela. Digamos que queremos entender o total de acessos em uma janela, não com base em dias, mas em um período de 24 horas com deslocamento a partir das 18h. Podemos usar a função date_diff() para calcular a diferença entre um horário de referência e o horário de cada registro. Nesse caso, a coluna day representará a diferença em dias (por exemplo, 1 dia atrás, 2 dias atrás 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 │
└─────┴───────────┘

​Agregação por bucket de tempo

​Intervalos personalizados de agrupamento

​Preenchendo grupos vazios

​Janelas de tempo móveis

Agregação por bucket de tempo

Intervalos personalizados de agrupamento

Preenchendo grupos vazios

Janelas de tempo móveis