Wie man mehrere CTEs in SQL schreibt

Nutzen Sie das volle Potenzial von CTEs, indem Sie zwei oder mehr von ihnen in einer einzigen SQL-Abfrage kombinieren.

Gemeinsame Tabellenausdrücke (Common Table Expressions, CTEs) können ein leistungsstarkes SQL-Tool sein. Wenn Sie zwei (oder sogar mehr) CTEs zusammen schreiben, vervielfacht sich diese Leistung. In diesem Artikel zeige ich Ihnen drei Möglichkeiten zum Schreiben mehrerer CTEs:

• Verwendung von zwei unabhängigen CTEs in einer SQL-Abfrage.
• Verwendung von zwei CTEs, wobei das zweite CTE auf das erste verweist.
• Verwendung von zwei CTEs, wobei ein CTE rekursiv ist.

Wenn Sie mit CTEs nicht vertraut sind, empfehle ich Ihnen unseren interaktiven Kurs Common Table Expressions, in dem alle CTE-Typen, einschließlich der rekursiven, in 114 praktischen Übungen behandelt werden. Sie können über den Kurs in einer Folge unserer Kurs des Monats -Serie lesen.

Wenn Sie Ihr Wissen schnell auffrischen möchten, sind diese Artikel über CTEs und deren Verwendung ein guter Anfang.

Einführung in die Daten

Ich zeige Ihnen die CTE-Beispiele an einem Datensatz, der aus zwei Tabellen besteht.

Die erste Tabelle ist cars. Sie enthält Daten über die von Renault und Nissan hergestellten Autos. Ihre Spalten sind:

• id - Die ID des Fahrzeugs und der Primärschlüssel (PK) der Tabelle.
• car_make - Der Hersteller des Fahrzeugs.
• model - Das Modell des Fahrzeugs.
• motor_type - Die Details über den Motortyp des Modells.
• year_of_production - Das Jahr, in dem das Auto hergestellt wurde.

Hier ein Beispiel für Daten aus der Tabelle:

Die zweite Tabelle, car_sales, hat diese Spalten:

• id - Die ID der Verkaufsinformationen und den Primärschlüssel (PK) der Tabelle.
• report_period - Das Datum des Verkaufsberichts.
• sales - Die Anzahl der verkauften Fahrzeuge.
• cars_id - Der Fremdschlüssel (FK), der auf die Tabelle cars.

Einige Beispieldaten aus der Tabelle:

Zwei CTEs: Unabhängig

In diesem ersten Beispiel zeige ich Ihnen, wie Sie mit zwei unabhängigen CTEs sowohl die Gesamtverkäufe als auch die Verkäufe nach Marken ausgeben können. Der Code ist hier zu sehen:

WITH sales_per_make AS (
	SELECT car_make,
		 SUM(sales) AS total_sales_per_make
	FROM cars c
JOIN car_sales cs
		ON c.id = cs.cars_id
GROUP BY car_make
),

sales_sum AS (
	SELECT SUM(sales) AS total_sales
	FROM car_sales
)
	
SELECT car_make,
	 total_sales_per_make,
	 total_sales
FROM sales_per_make, sales_sum ss;

Ich beginne mit dem Schreiben der ersten CTE, als ob es die einzige CTE in meiner Abfrage wäre. Der CTE-Name, sales_per_makefolgt auf das Schlüsselwort WITH, dann kommt das Schlüsselwort AS. Danach schreibe ich in Klammern, was die CTE tun soll.

In diesem Fall verwende ich die Aggregatfunktion SUMME(), um die Verkäufe pro Automarke zu ermitteln. Dazu muss ich die beiden Tabellen, die mir zur Verfügung stehen, verbinden.

Danach kommt das zweite CTE. Wichtig ist hier, dass die beiden CTEs durch ein Komma getrennt werden müssen. Dann beginnt die zweite CTE nicht mit dem Schlüsselwort WITH, sondern gleich mit dem Namen der zweiten CTE. Ich habe sie benannt sales_sum. Die SELECT-Anweisung in den Klammern berechnet die Gesamtverkäufe aller Automarken.

Diese beiden CTEs sind unabhängig voneinander, da die zweite CTE nicht auf die erste verweist.

Um diese Abfragen zu verwenden, muss ich eine SELECT -Anweisung (die Hauptabfrage) schreiben, die auf sie verweist. Dies ist dasselbe wie wenn Sie nur eine CTE schreiben. Die Anweisung SELECT verbindet hier die Ergebnisse der beiden CTEs, um diese Ausgabe zu liefern:

Das Ergebnis bedeutet, dass die Händler 176.569 Renault-Fahrzeuge und 185.359 Nissan-Fahrzeuge verkauft haben, also insgesamt 361.928 Fahrzeuge.

Sie sehen also, dass es gar nicht so schwer ist, zwei CTEs zu schreiben. Es gibt jedoch einige Fallstricke bei der Verwendung mehrerer CTEs in einer Abfrage. Die wichtigsten Dinge, auf die Sie beim Schreiben mehrerer CTEs achten sollten, sind:

• Verwenden Sie nur eine WITH.
• Trennen Sie CTEs mit Kommas.
• Verwenden Sie kein Komma vor der Hauptabfrage.
• Es gibt nur eine Hauptabfrage.

Mehrere CTEs funktionieren nur, wenn Sie das Schlüsselwort WITH einmal schreiben. Aber das ist nicht überall, wo Sie wollen. Sie müssen es vor die erste CTE schreiben. Die zweite und alle folgenden CTEs beginnen mit dem Namen der jeweiligen CTEs, im Gegensatz zur ersten CTE, die mit dem Schlüsselwort WITH beginnt.

Das erste CTE wird durch ein Komma vom zweiten getrennt. Dies gilt auch, wenn Sie mehr als zwei CTEs schreiben: alle CTEs werden durch ein Komma getrennt. Unabhängig von der Anzahl der CTEs gibt es jedoch kein Komma zwischen dem letzten CTE und der Hauptabfrage.

Schließlich gibt es nur eine einzige Hauptabfrage. Was auch immer Sie berechnen wollen, es funktioniert nur, wenn es eine Hauptabfrage gibt. Das erscheint logisch, denn Sie können alle CTEs, die Sie verbinden möchten, wie jede andere Tabelle referenzieren. Das ist einer der Vorteile von CTEs, also nutzen Sie ihn!

Zwei CTEs: Eine referenziert die andere

In dieser etwas komplexeren Berechnung verwende ich wieder zwei CTEs. Diesmal verweist die zweite auf die erste. Dies ist die einzige Möglichkeit, wenn eine CTE auf eine andere verweisen soll. Sie können nur auf die CTEs vor dem aktuellen CTE verweisen und nicht auf die CTEs, die darauf folgen.

Ich werde sie so schreiben, dass sie den tatsächlichen Umsatz im Jahr 2022 (d. h. im Januar und Februar) berechnen, den Jahresumsatz 2022 anhand des durchschnittlichen Umsatzes budgetieren und schließlich den noch ausstehenden Umsatz im Jahr 2022 ermitteln.

Im Folgenden führe ich Sie Schritt für Schritt durch den Code, damit Sie verstehen, was hier vor sich geht:

WITH sales_per_car AS (
	SELECT c.id,
		 c.car_make,
	   	 c.model,
	       c.motor_type,
	       c.year_of_production,
	       AVG(cs.sales)::INT AS average_sales_2022
	FROM cars c JOIN car_sales cs ON c.id = cs.cars_id
	WHERE c.year_of_production = 2022
	GROUP BY c.id, c.car_make, c.model, c.motor_type, c.year_of_production
),

budget AS (
	SELECT *,
		 average_sales_2022 * 12 AS annual_planned_sales_2022
	FROM sales_per_car
)
	
SELECT b.car_make,
	 b.model,
	 b.motor_type,
	 b.year_of_production,
	 SUM(cs.sales) AS actual_ytd_sales_2022,
	 b.annual_planned_sales_2022,
	 b.annual_planned_sales_2022 - SUM(cs.sales) AS remaining_annual_sales_2022
FROM budget b
JOIN car_sales cs
ON b.id = cs.cars_id
GROUP BY b.car_make, b.model, b.motor_type, b.year_of_production, b.annual_planned_sales_2022;

Die Syntax ist hier die gleiche wie im vorherigen Beispiel. Die CTE sales_per_car gibt einige Spalten aus der Tabelle zurück cars. Ich verwende auch die Funktion AVG(), um den durchschnittlichen Umsatz im Jahr 2022 zu berechnen. Diese Zahl wird in eine ganze Zahl umgewandelt, da es sich um Autos handelt und ich den Durchschnitt als ganze Zahl von Autos sehen möchte.

Mit diesem CTE erhalte ich die durchschnittlichen Verkäufe für jedes im Jahr 2022 produzierte Modell. Es gibt auch einen Hinweis darauf, warum ich weiß, dass die Verkäufe nur für 2022 gelten: Autos, die 2022 produziert werden, können nicht 2021 verkauft werden. Das ist zumindest die Annahme, die hier getroffen wurde.

Ein Komma trennt diese CTE von der zweiten, die mit ihrem Namen beginnt, budget. Diese CTE referenziert nun die erste CTE wie jede andere Tabelle. Sie können das in der FROM Klausel sehen. Ich verwende die Spalte average_sales_2022, weil es sich um den durchschnittlichen tatsächlichen monatlichen Verkauf für 2022 handelt. Angenommen, die Methode für die Budgetierung besteht darin, den durchschnittlichen Monatsumsatz für dieses Jahr mit 12 zu multiplizieren, um den geplanten Jahresumsatz zu erhalten. Das ist genau das, was diese zweite CTE macht, und das ist der Grund, warum sie auf die erste CTE verweisen muss.

Die Hauptabfrage verknüpft die CTE budget und die Tabelle car_sales. Ich verwende diese Abfrage, um die tatsächlichen Verkäufe pro Modell im Jahr 2022 zu ermitteln. Dann zeige ich die Spalte annual_planned_sales_2022 aus der zweiten CTE an. Schließlich ermittle ich durch Berechnung der Differenz zwischen diesen beiden Spalten die Anzahl der Verkäufe, die für den Rest des Jahres 2022 noch getätigt werden müssen.

Hier ist der Bericht, den ich durch Ausführen der Abfrage erhalte:

Zwei CTEs: Einer der CTEs ist rekursiv

Ich gehe nun von diesen Daten aus, bleibe aber innerhalb dieser Automarken. Nehmen wir an, Renault erwägt drei Investitionen: den Kauf von Jaguar für 2.300.000.000, Volvo für 1.548.470.000 oder Alfa Romeo für 2.450.000.000. Das Unternehmen erwägt, dies allein, zusammen mit Nissan oder sowohl mit Nissan als auch mit Citroën zu tun.

Dieses Beispiel eignet sich hervorragend, um zwei CTEs zu schreiben, von denen eine rekursiv ist:

WITH RECURSIVE company_purchase AS (
	SELECT 2300000000 AS amount
UNION
	SELECT 1548470000 AS amount
UNION
	SELECT 2450000000 AS amount
),

per_buyer AS (
	SELECT  0 AS number_of_buyers,
		  0::DECIMAL AS purchase_amount,
		  0::DECIMAL AS amount_per_buyer
UNION
	SELECT  number_of_buyers + 1,
		  amount,
		  amount/(number_of_buyers + 1)::DECIMAL
	FROM company_purchase, per_buyer
	WHERE number_of_buyers <= 3)

SELECT *
FROM per_buyer
ORDER BY purchase_amount, number_of_buyers;

Wann immer Sie eine rekursive CTE schreiben wollen, müssen Sie mit WITH RECURSIVE beginnen. Sie kündigen immer Ihre Absicht an, eine rekursive CTE zu schreiben, unabhängig davon, ob diese rekursive Abfrage die erste oder die zweite CTE ist. In diesem Fall ist meine erste CTE nicht rekursiv.

Ich verwende das company_purchase CTE, um verschiedene Investitionen in Jaguar, Volvo oder Alfa Romeo zu erstellen.

Dann kommt die rekursive Abfrage. Das Prinzip ist dasselbe: Trennen Sie die CTEs durch ein Komma und beginnen Sie die zweite CTE ohne das Schlüsselwort WITH. In dieser zweiten CTE gehe ich von keinerlei Investitionen und Käufern aus. Die Werte sind überall gleich Null. Dann verwende ich eine Rekursion, und die Abfrage berechnet den Betrag pro Käufer für einen, zwei oder drei Investoren für die erste Investition. Die Rekursion wiederholt dann die gleiche Berechnung für die zweite und dritte Investition. Dazu muss ich natürlich die rekursive Abfrage mit der nicht rekursiven Abfrage verknüpfen.

Schließlich wählt die Hauptabfrage alle Daten aus der per_buyer CTE, mit der folgenden Ausgabe:

Was sagen mir diese Daten? Wenn zum Beispiel drei Käufer (Renault, Nissan und Citroën) Volvo für 1.548.470.000 kaufen, sollte jedes Unternehmen 516.156.666,67 investieren.

Weitere Beispiele finden Sie in dem Artikel über die 5 wichtigsten SQL-CTE-Interviewfragen und in einem zusätzlichen Artikel über die Verwendung von zwei CTEs.

Multiplizieren Sie die Macht der CTEs weiter

Dies sind nur drei Beispiele dafür, was CTEs leisten können, vor allem, wenn Sie mehrere CTEs auf unterschiedliche Weise kombinieren. Das ist kein einfaches Konzept und erfordert eine Menge Übung.

Anstatt sich eigene Daten und Szenarien auszudenken, sollten Sie unseren interaktiven Kurs über Common Table Expressions besuchen! Er bietet Ihnen all das und beseitigt mit über 100 praktischen Übungen alle Sorgen, die Sie beim Üben von CTEs haben!