Was ist ein gemeinsamer Tabellenausdruck (CTE) in SQL?

Der gemeinsame Tabellenausdruck (CTE) ist ein leistungsfähiges Konstrukt in SQL, das eine Abfrage vereinfacht. CTEs funktionieren wie virtuelle Tabellen (mit Datensätzen und Spalten), die während der Ausführung einer Abfrage erstellt, von der Abfrage verwendet und nach der Ausführung der Abfrage gelöscht werden. CTEs dienen oft als Brücke, um die Daten in den Quelltabellen in das von der Abfrage erwartete Format umzuwandeln.

Ein gemeinsamer Tabellenausdruck (Common Table Expression, CTE) ist eine temporäre benannte Ergebnismenge, die aus einer einfachen SELECT -Anweisung erstellt wird und in einer nachfolgenden SELECT -Anweisung verwendet werden kann. Jeder SQL CTE ist wie eine benannte Abfrage, deren Ergebnis in einer virtuellen Tabelle (einem CTE) gespeichert wird, auf die später in der Hauptabfrage verwiesen werden kann.

Der beste Weg, gemeinsame Tabellenausdrücke zu lernen, ist die Praxis. Ich empfehle LearnSQL.de's interaktiven Rekursive Abfragen Kurs. Er enthält über 100 Übungen, in denen CTEs gelehrt werden, angefangen bei den Grundlagen bis hin zu fortgeschrittenen Themen wie rekursiven allgemeinen Tabellenausdrücken.

CTEs helfen bei der Vereinfachung von Abfragen

Beginnen wir mit der Syntax eines gemeinsamen Tabellenausdrucks.

WITH my_cte AS (
  SELECT a,b,c
  FROM T1
)
SELECT a,c
FROM my_cte
WHERE ....

Der Name dieses CTEs lautet my_cteund die CTE-Abfrage lautet SELECT a,b,c FROM T1. Die CTE-Abfrage beginnt mit dem Schlüsselwort WITH. Danach geben Sie den Namen Ihrer CTE-Abfrage an und anschließend den Inhalt der Abfrage in Klammern. Die Hauptabfrage steht nach der schließenden Klammer und bezieht sich auf die CTE. Hier lautet die Hauptabfrage (auch als äußere Abfrage bezeichnet) SELECT a,c FROM my_cte WHERE ….

In diesem ausgezeichneten Einführungsartikel über CTEs finden Sie viele Beispiele für den Einstieg. Weitere einführende Artikel sind "Improving Query Readability with Common Table Expressions" und "When Should I Use a Common Table Expression (CTE)?", in denen gängige Tabellenausdrücke erklärt werden.

Lernen von SQL Common Table Expressions anhand von Beispielen

In diesem Abschnitt stellen wir einige Beispiele für SQL-Abfragen vor, die allgemeine Tabellenausdrücke verwenden. Alle Beispiele basieren auf einer Datenbank für eine Kette von Mobiltelefongeschäften. Die Tabelle sales(siehe unten) enthält einen Datensatz pro verkauftem Produkt:

Im ersten Beispiel erhalten wir einen Bericht mit denselben Datensätzen aus der Tabelle sales Tabelle, fügen aber eine zusätzliche Spalte mit dem Preis des teuersten Artikels hinzu, der an diesem Tag in der gleichen Filiale verkauft wurde. Um den Preis des teuersten Artikels zu erhalten, verwenden wir einen allgemeinen Tabellenausdruck wie diesen:

WITH highest AS (
  SELECT
    branch,
    date,
    MAX(unit_price) AS highest_price
  FROM sales
  GROUP BY branch, date
)
SELECT
  sales.*,
  h.highest_price
FROM sales
JOIN highest h
  ON sales.branch = h.branch
    AND sales.date = h.date

Diese Abfrage definiert eine SQL CTE namens highest deren Ergebnis eine virtuelle Tabelle erzeugt. Die virtuelle Tabelle hat die Spalten Filiale, Datum und highest_price, die jeweils den Namen der Filiale, das Datum und den höchsten an diesem Tag in dieser Filiale verkauften Stückpreis enthalten.

Dann wird die äußere Abfrage ausgeführt, die die highest virtuelle Tabelle verwendet, als wäre sie eine reguläre Tabelle. Schließlich verbinden wir das Ergebnis der CTE highest mit der sales Tabelle.

Das Ergebnis der gesamten Abfrage ist unten dargestellt:

Im nächsten Beispiel erstellen wir einen Bericht mit den höchsten Tageseinnahmen nach Filialen.

WITH daily_revenue AS (
  SELECT
    branch,
    date,
    SUM(unit_price * quantity) AS daily_revenue
  FROM   sales
  WHERE EXTRACT(YEAR FROM date) = 2021
  GROUP BY 1,2
)
SELECT
  branch,
  MAX(daily_revenue) max_daily_revenue
FROM daily_revenue
GROUP BY 1
ORDER BY 2 DESC

In dieser Abfrage hat eine CTE namens daily_revenue mit den Spalten Branche, Datum und daily_revenue für jedes Datum im Jahr 2021. In der äußeren Abfrage erhalten wir dann den höchsten Umsatzbetrag für jede Filiale im Jahr 2021. Der Bericht ist nach max_daily_revenue in absteigender Reihenfolge geordnet.

Nachfolgend finden Sie die Ergebnisse dieser Abfrage.

Verwendung von CTEs in Fortgeschrittenes SQL Abfragen

Sie können zwei oder mehr CTEs definieren und sie in der Hauptabfrage verwenden. Im nächsten Beispiel zeigen wir Ihnen, wie Sie eine lange Abfrage mithilfe von SQL CTEs aufteilen und organisieren können. Durch die Benennung verschiedener Teile der Abfrage machen CTEs die Abfrage übersichtlicher.

Angenommen, wir möchten einen Bericht mit den monatlichen Gesamteinnahmen in London im Jahr 2021, aber wir möchten auch die Einnahmen für jede Filiale in London in demselben Bericht haben. Hier erstellen wir zwei CTEs und fügen sie dann in der Hauptabfrage zusammen.

WITH london1_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-1'
  GROUP BY 1
),
london2_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-2'
  GROUP BY 1
)
SELECT
  l1.month,
  l1.revenue + l2.revenue AS london_revenue,
  l1.revenue AS london1_revenue,
  l2.revenue AS london2_revenue
FROM london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month

In der obigen Abfrage definieren wir zwei CTEs, london1_monthly_revenue und london2_monthly_revenueum die monatlichen Einnahmen im Jahr 2021 für jede Filiale in London zu erhalten. Schließlich verbinden wir beide CTEs mit der Monatsspalte und berechnen den Gesamtumsatz für London, indem wir die Umsätze der beiden Filialen addieren.

Das Ergebnis der Abfrage sehen Sie unten:

Im folgenden Beispiel erhalten wir einen Bericht, der jede Filiale über das Datum, an dem das größte Ticket (d. h. die Menge der Artikel-Mengen-Kombination) verkauft wurde, und den Betrag dieses Tickets informiert. Zu diesem Zweck müssen wir eine CTE erstellen, die die Tickets (die Spalte position ist die Rangfolge) für jede Filiale nach dem Ticketbetrag ordnet.

WITH tickets AS (
  SELECT distinct
    branch,
    date,
    unit_price * quantity AS ticket_amount,
    ROW_NUMBER() OVER (
      PARTITION BY branch
      ORDER by unit_price * quantity DESC
    ) AS position
  FROM sales
  ORDER BY 3 DESC
)
SELECT
  branch,
  date,
  ticket_amount
FROM tickets
WHERE position =1

In der obigen Abfrage erstellen wir eine CTE mit den Spalten branch, date, ticket_amount und position. In der äußeren Abfrage filtern wir dann nur nach den Datensätzen mit position = 1, um das zu erhalten, was wir wollen, nämlich das größte Ticket nach Zweigstelle.

Das Ergebnis der Abfrage ist unten dargestellt:

Verschachtelte CTEs in SQL-Abfragen

Das nächste Beispiel zeigt eine verschachtelte CTE. Die Idee ist, einen Bericht mit allen Artikeln zu erstellen, deren Preis über 90 $ liegt, sowie die Menge dieser Artikel, die von der Filiale London-2 verkauft wurden.

WITH over_90_items AS (
  SELECT DISTINCT
    item,
    unit_price
  FROM sales
  WHERE unit_price >=90
),
london2_over_90 AS (
  SELECT
    o90.item,
    o90.unit_price,
    coalesce(SUM(s.quantity), 0) as total_sold
  FROM over_90_items o90
  LEFT JOIN sales s
  ON o90.item = s.item AND s.branch = 'London-2'
  GROUP BY o90.item, o90.unit_price
)
SELECT item, unit_price, total_sold
FROM   london2_over_90;

Die erste CTE lautet over_90_itemsdie alle Artikel auswählt, deren Preis größer als oder gleich 90 $ ist. Das zweite CTE ist london2_over_90mit der die von London-2 verkaufte Menge für jeden Artikel in over_90_items. Diese Abfrage hat eine verschachtelte CTE - beachten Sie die FROM in der zweiten CTE, die sich auf die erste bezieht. Wir verwenden LEFT JOIN sales, weil London-2 möglicherweise nicht jeden Artikel in over_90_items.

Das Ergebnis der Abfrage ist:

Bevor wir zum nächsten Abschnitt kommen, möchte ich Ihnen einige Artikel über allgemeine Tabellenausdrücke empfehlen. Sowohl "SQL CTEs Explained with Examples" als auch "Where Can I Find Good SQL CTE Exercises?" enthalten eine Menge Beispiele und Übungen.

Rekursive Abfragen und Gemeinsame Tabellenausdrücke

In relationalen Datenbanken ist es üblich, Tabellen zu haben, die Datenhierarchien wie Mitarbeiter-Manager, Teil-Teil oder Eltern-Kind darstellen. Um diese Hierarchien in beliebiger Richtung zu durchlaufen (von oben nach unten oder von unten nach oben), verwenden Datenbanken ein Konstrukt namens rekursive CTEs.

RECURSIVE ist ein reserviertes Wort zur Definition eines CTE für die Durchquerung einer rekursiven Datenstruktur. Die Form der rekursiven Abfrage ist wie folgt:

WITH RECURSIVE  cte_name AS (
     CTE_query_definition  -- non recursive query term
UNION ALL
     CTE_query_definition  -- recursive query term
)
SELECT * FROM cte_name;

Rekursive Abfragen liegen außerhalb des Rahmens dieses Einführungsartikels, aber ich empfehle drei weitere Artikel für diejenigen, die mehr über dieses Thema erfahren möchten: "How to Organize SQL Queries with CTEs", "Doit in SQL": Rekursives SQL Tree Traversal" und "LernenSie die Leistungsfähigkeit von SQL kennen Rekursive Abfragen." Sie erklären rekursive Abfragen im Detail mit vielen Beispielen.

SQL CTEs sind eine mächtige Ressource

Gemeinsame Tabellenausdrücke sind eine mächtige Ressource der SQL-Sprache. Mit ihnen lassen sich besser lesbare Abfragen erstellen und Formatunterschiede zwischen Tabellen- und Berichtsdaten verwalten. In diesem Artikel haben wir uns damit beschäftigt, was ein CTE ist und wie man sie in verschiedenen Abfragetypen verwendet. Wir haben auch erwähnt, dass CTEs in rekursiven Abfragen verwendet werden können.

Ich empfehle den Kurs Rekursive Abfragen auf LearnSQL.dein dem Sie interaktiv lernen, wie man mit allgemeinen Tabellenausdrücken in SQL arbeitet. Sie lernen auch, wie Sie rekursive Datenstrukturen wie Graphen und Bäume in SQL mit rekursiven CTEs verarbeiten können.

Besuchen Sie auch den Track Fortgeschrittenes SQLan, in dem Sie über die Grundlagen hinausgehen, um ein SQL-Meister zu werden. Wenn Sie sich auf ein SQL-Interview vorbereiten müssen, gibt es einen interessanten Artikel "Top 5 SQL CTE Interview Questions", in dem gängige Tabellenausdrücke aus einer anderen Perspektive betrachtet werden.

Wenn Sie gelernt haben, wie man CTEs verwendet, sind Sie auf dem Weg zum SQL-Entwickler schon einen Schritt weiter. Wachsen Sie weiter!