Zeitreihen-Aggregation in SQL: Buckets bilden und Lücken füllen

1. Warum Zeitreihen-Aggregation ein eigenes Problem ist

Bei der klassischen Zeitreihen-Aggregation gruppiert man Ereignisse, Bestellungen oder Messwerte über eine Zeitspalte, um Tages-, Wochen- oder Monatswerte zu erhalten. Das klingt nach einem einfachen GROUP BY, unterscheidet sich aber in einem entscheidenden Punkt von normaler Aggregation: Die Zeitachse hat eine feste, erwartete Struktur, die in den Rohdaten oft gar nicht vorhanden ist. Ein Reporting-Dashboard erwartet für jeden Tag eines Monats eine Zeile, selbst wenn an einzelnen Tagen keine einzige Bestellung einging.

Genau hier entsteht das strukturelle Problem: Ein reines GROUP BY über eine Datumsspalte erzeugt ausschließlich Zeilen für Perioden, in denen tatsächlich Daten vorhanden sind. Fehlende Tage, Wochen oder Monate fallen im Ergebnis komplett weg, statt mit einem Wert von null zu erscheinen. Für ein Liniendiagramm im Reporting bedeutet das falsch interpolierte Lücken, für eine Vorjahresvergleichsauswertung bedeutet es schlicht fehlende Vergleichspunkte.

Dieser Artikel zeigt systematisch, wie sich Zeitreihen-Aggregation in SQL sauber umsetzen lässt: von der Bildung fester Zeitfenster über das Auffüllen fehlender Perioden bis zu Vergleichszeiträumen und Performance-Aspekten bei großen Zeitreihen-Tabellen. Alle Beispiele funktionieren mit kleinen Anpassungen sowohl in PostgreSQL als auch in MySQL und SQL Server.

2. Buckets bilden mit DATE_TRUNC und DATE_FORMAT

Der erste Schritt jeder Zeitreihen-Aggregation ist die Bildung von Zeitfenstern, sogenannten Buckets, aus einem Zeitstempel. In PostgreSQL übernimmt das DATE_TRUNC, das einen Zeitstempel auf die gewünschte Präzision abrundet, etwa auf den Tagesbeginn, den Wochenbeginn oder den Monatsersten. In MySQL erreicht man dasselbe Ergebnis meist über DATE_FORMAT in Kombination mit STR_TO_DATE, weil MySQL kein direktes Äquivalent zu DATE_TRUNC anbietet. In SQL Server übernimmt DATETRUNC ab Version 2022 dieselbe Aufgabe.

Wichtig für eine korrekte Zeitreihen-Aggregation ist, dass alle Zeitstempel eines Buckets tatsächlich denselben abgerundeten Wert erhalten, unabhängig von Uhrzeit oder Zeitzone der einzelnen Ereignisse. Wer Zeitzonen ignoriert, riskiert, dass ein Ereignis um 23:50 Uhr Ortszeit im falschen Tages-Bucket landet, sobald die Datenbank intern in UTC rechnet. Bei international genutzten Systemen sollte die Zeitzonenkonvertierung deshalb vor der Bucket-Bildung erfolgen, nicht danach.

-- Time series aggregation: build daily buckets in PostgreSQL
SELECT
    date_trunc('day', created_at) AS day_bucket,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2026-06-01'
GROUP BY date_trunc('day', created_at)
ORDER BY day_bucket;

-- Equivalent bucket formation in MySQL 8+
SELECT
    DATE(created_at) AS day_bucket,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2026-06-01'
GROUP BY DATE(created_at)
ORDER BY day_bucket;

3. Das Lückenproblem: warum GROUP BY fehlende Tage verschweigt

Sobald der erste Report mit Buckets fertig ist, zeigt sich das eigentliche Problem der Zeitreihen-Aggregation: An Tagen ohne Bestellungen existiert in der Quelltabelle keine einzige Zeile, also erzeugt GROUP BY für diesen Tag auch keine Ausgabezeile. Ein Report über dreißig Tage kann dadurch plötzlich nur zweiundzwanzig Zeilen liefern, ohne dass ein Fehler auftritt. Das ist aus Sicht der Datenbank korrektes Verhalten, aus Sicht des Reportings jedoch fast immer falsch.

Die Konsequenzen reichen von harmlos bis kritisch. Ein Liniendiagramm zieht die fehlenden Punkte optisch einfach gerade, was den Eindruck kontinuierlicher Umsätze erweckt, obwohl an diesem Tag schlicht nichts passiert ist. Eine Kennzahl wie die durchschnittliche tägliche Bestellmenge über den Monat wird verfälscht, weil Tage mit null Bestellungen aus der Durchschnittsberechnung faktisch herausfallen, statt mit null in die Rechnung einzugehen. Für belastbares Reporting ist es deshalb Pflicht, jede erwartete Periode explizit im Ergebnis zu haben, auch mit dem Wert null.

4. Kalendertabellen: die robuste Lösung für lückenlose Reports

Die in der Praxis robusteste Lösung für lückenlose Zeitreihen-Aggregation ist eine dedizierte Kalendertabelle. Dabei handelt es sich um eine einfache Tabelle mit einer Zeile pro Kalendertag über einen ausreichend langen Zeitraum, oft mehrere Jahrzehnte im Voraus befüllt. Zusätzliche Spalten für Wochentag, Kalenderwoche, Monat, Quartal und Feiertagsflag machen die Tabelle zur zentralen Referenz für sämtliche zeitbasierten Auswertungen im Unternehmen, nicht nur für ein einzelnes Reporting.

Der eigentliche Trick liegt in einem LEFT JOIN von der Kalendertabelle auf die aggregierten Rohdaten, statt umgekehrt. Weil die Kalendertabelle links steht, bleibt jede Kalenderzeile im Ergebnis erhalten, selbst wenn die rechte Seite für diesen Tag keine passende Zeile liefert. COALESCE wandelt die dabei entstehenden NULL-Werte in saubere Nullen um. Diese Technik ist datenbankunabhängig, funktioniert identisch in PostgreSQL, MySQL und SQL Server und lässt sich problemlos mit weiteren Dimensionen wie Filialen oder Produktkategorien per CROSS JOIN kombinieren.

-- Time series aggregation with a calendar table: no gaps in the output
SELECT
    cal.calendar_date,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.revenue, 0) AS revenue
FROM calendar cal
LEFT JOIN (
    SELECT
        DATE(created_at) AS day_bucket,
        COUNT(*) AS order_count,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE(created_at)
) o ON o.day_bucket = cal.calendar_date
WHERE cal.calendar_date BETWEEN '2026-06-01' AND '2026-06-30'
ORDER BY cal.calendar_date;

5. generate_series und rekursive CTEs als Alternative

Wer keine dauerhafte Kalendertabelle pflegen möchte, kann eine Zeitreihe auch ad hoc erzeugen. PostgreSQL bietet dafür die Funktion generate_series, die zwischen einem Start- und einem Endzeitpunkt in einem festen Intervall Zeitstempel erzeugt, ganz ohne Hilfstabelle. Das Ergebnis lässt sich exakt wie eine Kalendertabelle per LEFT JOIN mit den aggregierten Rohdaten verbinden, mit dem Vorteil, dass sich der Zeitraum flexibel als Parameter der Abfrage übergeben lässt, statt vorab in einer Tabelle gepflegt zu sein.

In Datenbanksystemen ohne generate_series, etwa MySQL oder SQL Server, übernimmt eine rekursive Common Table Expression dieselbe Aufgabe. Ausgehend von einem Startdatum wird in jedem rekursiven Schritt ein Tag addiert, bis das Enddatum erreicht ist. Diese Technik ist etwas umständlicher zu schreiben, liefert aber funktional dasselbe Ergebnis wie generate_series und ist damit die portable Variante für Systeme ohne native Serienfunktion. Für sehr lange Zeiträume empfiehlt sich trotzdem die Kalendertabelle, weil rekursive CTEs bei tausenden Iterationen spürbar mehr Zeit benötigen.

-- PostgreSQL: ad hoc time series with generate_series, no helper table needed
SELECT
    d.day_bucket,
    COALESCE(o.order_count, 0) AS order_count
FROM generate_series(
    '2026-06-01'::date, '2026-06-30'::date, '1 day'
) AS d(day_bucket)
LEFT JOIN (
    SELECT DATE(created_at) AS day_bucket, COUNT(*) AS order_count
    FROM orders GROUP BY DATE(created_at)
) o ON o.day_bucket = d.day_bucket
ORDER BY d.day_bucket;

-- Portable alternative: recursive CTE for databases without generate_series
WITH RECURSIVE date_range AS (
    SELECT CAST('2026-06-01' AS DATE) AS day_bucket
    UNION ALL
    SELECT DATE_ADD(day_bucket, INTERVAL 1 DAY)
    FROM date_range
    WHERE day_bucket < '2026-06-30'
)
SELECT day_bucket FROM date_range;

6. Mehrere Granularitäten: Tag, Woche, Monat, Quartal

Reporting-Dashboards benötigen selten nur eine einzige Granularität. Dieselbe Zeitreihen-Aggregation soll häufig auf Tages, Wochen, Monats und Quartalsebene gleichzeitig verfügbar sein, ohne für jede Ebene eine separate Abfrage zu schreiben. Der elegante Weg ist, DATE_TRUNC einmal mit der Zielgranularität als Parameter zu verwenden und dieselbe Basisabfrage für alle Ebenen wiederzuverwenden, statt den Aggregationscode für jede Ebene zu duplizieren.

Ein üblicher Ansatz für Dashboards mit umschaltbarer Granularität ist, die Rohdaten zunächst auf Tagesebene vorzuaggregieren und aus dieser bereits verdichteten Zwischenschicht die gröberen Ebenen per weiterem GROUP BY abzuleiten. Das reduziert die Datenmenge, über die für Wochen, Monats und Quartalsauswertungen erneut gruppiert werden muss, drastisch und macht das Umschalten zwischen Granularitäten im Frontend spürbar schneller, weil nicht jedes Mal die vollständige Rohdatentabelle gescannt werden muss.

-- Multiple granularities from one pre-aggregated daily layer
WITH daily AS (
    SELECT DATE(created_at) AS day_bucket, SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE(created_at)
)
SELECT
    date_trunc('month', day_bucket) AS month_bucket,
    SUM(revenue) AS monthly_revenue,
    date_trunc('quarter', day_bucket) AS quarter_bucket
FROM daily
GROUP BY date_trunc('month', day_bucket), date_trunc('quarter', day_bucket)
ORDER BY month_bucket;

7. Vergleichszeiträume mit LAG: Vorjahr und Vorwoche

Ein zentraler Bestandteil von Reporting ist der Vergleich mit vorherigen Perioden. Statt zwei separate Abfragen für den aktuellen und den vorherigen Zeitraum zu schreiben und die Ergebnisse in der Anwendungsschicht zusammenzuführen, löst die Window Function LAG diese Aufgabe direkt in einer einzigen Zeitreihen-Aggregation. LAG greift auf den Wert einer vorherigen Zeile innerhalb derselben, nach Zeit sortierten Ergebnismenge zu, ohne dass ein Self-Join notwendig wäre.

Für einen Year-over-Year-Vergleich reicht ein LAG mit dem Offset zwölf auf monatlich aggregierten Daten, für einen Week-over-Week-Vergleich genügt ein Offset von eins auf wöchentlich aggregierten Daten. Aus dem aktuellen und dem vorherigen Wert lässt sich die prozentuale Veränderung direkt in derselben Abfrage berechnen, was für Wachstumskennzahlen in Dashboards praktisch immer benötigt wird. Diese Technik funktioniert nur zuverlässig, wenn die Zeitreihe vorher wie in den Abschnitten vier und fünf beschrieben lückenlos aufgefüllt wurde, sonst verschiebt eine fehlende Periode sämtliche LAG-Offsets.

8. Performance: Indizierung und Pre-Aggregation

Große Zeitreihen-Tabellen mit Millionen von Ereignissen stellen eigene Performance-Anforderungen an Zeitreihen-Aggregation. Ein Index auf der Zeitstempelspalte ist die Grundvoraussetzung, reicht bei funktionsbasierter Filterung wie WHERE DATE(created_at) = ... aber oft nicht aus, weil viele Datenbanken einen normalen Index bei einer Funktion auf der indizierten Spalte nicht mehr nutzen können. Ein funktionsbasierter Index auf genau diesem Ausdruck, oder alternativ ein Bereichsfilter mit BETWEEN auf den rohen Zeitstempel, umgeht dieses Problem zuverlässig.

Bei sehr großen Tabellen und wiederkehrenden Dashboards lohnt sich zusätzlich eine echte Pre-Aggregation: Statt bei jedem Seitenaufruf über Millionen Rohzeilen zu gruppieren, wird ein separates, regelmäßig aktualisiertes Rollup mit den Tageswerten gepflegt, etwa als materialisierte Sicht oder als per Cronjob befüllte Tabelle. Dashboards lesen dann nur noch aus diesem bereits verdichteten Rollup, was die Antwortzeit von Sekunden auf Millisekunden reduzieren kann, ohne die zugrunde liegende Zeitreihen-Aggregation inhaltlich zu verändern.

9. Ansätze für Zeitreihen-Aggregation im Vergleich

Die vorgestellten Techniken lösen dasselbe Grundproblem auf unterschiedliche Weise, mit klaren Trade-offs zwischen Wartungsaufwand, Flexibilität und Performance. Die folgende Übersicht ordnet die vier wichtigsten Ansätze für Zeitreihen-Aggregation nach ihrem typischen Einsatzgebiet ein.

Wer Zeitreihen-Aggregation von Anfang an mit Kalendertabelle und expliziter Lückenfüllung plant, spart sich später aufwendige Korrekturen an Dashboards, die scheinbar korrekte, in Wahrheit aber lückenhafte Durchschnittswerte anzeigen. Diese Investition zahlt sich besonders bei Kennzahlen aus, die über längere Zeiträume verglichen werden, etwa Jahresumsätze oder Kundenwachstum.

10. Zusammenfassung

Saubere Zeitreihen-Aggregation unterscheidet sich von normaler Aggregation dadurch, dass die Zeitachse eine feste, erwartete Struktur besitzt, die in den Rohdaten oft fehlt. DATE_TRUNC bildet die Buckets, eine Kalendertabelle oder generate_series füllt fehlende Perioden mit dem Wert null auf, und LAG berechnet Vergleichszeiträume wie Vorjahr oder Vorwoche direkt in derselben Abfrage. Wer diese Bausteine kombiniert, erhält Reports, die tatsächlich jede erwartete Periode zeigen, statt Lücken stillschweigend zu verschweigen.

Bei großen Zeitreihen-Tabellen kommt die Performance-Dimension hinzu: funktionsbasierte Indizes und eine vorab berechnete Pre-Aggregation reduzieren die Antwortzeit von Dashboards erheblich, ohne die inhaltliche Korrektheit der Zeitreihen-Aggregation zu beeinträchtigen. Die vorgestellten Techniken sind größtenteils datenbankübergreifend anwendbar und lassen sich mit geringem Aufwand von PostgreSQL auf MySQL oder SQL Server übertragen.

11. FAQ: Zeitreihen-Aggregation in SQL