Opérations de base - séries temporelles

ClickHouse propose plusieurs méthodes pour travailler avec des séries temporelles, ce qui vous permet d’agréger, de regrouper et d’analyser des points de données sur différentes périodes. Cette section présente les opérations fondamentales couramment utilisées avec des données temporelles. Parmi les opérations courantes figurent le regroupement des données par intervalles de temps, la gestion des trous dans les séries temporelles et le calcul des variations entre différentes périodes. Ces opérations peuvent être effectuées à l’aide de la syntaxe SQL standard, combinée aux fonctions temporelles intégrées de ClickHouse. Nous allons explorer les capacités de requêtage sur les séries temporelles dans ClickHouse avec le jeu de données Wikistat (données de pages vues de Wikipedia) :

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

Remplissons cette table avec 1 milliard d’enregistrements :

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

Agrégation par tranche de temps

Le besoin le plus courant consiste à agréger les données par période, par exemple pour obtenir le nombre total de hits pour chaque jour :

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

Nous avons utilisé ici la fonction toDate(), qui convertit l’heure spécifiée en type Date. Sinon, nous pouvons regrouper par heure et filtrer sur une date précise :

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 fonction toStartOfHour() utilisée ici ramène l’heure indiquée au début de l’heure. Vous pouvez également regrouper par année, trimestre, mois ou jour.

Intervalles de regroupement personnalisés

Nous pouvons même regrouper selon des intervalles arbitraires, par exemple 5 minutes, à l’aide de la fonction toStartOfInterval(). Supposons que nous voulions regrouper selon des intervalles de 4 heures. Nous pouvons spécifier l’intervalle de regroupement à l’aide de la clause 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 bien, nous pouvons utiliser la fonction 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;

Dans les deux cas, nous obtenons les résultats suivants :

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

Remplissage des groupes vides

Dans de nombreux cas, nous traitons des données clairsemées avec certains intervalles absents. Cela se traduit par des groupes vides. Prenons l’exemple suivant, dans lequel nous regroupons les données par intervalles d’une heure. Cela produira les statistiques suivantes, avec des valeurs manquantes pour certaines heures :

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 │ <- missing values
│ 2015-07-01 02:00:00 │         1 │ <- missing values
│ 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 │ <- missing values
│ 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 fournit le modificateur WITH FILL pour remédier à ce problème. Cela permet de remplir toutes les heures sans données avec des zéros, afin de mieux comprendre la distribution dans le temps :

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

Fenêtres temporelles glissantes

Parfois, nous ne voulons pas nous baser sur le début des intervalles (comme le début d’une journée ou d’une heure), mais sur des fenêtres temporelles. Supposons que nous voulions connaître le nombre total de hits pour une fenêtre, non pas par journée, mais sur une période de 24 heures décalée à partir de 18 h. Nous pouvons utiliser la fonction date_diff() pour calculer la différence entre une heure de référence et l’heure de chaque enregistrement. Dans ce cas, la colonne day représentera l’écart en jours (par exemple, il y a 1 jour, il y a 2 jours, 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 │
└─────┴───────────┘

​Agrégation par tranche de temps

​Intervalles de regroupement personnalisés

​Remplissage des groupes vides

​Fenêtres temporelles glissantes

Agrégation par tranche de temps

Intervalles de regroupement personnalisés

Remplissage des groupes vides

Fenêtres temporelles glissantes