Antwort

Die Frage der Verarbeitung von Zeitreihen in relationalen Datenbanken entstand an der Schnittstelle zwischen Analytics und klassischer Programmierung mit SQL. In SQL-92 gibt es keine speziellen Fensterfunktionen, daher muss man sich mit Unterabfragen behelfen, um dynamische Metriken (gleitende Summen, Durchschnitte usw.) und zeitabhängige Bedingungen zu berechnen.

Problem — Mangel an standardisierten Werkzeugen zur Aggregation über gleitende Fenster, zum Finden des vorherigen/nächsten Wertes über Zeit und zur effizienten Gruppierung nach beliebigen Kalenderintervallen (z.B. Berechnung wöchentlicher/montatlicher Kennzahlen).

Lösung:

Unter Verwendung nur der Standardmittel werden Unterabfragen zur Korrelation für jede Zeile oder Gruppierungen nach berechneten Kriterien (z.B. Monat, Woche) verwendet:

Beispielcode:

-- Beispiel für Gruppierung nach Wochen ohne Fensterfunktionen
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;

-- Korrellierte Unterabfrage zur Suche nach dem vorherigen Eintrag
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;

Schlüsselmerkmale:

Es ist notwendig, explizit nach zeitlichen Funktionen (YEAR, MONTH, WEEK usw.) zu gruppieren und zu aggregieren.
Um gleitende Berechnungen zu erhalten, muss man korrelierte Unterabfragen oder temporäre Tabellen verwenden.
Die Leistung leidet bei großen Datensätzen — eine Unterabfrage für jede Zeile verlangsamt die Ausführung schnell.

Fragen mit Hintergedanken.

Definiert WEEK(event_date) immer eindeutig die Kalenderwoche für beliebige Daten?

Nein — verschiedene DBMS (und sogar die Parameter eines DBMS) definieren die erste Woche des Jahres unterschiedlich (z.B. ISO 8601 vs. amerikanisches System), was zu unterschiedlichen Ergebnissen bei der Aggregation führen kann. Es ist notwendig, den Betriebsmodus der Funktion explizit anzugeben oder YEARWEEK zu verwenden.

SELECT YEARWEEK(event_date, 1)  -- 1: ISO-Woche beginnt am Montag
FROM timeseries;

Entfernt die korrelierte Unterabfrage automatisch Duplikate bei der Suche nach dem vorherigen Wert?

Nein, die korrelierte Unterabfrage filtert Duplikate standardmäßig nicht. Wenn es in der Tabelle mehrere Ereignisse an einem Datum gibt, gibt die Unterabfrage das erste nach Sortierung zurück, ignoriert aber die anderen.

Kann man über GROUP BY Aggregationen nach Daten durchführen, ohne die Zeit zu berücksichtigen?

Ja, aber man muss den Zeitanteil explizit ablegen, z.B. mit DATE(event_date) oder TRUNC(event_date) in verschiedenen DBMS:

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

Typische Fehler und Anti-Patterns

Verwendung von YEAR/MONTH/WEEK ohne Berücksichtigung der Lokalisierung und Kalenderstandards für Berichtszeiträume
Erstellung von zu komplexen korrelierten Unterabfragen für gleitende Fenster — explosionsartiger Anstieg der Ausführungszeit bei großen Volumina
Arbeiten mit zeitlichen Funktionen ohne Berücksichtigung der Zeitzone

Beispiel aus dem Leben

Negativer Fall

Das Team hat die wöchentliche Analyse mit den Funktionen WEEK(date) durchgeführt, ohne die Parameter auf ISO-8601 einzustellen. Infolgedessen gingen die Berichte für die erste Woche im Januar „verloren“ – ein Teil der Ereignisse gehörte laut amerikanischer Logik zum Dezember des Vorjahres. Die Analytik stimmte nicht überein.

Vorteile:

Schnell implementierte Dekonstruktion der Perioden

Nachteile:

Daten sind ungenau, Berichte stimmen nicht mit der Geschäftslogik überein

Positiver Fall

Ein Spezialist implementierte YEARWEEK(date, 1) und eine Kalendertabelle, was die Konsistenz der Berichte zwischen Ländern und Geschäftsbereichen erheblich erhöhte.