In der Welt der Datenanalyse sind rollierende Zeiträume ein mächtiges Werkzeug, um Trends zu erkennen, Anomalien aufzudecken und fundierte Geschäftsentscheidungen zu treffen. Ob es um Verkaufszahlen, Besucherstatistiken oder Finanztransaktionen geht – die Fähigkeit, Daten über ein gleitendes Fenster hinweg zu aggregieren, ist unerlässlich. Eine der häufigsten Anforderungen ist die summierte Menge für einen 7-Tage-Zeitraum. Wie aber implementiert man dies elegant und performant in SQL? Dieser Artikel führt Dich Schritt für Schritt zur perfekten Query.
Warum rollierende Summen so wichtig sind
Stell Dir vor, Du bist für die Analyse von Verkaufsdaten verantwortlich. Ein einfacher Tagesumsatz gibt Dir nur eine Momentaufnahme. Eine wöchentliche Summe, die aber immer an einem Sonntag beginnt und am Samstag endet, kann Verzerrungen aufweisen, wenn Feiertage oder besondere Ereignisse in der Mitte der Woche stattfinden. Hier kommen rollierende 7-Tage-Summen ins Spiel. Sie bieten eine viel flüssigere und repräsentativere Ansicht der Entwicklung, da sich das „Fenster” der Betrachtung jeden Tag um einen Tag verschiebt. Du siehst nicht nur den Umsatz einer festen Woche, sondern den Umsatz der letzten sieben Tage, *jeden Tag neu berechnet*. Das hilft, saisonale Schwankungen zu glätten und die zugrunde liegenden Trends klarer zu erkennen.
Das Problem: Eine konkrete Aufgabenstellung
Nehmen wir an, wir haben eine Tabelle namens umsatzdaten mit den Spalten datum (vom Typ DATE) und menge (vom Typ DECIMAL oder INTEGER), die den täglichen Umsatz darstellt. Unser Ziel ist es, für jedes Datum in dieser Tabelle die Summe der menge der letzten 7 Tage (einschließlich des aktuellen Tages) zu berechnen.
Beispiel der Tabelle umsatzdaten:
CREATE TABLE umsatzdaten (
datum DATE PRIMARY KEY,
menge DECIMAL(10, 2)
);
INSERT INTO umsatzdaten (datum, menge) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 120.00),
('2023-01-04', 200.00),
('2023-01-05', 130.00),
('2023-01-06', 180.00),
('2023-01-07', 220.00),
('2023-01-08', 190.00),
('2023-01-09', 250.00),
('2023-01-10', 160.00);
Für den 2023-01-07 sollte die Summe beispielsweise die Mengen vom 2023-01-01 bis 2023-01-07 umfassen.
Ansatz 1: Die einfache WHERE-Klausel (für einen fixen Zeitraum)
Bevor wir uns den komplexeren, dynamischen Lösungen widmen, werfen wir einen Blick auf die einfachste Methode, die allerdings nur für einen *festen* 7-Tage-Zeitraum funktioniert. Wenn Du beispielsweise nur die Summe für die erste Kalenderwoche des Jahres 2023 wissen möchtest, reicht ein einfacher SELECT-Statement mit einer WHERE-Klausel:
SELECT SUM(menge) AS summe_7_tage
FROM umsatzdaten
WHERE datum BETWEEN '2023-01-01' AND '2023-01-07';
Diese Abfrage ist korrekt für ihren Zweck, liefert aber nur einen Wert. Sie berechnet keine rollierende Summe für jeden Tag der Tabelle, was unser eigentliches Ziel ist.
Ansatz 2: Die Subquery oder Korrelierte Subquery (weniger effizient)
Eine Möglichkeit, für jeden Tag eine rollierende Summe zu berechnen, ist die Verwendung einer korrelierten Subquery. Dabei wird für jede Zeile der äußeren Abfrage eine separate innere Abfrage ausgeführt. Diese innere Abfrage summiert die Werte innerhalb des definierten 7-Tage-Fensters.
SELECT
t1.datum,
t1.menge,
(SELECT SUM(t2.menge)
FROM umsatzdaten t2
WHERE t2.datum BETWEEN DATE_SUB(t1.datum, INTERVAL 6 DAY) AND t1.datum
) AS summe_7_tage_korreliert
FROM
umsatzdaten t1
ORDER BY
t1.datum;
Erläuterung der Datum-Funktion pro Datenbank:
- MySQL:
DATE_SUB(datum, INTERVAL 6 DAY) - PostgreSQL:
datum - INTERVAL '6 days' - SQL Server:
DATEADD(day, -6, datum) - Oracle:
datum - 6
Vorteile: Relativ einfach zu verstehen, da es die Logik „für jeden Tag schau 6 Tage zurück” direkt abbildet.
Nachteile: Diese Methode ist bei großen Datenmengen extrem ineffizient. Für jede Zeile der Hauptabfrage wird die Subquery neu ausgeführt, was zu erheblichen Performance-Problemen führen kann (O(n^2) Komplexität). Für kleinere Datensätze mag sie funktionieren, aber für produktive Umgebungen mit Millionen von Zeilen ist sie ungeeignet.
Ansatz 3: Der Königsweg – Window Functions (Fensterfunktionen)
Die eleganteste, leistungsfähigste und meist empfohlene Lösung für rollierende Berechnungen in modernen SQL-Datenbanken sind Window Functions. Sie ermöglichen es, Berechnungen über eine Menge von Zeilen durchzuführen, die in einem „Fenster” zueinander in Beziehung stehen, ohne dabei Zeilen zu gruppieren und zu reduzieren (wie GROUP BY es tun würde).
Grundlagen der Window Functions für rollierende Summen
Die Kernidee ist die OVER()-Klausel. Innerhalb dieser Klausel definieren wir, wie das Fenster aussieht:
ORDER BY datum: Definiert die Reihenfolge, in der die Zeilen innerhalb des Fensters verarbeitet werden. Für eine Zeitreihenanalyse ist das Datum meist aufsteigend.ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Dies ist der entscheidende Teil. Er definiert das „Fenster”. Es bedeutet: „Beginne 6 Zeilen vor der aktuellen Zeile und ende mit der aktuellen Zeile selbst.” Da unsere Daten pro Tag vorliegen und nach Datum sortiert sind, entspricht dies genau einem 7-Tage-Fenster (6 vorherige + 1 aktuelle = 7).
SELECT
datum,
menge,
SUM(menge) OVER (ORDER BY datum ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS summe_7_tage_rollierend
FROM
umsatzdaten
ORDER BY
datum;
Erläuterung:
- Für
2023-01-01: Die Summe ist nur 100.00 (da es keine 6 vorherigen Tage gibt, summiert die Funktion nur die vorhandenen Zeilen im Fenster). - Für
2023-01-02: Die Summe ist 100.00 (01.01) + 150.00 (02.01) = 250.00. - …
- Für
2023-01-07: Die Summe ist 100.00 (01.01) + … + 220.00 (07.01) = 1200.00. - Für
2023-01-08: Die Summe ist 150.00 (02.01) + … + 190.00 (08.01) = 1290.00 (das Fenster verschiebt sich).
Vorteile:
- Performance: Datenbankmanagementsysteme (DBMS) sind stark darauf optimiert, Fensterfunktionen effizient zu verarbeiten. Sie benötigen oft nur einen einzigen Scan der Daten.
- Lesbarkeit: Die Syntax ist, sobald man sich daran gewöhnt hat, sehr ausdrucksstark und prägnant.
- Flexibilität: Fensterfunktionen können für viele Arten von rollierenden Berechnungen (Durchschnitte, Min/Max, Ränge) und komplexeren Analysen verwendet werden.
Umgang mit fehlenden Daten (Gaps)
Ein wichtiger Hinweis zu ROWS BETWEEN ...: Diese Klausel zählt *Zeilen*, nicht *Kalendertage*. Wenn an einem Tag keine Daten vorhanden sind (z.B. kein Umsatz am 2023-01-03), würde die Abfrage trotzdem 7 *Einträge* summieren, auch wenn diese Einträge einen längeren Kalenderzeitraum als 7 Tage umfassen könnten. Wenn es für Dich entscheidend ist, *immer* genau 7 Kalendertage zu betrachten, selbst wenn einzelne Tage fehlen, musst Du einen Zwischenschritt einlegen:
- Erzeuge eine vollständige Datumsreihe für den gewünschten Zeitraum.
- Verbinde Deine Umsatzdaten über einen
LEFT JOINmit dieser Datumsreihe, um auch die Tage mit fehlenden Umsätzen zu erfassen (wobeimengedannNULList). - Ersetze
NULL-Werte in dermenge-Spalte durch 0, bevor Du die Fensterfunktion anwendest.
Beispiel mit generierter Datumsreihe (PostgreSQL):
WITH datum_reihe AS (
SELECT generate_series('2023-01-01'::date, '2023-01-10'::date, '1 day'::interval) AS kalender_datum
),
umsatz_mit_luecken AS (
SELECT
dr.kalender_datum AS datum,
COALESCE(ud.menge, 0.00) AS menge_bereinigt -- Ersetze NULL durch 0
FROM
datum_reihe dr
LEFT JOIN
umsatzdaten ud ON dr.kalender_datum = ud.datum
)
SELECT
datum,
menge_bereinigt,
SUM(menge_bereinigt) OVER (ORDER BY datum ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS summe_7_tage_rollierend_mit_luecken
FROM
umsatz_mit_luecken
ORDER BY
datum;
Dies ist die robustere Lösung, wenn die Daten nicht garantiert lückenlos sind und die 7-Tage-Periode sich auf 7 Kalendertage beziehen soll, unabhängig davon, ob an jedem dieser Tage ein Eintrag vorhanden ist.
Anpassungen des Fensters
- Summe der letzten 7 Tage, *ausschließlich* des aktuellen Tages:
SUM(menge) OVER (ORDER BY datum ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)(Summiert die 7 Tage *vor* dem aktuellen Tag.)
- Summe der nächsten 7 Tage (einschließlich des aktuellen Tages):
SUM(menge) OVER (ORDER BY datum ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)
Ansatz 4: Self-Join mit Aggregation (für spezielle Fälle oder ältere SQL-Versionen)
Eine weitere Methode, die vor der weiten Verbreitung von Fensterfunktionen häufig verwendet wurde (und in einigen spezifischen Szenarien immer noch nützlich sein kann), ist der Self-Join mit anschließender Aggregation. Dabei wird die Tabelle mit sich selbst verbunden, basierend auf dem Datumsbereich:
SELECT
t1.datum,
SUM(t2.menge) AS summe_7_tage_self_join
FROM
umsatzdaten t1
JOIN
umsatzdaten t2 ON t2.datum BETWEEN DATE_SUB(t1.datum, INTERVAL 6 DAY) AND t1.datum
GROUP BY
t1.datum
ORDER BY
t1.datum;
Vorteile: Funktioniert auch in älteren SQL-Versionen, die keine Fensterfunktionen unterstützen. Kann in komplexeren Fällen, wo die Join-Bedingung komplizierter ist als ein einfaches Fenster, flexibler sein.
Nachteile:
- Performance: Ähnlich wie die korrelierte Subquery kann dies bei großen Datensätzen sehr ineffizient werden, da eine große Anzahl von Zeilen im Join erzeugt werden kann, die dann aggregiert werden müssen.
- Lesbarkeit: Für diesen Anwendungsfall ist die Fensterfunktion deutlich klarer und prägnanter.
Performance-Überlegungen und Best Practices
Indizierung
Unabhängig davon, welchen Ansatz Du wählst, ist die Indizierung Deiner Datum-Spalte (datum) absolut entscheidend für die Performance. Ein Index auf datum beschleunigt die ORDER BY-Klauseln (bei Fensterfunktionen und allgemeinen Sortierungen) und die WHERE-Bedingungen (bei Subqueries und Self-Joins). Für die umsatzdaten Tabelle wäre ein Index auf datum, vielleicht sogar ein kombinierter Index (datum, menge), sehr vorteilhaft.
CREATE INDEX idx_umsatzdaten_datum ON umsatzdaten (datum);
Datenvolumen
Bei sehr großen Datenmengen (Milliarden von Zeilen) können selbst optimierte Fensterfunktionen an ihre Grenzen stoßen. In solchen Fällen könnten fortgeschrittene Techniken wie materialisierte Sichten (Materialized Views) oder Aggregationen auf einer vorgelagerten Ebene (z.B. ETL-Prozesse, die tägliche oder wöchentliche Summen vorberechnen) notwendig sein.
Wahl der richtigen Methode
- Für die Berechnung von rollierenden Summen über feste Zeiträume (wie 7 Tage) sind Fensterfunktionen (
SUM(...) OVER (ORDER BY ... ROWS BETWEEN ... AND ...)) fast immer die beste Wahl in Bezug auf Performance und Lesbarkeit, vorausgesetzt Deine Datenbank unterstützt sie (was die meisten modernen relationalen Datenbanken tun). - Wenn Datenlücken berücksichtigt und *Kalendertage* präzise eingehalten werden müssen, kombiniere die Fensterfunktion mit einer vorherigen Erzeugung einer Datumsreihe und einem
LEFT JOIN. - Subqueries und Self-Joins sollten nur in Betracht gezogen werden, wenn Window Functions nicht verfügbar sind oder die Problemstellung durch einen Join wesentlich einfacher abzubilden ist.
Klarheit und Wartbarkeit
Gute SQL-Abfragen sind nicht nur schnell, sondern auch leicht zu verstehen und zu warten. Verwende aussagekräftige Spaltennamen, Aliase und Kommentare, um Deine Logik zu erklären. Teste Deine Abfragen immer mit repräsentativen Daten, um sicherzustellen, dass sie auch in Randfällen (z.B. am Anfang des Datensatzes, bei fehlenden Daten) korrekt funktionieren.
Fazit: Die perfekte Query ist nicht nur schnell, sondern auch verständlich
Die perfekte SQL-Query zur Berechnung einer summierten Menge über einen 7-Tage-Zeitraum nutzt in den allermeisten Fällen Window Functions. Sie sind leistungsstark, effizient und die moderne Art, solche Berechnungen direkt in der Datenbank durchzuführen. Mit der richtigen Indizierung und einem Verständnis für die Datenlücken in Deinen Zeitreihendaten, kannst Du robuste und performante Analysen erstellen.
Das Meistern dieser Techniken ist ein fundamentaler Schritt, um aus Rohdaten wertvolle Geschäftseinblicke zu gewinnen und eine datengesteuerte Entscheidungsfindung zu ermöglichen. Experimentiere mit den gezeigten Beispielen in Deiner eigenen Datenbank und entdecke die Mächtigkeit von SQL für die Zeitreihenanalyse!