PostgreSQL CTE: Was es ist und wie man es benutzt

CTEs, oder Common Table Expressions, sind ein mächtiges PostgreSQL-Tool, das oft ignoriert wird. Dieser Artikel befasst sich mit verschiedenen PostgreSQL CTEs - einschließlich verschachtelter und rekursiver CTEs - und was Sie mit ihnen machen können.

Wenn Sie komplexe Abfragen in SQL schreiben, werden Sie bald feststellen, dass Ihr Code unübersichtlich und schwer zu lesen ist. CTEs - auch bekannt als WITH-Klauseln - sind in erster Linie eine Möglichkeit, Abfragen zu vereinfachen. Sie ermöglichen Ihnen aber auch die Verwendung von Rekursionen. Mit Hilfe der Rekursion können Sie unter anderem leicht durch hierarchische Strukturen navigieren.

PostgreSQL CTEs (oder Common Table Expressions) sind den Unterabfragen sehr ähnlich; der Unterschied besteht darin, dass CTEs benannt und am Anfang Ihres Codes definiert werden. Dadurch können Sie eine große Abfrage in kleine Abschnitte unterteilen.

In diesem Artikel werde ich mehrere Beispiele für PostgreSQL CTEs durcharbeiten. Ich gehe davon aus, dass Sie mit dem Schreiben von Abfragen in PostgreSQL bereits vertraut sind. Falls nicht, lohnt es sich, unser PostgreSQL Cheat Sheet herunterzuladen.

Wenn Sie glauben, dass PostgreSQL CTEs Ihnen bei Ihrer Arbeit helfen werden, dann sollten Sie sich unseren interaktiven Kurs Common Table Expressions in PostgreSQL ansehen. Dieser Kurs ist für diejenigen gedacht, die bereits mit den Grundlagen von SQL vertraut sind. Dank der über hundert interaktiven Übungen können Sie die Verwendung von PostgreSQL CTEs ausgiebig üben.

Lassen Sie uns in die gemeinsamen Tabellenausdrücke in PostgreSQL eintauchen!

Übliche Tabellenausdrücke in PostgreSQL

CTE in PostgreSQL-Syntax

Sehen wir uns nun die CTE-Syntax genauer an. In ihrer einfachsten Form sieht sie wie folgt aus:

WITH cte_name AS (query_1)
query_2;

• cte_name ist der Name, den Sie der CTE zuweisen. Auf diesen Namen können Sie sich in Ihrer Hauptabfrage oder in Unterabfragen beziehen, so wie Sie es bei einer Tabelle tun würden.
• query_1 ist ein beliebiges gültiges SELECT
• query_2 ist eine gültige SQL-Anweisung. Es könnte ein SELECT, ein UPDATE, ein INSERT oder ein DELETE sein.

Die Ergebnisse von query_1 sind verfügbar, als ob sie eine Tabelle wären. Der Tabellenname ist der Name, den Sie als cte_name angegeben haben. Sie können sie im Rest Ihrer Abfrage auf die gleiche Weise verwenden wie andere Tabellen.

CTE in PostgreSQL Beispiel

Lassen Sie uns ein Beispiel betrachten. Alpha Sales ist ein Online-Händler. Sie möchten wissen, ob ihre neueste Marketingstrategie effektiv war und welche Art von Kunden am besten darauf reagiert hat.

Hier ist ein Beispiel für ihre order_summary Tabelle, die den Wert jeder Bestellung aus den Monaten April, Mai und Juni 2024 enthält.

Als ersten Schritt zur Analyse des Erfolgs ihrer Marketingkampagne möchten die Verantwortlichen des Unternehmens den Umsatz im Juni nach Kunden mit dem durchschnittlichen monatlichen Umsatz nach Kunden im April und Mai vergleichen und die prozentuale Veränderung berechnen.

Natürlich könnte man dies mit Hilfe von Unterabfragen erreichen, aber der Code wäre ziemlich komplex. Sie möchten den Durchschnitt des Vormonats im Bericht anzeigen, ihn aber auch für die Berechnung der prozentualen Veränderung verwenden.

Mit einer CTE würde die Abfrage wie folgt aussehen:

WITH april_may_sales AS
(SELECT 
   customer_id, 
   SUM(value) / 2 AS prev_avg
 FROM order_summary 
 WHERE EXTRACT (MONTH FROM order_date) in (4,5)
 GROUP BY customer_id;
)
SELECT 
  order_summary.customer_id, 
  prev_avg, 
  SUM(value) AS jun_total, 
  (SUM(value) - prev_avg) * 100 / prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ONapril_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Diese Abfrage verwendet die WITH Klausel, um eine virtuelle Tabelle namens april_may_sales zu erstellen. Sie extrahiert die Gesamtverkäufe nach Kunde für die Monate April und Mai, teilt das Ergebnis durch 2, um einen Monatsdurchschnitt zu erhalten, und speichert diese Informationen in einer Spalte mit dem Namen prev_avg.

Diese Tabelle ist verbunden mit der order_summary in der Hauptabfrage verbunden, so dass wir die Gesamtsumme des Monats Juni zusammen mit dem Durchschnitt der Monate April und Mai betrachten können.

Die Abfrage erzeugt die folgende Ergebnismenge:

Definieren von CTE-Spaltennamen

Optional können Sie die Spaltennamen für die CTE-Tabelle mit der folgenden Syntax definieren:

WITH cte_name (column_name_list)
AS (query_1)
query_2;

Hier ist column_name_list eine Liste von Spaltennamen, die durch Kommas getrennt sind.

Wenn Sie das vorherige Beispiel ändern, um diese Syntax zu verwenden, erhalten Sie die folgende Abfrage:

WITH april_may_sales (customer_id, prev_avg)
AS (
  SELECT 
 	customer_id, 
      SUM(value) /2 
  FROM order_summary 
  WHERE EXTRACT (MONTH FROM order_date) in (4,5)
  GROUP BY customer_id
)
SELECT 
order_summary.customer_id,
prev_avg, 
SUM(value) AS jun_total, 
(SUM(value) - prev_avg) * 100/prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ON april_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Dies ändert nichts an der Ausgabe der Abfrage, die dieselbe ist wie die vorherige Beispielabfrage. Es macht es jedoch für andere Personen einfacher, Ihre Abfrage zu verstehen.

Verschachtelte Abfragen mit PostgreSQL CTE

Sie können zwei oder mehr CTEs mit einem WITH Schlüsselwort in PostgreSQL definieren. Sie beginnen einfach mit dem Schlüsselwort WITH und geben dann jede CTE durch Kommas getrennt an. Die Syntax sieht wie folgt aus:

WITH 
cte_name_1 AS (query_1),
cte_name_2 AS (query_2)
query_3;

Jede CTE hat ihren eigenen Namen und ihre eigene Select-Anweisung. Jedes CTE kann auf jedes zuvor definierte CTE verweisen, um alle benötigten Daten zu erhalten. Beachten Sie, dass Sie das Schlüsselwort WITH nicht wiederholen: Sie listen die CTEs einfach durch Kommas getrennt auf.

Schauen wir uns das einmal in Aktion an. Nehmen wir an, Alpha Sales möchte diese Analyse noch weiter ausbauen. Sie möchten die demografischen Daten derjenigen Kunden extrahieren, die im Juni mehr gekauft haben als im Durchschnitt der Monate April und Mai.

Dazu müssen sie die in der vorherigen Abfrage extrahierten Daten mit Daten aus ihrer customer Tabelle. Hier ist ein Beispiel für die Daten:

Das können Sie tun:

• Verschieben Sie die vorherige Hauptabfrage als verschachtelte CTE in den Vordergrund. Dadurch wird eine virtuelle Tabelle erstellt, die customer_id, den vorherigen Durchschnitt, die Gesamtzahl für Juni und die prozentuale Veränderung enthält.
• Schreiben Sie eine neue Hauptabfrage, die diese Tabelle mit der Tabelle customer verbindet, um das Alter des Kunden zu berechnen und sein Bundesland zu extrahieren.

Die neue Abfrage sieht wie folgt aus:

WITH april_may_sales AS
  (SELECT 
     customer_id, 
     SUM(value) / 2 AS prev_avg
   FROM order_summary 
   WHERE EXTRACT (MONTH FROM order_date) in (4,5)
   GROUP BY customer_id
),
comparison AS
  (
    SELECT 
      order_summary.customer_id, 
      prev_avg, 
      SUM(value) AS jun_total, 
      (SUM(value) - prev_avg) * 100/prev_avg AS percent_change
    FROM order_summary  
    JOIN april_may_sales
    ON april_may_sales.customer_id = order_summary.customer_id
    WHERE EXTRACT (MONTH FROM order_date) = 6
    GROUP BY order_summary.customer_id, prev_avg
  )
SELECT 
  customer.customer_id,
  name,
  EXTRACT(YEAR from CURRENT_DATE) - 
		EXTRACT(YEAR from date_of_birth) AS age,
  state,
  prev_avg, 
  jun_total,
  percent_change
FROM customer
JOIN comparison 
    ON comparison.customer_id = customer.customer_id
WHERE percent_change > 0;

Wie zuvor definiert die Abfrage die CTE mit dem Namen april_may_sales als eine virtuelle Tabelle, die die durchschnittlichen Verkäufe für April und Mai enthält.

Dann wird eine neue CTE mit dem Namen comparison definiert, die einen Vergleich der Juni-Summen nach Kunden mit dem Inhalt von april_may_sales enthält.

Schließlich kombiniert die Hauptabfrage die Daten in der virtuellen Tabelle comparison mit Daten aus der Tabelle customer Tabelle.

Die Ergebnismenge sieht wie folgt aus:

PostgreSQL CTE in der Datenmanipulationssprache

Sehen wir uns nun Datenmanipulationsanweisungen wie INSERT, UPDATE und DELETE an.

Eine der Einschränkungen von CTEs ist, dass Sie sie nicht direkt anstelle eines Wertes in einer UPDATE Anweisung verwenden können, so wie Sie es mit einer Subquery können.

Angenommen, Sie möchten den Saldo in der Tabelle customer aktualisieren, indem Sie den Wert aller Bestellungen vom Juni hinzufügen. Mit normalen Unterabfragen können Sie so etwas tun:

UPDATE customer 
SET balance = balance + 
(select SUM(value) FROM order_summary 
WHERE customer.customer_id = order_summary.customer_id
   AND EXTRACT (MONTH from order_date) = 6);

Mit einer CTE können Sie das nicht tun. Was Sie jedoch tun können, ist die Verwendung der folgenden Syntax:

WITH cte_name AS (select_statement)
UPDATE tablename 
SET column_name_1 = column_name_2
FROM cte_name 
WHERE join_clause;

• cte_name ist der Name, den Sie verwenden, um auf die von der CTE erstellte "Tabelle" zu verweisen.
• select_statement ist die Anweisung, mit der Sie die CTE ausfüllen.
• column_name_1 ist der Name der Spalte in der Haupttabelle, die Sie aktualisieren möchten.
• column_name_2 ist der Spaltenname in Ihrer CTE, den Sie zum Setzen des neuen Wertes verwenden werden.
• join_clause gibt die Bedingung an, mit der Sie die beiden Tabellen verbinden wollen.

Die folgende Abfrage addiert die Summe der Juni-Bestellungen aus der Tabelle order_summary Tabelle zum Saldo in der customer Tabelle:

WITH june_total AS
(SELECT 
   customer_id, 
   SUM(value) AS jun_tot
 FROM order_summary WHERE EXTRACT(MONTH FROM order_date) = 6
 GROUP BY customer_id
)
UPDATE customer
SET balance = balance + jun_tot
FROM june_total 
WHERE customer.customer_id = june_total.customer_id;

Zunächst wird mit der Klausel WITH eine Pseudotabelle namens june_total erstellt. Sie enthält die Gesamtsummen der Bestellungen nach customer_id, wobei der Monat von order_date 6 ist.

Anschließend wird die Spalte jun_tot aus dieser Tabelle verwendet, um den Saldo zu erhöhen, wenn customer_id zwischen den beiden Tabellen übereinstimmt.

Die Tabelle customer enthält nun die folgenden Daten:

Sie können auch CTEs verwenden, um Zeilen auf dieselbe Weise einzufügen oder zu löschen.

Rekursive Abfragen

Rekursive Abfragen sind ein Merkmal von CTEs. Mit diesen Abfragen können Sie ausgehend von einer einzigen Basisabfrage eine Schleife bilden, um eine bestimmte Aufgabe wiederholt auszuführen. Sie sind besonders nützlich für die Abfrage von hierarchischen Daten wie Organisationsstrukturen und Stücklisten.

Eine vollständige Behandlung rekursiver Abfragen würde den Rahmen dieses Artikels sprengen. Wir werden lediglich die Syntax und ein einfaches Beispiel betrachten. Weitere Einzelheiten finden Sie unter Was ist eine rekursive CTE in SQL, die eine vollständige Erklärung und mehrere Beispiele enthält.

Die Syntax von rekursiven Abfragen in PostgreSQL lautet:

WITH RECURSIVE cte_name AS 
(query_1 UNION query_2)
query_3;

• Das Schlüsselwort RECURSIVE zeigt an, dass es sich um eine rekursive Abfrage handelt.
• query_1 ist die Basisabfrage oder der Startpunkt. Nehmen wir zum Beispiel an, Sie arbeiten mit einer Organisationsstruktur. In diesem Fall könnte query_1 eine Abfrage sein, die den Top-Level-Manager aus einer Mitarbeiterdatei auswählt.
• query_2 ist die rekursive Abfrage. Diese Abfrage wird so lange wiederholt, bis keine Zeilen mehr die in WHERE angegebenen Kriterien erfüllen. Sie kann auf die letzte Zeile verweisen, die der Ergebnismenge hinzugefügt wurde, um Daten zu sammeln. Dies könnte verwendet werden, um alle Mitarbeiter zu finden, die an einen Manager berichten.
• UNION kombiniert die Ergebnisse. Wenn Sie UNION ALL verwenden, werden Duplikate beibehalten, andernfalls werden sie ausgelassen.
• query_3 wird verwendet, um die endgültige Ergebnismenge zurückzugeben. Sie kann auf die virtuelle Tabelle verweisen, die von der CTE erstellt wurde.

Betrachten wir ein Beispiel für eine employee Tabelle, in der Mitarbeiterdatensätze ein Feld haben, das den Manager identifiziert, dem sie unterstellt sind. Was passiert eigentlich, wenn Sie eine rekursive Abfrage verwenden, um in dieser Hierarchie zu navigieren?

Die Ergebnisse der Basisabfrage werden der virtuellen Tabelle hinzugefügt. Die Basisabfrage extrahiert den Mitarbeiterdatensatz des CEOs. Die Datenbankmaschine verwendet dann diese Zeile, um alle Zeilen zu finden, die den Kriterien des rekursiven Teils der Abfrage entsprechen. Dies sind alle Mitarbeiter, die direkt an den obersten Manager berichten.

Für jeden dieser Datensätze findet die Engine wiederum alle Mitarbeiter, die dieser Person unterstellt sind. Dies wird so lange wiederholt, bis es keine Mitarbeiter mehr gibt, die die Bedingung erfüllen.

Lassen Sie uns ein einfaches Beispiel betrachten. Ein IT-Beratungsunternehmen hat mehrere laufende Projekte, für die wöchentliche Besprechungen zum Projektfortschritt angesetzt werden. Eine Tabelle namens projects enthält Details über neue Projekte. Ein Beispiel aus dieser Tabelle sieht wie folgt aus:

Die Firma möchte Details zu geplanten Besprechungen in einer Tabelle mit dem Namen meetingsDiese Informationen werden verwendet, um wöchentlich Erinnerungen zu versenden und einen Veranstaltungsort zu buchen. Die Spalte meet_day enthält den Wochentag, an dem die Besprechungen geplant werden. Sie wird als Tagesnummer innerhalb der Woche gespeichert, wobei 0 für Sonntag steht.

Sie könnten dies mit der folgenden rekursiven Abfrage erreichen:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)
AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	FROM projects
    UNION ALL
    SELECT 
proj_name, 
meet_date + 1,
	end_date, 
meet_day, 
meet_time
    FROM date_list
    WHERE meet_date + 1 <= end_date
	
)
INSERT INTO meetings
SELECT proj_name, meet_date, meet_time
FROM date_list 
WHERE meet_day = EXTRACT (DOW from meet_date)
ORDER BY proj_name, meet_date;

Nachdem die Abfrage ausgeführt wurde, enthält die Tabelle meetings die folgenden Daten:

Zerlegen wir die Abfrage und schauen wir uns an, was sie tatsächlich tut.

Zunächst werden die Spalten definiert, die in die CTE date_list aufgenommen werden sollen:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)

Als nächstes werden die Basisdaten für die Rekursion festgelegt, d. h. der Inhalt der Tabelle "Projekte":

AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	from projects

Dann wird festgelegt, welche Daten in jeder Rekursion enthalten sein müssen, mit einer Bedingung, die sicherstellt, dass die Rekursion beendet wird, wenn sie abgeschlossen ist:

    UNION ALL
    SELECT proj_name, 
	meet_date + 1,
	end_date, meet_day, meet_time
	FROM date_list
    WHERE meet_date + 1 <= end_date

Schließlich fügt die Hauptabfrage die in der virtuellen Tabelle enthaltenen Ergebnisse in die Tabelle meetings.

Hört sich das nützlich an? Sie können mehr über rekursive Abfragen erfahren und einige Beispiele aus der Praxis üben, wenn Sie unseren Online-Kurs CTE in PostgreSQL besuchen.

Erfahren Sie mehr über PostgreSQL CTEs

Obwohl CTEs in PostgreSQL die Leistung Ihrer Abfragen nicht verbessern, machen sie komplexe Abfragen einfacher zu schreiben und leichter zu verstehen. Indem Sie eine lange Abfrage in einzelne Komponenten aufteilen, können Sie Ihre Gedanken ordnen und Ihre Kodierung einfach halten. CTEs erleichtern auch die Arbeit mit hierarchischen Strukturen unter Verwendung der RECURSIVE Klausel.

Dieser Artikel verwendet speziell die PostgreSQL-Syntax und -Beispiele, aber CTEs funktionieren in ähnlicher Weise für andere SQL-Dialekte wie MS SQL Server.

Wenn Sie sich mit der Verwendung von CTEs vertraut machen wollen, finden Sie im LearnSQL-Kurs Common Table Expressions in PostgreSQL über 100 praktische Übungen, die Ihnen helfen werden, dieses Tool wirklich zu verstehen.

Wenn Sie zusätzlich üben möchten, probieren Sie diese 11 kostenlosen SQL Common Table Expression-Übungen aus. Jede Übung bietet Ihnen die Art von Herausforderung, mit der Sie in der realen Welt konfrontiert werden, und die Lösungen und Erklärungen sind enthalten. Und wenn Sie sich auf ein Vorstellungsgespräch vorbereiten, finden Sie hier einige Beispielfragen und -antworten zu CTE.

Ich hoffe, dass dieser Artikel Ihnen eine gute Vorstellung davon vermittelt hat, was PostgreSQL CTE für Sie tun kann. Wenn Sie weitere fortgeschrittene PostgreSQL-Konzepte kennenlernen möchten, ist dieser Artikel ein guter Anfang.

Jetzt liegt es an Ihnen! Denken Sie daran, dass Übung den Meister macht, also schauen Sie sich unseren Fortgeschrittenes SQL Practice Learning Track an, um die fortgeschrittenen SQL-Features praktisch zu üben!