Verschachtelte Aggregation: Aggregate über Aggregate berechnen

1. Was verschachtelte Aggregation bedeutet

Verschachtelte Aggregation, manchmal auch Aggregat zweiter Ordnung genannt, berechnet eine Kennzahl nicht direkt aus den einzelnen Zeilen einer Tabelle, sondern aus einer bereits aggregierten Zwischenebene. Ein klassisches Beispiel: der durchschnittliche Tagesumsatz eines Shops. Dafür muss zunächst pro Tag die Summe aller Bestellungen berechnet werden, und erst danach der Durchschnitt über diese täglichen Summen. Das ist konzeptionell etwas anderes als ein einfacher Durchschnitt über alle Bestellzeilen, weil Tage mit vielen kleinen Bestellungen sonst überproportional ins Gewicht fallen würden.

Viele Entwickler versuchen intuitiv, verschachtelte Aggregation direkt mit AVG(SUM(betrag)) in einer einzigen SELECT-Anweisung umzusetzen, stoßen dabei aber auf eine Fehlermeldung, weil SQL das direkte Verschachteln von zwei Aggregatfunktionen ohne Zwischenschritt nicht erlaubt. Die korrekte Lösung führt über eine Zwischenaggregation, meist mit einer Subquery oder einer Common Table Expression, die zunächst die erste Aggregationsebene berechnet, bevor die äußere Abfrage die zweite Aggregatfunktion darauf anwendet.

Dieser Artikel erklärt, warum das direkte Verschachteln technisch verboten ist, zeigt Subqueries und CTEs als saubere Lösungen für verschachtelte Aggregation, grenzt das Konzept von Window Functions ab und behandelt typische Fallstricke wie leere Zwischenaggregate und Rundungsfehler bei mehrstufiger Berechnung.

2. Warum SQL Aggregatfunktionen nicht direkt verschachteln lässt

Der Grund, warum verschachtelte Aggregation nicht direkt über AVG(SUM(spalte)) in derselben Abfrageebene funktioniert, liegt in der Ausführungsreihenfolge von SQL-Abfragen. Aggregatfunktionen arbeiten auf einer Menge von Zeilen innerhalb einer Gruppe, und das Ergebnis einer Aggregatfunktion ist genau ein Wert pro Gruppe. Eine zweite Aggregatfunktion, die direkt darauf angewendet wird, hätte nur einen einzigen Wert zur Verfügung, über den nichts mehr zu aggregieren wäre, weshalb die meisten Datenbanksysteme diese Konstruktion mit einer Fehlermeldung ablehnen.

Das eigentliche Ziel bei verschachtelter Aggregation ist aber, über mehrere Gruppenwerte hinweg erneut zu aggregieren, nicht über einen einzelnen bereits reduzierten Wert. Um AVG(SUM(betrag)) sinnvoll auszuwerten, muss die Datenbank zuerst pro Tag (oder pro einer anderen Zwischengruppe) SUM(betrag) berechnen und diese Zwischenwerte als eigene Zeilen bereitstellen, bevor AVG auf diese Menge von Zwischenwerten angewendet werden kann. Genau diese zwei getrennten Schritte müssen in SQL explizit als zwei Abfrageebenen formuliert werden.

-- This fails in virtually every SQL database: direct nesting is not allowed
SELECT AVG(SUM(total_amount)) AS avg_daily_revenue
FROM orders
GROUP BY DATE(order_date);
-- ERROR: aggregate function calls cannot be nested

3. Die Lösung: Subquery als Zwischenaggregation

Die klassische Lösung für verschachtelte Aggregation nutzt eine Subquery in der FROM-Klausel, die zunächst die erste Aggregationsebene berechnet und als eigenständige Zwischentabelle bereitstellt. Die äußere Abfrage wendet dann die zweite Aggregatfunktion auf das Ergebnis dieser Subquery an, ohne dass eine direkte Verschachtelung nötig wäre. Diese Technik wird manchmal auch als derived table bezeichnet, weil die innere Abfrage eine abgeleitete, temporäre Tabelle erzeugt, die nur für die Dauer der äußeren Abfrage existiert.

Der entscheidende Vorteil dieses Ansatzes bei verschachtelter Aggregation liegt darin, dass jede Aggregationsebene ihre eigene, unabhängige GROUP BY-Klausel besitzt. Die innere Abfrage gruppiert nach Tag und berechnet die Tagessumme, die äußere Abfrage hat keine eigene GROUP BY-Klausel mehr, weil sie über die gesamte Ergebnismenge der Subquery aggregiert. Dieses Muster lässt sich beliebig oft wiederholen, sofern jede neue Ebene eine weitere Subquery um die vorherige herum legt.

-- Nested aggregation via a subquery: average of daily sums
SELECT AVG(daily_total) AS avg_daily_revenue
FROM (
    SELECT DATE(order_date) AS order_day, SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY DATE(order_date)
) AS daily_sums;

-- Result: a single row with the average revenue per day

4. CTEs für lesbare, mehrstufige Aggregation

Eine Common Table Expression, kurz CTE, ist bei verschachtelter Aggregation oft die lesbarere Alternative zu einer Subquery in der FROM-Klausel, besonders sobald mehrere Aggregationsebenen oder zusätzliche Berechnungsschritte dazwischenliegen. Mit der WITH-Klausel wird die erste Aggregationsebene benannt und dadurch für die äußere Abfrage klar von der zweiten Ebene getrennt, was den Code deutlich verständlicher macht als tief verschachtelte Subqueries in der FROM-Klausel.

Der funktionale Unterschied zwischen CTE und Subquery ist bei den meisten modernen Datenbanksystemen gering, weil der Query-Optimizer beide Formen in der Regel zu einem ähnlichen Ausführungsplan kompiliert. Der Vorteil der CTE liegt fast ausschließlich in der Lesbarkeit und Wartbarkeit, was bei komplexer verschachtelter Aggregation mit mehreren Zwischenschritten den entscheidenden Unterschied zwischen einer nachvollziehbaren und einer schwer zu debuggenden Abfrage ausmachen kann.

-- Nested aggregation via a CTE: median-like spread of daily order counts
WITH daily_stats AS (
    SELECT
        DATE(order_date) AS order_day,
        COUNT(*)          AS order_count,
        SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY DATE(order_date)
)
SELECT
    AVG(order_count)  AS avg_orders_per_day,
    MAX(order_count)  AS busiest_day_orders,
    AVG(daily_total)  AS avg_daily_revenue,
    STDDEV(daily_total) AS revenue_volatility
FROM daily_stats;

5. Verschachtelte Aggregation vs. Window Functions

Ein wichtiger Unterschied bei verschachtelter Aggregation betrifft die Abgrenzung zu Window Functions. Eine Window Function wie SUM(betrag) OVER (PARTITION BY kunde) fügt zu jeder einzelnen Zeile einen zusätzlichen aggregierten Wert hinzu, ohne die Anzahl der Zeilen zu reduzieren. Verschachtelte Aggregation dagegen reduziert die Zeilenanzahl in jedem Schritt: Die erste Ebene reduziert von einzelnen Bestellungen auf eine Zeile pro Tag, die zweite Ebene reduziert diese Tageszeilen auf eine einzige Gesamtzeile.

Wer also einen Durchschnitt von Tagessummen berechnen möchte und dabei trotzdem jede einzelne Bestellzeile im Ergebnis sehen will, kombiniert typischerweise beide Techniken: eine Window Function für die Tagessumme pro Zeile, gefolgt von einer weiteren Aggregation, die diese sich wiederholenden Tageswerte für den finalen Durchschnitt zusammenfasst. Verschachtelte Aggregation mit reinen Subqueries oder CTEs eignet sich dagegen genau dann, wenn nur das finale, verdichtete Ergebnis interessiert und die Einzelzeilen nicht mehr benötigt werden.

6. Mehr als zwei Ebenen: dreistufige Aggregation

Das Prinzip der verschachtelten Aggregation lässt sich problemlos auf drei oder mehr Ebenen erweitern, indem jede zusätzliche Ebene als weitere CTE oder Subquery um die vorherige Ebene gelegt wird. Ein Beispiel aus der Praxis: zunächst der Umsatz pro Bestellung, dann die Summe pro Tag, und schließlich der Durchschnitt dieser Tagessummen pro Kalenderwoche. Jede dieser drei Ebenen hat eine eigene, klar abgegrenzte Gruppierungsebene, wodurch die Abfrage trotz mehrerer Aggregationsschritte nachvollziehbar bleibt.

Mit zunehmender Anzahl von Ebenen wächst allerdings auch die Wahrscheinlichkeit für logische Fehler, etwa wenn eine Zwischenebene versehentlich nach der falschen Spalte gruppiert oder eine Filterbedingung auf der falschen Ebene platziert wird. Bei mehrstufiger verschachtelter Aggregation empfiehlt es sich deshalb, jede CTE einzeln mit einem separaten SELECT zu testen, bevor die nächste Ebene darauf aufgebaut wird, statt die komplette mehrstufige Abfrage in einem Schritt zu schreiben und erst am Ende zu prüfen.

-- Three-level nested aggregation: order -> day -> week average
WITH daily_totals AS (
    SELECT DATE(order_date) AS order_day, SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY DATE(order_date)
),
weekly_totals AS (
    SELECT DATE_TRUNC('week', order_day) AS order_week, SUM(daily_total) AS weekly_total
    FROM daily_totals
    GROUP BY DATE_TRUNC('week', order_day)
)
SELECT AVG(weekly_total) AS avg_weekly_revenue
FROM weekly_totals;

7. Typische Anwendungsfälle in Reports und Dashboards

Verschachtelte Aggregation taucht in der Praxis überall dort auf, wo eine Kennzahl über eine natürliche Zeitachse oder eine andere Gruppierungsebene hinweg verdichtet werden soll. Typische Beispiele sind der durchschnittliche monatliche Umsatz aus wöchentlichen Summen, die durchschnittliche Anzahl aktiver Nutzer pro Tag aus stündlichen Zählungen, oder die durchschnittliche Warenkorbgröße pro Kunde, berechnet aus der Summe der Artikel je Bestellung, gemittelt über alle Bestellungen eines Kunden.

Ein weiterer verbreiteter Anwendungsfall ist die Berechnung von Schwankungsbreiten mit verschachtelter Aggregation: Nachdem eine Zwischenebene den Tagesumsatz liefert, kann die äußere Abfrage nicht nur den Durchschnitt, sondern auch Minimum, Maximum und Standardabweichung dieser Tagessummen berechnen. Damit lässt sich in einer einzigen, gut strukturierten Abfrage sowohl der typische Wert als auch die Streuung eines Geschäftsvorgangs über die Zeit darstellen, ohne separate Abfragen für jede Kennzahl zu benötigen.

8. Fallstricke: leere Zwischenaggregate und Rundungsfehler

Ein häufiger Fallstrick bei verschachtelter Aggregation betrifft Tage ohne jegliche Bestellung. Wenn die innere Abfrage nur Tage mit mindestens einer Bestellung liefert, weil sie direkt auf der Bestelltabelle gruppiert, fehlen umsatzlose Tage in der Zwischenaggregation komplett, statt mit einer Zeile mit dem Wert 0 zu erscheinen. Der äußere Durchschnitt wird dadurch systematisch zu hoch berechnet, weil er faktisch nur über die aktiven Tage mittelt, nicht über den gesamten betrachteten Zeitraum.

Die Lösung besteht darin, die Zwischenaggregation nicht direkt auf der Bestelltabelle, sondern auf einer generierten Datumsreihe mit einem LEFT JOIN zur Bestelltabelle aufzubauen, damit auch Tage ohne Bestellung mit einem Umsatz von 0 in der Zwischenebene erscheinen. Ein zweiter, kleinerer Fallstrick betrifft Rundungsfehler bei mehrstufiger Aggregation: Wird bereits die erste Ebene gerundet, bevor die zweite Ebene darauf aggregiert, akkumulieren sich Rundungsfehler über die Ebenen hinweg. Rundung sollte deshalb grundsätzlich erst auf der letzten, äußersten Ebene erfolgen, nicht bei jeder Zwischenaggregation.

-- Correct nested aggregation: generate a date series so empty days count as 0
WITH date_range AS (
    SELECT generate_series(
        DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day'
    )::date AS order_day
),
daily_totals AS (
    SELECT
        d.order_day,
        COALESCE(SUM(o.total_amount), 0) AS daily_total
    FROM date_range d
    LEFT JOIN orders o ON DATE(o.order_date) = d.order_day
    GROUP BY d.order_day
)
SELECT AVG(daily_total) AS avg_daily_revenue_including_empty_days
FROM daily_totals;

9. Ansätze für verschachtelte Aggregation im Vergleich

Die folgende Übersicht vergleicht die gängigen Techniken, um Aggregate über bereits aggregierte Werte zu berechnen, und ordnet ein, wann welcher Ansatz für verschachtelte Aggregation geeignet ist.

In den meisten Fällen ist eine CTE die klarste Wahl für verschachtelte Aggregation, weil sie jede Aggregationsebene explizit benennt und dadurch auch bei drei oder mehr Ebenen nachvollziehbar bleibt. Subqueries in der FROM-Klausel sind bei nur zwei Ebenen gleichwertig, verlieren aber schnell an Übersichtlichkeit, sobald weitere Ebenen oder Filterbedingungen hinzukommen.

Wer verschachtelte Aggregation als eigenes, benennbares Muster erkennt, statt sie als Sonderfall zu behandeln, schreibt Reporting-Abfragen, die von Anfang an korrekt zwischen einzelnen Zeilen, Zwischenaggregaten und finalen Kennzahlen unterscheiden.

10. Zusammenfassung

Verschachtelte Aggregation berechnet eine Kennzahl aus bereits aggregierten Zwischenwerten, etwa den Durchschnitt täglicher Summen. Da SQL das direkte Verschachteln von Aggregatfunktionen wie AVG(SUM(spalte)) in derselben Abfrageebene verbietet, führt der korrekte Weg über eine Subquery oder eine CTE, die zunächst die erste Aggregationsebene bereitstellt, bevor die äußere Abfrage die zweite Aggregatfunktion darauf anwendet.

CTEs sind bei mehrstufiger verschachtelter Aggregation meist die klarere Wahl, weil jede Ebene explizit benannt wird. Fehlende Zwischenwerte, etwa Tage ohne Bestellung, müssen mit einer generierten Datumsreihe und LEFT JOIN abgefangen werden, sonst verzerrt sich der äußere Durchschnitt systematisch. Rundung gehört ausschließlich auf die letzte Ebene, nicht in jede Zwischenaggregation.

11. FAQ: Verschachtelte Aggregation