Réponse

La question du traitement des séries temporelles dans les bases de données relationnelles est apparue à la croisée de l'analyse et de la programmation classique avec SQL. Dans SQL-92, il n'existe pas de fonctions de fenêtre spéciales, donc il faut faire preuve de créativité avec des sous-requêtes pour calculer des métriques dynamiques (somme glissante, moyenne, etc.) et des conditions temporelles.

Problème — absence d'outils standard pour l'agrégation par fenêtre glissante, la recherche de valeurs précédentes/suivantes dans le temps, et la groupement efficace par intervalle de calendrier arbitraire (par exemple, calcul des indicateurs hebdomadaires/mensuels).

Solution :

En utilisant uniquement les moyens standard, des sous-requêtes de corrélation sont appliquées pour chaque ligne ou un groupement selon un critère calculé (par exemple, mois, semaine) :

Exemple de code :

-- Exemple de groupement par semaines sans fonctions de fenêtre
SELECT 
   YEAR(event_date) AS year,
   WEEK(event_date) AS week,
   SUM(value) AS total
FROM timeseries
GROUP BY YEAR(event_date), WEEK(event_date)
ORDER BY year, week;

-- Sous-requête corrélée pour trouver l'enregistrement précédent
SELECT t1.id, t1.event_date, t1.value,
       (
         SELECT t2.value 
           FROM timeseries t2 
          WHERE t2.event_date < t1.event_date
          ORDER BY t2.event_date DESC
          LIMIT 1
       ) as prev_value
FROM timeseries t1;

Caractéristiques principales :

Il est nécessaire de grouper et d'agréger explicitement par des fonctions temporelles (YEAR, MONTH, WEEK, etc.).
Pour obtenir des calculs glissants, il faut utiliser des sous-requêtes corrélées ou des tables temporaires.
Les performances sont affectées sur de grands volumes de données — la sous-requête pour chaque ligne ralentit rapidement l'exécution.

Questions pièges.

WEEK(event_date) détermine-t-il toujours la semaine calendaire de manière sans ambiguïté pour toutes les dates ?

Non — différentes SGBD (et même les paramètres d'un même SGBD) définissent la première semaine de l'année de manière différente (par exemple, ISO 8601 vs système américain), ce qui peut entraîner des résultats différents lors de l'agrégation. Il faut spécifier clairement le mode de fonctionnement de la fonction ou utiliser YEARWEEK.

SELECT YEARWEEK(event_date, 1)  -- 1 : la semaine ISO commence le lundi
FROM timeseries;

La sous-requête corrélée élimine-t-elle les doublons automatiquement lors de la recherche de la valeur précédente ?

Non, la sous-requête corrélée ne filtre pas les doublons par défaut. Si plusieurs événements existent dans la table à une même date, la sous-requête retournera le premier selon le tri, mais ignorera les autres.

Peut-on agréger par dates via GROUP BY sans tenir compte du temps ?

Oui, mais il faut explicitement exclure la partie temporelle, par exemple, en utilisant DATE(event_date) ou TRUNC(event_date) dans différents SGBD :

SELECT DATE(event_datetime), COUNT(*)
FROM events
GROUP BY DATE(event_datetime)

Erreurs typiques et anti-patterns

Utilisation de YEAR/MONTH/WEEK sans tenir compte de la locale et des normes de calendrier pour les périodes de rapport.
Création de sous-requêtes corrélées trop complexes pour les fenêtres glissantes — augmentation explosive du temps d'exécution sur de grands volumes de données.
Travaux avec des fonctions temporelles sans tenir compte du fuseau horaire.

Exemple de la vie réelle

Cas négatif

L'équipe a calculé l'analyse hebdomadaire en utilisant WEEK(date), sans régler les paramètres sur ISO-8601. En conséquence, les rapports pour la première semaine de janvier "se sont perdus" — une partie des événements remontait à décembre de l'année précédente selon la logique américaine. L'analyse ne concordait pas.

Avantages :

Mise en œuvre rapide de la décomposition des périodes.

Inconvénients :

Les données sont incorrectes, les rapports ne correspondent pas à la logique commerciale.

Cas positif

Un spécialiste a mis en œuvre YEARWEEK(date, 1) et une table de calendrier, améliorant considérablement la cohérence des rapports entre les pays et les départements commerciaux.