5 praktische Beispiele für die WITH-Klausel von SQL

Diese fünf praktischen Anwendungsfälle werden Sie dazu veranlassen, die SQL WITH-Klausel täglich zu verwenden .

Die WITH Klausel kann Ihnen helfen, lesbare SQL-Abfragen zu schreiben und komplexe Berechnungen in logische Schritte zu unterteilen. Sie wurde zu SQL hinzugefügt, um komplizierte lange Abfragen zu vereinfachen. In diesem Artikel zeigen wir Ihnen 5 praktische Beispiele für die WITH Klausel und erklären Ihnen, wie Sie mit ihr SQL-Abfragen lesbarer machen können.

Die WITH Klausel wird auch als Common Table Expression (CTE) bezeichnet. Der Umgang damit gehört nicht zu den SQL-Grundkenntnissen, daher empfehlen wir, WITH in einem strukturierten interaktiven Kurs zu üben, z. B. in unserem Rekursive Abfragen Kurs. In über 100 Übungen lernen Sie die grundlegende Syntax von CTEs sowie die fortgeschrittenen Konzepte von verschachtelten und rekursiven CTEs.

Was ist die WITH-Klausel in SQL?

Der andere Name der WITH Klausel, Common Table Expression, gibt einen Hinweis darauf, was sie tut. Mit einer WITH -Klausel können Sie eine SELECT -Anweisung erstellen, die ein temporäres Ergebnis zurückgibt; Sie können dieses Ergebnis benennen und in einer anderen Abfrage darauf verweisen. Im Grunde handelt es sich um eine benannte Unterabfrage, die jedoch rekursiv sein kann. Im Folgenden wird eine WITH-Klausel mit einer Unterabfrage verglichen.

Die CTE kann nicht ohne die Hauptabfrage ausgeführt werden, daher muss ihr eine reguläre Abfrage folgen. Diese Abfrage ist in der Regel ebenfalls eine SELECT-Anweisung, kann aber INSERT, UPDATE oder DELETE lauten.

Stellen Sie sich eine CTE als eine temporäre Tabelle mit einer Abfrage vor, die immer ausgeführt werden muss, damit die Tabelle verwendet werden kann. Da es sich um eine Art von Tabelle handelt, können Sie in der FROM -Klausel auf die CTE wie auf eine normale Tabelle verweisen.

Grundlegende WITH-Klausel-Syntax

Was wir oben erklärt haben, kann als SQL-Code dargestellt werden:

WITH cte AS (
  SELECT …
  FROM table
)

SELECT …
FROM cte;

Werfen wir einen Blick auf die wichtigsten Teile der Syntax. Der CTE wird immer, ohne Ausnahme, durch die WITH Klausel eingeleitet. Danach folgt der CTE-Name, der im obigen generischen Beispiel cte lautet. Nach dem CTE-Namen folgt das Schlüsselwort AS. Was in den Klammern folgt, ist die Definition des CTE. In einem sehr einfachen Beispiel ist dies nur eine SELECT Anweisung. Schließlich folgt die Hauptabfrage (wie bei normalen Unterabfragen), die auf das CTE verweist.

Wohlgemerkt, das ist die grundlegende Syntax. Wir werden sie in den Beispielen wieder aufgreifen. Dabei werden Sie auch sehen, wie diese grundlegende Syntax durch das Schreiben mehrerer oder verschachtelter CTEs erweitert werden kann.

Beispiele 1 und 2: Gehaltsinformationen

Beispiel Daten

Wir verwenden die Tabelle employees in den ersten beiden Beispielen. Sie hat die folgenden Spalten:

• id - Die ID des Mitarbeiters.
• first_name - Der Vorname des Mitarbeiters.
• last_name - Der Nachname des Mitarbeiters.
• salary - Das Gehalt des Mitarbeiters.
• department - Die Abteilung des Angestellten.

Hier sind die Daten:

Und hier ist ein Link zu einer Abfrage, die Sie ausführen können, um die Tabelle zu erstellen:

Beispiel 1: Zeigen Sie, wie das Gehalt jedes Mitarbeiters im Vergleich zum Durchschnitt des Unternehmens ist

Um diese Aufgabe zu lösen, müssen Sie alle Daten der Tabelle anzeigen employees. Außerdem müssen Sie das Durchschnittsgehalt des Unternehmens und dann die Differenz zu den Gehältern der einzelnen Mitarbeiter anzeigen.

Hier ist die Lösung:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
)
	
SELECT id,
	 first_name,
	 last_name,
	 salary,
	 department,
	 average_company_salary,
	 salary - average_company_salary  AS salary_difference
FROM employees, avg_total_salary;

Starten Sie zunächst die CTE mit der Klausel WITH. Der Name der CTE ("avg_total_salary") kommt danach. Öffnen Sie die Klammern nach AS, und schreiben Sie die reguläre Anweisung SELECT. Sie berechnet das Durchschnittsgehalt des Unternehmens.

Um die CTE-Ausgabe zu verwenden, schreiben Sie eine weitere SELECT -Anweisung als Hauptabfrage. Beachten Sie, dass die CTE und die Hauptabfrage nur durch Klammern getrennt sind. Die neue Zeile dient nur der besseren Lesbarkeit. Die Hauptabfrage wählt alle Spalten sowohl aus der Tabelle als auch aus dem CTE aus. Die CTE und die Tabelle employees werden wie zwei normale Tabellen miteinander verbunden. Zusätzlich gibt es die berechnete Spalte salary_difference.

Die Abfrage gibt dies zurück:

Wir können die Daten aller Mitarbeiter sehen und um wie viel ihr Gehalt über oder unter dem Unternehmensdurchschnitt liegt. Zum Beispiel liegt das Gehalt von Melissa Allman um 293,48 höher als der Durchschnitt. Das Gehalt von Nina Longhetti liegt 993,20 unter dem Durchschnitt.

Beispiel 2: Zeigen Sie die Daten der Mitarbeiter zusammen mit dem durchschnittlichen Abteilungs- und dem durchschnittlichen Unternehmensgehalt an

Gehen wir noch ein wenig weiter als im vorherigen Beispiel. Diesmal müssen Sie nicht die Differenz zwischen den Gehältern berechnen. Aber Sie müssen die analytischen Daten aller Mitarbeiter anzeigen und dann das durchschnittliche Abteilungs- und Firmengehalt.

Dies können Sie erreichen, indem Sie zwei CTEs statt einer schreiben:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
),
	
avg_dpt_salary AS (
	SELECT department,
		 AVG(salary) AS average_department_salary
	FROM employees
	GROUP BY department
)
	
SELECT e.id,
	   e.first_name,
	   e.last_name,
	   e.salary,
	   average_department_salary,
	   average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

Erinnern Sie sich, dass wir erwähnt haben, dass die Basissyntax erweitert werden kann. Hier ist ein Beispiel für diese Vorgehensweise. Ja, Sie können mehrere CTEs schreiben, eine nach der anderen, und dann in der Hauptabfrage auf sie verweisen.

Schauen wir uns an, wie man das macht. Bei der ersten CTE ist alles wie gewohnt: WITH-Klausel, der CTE-Name, AS und die CTE-Definition in Klammern. Diese CTE berechnet das Durchschnittsgehalt des Unternehmens.

Die zweite CTE berechnet das Durchschnittsgehalt nach Abteilung. Aber hier ist etwas anders! Beachten Sie zwei wichtige Dinge: Es gibt keine weitere WITH-Klausel, und die CTEs sind durch ein Komma getrennt.

So schreibt man mehrere CTEs: Die WITH-Klausel wird nur vor der ersten Abfrage geschrieben (sie darf vor keiner anderen CTE erscheinen!), und die CTEs müssen durch ein Komma getrennt werden.

Unabhängig von der Anzahl der CTEs muss ihnen immer die Hauptabfrage folgen. Und zwischen der letzten CTE und der Hauptabfrage steht kein Komma!

Die Hauptabfrage verknüpft die Tabelle mit den beiden CTEs und wählt die relevanten Daten aus. Die Verknüpfung erfolgt auf dieselbe Weise wie bei regulären Tabellen: Geben Sie die Art der Verknüpfung und die Spalte an, über die die Tabellen verknüpft werden sollen.

Hier ist die Ausgabe:

Jetzt haben Sie alle Daten an einem Ort: individuelle Gehälter, Abteilungsdurchschnitt und Unternehmensdurchschnitt. Von hier aus können Sie Ihre Datenanalyse fortsetzen.

Beispiele 3 und 4: Umsatzerlöse

Beispiel Daten

In den nächsten beiden Beispielen werden wir die Tabelle revenue. Wir haben sie mit der Abfrage CREATE TABLE erstellt, die Sie hier finden. Sie hat die folgenden Spalten:

• id - Die ID des Umsatzdatensatzes.
• year - Das Jahr des Umsatzes.
• quarter - Das Quartal der Einnahme.
• revenue_amount - Der Betrag der Einnahme.

Machen Sie sich mit den unten gezeigten Daten vertraut:

Beispiel 3: Jedes Jahr mit den entsprechenden Jahreseinnahmen und Gesamteinnahmen anzeigen

Verwenden Sie die obigen Daten, um alle verfügbaren Jahre anzuzeigen. Geben Sie zu jedem Jahr den Jahresumsatz und den Gesamtumsatz des Unternehmens in allen Jahren an.

Diese Aufgabe ist ähnlich wie in Beispiel 1, aber wir verwenden eine andere Aggregatfunktion:

WITH total_revenue AS (
	SELECT SUM(revenue_amount) AS total_company_revenue
	FROM revenue
)
	
SELECT year,
	 SUM (revenue_amount) AS annual_revenue,
	 total_company_revenue
FROM revenue, total_revenue
GROUP BY year, total_company_revenue
ORDER BY year;

Das CTE verwendet die Aggregatfunktion SUM(), um den Gesamtumsatz des Unternehmens zu berechnen.

Die Hauptabfrage verknüpft die CTE mit der Tabelle revenue. Wir verwenden diese SELECT Anweisung, um das Jahr anzuzeigen, dann die jährlichen Einnahmen für jedes Jahr zu berechnen und die Gesamteinnahmen anzuzeigen.

Dies ist die Ausgabe der Abfrage:

Das Ergebnis zeigt, dass der Jahresumsatz im Jahr 2019 10.489.632,27 betrug. Im Jahr 2020 waren es 20.919.087,62 und so weiter. Wenn man alle vier Jahre zusammenzählt, ergibt sich ein Gesamtumsatz von 80.812.015,89.

Beispiel 4: Auflistung der einzelnen Quartale mit den höchsten und niedrigsten Quartalseinnahmen für das jeweilige Jahr und insgesamt

Sie müssen alle Jahre und Quartale mit den entsprechenden Einnahmen auflisten. So weit, so gut. Dann müssen Sie den niedrigsten Quartalsumsatz für dieses Jahr und den niedrigsten Quartalsumsatz für alle Jahre angeben. Als nächstes machen Sie das Gleiche für den höchsten Umsatz.

Hier ist die Lösung:

WITH yearly_min_max_quarter AS (
	SELECT year,
		   MIN(revenue_amount) AS minimum_quarterly_revenue_annual,
		   MAX(revenue_amount) AS maximum_quarterly_revenue_annual
FROM revenue
GROUP BY year),

min_max_overall AS (
	SELECT MIN(revenue_amount) AS overall_min_revenue,
	       MAX(revenue_amount) AS overall_max_revenue
FROM revenue)

SELECT r.year,
	   quarter,
	   revenue_amount,
	   minimum_quarterly_revenue_annual,
	   overall_min_revenue,
	   maximum_quarterly_revenue_annual, 
	   overall_max_revenue
FROM revenue r 
JOIN yearly_min_max_quarter ymmq 
ON r.year = ymmq.year, min_max_overall
ORDER BY year, quarter ASC;

Auch für diese Lösung sind zwei CTEs erforderlich. Wahrscheinlich wissen Sie bereits, wie man sie schreibt, aber lassen Sie uns die einzelnen Schritte erklären.

Die erste CTE ermittelt die niedrigsten und höchsten Quartalsumsätze für jedes Jahr. Verwenden Sie dazu die Funktionen MIN() und MAX() und gruppieren Sie die Daten nach Jahr.

Schreiben Sie dann die zweite CTE ohne die WITH Klausel und trennen Sie sie durch ein Komma von der ersten. Diese CTE gibt den niedrigsten und den höchsten Quartalsumsatz in allen Jahren zurück.

Die Hauptabfrage verknüpft die Tabelle revenue mit dem ersten und dann mit dem zweiten CTE. Sie zeigt die Daten aus der Tabelle und dem CTE als eine Tabelle an.

Werfen Sie einen Blick auf das Ergebnis:

Zusammen mit den Einnahmen der einzelnen Quartale haben Sie nun einige weitere wichtige Informationen. Sie wissen, dass der niedrigste Umsatz im Jahr 2019 984.157,15 betrug, und Sie können sehen, dass dies im 3. Der niedrigste Umsatz im Jahr 2020 (2.497.441,68) wurde in den ersten drei Monaten des Jahres erzielt. Sie können alle anderen Jahre auf ähnliche Weise analysieren, indem Sie die Spalte minimum_quarterly_revenue_annual betrachten.

Der Wert in der Spalte overall_min_revenue stellt den niedrigsten Umsatz aller Zeiten dar. Er ist in allen Zeilen gleich und entspricht dem Jahr 2019Q3. Die nächsten beiden Spalten sind ähnlich, zeigen aber den höchsten statt den niedrigsten Umsatz an. Mit anderen Worten: Die höchsten Einnahmen im Jahr 2019 waren 5.417.884,15, die aus Q4 stammen. Der höchste Umsatz aller Zeiten ist 12.478.945,47, der im Jahr 2021Q1 erzielt wurde.

Beispiel 5: Geleistete Arbeitsstunden

Beispiel Daten

Die Tabelle für das letzte Beispiel heißt Mitarbeiter_Stundenzettel. Sie erfasst die Arbeitszeiten der Mitarbeiter. Ihre Spalten sind selbsterklärend, also schauen wir uns nur die Daten an:

Beispiel 5: Die niedrigsten und höchsten durchschnittlichen Arbeitsstunden anzeigen

In diesem Beispiel sollen Sie zunächst die durchschnittlichen Arbeitsstunden pro Mitarbeiter ermitteln und dann nur den niedrigsten und den höchsten Durchschnitt ausgeben.

Hier ist der Code, um dieses Problem zu lösen:

WITH login_times AS (
SELECT 
id,
employee_id,
start_time,
end_time,
end_time - start_time AS working_hours
FROM employee_timesheet),

avg_login AS (
	SELECT 
employee_id,
AVG(working_hours) AS average_working_hours
FROM login_times
GROUP BY employee_id)

SELECT MIN(average_working_hours) AS min_average_working_hours,
	 MAX(average_working_hours) AS max_average_working_hours
FROM avg_login;

Auf den ersten Blick könnte dies wie eine beliebige Abfrage mit zwei CTEs aussehen. Schauen Sie genauer hin, und Sie werden sehen, dass es nicht so ist! Ja, es gibt zwei CTEs. Der Unterschied ist jedoch, dass die zweite CTE auf die erste verweist, was in den Beispielen 2 und 4 nicht der Fall war.

Dies nennt man eine verschachtelte CTE. Die erste CTE wird verwendet, um die Differenz zwischen dem Beginn und dem Ende der Anmeldung zu ermitteln; so erhalten Sie die Arbeitsstunden für jede Sitzung.

Da ein Mitarbeiter mehrere Sitzungen hat, müssen wir die durchschnittliche Sitzungsdauer ermitteln, d. h. die durchschnittlich geleisteten Arbeitsstunden. Zu diesem Zweck wird die zweite CTE verwendet. Von der Syntax her ist nichts Neues, außer dass der CTE in der FROM -Klausel auf den ersten CTE verweist.

In der Hauptabfrage führen wir dann eine so genannte mehrstufige Aggregation durch. Wir nehmen die durchschnittlichen Arbeitsstunden pro Mitarbeiter (Aggregation auf erster Ebene) und finden das Minimum und Maximum dieser Werte (Aggregation auf zweiter Ebene).

Hier ist das Ergebnis:

Das Ergebnis zeigt uns, dass die niedrigste durchschnittliche Arbeitszeit pro Arbeitnehmer 5 Stunden, 57 Minuten und 4 Sekunden beträgt. Der höchste Durchschnitt liegt bei 13:24:29.

Vorteile der SQL WITH-Klausel

Die fünf Beispiele, die wir Ihnen gezeigt haben, wurden sorgfältig ausgewählt, um die typische Verwendung und die Vorteile der WITH-Klausel zu verdeutlichen.

Der erste Vorteil wird deutlich, wenn Sie mehrere Berechnungsschritte haben, wie wir es in den obigen Beispielen gesehen haben. Mit der WITH -Klausel können Sie den Code gut organisieren und in logische Teile unterteilen.

Wenn die Berechnungen komplizierter werden, nimmt auch die Länge und Komplexität des Codes zu. Mit der WITH Klausel lässt sich das gut in den Griff bekommen. Die Codes in den obigen Beispielen mögen zwar lang erscheinen, aber sie würden deutlich länger (und weniger lesbar) werden, wenn wir Unterabfragen anstelle der WITH Klausel verwenden würden. Und wie Sie im letzten Beispiel gesehen haben, können Sie mit der WITH Klausel problemlos mehrstufige Aggregationen berechnen.

Ein weiterer Vorteil ist, dass Sie mit der WITH -Klausel rekursive Abfragen in SQL schreiben können, was eine ganz neue Welt von Möglichkeiten eröffnet.

Das Erlernen der WITH Klausel kann manchmal überwältigend sein, daher haben wir einen Leitfaden erstellt, der Ihnen helfen wird, Ihren Ansatz zu strukturieren. Viel Spaß beim Lernen, und wir sind sicher, dass die SQL WITH Klausel Ihre Bemühungen mehr als belohnen wird!