Rekursive CTEs in SQL: Aufbau, Beispiel und Grenzen

Ein rekursives CTE ist ein Common Table Expression, der sich selbst referenziert. Es startet mit einer Menge von Zeilen, dem Anker, und verknüpft sich dann wiederholt mit der eigenen Ausgabe, bis keine neuen Zeilen mehr erscheinen. Damit durchlaufen Sie Hierarchien und Ketten: die Managerkette eines Mitarbeiters, eine Stückliste oder eine Konten-Hierarchie mit Eltern und Kindern. In SQL Server und Azure SQL schreiben Sie es mit einem einfachen WITH. PostgreSQL und MySQL verlangen WITH RECURSIVE. Fabric Data Warehouse unterstützt rekursive Abfragen gar nicht.

Zuerst ein normales CTE

Ein CTE ist ein benanntes, temporäres Ergebnis, das mit WITH definiert und von der folgenden Anweisung verwendet wird. Es existiert nur für diese eine Anweisung.

WITH recent_orders AS (
    SELECT customer_id, order_date
    FROM dbo.orders
    WHERE order_date >= DATEADD(month, -1, CAST(GETDATE() AS date))
)
SELECT customer_id, COUNT(*) AS num_orders
FROM recent_orders
GROUP BY customer_id;

Das entspricht einer Unterabfrage, ist aber leichter zu lesen, und Sie können es in der folgenden Anweisung mehrfach referenzieren.

Wie ein rekursives CTE funktioniert

Ein rekursives CTE hat zwei Teile, die mit UNION ALL verbunden sind:

1. Das Anker-Member läuft einmal und liefert die Startzeilen.

2. Das rekursive Member referenziert das CTE selbst. Es läuft über die Zeilen, die der vorherige Schritt geliefert hat, und fügt die Zeilen hinzu, die es findet.

Das rekursive Member läuft weiter, bis es keine Zeilen mehr liefert. Dann werden alle Schritte zu einem Ergebnis zusammengefasst.

Beispiel: eine Mitarbeiter-Hierarchie

Nehmen Sie eine Tabelle employees, in der jede Zeile eine manager_id hat, die auf einen anderen Mitarbeiter zeigt. Der CEO hat keinen Manager.

Diese Abfrage liefert jeden Mitarbeiter mit seiner Ebene in der Hierarchie:

WITH company_hierarchy (employee_id, name, manager_id, level) AS (
    -- Anchor: the person with no manager
    SELECT employee_id, name, manager_id, 1
    FROM dbo.employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: everyone who reports to someone already found
    SELECT e.employee_id, e.name, e.manager_id, ch.level + 1
    FROM dbo.employees AS e
    INNER JOIN company_hierarchy AS ch
        ON e.manager_id = ch.employee_id
)
SELECT employee_id, name, manager_id, level
FROM company_hierarchy
ORDER BY level, employee_id;

Ergebnis:

Schritt für Schritt: Der Anker liefert Ken. Der erste rekursive Durchlauf findet Brian, der an Ken berichtet. Der zweite findet Stephen und Syed. Der dritte findet Linda. Der vierte findet niemanden, also endet die Rekursion.

Das Schlüsselwort RECURSIVE hängt von Ihrer Datenbank ab

Die Logik ist überall gleich. Nur das Schlüsselwort ändert sich.

Endlose Rekursion mit MAXRECURSION stoppen

Enthalten Ihre Daten eine Schleife, etwa zwei Mitarbeiter, die jeweils als Manager des anderen erfasst sind, findet das rekursive Member immer weiter Zeilen und hört nie auf. SQL Server schützt Sie mit einem Standardlimit von 100 Ebenen. Eine Abfrage, die tiefer geht, schlägt mit einem Fehler fehl, der meldet, dass die maximale Rekursion erschöpft ist.

Sie können das Limit mit einem Hint an der äußeren Anweisung ändern, nach der CTE-Definition:

-- WITH company_hierarchy AS ( ... ) as above, then:
SELECT employee_id, name, level
FROM company_hierarchy
OPTION (MAXRECURSION 500);

Der Wert kann zwischen 0 und 32.767 liegen. 0 hebt das Limit auf. Nutzen Sie das nur, wenn Sie sicher sind, dass die Daten keine Zyklen enthalten. Die OPTION-Klausel gehört an die äußere Abfrage, nie in das CTE.

Regeln für das rekursive Member in T-SQL

• Anker- und rekursive Member müssen dieselbe Anzahl Spalten mit denselben Datentypen liefern.

• UNION ALL ist der einzige erlaubte Operator zwischen dem letzten Anker-Member und dem ersten rekursiven Member.

• Das rekursive Member darf weder SELECT DISTINCT, GROUP BY, HAVING, TOP, skalare Aggregate, Unterabfragen noch LEFT, RIGHT oder OUTER JOIN verwenden. INNER JOIN ist in Ordnung.

• Das CTE darf in der FROM-Klausel des rekursiven Members nur einmal referenziert werden.

Ein häufiger Datentypfehler: Der Anker liefert 1 als int, und das rekursive Member liefert einen berechneten varchar-Pfad. Wandeln Sie beide ausdrücklich in denselben Typ um.

Wo rekursive CTEs nicht funktionieren: Fabric und Synapse

Microsoft Fabric Data Warehouse und der SQL analytics endpoint eines Fabric-Lakehouse unterstützen keine rekursiven Abfragen. Standard-CTEs, sequenzielle und verschachtelte CTEs funktionieren dort, ein CTE, das sich selbst referenziert, jedoch nicht. Auch Azure Synapse Dedicated SQL Pools unterstützen sie nicht.

Brauchen Sie in Fabric eine Hierarchie, haben Sie drei Möglichkeiten:

1. Einmal in einem Notebook oder einer Pipeline abflachen und den vollständigen Pfad und die Ebene jedes Mitarbeiters als Spalten speichern. Das ist meist die beste Antwort, denn auch Power BI will eine abgeflachte Hierarchie.

2. Eine feste Anzahl Self-Joins verwenden, wenn die Tiefe bekannt und klein ist, etwa drei Ebenen eines Kontenplans.

3. Die rekursive Abfrage in einer Quelle ausführen, die sie unterstützt, etwa Azure SQL Database, und das Ergebnis laden.

Rekursive CTEs und Power BI

Power BI verarbeitet Eltern-Kind-Hierarchien mit den DAX-Funktionen PATH, aber die funktionieren nur im Modell. Bei großen Hierarchien ist es oft sauberer, die Struktur in SQL mit einem rekursiven CTE abzuflachen, eine Spalte pro Ebene zu speichern und Power BI aus diesen Spalten eine normale Hierarchie bauen zu lassen. So bleibt das Modell einfach, wie es unsere Best Practices zur Datenmodellierung empfehlen.

Wann Sie eines nutzen und wann nicht

Gute Einsatzfälle:

• Organigramme und Reporting-Linien

• Stücklisten und Produktbaugruppen

• Konten-Hierarchien und Kostenstellenbäume

• Eine Folge erzeugen, etwa eine Zeile pro Tag zwischen zwei Daten, wenn keine Kalendertabelle existiert

Kein guter Einsatzfall:

• Laufende Summen. Eine Fensterfunktion wie SUM(amount) OVER (ORDER BY order_date) ist einfacher und deutlich schneller.

Eine weitere SQL-Funktion, die beim Wechsel zwischen Datenbanken oft verwirrt, behandelt unser Leitfaden zu TO_JSON_STRING in BigQuery. Wenn Sie einen Fabric-Datenspeicher auswählen, vergleicht Warum Microsoft Fabric das Lakehouse und das Warehouse.

Quellen

• WITH common_table_expression (Transact-SQL) - Microsoft Learn

• T-SQL surface area in Fabric Data Warehouse - Microsoft Learn

• Recursive common table expressions overview - MariaDB