Wie man 2 CTEs in einer einzigen SQL-Abfrage verwendet

Haben Sie sich jemals gefragt, wie Sie mehrere CTEs in einer SQL-Abfrage verwenden können? Lesen Sie diesen Artikel und erfahren Sie mehr über rekursive CTEs.

Nachdem Sie gemeinsame Tabellenausdrücke oder CTEs kennengelernt haben, stellt sich natürlich die Frage : "Kann ich mehrere CTEs in einer Abfrage verwenden?" Ja, das können Sie! Und Sie können es ganz einfach tun, vor allem, wenn Sie bereits einige Grundkenntnisse über CTEs haben. Unabhängig davon, ob Sie ein wenig über CTEs wissen oder ob Sie ganz neu in der CTE-Welt sind, ist es immer ein guter Anfang, zu lesen, was ein CTE ist.

Was Sie in diesem Artikel lernen, können Sie im Kurs LearnSQL.de Rekursive Abfragen üben, der im August unser Kurs des Monats war.

Zu Beginn erkläre ich Ihnen, wie Sie zwei CTEs in einer Abfrage verwenden können. Dann zeige ich Ihnen, wie Sie CTEs verwenden, bei denen sich die zweite CTE auf die erste bezieht. Um diesen Artikel aufzupeppen, zeige ich Ihnen zum Schluss zwei Abfragen mit CTEs, von denen eine rekursiv ist.

2 CTEs, 1 SQL-Abfrage, 0 Probleme

Nehmen wir an, es gibt eine Tabelle namens logins, in der login Daten. Sie enthält die Spalten:

• id - Die ID der Anmeldung.
• username - Der Benutzer, der sich angemeldet hat.
• login_date - das Datum dieser Anmeldung.
• login_start - Wann sich der Benutzer angemeldet hat.
• login_end - Wann sich der Benutzer abgemeldet hat.

Wenn Sie sich die Tabelle ansehen, werden Sie feststellen, dass jeder Benutzername mindestens einmal vorkommt.

Wenn Ihre Aufgabe darin besteht, die durchschnittliche Zeit (in Minuten) zu berechnen, die jeder Benutzer eingeloggt war, wie würden Sie das mit CTEs machen?

Sie benötigen zwei CTEs, wobei der Code wie folgt aussehen könnte:

WITH distinct_user AS (
	SELECT DISTINCT username
FROM logins),

minutes_logged AS (
	SELECT	username,
			DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins)

SELECT	u.username,
		AVG (minutes) AS avg_time_logged
FROM distinct_user AS u 
JOIN minutes_logged AS m 
ON u.username = m.username
GROUP BY u.username;

Zunächst möchte ich erwähnen, dass Sie eindeutige Benutzernamen benötigen, damit dies funktioniert. Beginnen wir also mit dem Schreiben der ersten CTE! Das ist nichts Ungewöhnliches - wie bei jeder CTE definieren Sie sie über WITH; sie heißen nicht umsonst WITH abfragen!

Was folgt, ist der CTE-Name; in diesem Fall heißt das erste CTE distinct_user. Es folgt das Schlüsselwort AS, und dann definieren Sie einfach die reguläre Anweisung SELECT innerhalb der Klammern. Diese SELECT Anweisung wählt einfach eindeutige Benutzernamen aus der Tabelle logins.

Sobald Sie eindeutige Benutzernamen haben, müssen Sie berechnen, wie lange der Benutzer eingeloggt war. Dies ist der kritische Teil - das Schreiben der zweiten CTE. Meine zweite CTE heißt minutes_logged.

Sie werden feststellen, dass vor der zweiten CTE kein WITH steht. Dies ist äußerst wichtig! Nachdem Sie die erste CTE definiert haben, wird sie von der zweiten nur durch das Komma getrennt, d. h. Sie schreiben WITH nur einmal. Danach ist es egal, wie viele CTEs Sie definieren; wichtig ist nur, dass Sie sie durch Komma trennen und jedes CTE mit seinem Namen beginnen.

Lassen Sie uns nun analysieren, was die minutes_logged CTE tut. Sie entnimmt zunächst die Spalte username aus der Tabelle logins. Dann berechnet sie die Differenz zwischen login_start und login_end in Minuten. Dieser neue Wert wird in der Spalte minutes angezeigt.

Sie müssen die durchschnittliche Zeit (in Minuten) berechnen, die jeder Benutzer eingeloggt war. Für diesen Teil der Berechnung habe ich die äußere Abfrage verwendet. Sie wählt den Benutzernamen aus der CTE distinct_user aus und berechnet dann die durchschnittlichen eingeloggten Minuten anhand der Spalte minutes aus der CTE minutes_logged.

Diese beiden CTEs werden über die Spalte username verbunden. Schließlich wird das Ergebnis nach derselben Spalte gruppiert, da wir das Ergebnis auf Benutzerebene haben wollen.

Aber kann ich eine CTE innerhalb einer anderen CTE verwenden?

Eine einfache Frage verdient eine einfache Antwort: Ja, das können Sie. Da Sie nun wissen, wie man mehrere CTEs verwendet, ist das Schreiben einer CTE, die auf eine andere CTE verweist, nur eine Abwandlung dessen, was Sie gelernt haben. Da es sich um eine Abwandlung handelt, halte ich es für das Beste, Ihnen die Vorgehensweise anhand eines Beispiels zu zeigen, mit dem Sie bereits vertraut sind.

Kehren wir zum vorherigen Beispiel zurück. Ich werde den Code ein wenig abändern, um Ihnen zu zeigen, wie Sie eine CTE schreiben, die auf die erste CTE verweist. Dies ist die neue Version unseres früheren Codes:

WITH difference AS (
	SELECT 	username,
		 	DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins),

average_logged AS (
SELECT	username,
			AVG (minutes) AS average
FROM difference
GROUP BY username)

SELECT DISTINCT 		username,
				average
FROM average_logged;

Die Logik ist dieselbe wie im ersten Beispiel, nur dass die Schritte in der Reihenfolge different stehen. Die erste CTE, Differenz, berechnet die mit jeder Anmeldung verbrachten Minuten; dies ist die gleiche wie im ersten Beispiel. Jetzt, da ich die Minuten habe, muss ich die durchschnittliche Zeit (in Minuten) berechnen, die jeder Benutzer verbracht hat. Dafür werde ich den CTE-Durchschnitt verwenden. Wie Sie sich erinnern, setzen wir ein Komma zwischen die beiden CTEs und beginnen das zweite CTE mit seinem Namen - Sie brauchen nicht noch einmal WITH zu verwenden! Diese zweite CTE berechnet die durchschnittliche Zeit für jede Anmeldung; dazu verwendet sie die Spalte minutes aus der ersten CTE und speichert das Ergebnis in der Spalte average.

Um die CTE difference in der zweiten CTE zu referenzieren, behandeln Sie sie wie eine Tabelle: FROM difference. Schließlich gruppieren Sie das Ergebnis nach dem Benutzernamen, da Sie nicht den Durchschnitt für alle Benutzer haben wollen.

Die äußere Abfrage wählt dann einfach die Spalten username und Durchschnitt aus der CTE average_logged aus. Auf diese Weise erhalten Sie das gleiche Ergebnis wie im ersten Beispiel:

Wenn Ihnen diese Beispiele nicht ausreichen, finden Sie in diesem Artikel weitere Beispiele zum Üben. Dazu gehört auch die Verwendung von mehr als einem CTE.

Verwendung rekursiver CTEs mit nicht rekursiven CTEs

Es gibt einen Grund, warum CTEs in unserem Kurs gelehrt werden Rekursive Abfragen Kurs gelehrt werden. Weil CTEs auch rekursiv sein können. Dies ist der komplizierteste Teil beim Erlernen von CTEs. (Ich gehe davon aus, dass Sie bereits wissen, was eine rekursive CTE ist und wie man sie schreibt. Falls nicht, finden Sie eine Einführung in Lange SQL-Abfrage vs. rekursive SQL-Abfrage und Machen Sie es in SQL: Rekursiver SQL-Baum-Traversal für ein ausführliches Beispiel. ) Vielleicht wussten Sie jedoch nicht, dass Sie mehr als eine CTE verwenden können, selbst wenn eine davon rekursiv ist. Schauen wir uns an, wie das geht.

Stellen wir uns vor, dass Sie mit Ihren Freunden ein Unternehmen kaufen wollen. Es gibt vier Optionen für die Investition, die jeweils einen unterschiedlichen Geldbetrag erfordern. Sie befinden sich noch in frühen Gesprächen. Sie sind sich nicht sicher, wie viele Freunde sich beteiligen werden; ihre Beteiligung hängt von dem Geldbetrag ab, den sie zu investieren haben. Dieser Betrag hängt von der Gesamtzahl der beteiligten Investoren und der gewählten Investitionsoption ab.

Um ihnen die Entscheidung zu erleichtern, haben Sie beschlossen, den erforderlichen Betrag pro Investor für alle vier Investitionsoptionen mit einer Anzahl von Investoren von eins bis zehn zu berechnen.

In diesem Beispiel haben wir zunächst ein nicht rekursives CTE, gefolgt von einem rekursiven CTE:

WITH RECURSIVE investment AS (
	SELECT amount 5897645 AS investment_amount
	UNION 
SELECT 4536841 AS investment_amount
	UNION 
SELECT 3852457 AS investment_amount
	UNION 
SELECT 3452115 AS investment_amount
),

per_investor AS (
	SELECT	0 AS investors_number,
			0 AS amount,
			0 AS individual_amount
	UNION 
	SELECT	investors_number + 1,
			investment_amount,
			investment_amount/(investors_number + 1)
	FROM investment, per_investor
	WHERE investors_number < 10)

SELECT *
FROM per_investor
ORDER BY  amount, investors_number;

Der Hauptunterschied ist von Anfang an offensichtlich! Wenn Sie rekursive Abfragen schreiben, müssen Sie Ihren Code mit WITH RECURSIVE beginnen. Wie Sie feststellen werden, müssen Sie nicht mit dem rekursiven CTE selbst beginnen; in diesem Beispiel steht ein nicht rekursiver CTE an erster Stelle, auch wenn wir WITH RECURSIVE unmittelbar vor seiner Definition verwenden. Sie können CTEs in beliebiger Reihenfolge schreiben; der rekursive CTE kann an erster oder letzter Stelle stehen. Es ist jedoch wichtig, sich daran zu erinnern, dass WITH RECURSIVE zwingend erforderlich ist, wenn Sie mindestens eine rekursive Abfrage haben wollen.

Im aktuellen Beispiel heißt meine erste (nicht rekursive) CTE Investition. Da ich nicht über die Tabelle mit allen Investitionsbeträgen verfüge, habe ich beschlossen, die CTE als temporäre Tabelle zu verwenden. Wie Sie sehen können, sind die möglichen Investitionsbeträge wie folgt:

• 5,897,645
• 4,536,841
• 3,852,457
• 3,452,115

Durch die Verwendung von UNION mit SELECT im ersten CTE wird praktisch eine Tabelle erstellt, die diese vier Investitionsmöglichkeiten enthält. Sie werden in der Spalte investment_amount angezeigt. Die resultierende Tabelle für diese CTE lautet:

Die zweite CTE ist rekursiv. Dies ändert jedoch nichts im Vergleich zum Schreiben von zwei nicht rekursiven CTEs: Das Komma trennt die CTEs wieder.

Analysieren wir das zweite CTE ein wenig. Die erste Anweisung SELECT definiert drei Spalten, die den Wert 0 haben: investors_number, Betrag und individual_amount. Wie gesagt, die rekursive CTE bezieht sich auf sich selbst, d. h. der Operator UNION ist erforderlich.

Auf den UNION-Operator folgen eine oder mehrere SELECT Anweisungen, die die gewünschte Operation an der ersten SELECT Anweisung durchführen. Das bedeutet, dass der Spalte investors_number eine 1 hinzugefügt wird. Dann wird investment_amount aus dem ersten CTE in die Spalte Betrag eingesetzt. Der Investitionsbetrag wird durch die Anzahl der beteiligten Investoren geteilt; das Ergebnis wird in der Spalte individual_amount angezeigt. Diese Operation wird für alle vier Anlagebeträge durchgeführt, bis zehn Anleger erreicht sind.

Die äußere Abfrage wählt einfach alle Daten aus der CTE per_investor aus, wobei die Daten nach den Spalten Betrag und investors_number geordnet sind. Führen Sie diese Abfrage aus und genießen Sie all die Möglichkeiten, die Sie berechnet haben:

Ziemlich beeindruckend, nicht wahr?

Die Kombination von zwei oder mehr CTEs funktioniert wirklich

SQL Common Table Expressions sind ein mächtiges Werkzeug. Sie sind hilfreich, wenn Sie möchten, dass Ihr Code besser lesbar ist, aber ihr Wert ist nicht nur kosmetisch; ihre wirklichen Möglichkeiten zeigen sich, wenn Sie wissen, wie Sie mehrere CTEs in einer Abfrage verwenden oder sogar eine rekursive CTE in SQL schreiben können.

Wie Sie gesehen haben, ist es nicht schwer, zwei oder mehr CTEs zu kombinieren. Sobald Sie das wissen, eröffnen sich Ihnen die Möglichkeiten, CTEs in verschiedenen Situationen zu verwenden. Jetzt liegt es an Ihnen, Fälle zu finden, in denen Sie CTEs verwenden müssen. Und üben, üben, üben! Es ist immer ratsam, dafür unseren Rekursive Abfragen Kurs zu benutzen. Er bietet Ihnen Tabellen zum Üben, so dass Sie sich nicht selbst um die Erstellung von Szenarien kümmern müssen.

Teilen Sie uns Ihre Erfahrungen mit den SQL CTEs im Kommentarbereich mit.