Was ist ein CTE?

Nachdem Sie Anweisungen wie SELECT, DELETE, INSERT und GROUP BY beherrschen, suchen Sie vielleicht nach Möglichkeiten, die Wartbarkeit, Reproduzierbarkeit und Lesbarkeit von Code zu verbessern. An diesem Punkt werden Sie wahrscheinlich damit beginnen, sich mit modernen SQL-Konzepten vertraut zu machen, die in den frühen 2000er Jahren eingeführt wurden. Eine dieser SQL-Techniken ist die CTE? (common table expression) - eine temporäre benannte Ergebnismenge. In diesem Artikel erfahren Sie, was ein CTE ist und wie Sie es verwenden können, um die Wartung und Lesbarkeit Ihres Codes zu verbessern.

CTE: Definition und Grundsyntax

Ein gemeinsamer Tabellenausdruck (common table expression, CTE) ist eine temporäre benannte Ergebnismenge, die aus einer einfachen SQL-Anweisung erstellt wird und in nachfolgenden SELECT, DELETE, INSERT oder UPDATE Anweisungen verwendet werden kann.

Lassen Sie uns mit einem Beispiel beginnen. Betrachten Sie die folgenden zwei Tabellen:

• job_offersEine Tabelle, die Data-Science-Rollen enthält, mit Gehältern nach Stufe und Standort in Großbritannien.

  role	location	level	salary
  data scientist	London	entry	45000
  data scientist	outside London	entry	34000
  data scientist	London	mid	65000
  data scientist	outside London	mid	60000
  data scientist	London	tech lead	95000
  data scientist	outside London	tech lead	73000
  data scientist	London	director	140000
  data scientist	outside London	director	101000
  quantitative analyst	London	entry	55000
  quantitative analyst	outside London	entry	43000
  quantitative analyst	London	mid	83000
  quantitative analyst	outside London	mid	66000
  quantitative analyst	London	tech lead	100000
  quantitative analyst	outside London	tech lead	72000
  quantitative analyst	London	director	155000
  quantitative analyst	outside London	director	120000
  machine learning engineer	London	entry	44000
  machine learning engineer	outside London	entry	36000
  machine learning engineer	London	mid	67000
  machine learning engineer	outside London	mid	58000
  machine learning engineer	London	tech lead	95000
  machine learning engineer	outside London	tech lead	84000

• employee_occupationEine Tabelle, die 5 Mitarbeiter von Unternehmen X enthält, mit ihren jeweiligen Berufen und Arbeitsorten.

  name	role	location
  Tim Smith	data scientist	London
  Joana Lo	quantitative analyst	outside London
  Ed Merith	machine learning engineer	London
  Maria Soldini	machine learning engineer	outside London
  Tina Morito	quantitative analyst	outside London

Stellen Sie sich vor, Sie möchten alle Mitarbeiter mit dem Durchschnittsgehalt auflisten, das ihrer spezifischen Rolle entspricht (Spalte role). Dazu müssten Sie folgendes tun:

1. Berechnen Sie das Durchschnittsgehalt für jede Rolle. Hierfür können Sie die job_offers Tabelle verwenden.
2. Führen Sie die aggregierten Werte mit der employee_occupation Tabelle zusammen (d. h. verbinden Sie sie miteinander).

Hier ist das CTE für die Berechnung und Anzeige der gewünschten Ausgabe:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
)
SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN average_salary b 
ON a.role = b.role;

Der Code sieht folgendermaßen aus:

1. Die CTE-Definition beginnt mit einem speziellen Schlüsselwort WITH.
2. Nach der WITH wird dem CTE ein Name gegeben. In unserem Beispiel lautet der CTE-Name average_salary.
3. Auf den CTE-Namen folgt ein spezielles Schlüsselwort AS.
4. Die Anweisung SELECT befindet sich innerhalb der Klammern, deren Ergebnismenge als CTE gespeichert wird. In unserem Beispiel wird die temporäre Ergebnismenge average_salary mit einer SELECT erstellt.
5. Da Sie nun eine CTE haben, können Sie sie in einer nachfolgenden SQL-Anweisung verwenden, indem Sie auf sie wie auf jede andere Tabelle verweisen. Hier verwenden wir unsere temporäre Ergebnismenge in einer JOIN -Anweisung. Es folgt die Hauptabfrage, in der die CTE average_salary verwendet wird.
6. Die temporäre Ergebnismenge average_salary existiert nur innerhalb des Bereichs der Anweisung, die unmittelbar auf die WITH -Klausel folgt. In unserem Beispiel bedeutet dies, dass average_salary automatisch verschwindet, nachdem die Hauptabfrage ausgeführt wurde, und nirgendwo sonst im Code verwendet werden kann. Das ist so, als ob Sie eine Tabelle erstellen, sie in einer SQL-Anweisung verwenden und sie dann aus der Datenbank löschen oder löschen lassen.

Das Ergebnis ist, dass SQL für jeden Mitarbeiter das durchschnittliche Gehalt für seine Rolle ausgibt:

Die grundlegende Syntax für die Verwendung von CTEs sieht wie folgt aus:

Wie Sie in der Abbildung sehen können, definieren wir eine temporäre Ergebnismenge (in unserem Beispiel, average_salary), die wir dann in derselben Anweisung verwenden (in unserem Beispiel, average_salary wird in der Anweisung JOIN verwendet). Es gibt einen großartigen Kurs, Rekursive Abfragen, angeboten von LearnSQL.deangeboten wird; ich empfehle, damit zu beginnen, wenn Sie mehr über die CTE-Syntax erfahren möchten.

Unterabfragen vs. CTEs

Sie haben vielleicht bemerkt, dass unser letztes Beispiel mit einer Unterabfrage wie dieser geschrieben werden kann:

SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN 
  (SELECT role, avg(salary) AS avg_salary 
   FROM job_offers 
   GROUP BY role) b
ON a.role = b.role;

Obwohl sie anders geschrieben ist, liefert sie genau die gleiche Ausgabe wie unser CTE-Code von vorhin. Dieser Code mit einer Unterabfrage funktioniert folgendermaßen:

• Innerhalb der Anweisung JOIN haben wir eine Unterabfrage in Klammern: "select role, avg(salary) as avg_salary from job_offers group by role" ist eine Unterabfrage.
• Die SQL-Engine führt zuerst diese Subquery aus und führt dann die Verknüpfung mit dem von der Subquery zurückgegebenen Ergebnis durch.

Obwohl eine Subquery das gleiche Ergebnis liefert wie eine Anweisung, die eine CTE verwendet, empfehle ich in einem Fall wie diesem immer CTEs gegenüber Subqueries. Warum? Nun, sie machen den Code lesbarer und verständlicher. Lange und komplizierte Abfragen können viele, viele Unterabfragen haben, die schnell schwer zu lesen und zu verstehen sind.

Mehrere CTEs in einer Anweisung

Bisher haben wir nur eine temporäre Ergebnismenge in einer WITH Klausel verwendet. Was aber, wenn wir mehrere CTEs in einer einzigen Anweisung erstellen müssen? Das kann passieren, wenn Sie eine SQL-Anweisung mit vielen Unterabfragen umschreiben müssen. Nun, das können Sie tun - es ist möglich, mehrere CTEs in einer einzigen SQL-Anweisung zu haben.

Lassen Sie uns ein Beispiel mit zwei Unterabfragen durchgehen. Stellen Sie sich vor, dass Sie zusätzlich zu den Unterschieden nach Rolle auch die Gehaltsunterschiede nach Standort feststellen. Sie möchten in Ihrer Ausgabe zusätzliche Informationen anzeigen, nämlich das Durchschnittsgehalt nach Rolle und Standort, und diese mit Informationen auf Mitarbeiterebene darstellen.

In diesem Fall müssen Sie zusätzlich zu einer Unterabfrage wie der folgenden, die das Durchschnittsgehalt nach Rolle berechnet

SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role

müssen Sie eine weitere Unterabfrage wie die folgende schreiben:

SELECT role, location, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role, location

Mit diesen Unterabfragen sieht Ihre endgültige SELECT wie folgt aus:

SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT join 
  (SELECT role, avg(salary) as avg_salary 
   FROM job_offers 
   GROUP BY role) b 
ON a.role = b.role
LEFT JOIN 
  (SELECT role, location, avg(salary) AS avg_salary_additional 
   FROM job_offers 
   GROUP BY role, location) c
ON a.role = c.role AND a.location = c.location;

Die endgültige Abfrage sieht jetzt viel komplizierter aus; sie ist schwerer zu verstehen.

Hier sehen Sie, wie die gleiche Abfrage unter Verwendung von CTEs aussieht:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
average_salary_additional AS (
  SELECT 
    role, location, avg(salary) AS avg_salary_additional 
  FROM job_offers 
  GROUP BY role, location
)
SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT JOIN average_salary b 
  ON a.role = b.role
LEFT JOIN average_salary_additional c 
  ON a.role = c.role and a.location = c.location;

Sieht besser aus, oder? Hier haben wir zwei CTEs definiert. In diesem Code geschieht Folgendes:

• Zwei CTEs werden innerhalb einer einzigen WITH -Klausel definiert und verwendet: average_salary und average_salary_additional. Sie werden innerhalb derselben WITH-Anweisung durch ein Komma getrennt. Jede Unterabfrage wird separat benannt, was die Lesbarkeit der endgültigen SELECT erhöht.
• Die erste SQL CTE (average_salary) speichert das Durchschnittsgehalt nach Rolle. Die zweite CTE (average_salaries_additional) speichert das Durchschnittsgehalt für jede Kombination von Rolle und Standort in der Tabelle job_offers.

• Sobald sie definiert sind, werden beide CTEs in der Hauptabfrage in einer einzigen Anweisung verwendet. Das Ergebnis zeigt alle Durchschnittswerte - den Durchschnitt nach Rolle und den Durchschnitt für jede Kombination von Rolle und Standort - mit den Details jedes Mitarbeiters an:

  name	role	location	avg_salary	avg_salary_additional
  Tim Smith	data scientist	London	76625	86250
  Ed Merith	machine learning engineer	London	64000	68666.6667
  Maria Soldini	machine learning engineer	outside London	64000	59333.3333
  Joana Lo	quantitative analyst	outside London	86750	75250
  Tina Morito	quantitative analyst	outside London	86750	75250

• Sobald diese einzelne SQL-Anweisung ausgeführt und das Ergebnis auf dem Bildschirm angezeigt wird, werden beide CTEs verworfen und stehen für die weitere Verwendung im Code nicht mehr zur Verfügung.

Das gleiche Ergebnis kann mit Fenster-SQL-Funktionen und nur einer CTE erzielt werden, aber wir haben hier mehrere gemeinsame Tabellenausdrücke verwendet. Dies ist eine gute Illustration dafür, wie zwei CTEs in einer Abfrage verwendet werden können.

Sie haben vielleicht bemerkt, dass jede CTE in unserem Beispiel eine SQL-Tabelle namens job_offers. Natürlich kann jede CTE verschiedene Tabellen in der Datenbank lesen oder sogar eine andere CTE, die in derselben Anweisung definiert ist. Das heißt, bei der Arbeit mit CTEs ist eine Verschachtelung erlaubt - ein CTE kann ein anderes CTE lesen oder referenzieren. Die einzige Voraussetzung ist, dass sie in derselben WITH Abfrage definiert sind.

Hier ist ein Beispiel mit verschachtelten CTEs:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

In diesem Code:

• Wir haben zwei CTEs definiert: avg_salary_per_role und min_avg_salary. Die erste CTE (avg_salary_per_role) berechnet das Durchschnittsgehalt nach Rolle. Die zweite CTE (min_avg_salary) liest das erste CTE (avg_salary_per_role) und berechnet das Minimum der Durchschnittsgehälter auf Rollenebene. Die Durchschnittsgehälter wurden von der ersten CTE berechnet.

• Die Hauptabfrage verwendet beide CTEs in einer JOIN -Anweisung und gibt die Rolle mit dem niedrigsten Durchschnittsgehalt unter allen Rollen zurück:

  role	avg_salary	min_avg_salary
  machine learning engineer	64000	64000

• Sobald diese SQL-Anweisung ausgeführt und das Ergebnis auf dem Bildschirm angezeigt wird, werden beide CTEs gelöscht und stehen nicht mehr für die weitere Verwendung im Code zur Verfügung.

Tabellenerstellung vs. CTEs

Wir haben gesehen, dass die Verwendung von CTEs eine gute Alternative zu Unterabfragen ist. Es ist auch eine gute Alternative zur Erstellung einer echten Tabelle.

Entwickler erstellen oft temporäre Tabellen in der Datenbank, verwenden sie in der nächsten Abfrage und lassen sie danach fallen. Dieser Ansatz kann durch die Verwendung von CTEs ersetzt werden.

Warum erwähne ich das? Bevor ich angefangen habe, mit CTEs zu arbeiten, habe ich genau das getan, was ich oben beschrieben habe.

Nehmen wir eine Abfrage aus unserem letzten Beispiel, die so aussieht:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Ich hätte sie wie folgt geschrieben:

CREATE TABLE avg_salary_per_role AS
SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role;

CREATE TABLE min_avg_salary AS
SELECT min(avg_salary) AS min_avg_salary 
FROM avg_salary_per_role;

SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

DROP TABLE avg_salary_per_role;
DROP TABLE min_avg_salary;

Obwohl beide Lösungen das gleiche Ergebnis liefern, ist es besser, CTEs zu verwenden, damit Sie sich nicht um Metadaten kümmern müssen. Sie brauchen sich keine Tabellenabbrüche zu merken, weil die SQL-Engine dies mit CTEs für Sie erledigt. Toll, nicht wahr?

Nicht rekursive und rekursive CTEs

Bisher haben wir die so genannten "nicht-rekursiven" CTEs besprochen. Im Allgemeinen unterteilen wir CTEs jedoch in zwei Haupttypen:

• Nicht-rekursive CTEs
• Rekursive CTEs

Ein rekursiver CTE ist eine spezielle Form von verschachtelten CTEs. Eine rekursive CTE ist eine CTE, die sich selbst innerhalb desselben gemeinsamen Tabellenausdrucks referenziert.

So sieht ein rekursiver CTE aus:

WITH recursive_cte (column_list)
AS
(
   -----(in this query we are referencing recursive_cte)----
)

SELECT *
FROM recursive_cte

Rekursive CTEs arbeiten mit hierarchischen Daten, daher müssen die Daten richtig definiert sein. Der Code wird so lange ausgeführt, bis alle Hierarchieebenen ausgeschöpft sind.

Ich weiß noch, wie ich meine erste rekursive CTE geschrieben habe. Ich musste grundlegende Informationen über die Organisationsstruktur in einer Bank extrahieren. Eine rekursive CTE ist hier angebracht, weil die Organisationsstruktur in einer Bank hierarchisch ist (d. h. eine Baumstruktur hat):

• Es gibt eine Haupteinheit, zu der alle Einheiten gehören (der sogenannte "Anker"). In einer Bank ist dies der Aufsichtsrat.
• Es gibt Organisationseinheiten, für die eine klare Hierarchie definiert ist. Zum Beispiel ist die Datenanalysegruppe zusammen mit anderen Einheiten der IT-Abteilung unterstellt, die IT-Abteilung ist ein Teil der Geschäftsunterstützungseinheit, und die Geschäftsunterstützungseinheit ist dem Aufsichtsrat unterstellt, der die Haupteinheit (Anker) ist.

Da ich die Anzahl der Mitarbeiter für jede Einheit (Anker, übergeordnete Einheit, untergeordnete Einheit) benötigte, war der rekursive CTE der richtige Weg. Ich weiß noch, wie glücklich ich war, als mein Skript funktionierte - es hatte nur eine SQL-Anweisung! Ich hätte für jede Abteilung, für die ich die Informationen benötigte, eine eigene Abfrage schreiben können. Da ich jedoch eine rekursive CTE verwendete, erhielt ich die Ergebnisse, ohne so viele Abfragen schreiben zu müssen.

Wir werden hier nicht näher auf rekursive CTEs eingehen; weitere Details finden Sie in unserem interaktiven Online-Kurs zu LearnSQL.de der dieses Thema behandelt. Wenn Sie CTEs in einem echten Geschäftsbeispiel sehen möchten, empfehle ich Erstellen einfacher SQL-Berichte-Dort wird die mehrstufige Aggregation, die häufig in Geschäftsberichten erforderlich ist, mit Hilfe von CTEs demonstriert. Darüber hinaus gibt es mehrere gute Artikel zu verschiedenen CTE-Themen mit Beispielen; Sie finden sie hier und hier.

Lesbarkeit des Codes und CTEs

Zusammenfassend kann man sagen, dass es einige Gründe gibt, warum CTEs wichtig sind und warum und wann Sie CTEs verwenden sollten:

• Um Unterabfragen in SQL-Anweisungenzu vermeiden. Dies bezieht sich speziell auf Situationen, in denen wir mehrere Tabellen in einer Hauptabfrage verbinden und eine oder mehrere dieser Tabellen eine Unterabfrage sind. In diesem Fall ist eine CTE eine hervorragende Alternative, die die Lesbarkeit Ihres Codes verbessert.
• Sie vermeiden die unnötige Erstellung von Tabellen und Ansichten in Ihrer Datenbank. CTEs helfen, unnötige Metadaten zu vermeiden. Es ist üblich, Tabellen zu erstellen, die in Joins verwendet werden, damit Sie keine Unterabfragen schreiben müssen. Anstatt jedoch eine "echte" Tabelle zu erstellen, deren Metadaten in einer Datenbank gespeichert sind, können Sie alternativ eine CTE verwenden. Da es sich um eine temporäre benannte Ergebnismenge handelt, wird sie nicht in einer Datenbank gespeichert oder später in Ihrem Code verwendet, und Ihr Code ist genauso lesbar wie der Code, der echte Tabellen erstellt.
• Sie machen Ihren Code leichter verständlich und wartbar. CTEs sind besonders nützlich bei langen Abfragen. Sie sind ein sehr effektives Mittel, um kompliziertere Abfragen zu verwalten. Jede CTE hat einen Namen, und intuitive Namen können die Lesbarkeit und die Wartung des Codes enorm erleichtern. Mit intuitiven Namen für Codeabschnitte ist es viel einfacher herauszufinden, was im Code vor sich geht, da Sie anhand des Namens schnell verstehen können, worum es in dem Codeabschnitt geht.

Wir haben die grundlegende Syntax und mehrere Beispiele für die Definition und Verwendung von CTEs durchgespielt. Zum besseren Verständnis und zum Üben empfehle ich einen Kurs von LearnSQL.de namens Rekursive Abfragen. In diesem Kurs bringen wir Ihnen interaktiv bei, wie Sie CTEs (rekursiv und nicht rekursiv) in Ihrer täglichen Arbeit verwenden können. Mehr CTEs in der Praxis finden Sie auch in einem interaktiven Kurs mit dem Titel Creating Basic SQL reports, in dem Sie lernen, wie man CTEs in mehrstufigen Aggregationen (wie bei der Erstellung von Geschäftsberichten) verwendet. Sobald Sie die CTEs beherrschen, werden Sie sich darüber freuen, wie viel besser Ihre SQL-Skripte aussehen!