23rd Jun 2022 9 Leseminuten

Was ist eine rekursive CTE in SQL?

Inhaltsverzeichnis

Was sind CTEs?
- Nicht-rekursive CTE-Syntax
- Rekursive CTE-Syntax
Beispiel 1 - Suche nach Vorgesetzten und Hierarchiestufen für alle Mitarbeiter
Beispiel 2 - Ermittlung des Investitionsbetrags nach Investor
Beispiel 3 - Suche nach Strecken zwischen Städten
Rekursive CTEs weiter üben

Der Artikel, der Ihnen praktische Beispiele für die Verwendung rekursiver CTEs in SQL zeigt.

Wenn Sie schon von den rekursiven CTEs in SQL gehört haben, sie aber noch nie benutzt haben, ist dieser Artikel genau das Richtige für Sie. Er ist auch etwas für Sie, wenn Sie nie genug von rekursiven CTE-Beispielen bekommen können.

Bevor wir uns mit der Rekursion befassen, werde ich Sie daran erinnern, was CTEs sind und was ihre Syntax ist. Dann werde ich das Gleiche für rekursive CTEs tun. Danach zeige ich Ihnen anhand von drei Beispielen, wie rekursive CTEs funktionieren.

Was sind CTEs?

Die CTE (common table expression), auch bekannt als WITH Klausel, ist eine SQL-Funktion, die einen temporären Datensatz zurückgibt, der von einer anderen Abfrage verwendet werden kann. Da es sich um ein temporäres Ergebnis handelt, wird es nirgendwo gespeichert, aber es kann dennoch wie jede andere Tabelle referenziert werden.

Es gibt zwei Arten von CTEs, nicht rekursive und rekursive.

Hier ist ein guter Artikel, der Ihnen zeigt, was CTEs sind und wie sie funktionieren.

Nicht-rekursive CTE-Syntax

Die allgemeine Syntax einer nicht rekursiven CTE sieht wie folgt aus:

WITH cte_name AS (cte_query_definition)
 
SELECT *
FROM   cte_name;

Der erste Teil der Syntax ist das CTE. Er beginnt mit dem Schlüsselwort WITH. Dann geben Sie Ihrem CTE einen Namen. Danach folgt das Schlüsselwort AS, und in den Klammern können Sie das CTE definieren.

Der zweite Teil der Syntax ist eine einfache SELECT -Anweisung. Sie wird unmittelbar nach dem rekursiven CTE geschrieben, ohne Kommas, Semikolons oder ähnliche Zeichen. Wie ich bereits sagte, wird die CTE in einer anderen Abfrage wie jede andere Tabelle verwendet. Das ist genau das, was die Anweisung SELECT tut.

Hier ist der Artikel, der Ihnen zusätzlich mit der CTE-Syntax und ihren Regeln helfen kann. Und wenn Sie weitere CTE-Beispiele benötigen, ist dieser Artikel genau das Richtige für Sie.

Rekursive CTE-Syntax

Ein rekursiver CTE verweist auf sich selbst. Sie gibt eine Teilmenge der Ergebnisse zurück, verweist dann wiederholt (rekursiv) auf sich selbst und hört auf, wenn sie alle Ergebnisse zurückgibt.

Die Syntax für eine rekursive CTE unterscheidet sich nicht allzu sehr von der einer nicht rekursiven CTE:

WITH RECURSIVE cte_name AS (
    cte_query_definition (the anchor member)
 
    UNION ALL
 
    cte_query_definition (the recursive member)
)
 
 
SELECT *
FROM   cte_name;

Auch hier steht am Anfang des CTE die Klausel WITH. Wenn Sie jedoch möchten, dass Ihre CTE rekursiv ist, dann schreiben Sie nach WITH das Schlüsselwort RECURSIVE. Dann geht es weiter wie gewohnt: AS wird von den Klammern mit der CTE-Abfragedefinition gefolgt. Diese erste Abfragedefinition wird als Ankerelement bezeichnet.

Um das Ankerelement mit dem rekursiven Element zu verbinden, müssen Sie den Befehl UNION oder UNION ALL verwenden. Das rekursive Mitglied ist natürlich der rekursive Teil von CTE, der auf das CTE selbst verweist. Wie das funktioniert, werden Sie gleich in einem Beispiel sehen.

Rekursive CTEs werden vor allem dann verwendet, wenn Sie hierarchische Daten oder Diagramme abfragen möchten. Dabei kann es sich um die Organisationsstruktur eines Unternehmens, einen Familienstammbaum, eine Restaurantkarte oder verschiedene Routen zwischen Städten handeln. In diesen Artikeln erfahren Sie, wie CTEs mit hierarchischen Strukturen arbeiten und wie man Graphdaten abfragt.

Nachdem wir nun verstanden haben, wie rekursive CTEs funktionieren, wollen wir uns einige Beispiele ansehen.

Beispiel 1 - Suche nach Vorgesetzten und Hierarchiestufen für alle Mitarbeiter

Für dieses Problem verwende ich Daten aus der Tabelle employeesdie die folgenden Spalten enthält:

id: Die ID des Mitarbeiters.
first_name: Der Vorname des Mitarbeiters.
last_name: Nachname des Mitarbeiters.
boss_id: Die ID des Vorgesetzten des Mitarbeiters.

So sehen die Daten aus:

id	first_name	last_name	boss_id
1	Domenic	Leaver	5
2	Cleveland	Hewins	1
3	Kakalina	Atherton	8
4	Roxanna	Fairlie	NULL
5	Hermie	Comsty	4
6	Pooh	Goss	8
7	Faulkner	Challiss	5
8	Bobbe	Blakeway	4
9	Laurene	Burchill	1
10	Augusta	Gosdin	8

Es ist nicht allzu kompliziert. Zum Beispiel ist der Chef von Domenic Leaver der Mitarbeiter mit der ID 5; das ist Hermie Comsty. Das gleiche Prinzip gilt für alle anderen Mitarbeiter, außer für Roxanna Fairlie. Sie hat keinen Chef; in der Spalte boss_id steht der Wert NULL. Daraus können wir schließen, dass Roxanna der Präsident oder Eigentümer der Firma ist.

Schreiben wir nun die rekursive CTE, um alle Mitarbeiter und ihre direkten Vorgesetzten aufzulisten.

WITH RECURSIVE company_hierarchy AS (
  SELECT    id,
            first_name,
            last_name,
            boss_id,
        0 AS hierarchy_level
  FROM employees
  WHERE boss_id IS NULL
 
  UNION ALL
   
  SELECT    e.id,
            e.first_name,
            e.last_name,
            e.boss_id, 
        hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.boss_id = ch.id
)
 
SELECT   ch.first_name AS employee_first_name,
       ch.last_name AS employee_last_name,
       e.first_name AS boss_first_name,
       e.last_name AS boss_last_name,
       hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e
ON ch.boss_id = e.id
ORDER BY ch.hierarchy_level, ch.boss_id;

Was macht diese Abfrage? Es ist eine rekursive Abfrage, also beginnt sie mit WITH RECURSIVE. Der Name der CTE ist company_hierarchy. Nach AS steht die CTE-Definition in Klammern.

Die erste Anweisung SELECT wählt alle employee Tabellenspalten aus, in denen die Spalte boss_id den Wert NULL hat. Kurz gesagt, sie wählt Roxanna Fairlie aus, weil nur sie den Wert NULL in dieser Spalte hat. Noch kürzer: Ich beginne die Rekursion an der Spitze der Organisationsstruktur. Es gibt auch eine Spalte hierarchy_level mit dem Wert 0. Das bedeutet, dass die Ebene des Eigentümers/Präsidenten 0 ist - er befindet sich an der Spitze der Hierarchie.

Ich habe die UNION ALL verwendet, um diese SELECT-Anweisung mit der zweiten zu verbinden, d. h. mit dem rekursiven Mitglied. Im rekursiven Mitglied wähle ich alle Spalten aus der Tabelle employees und die CTE company_hierarchy, wobei die Spalte boss_id gleich der Spalte id ist. Beachten Sie den Teilhierarchy_level + 1. Das bedeutet, dass die CTE bei jeder Rekursion 1 zur vorherigen Hierarchieebene hinzufügt, und zwar so lange, bis sie das Ende der Hierarchie erreicht. Beachten Sie auch, dass ich diese CTE wie jede andere Tabelle behandle. Um die Definition der CTE zu beenden, schließen Sie einfach die Klammern.

Schließlich gibt es noch eine dritte Anweisung SELECT, die sich außerhalb der CTE befindet. Sie wählt die Spalten aus, in denen die Mitarbeiter, die Namen ihrer Vorgesetzten und die Hierarchieebene angezeigt werden sollen. Die Daten werden aus der CTE und der Tabelle employees. Ich habe diese beiden mit LEFT JOIN verknüpft, da ich alle Daten aus der CTE haben möchte - einschließlich Roxanna Fairlie, die den Wert NULL in der Spalte boss_id hat. Das Ergebnis wird in aufsteigender Reihenfolge angezeigt: zuerst nach der Hierarchieebene, dann nach der ID des Vorgesetzten. So sieht es aus:

employee_first_name	employee_last_name	boss_first_name	boss_last_name	hierarchy_level
Roxanna	Fairlie	NULL	NULL	0
Hermie	Comsty	Roxanna	Fairlie	1
Bobbe	Blakeway	Roxanna	Fairlie	1
Domenic	Leaver	Hermie	Comsty	2
Faulkner	Challiss	Hermie	Comsty	2
Augusta	Gosdin	Bobbe	Blakeway	2
Pooh	Goss	Bobbe	Blakeway	2
Kakalina	Atherton	Bobbe	Blakeway	2
Laurene	Burchill	Domenic	Leaver	3
Cleveland	Hewins	Domenic	Leaver	3

Roxanna Fairlie ist die oberste Chefin; das wussten Sie bereits. Es gibt zwei Mitarbeiter auf Ebene 1. Das heißt, Bobbe Blakeway und Hermie Comsty sind Roxanna Fairlie direkt unterstellt. Auf Ebene 2 gibt es Angestellte, deren direkte Vorgesetzte Bobbe Blakeway und Hermie Comsty sind. Es gibt auch eine dritte Ebene in der Hierarchie. Das sind die Mitarbeiter, deren direkter Vorgesetzter Domenic Leaver ist.

Beispiel 2 - Ermittlung des Investitionsbetrags nach Investor

In diesem Beispiel verwende ich die Tabelle investment:

id: Die ID der Investition.
investment_amount: Der Betrag der Investition.

Die Daten in der Tabelle sehen wie folgt aus:

id	investment_amount
1	9,705,321.00
2	5,612,948.60
3	5,322,146.00

Dies sind die Beträge der drei möglichen Investitionsoptionen. Sie werden von den drei Investoren berücksichtigt, die den gesamten Investitionsbetrag in gleiche Teile aufteilen werden. Ihre Aufgabe ist es, den Betrag pro Investor in Abhängigkeit von der Anzahl der Investoren zu berechnen, d. h. ob ein, zwei, drei oder kein Investor in jede Anlage investiert.

Die Abfrage, mit der dieses Problem gelöst wird, lautet:

WITH RECURSIVE per_investor_amount AS (
    SELECT  0 AS investors_number,
            0.00 AS investment_amount,
            0.00 AS individual_amount
    UNION
 
    SELECT  investors_number + 1,
            i.investment_amount,
            i.investment_amount / (investors_number + 1)
    FROM investment i, per_investor_amount pia
    WHERE investors_number << 3
)
 
SELECT *
FROM per_investor_amount
ORDER BY  investment_amount, investors_number;

Auch hier beginnt die CTE mit WITH RECURSIVE, gefolgt von ihrem Namen und der Abfragedefinition. Dieses Mal verwende ich das Ankerelement der rekursiven Abfrage, um einige Daten zu erstellen. Die Spalten sind investors_number, investment_amount und individual_amount. Dies ist der Punkt, an dem die Rekursion beginnen soll (genauso wie im vorherigen Beispiel mit hierarchy_level = 0).

Dann kommen UNION und das rekursive Mitglied. Dieser Teil der Abfrage wird die Spalte investors_number bei jeder Rekursion um eins erhöhen. Dies geschieht für jede investment_amount. In der dritten Spalte wird der Betrag dieser Investition pro Anleger berechnet, abhängig von der Anzahl der beteiligten Anleger. Die Rekursion wird für bis zu drei Anleger durchgeführt (d. h. bis die Bedingung WHERE investors_number < 3 erreicht ist).

Danach folgt die einfache SELECT-Anweisung, die alle Spalten aus dem CTE zurückgibt. Und hier ist das Ergebnis:

investors_number	investment_amount	individual_amount
0	0.00	0.00
1	5,322,146.00	5,322,146.00
2	5,322,146.00	2,661,073.00
3	5,322,146.00	1,774,048.67
1	5,612,948.60	5,612,948.60
2	5,612,948.60	2,806,474.30
3	5,612,948.60	1,870,982.87
1	9,705,321.00	9,705,321.00
2	9,705,321.00	4,852,660.50
3	9,705,321.00	3,235,107.00

Es ist nicht schwer, es zu analysieren. Wenn es keine Investoren gibt, ist der Investitionsbetrag gleich Null, ebenso wie der individuelle Betrag. Wenn die Investition 5.322.146,00 beträgt und es nur einen Anleger gibt, dann ist der Betrag pro Anleger 5.322.146,00. Wenn zwei Anleger den gleichen Betrag investieren, muss jeder von ihnen 2.661.073,00 zahlen. Wenn sich alle drei Anleger für eine Investition entscheiden, zahlt jeder von ihnen 1.774.048,67. Die beiden anderen Investitionsbeträge folgen demselben Muster, wie Sie in der Tabelle sehen können.

Beispiel 3 - Suche nach Strecken zwischen Städten

Im dritten Beispiel verwende ich die Tabelle cities_route, die Daten über niederländische Städte enthält:

city_from: Die Abfahrtsstadt.
city_to: Die Zielstadt.
distance: Die Entfernung zwischen zwei Städten, in Kilometern.

city_from	city_to	distance
Groningen	Heerenveen	61.4
Groningen	Harlingen	91.6
Harlingen	Wieringerwerf	52.3
Wieringerwerf	Hoorn	26.5
Hoorn	Amsterdam	46.1
Amsterdam	Haarlem	30
Heerenveen	Lelystad	74
Lelystad	Amsterdam	57.2

Verwenden Sie diese Tabelle, um alle möglichen Routen von Groningen nach Haarlem zu finden, mit Angabe der Städte auf der Route und der Gesamtentfernung.

Hier ist die Abfrage zur Lösung dieses Problems:

WITH RECURSIVE possible_route AS (
    SELECT  cr.city_to,
            cr.city_from || '->' ||cr.city_to AS route,
            cr.distance
      FROM cities_route cr
      WHERE cr.city_from = 'Groningen'
 
UNION ALL
 
SELECT  cr.city_to,
            pr.route || '->' || cr.city_to AS route,
                CAST((pr.distance + cr.distance) AS DECIMAL(10, 2))
      FROM possible_route pr
INNER JOIN cities_route cr
            ON cr.city_from = pr.city_to
)
 
SELECT  pr.route,
        pr.distance
FROM possible_route pr
WHERE pr.city_to = 'Haarlem'
ORDER BY pr.distance;

Schauen wir uns an, was diese Abfrage macht. Die erste Anweisung SELECT in der CTE-Definition wählt die Spalten aus der Tabelle cities_route aus, wobei die Ausgangsstadt Groningen ist. Beachten Sie, dass es auch eine neue Spalte mit dem Namen route gibt, die ich verwenden werde, um die Städte auf der Route zu verketten.

Die UNION ALL verbindet dies mit dem rekursiven Mitglied. Diese SELECT Anweisung wählt die Ankunftsstadt aus, verkettet die Städte auf der Route und addiert schließlich die Entfernungen zwischen diesen Städten zur Gesamtsumme der Route zwischen Groningen und Haarlem. Um all dies zu erreichen, habe ich die CTE mit der Tabelle cities_route.

Dann kommt die Anweisung SELECT, die Daten aus der CTE abruft. Sie wählt die Strecke und die Entfernung aus, wenn die Ankunftsstadt Haarlem ist, wobei die Daten nach der Entfernung in aufsteigender Reihenfolge geordnet sind.

Das Abfrageergebnis sieht wie folgt aus:

route	distance
Groningen->Heerenveen->Lelystad->Amsterdam->Haarlem	222.6
Groningen->Harlingen->Wieringerwerf->Hoorn->Amsterdam->Haarlem	246.5

Es ist nicht schwer, diese Tabelle zu verstehen. Es gibt zwei Strecken von Groningen nach Haarlem. Sie beinhalten verschiedene Städte dazwischen und sind 222,6 km bzw. 246,5 km lang.

Wenn Sie weiter lernen wollen, sehen Sie sich an, wie Sie eine rekursive CTE anstelle einer langen SQL-Abfrage verwenden können. Und nachdem Sie sich mit diesem Thema beschäftigt haben, machen Sie sich einen Spaß daraus, etwas mit einer rekursiven CTE zu zeichnen.

Rekursive CTEs weiter üben

Diese drei Beispiele haben die Möglichkeiten von rekursiven CTEs in SQL gezeigt. Jetzt ist es an der Zeit, das Gelernte zu vertiefen.

Am besten probieren Sie es mit unserem KursRekursive Abfragen . Er bietet Ihnen eine Fülle von Beispielen, Erklärungen und Übungsmöglichkeiten. Der Kurs ist Teil der Kursreihe Fortgeschrittenes SQL , in der Sie weitere fortgeschrittene SQL-Themen wie Fensterfunktionen, GROUP BY-Erweiterungen und rekursive Abfragen kennenlernen können.

Viel Spaß!

Tags: