CTE in SQL: Ein vollständiger Leitfaden mit Beispielen

Wie man eine SQL CTE erstellt

Wenn wir einen CTE erstellen, verwenden wir das Schlüsselwort WITH, um die CTE-Definition zu starten. Die allgemeine Syntax eines CTEs lautet wie folgt:

WITH cte_name (column1, column2, ...)
AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Wo:

• WITH: Leitet die CTE-Definition ein und gibt an, dass der folgende Name eine temporäre Ergebnismenge darstellt.

• cte_name: Der Name wird dem CTE zugewiesen, um es in der Hauptabfrage zu referenzieren.

• Optionale Spaltenliste (column1, column2, ...): Gibt die Spaltennamen für die Ergebnismenge der CTE an. Dies ist nützlich, wenn die Spaltennamen angepasst werden müssen.

• Abfrage, die das CTE definiert: Die innere Abfrage, die Daten auswählt und die temporäre Ergebnismenge formt.

• Hauptfrage: Verweist den CTE über seinen Namen und verwendet ihn wie eine Tabelle.

Schauen wir uns das folgende Beispiel für die Erstellung eines CTE mit einem mehrschichtigen Ansatz an. Nehmen wir an, wir haben eine Tabelle Employees und wollen eine CTE erstellen, die Mitarbeiter auswählt, die ein Gehalt von mehr als 50.000 $ verdienen.

Schritt 1: Schreibe die Basisabfrage

Wir beginnen mit dem Schreiben der grundlegenden SELECT Abfrage:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Schritt 2: Schließe die Abfrage mit dem Schlüsselwort WITH ein, um eine CTE zu erstellen

Benutze das Schlüsselwort WITH, um dem CTE einen Namen zu geben.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Schritt 3: Verwende die CTE in der Hauptabfrage

Schließlich verweist du in einer SELECT -Anweisung auf den CTE, indem du den oben definierten CTE-Namen aufrufst.

-- Define a Common Table Expression (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Use the CTE to select high-earning employees
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Um die obigen Schritte zusammenzufassen, haben wir das Schlüsselwort WITH verwendet, um den CTE namens HighEarningEmployees zu definieren. Die innere Abfrage wurde verwendet, um den temporären Datensatz zu erstellen. Die Hauptabfrage referenziert die HighEarningEmployees, um die angegebenen Spalten EmployeeID, FirstName und LastName anzuzeigen.

Warum SQL CTEs nützlich sind

Anhand des obigen Beispiels fragst du dich vielleicht, warum wir CTEs verwenden, wenn selbst einfache Abfragen die gleichen Ergebnisse liefern. Die Gründe dafür sind die folgenden:

Vereinfachen Sie komplexe Abfragen

CTEs zerlegen komplexe SQL-Anweisungen in kleinere, leichter zu handhabende Teile, wodurch der Code leichter zu lesen, zu schreiben und zu pflegen ist. 

Nehmen wir an, wir haben drei Tabellen: Orders, Customers, und Products. Wir wollen den Gesamtumsatz jedes Kunden ermitteln, der im Jahr 2024 gekauft hat. Wenn wir die Abfrage ohne CTE schreiben, sieht sie unübersichtlich aus und ist schwer zu lesen und zu verstehen.

-- Select customer names and total revenue from their orders
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Join to get customer and products table
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Durch die Verwendung einer CTE können wir die Logik in ein besser lesbares Format bringen:

-- Define the CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--Main query
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Wiederverwendbarkeit des Codes

CTEs helfen, Doppelarbeit zu vermeiden, indem sie die Wiederverwendung derselben Ergebnismenge in verschiedenen Teilen einer Abfrage ermöglichen. Wenn mehrere Berechnungen oder Operationen auf demselben Datensatz basieren, kannst du ihn einmal in einer CTE definieren und bei Bedarf darauf verweisen.

Angenommen, wir müssen den Durchschnitts- und Gesamtumsatz für jede Produktkategorie in einer E-Commerce-Datenbank berechnen. Wir können eine CTE verwenden, um die Berechnungen einmal zu definieren und sie in nachfolgenden Abfragen wiederzuverwenden.

-- Define a CTE to calculate total and average sales for each category
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select category, total sales, and average sales from the CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Andere Anwendungen

Neben der Vereinfachung von Abfragen und der Wiederverwendbarkeit von Code haben CTEs auch noch andere Funktionen. Ich kann nicht jede mögliche Verwendung von CTEs im Detail behandeln. Unser Kurs Datenbearbeitung in SQL ist eine gute Option, wenn du weiter üben möchtest. Ich werde hier jedoch einige der wichtigsten anderen Gründe dokumentieren:

• Organisation und Lesbarkeit von Abfragen: CTEs verbessern die Lesbarkeit von SQL-Code, indem sie Abfragen in logische, aufeinander folgende Schritte unterteilen. Jeder Schritt im Abfrageprozess kann durch eine eigene CTE dargestellt werden, so dass die gesamte Abfrage einfacher zu verfolgen ist.
• Hierarchisches Datentraversal: CTEs können bei der Navigation in hierarchischen Beziehungen helfen, z. B. in Organisationsstrukturen, Eltern-Kind-Beziehungen oder in jedem Datenmodell, das verschachtelte Ebenen enthält. Rekursive CTEs sind nützlich für die Abfrage hierarchischer Daten, weil sie es dir ermöglichen, iterativ durch die Ebenen zu gehen.
• Multi-Level-Aggregationen: CTEs können bei der Durchführung von Aggregationen auf mehreren Ebenen helfen, z. B. bei der Berechnung von Umsatzzahlen auf verschiedenen Granularitäten (z. B. nach Monat, Quartal und Jahr). Die Verwendung von CTEs zur Trennung dieser Aggregationsschritte stellt sicher, dass jede Ebene unabhängig und logisch berechnet wird.
• Kombiniere Daten aus mehreren Tabellen: Mehrere CTEs können verwendet werden, um Daten aus verschiedenen Tabellen zu kombinieren, wodurch der abschließende Kombinationsschritt strukturierter wird. Dieser Ansatz vereinfacht komplexe Verknüpfungen und stellt sicher, dass die Quelldaten logisch organisiert sind, um die Lesbarkeit zu verbessern.

Fortgeschrittene SQL CTE-Techniken

CTEs unterstützen fortgeschrittene SQL-Techniken, was sie vielseitig und nützlich für verschiedene Anwendungsfälle macht. Im Folgenden findest du einige der fortschrittlichen Anwendungen der CTEs.

Mehrere CTEs in einer einzigen Abfrage

Du kannst mehrere CTEs in einer einzigen Abfrage definieren, wodurch komplexe Transformationen und Berechnungen möglich sind. Diese Methode ist nützlich, wenn ein Problem mehrere Stufen der Datenverarbeitung erfordert, wobei jede CTE eine bestimmte Stufe darstellt.

Angenommen, wir haben Verkaufsdaten in einer Tabelle namens Sales und wollen den Gesamtumsatz für jedes Produkt berechnen, Produkte mit überdurchschnittlichem Gesamtumsatz identifizieren und diese Produkte nach ihrem Gesamtumsatz einstufen.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Step 2: Calculate the average total sales across all products
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Step 3: Filter products with above-average total sales
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Step 4: Rank the high-sales products
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Im obigen Beispiel;

• Das erste CTE (ProductSales) berechnet den Gesamtumsatz pro Produkt.

• Der zweite CTE (AverageSales) berechnet den durchschnittlichen Gesamtumsatz über alle Produkte hinweg.

• Der dritte CTE (HighSalesProducts) filtert nach Produkten, deren Gesamtumsatz den Durchschnitt übersteigt.

• Die letzte Abfrage ordnet diese Produkte nach ihrem Gesamtumsatz.

CTEs in UPDATE-, DELETE- und MERGE-Anweisungen

Wenn sie in die Operationen UPDATE, DELETE und MERGE integriert werden, können CTEs die Datenmanipulation vereinfachen, vor allem wenn es um komplexe Filter oder hierarchische Daten geht.

Verwendung von CTE mit einer UPDATE-Anweisung

Angenommen, wir haben eine Tabelle Employees mit einer Spalte EmployeeSalary. Wir wollen allen Beschäftigten, die mehr als 5 Jahre für das Unternehmen arbeiten, eine 10%ige Gehaltserhöhung geben.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

Der CTE LongTermEmployees identifiziert Beschäftigte, die mehr als fünf Jahre gearbeitet haben. In der Erklärung UPDATE wird dieser CTE verwendet, um die Gehälter selektiv zu erhöhen.

CTE mit einer DELETE-Anweisung verwenden

Nehmen wir an, wir haben eine Tabelle namens Products und wollen alle Produkte löschen, die in den letzten 2 Jahren nicht verkauft wurden. Wir können ein CTE verwenden, um die Produkte zu filtern:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Use DATEADD to find products with a LastSoldDate more than 2 years ago
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

Der CTE OldProducts identifiziert Produkte, die in den letzten zwei Jahren nicht verkauft wurden, und die DELETE Erklärung verwendet diesen CTE, um diese Produkte zu entfernen.

Verwendung von CTE mit einer MERGE-Anweisung

Die Anweisung MERGE in SQL ermöglicht bedingte Aktualisierungen, Einfügungen oder Löschungen in einer Zieltabelle auf der Grundlage von Daten in einer Quelltabelle. Im folgenden Beispiel kombiniert der CTE MergedInventory neue und bestehende Bestandsdaten. Die Anweisung MERGE aktualisiert dann die Mengen für bestehende Produkte oder fügt neue Produkte auf der Grundlage der CTE-Daten ein.

-- CTE to merge new and existing inventory data
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Use LEFT JOIN to include all new data, even if not in current inventory
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Rekursive Common Table Expressions (CTEs)

Rekursive CTEs helfen dabei, erweiterte und wiederholte Operationen durchzuführen.

Einführung in rekursive CTEs

Rekursive CTEs sind eine spezielle Art von CTE, die sich innerhalb ihrer Definition selbst referenzieren und es der Abfrage ermöglichen, wiederholte Operationen durchzuführen. Das macht sie ideal für die Arbeit mit hierarchischen oder baumartigen Daten, wie z. B. Organigrammen, Verzeichnisstrukturen oder Produktzusammenstellungen. Das rekursive CTE verarbeitet Daten iterativ und liefert Schritt für Schritt Ergebnisse, bis eine Abbruchbedingung erfüllt ist.

Anker und rekursive Mitglieder

Ein rekursiver CTE besteht aus zwei Hauptteilen:

• Ankermitglied: Der Teil, der die Basisabfrage definiert, mit der die Rekursion beginnt.
• Rekursives Mitglied: Der Teil, der auf das CTE selbst verweist, damit es die "rekursiven" Operationen durchführen kann.

Nehmen wir an, wir haben eine Tabelle Employees, in der jede Zeile ein EmployeeID, EmployeeName und ManagerID enthält. Wenn wir alle direkten und indirekten Berichte für einen bestimmten Manager finden wollen, beginnen wir mit dem Ankermitglied, das den Top-Level-Manager identifiziert. Das Ankermitglied beginnt mit dem Arbeitnehmer mit EmployeeID = 1.

Das rekursive Mitglied findet Arbeitnehmer, deren ManagerID mit der EmployeeID aus der vorherigen Iteration übereinstimmt. Jede Iteration ruft die nächste Ebene der Hierarchie auf.

WITH EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    UNION ALL
    -- Recursive member: find employees who report to the current managers
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Mögliche Probleme oder Einschränkungen von CTEs in SQL

Um logische und lesbare Abfragen zu schreiben, ist es wichtig, die Funktionen und Grenzen von CTEs zu verstehen. Sehen wir uns einige Einschränkungen und mögliche Probleme bei der Verwendung von CTEs in verschiedenen Datenbanken an.

SQL Server und Azure Einschränkungen

Es gibt einige umgebungsspezifische Einschränkungen für SQL CTEs, wenn du mit SQL Server oder Azure Synapse Analytics arbeitest. Sie umfassen die folgenden Punkte:

• SQL Server: Der Standardwert für die maximale Rekursionsstufe für rekursive CTEs ist 100, der mit dem Hinweis OPTION (MAXRECURSION) geändert werden kann. Wenn diese Grenze ohne Anpassung überschritten wird, tritt ein Fehler auf. CTEs können nicht direkt ineinander verschachtelt oder innerhalb eines anderen CTEs definiert werden.

• Azure Synapse Analytics: CTEs haben eine begrenzte Unterstützung für bestimmte SQL-Operationen wie INSERT, UPDATE, DELETE und MERGE. Außerdem werden rekursive CTEs in den cloudbasierten Umgebungen von Azure Synapse Analytics nicht unterstützt, was die Möglichkeit einschränkt, bestimmte hierarchische Datenoperationen durchzuführen.

Wenn du mit SQL Server arbeitest, kannst du dich darauf verlassen, dass das DataCamp eine Menge hilfreicher Ressourcen bereithält. Für den Anfang empfehle ich den Kurs "Einführung in SQL Server" von DataCamp, um die Grundlagen von SQL Server für die Datenanalyse zu erlernen. Du kannst unseren Lernpfad "SQL Server Developer" ausprobieren, der alles von Transaktionen und Fehlerbehandlung bis hin zur Zeitreihenanalyse abdeckt. In unserem Kurs Hierarchische und rekursive Abfragen in SQL Server geht es darum, wie man fortgeschrittene Abfragen in SQL Server schreibt, einschließlich Methoden mit CTEs.

Andere mögliche Probleme

Obwohl CTEs nützlich sind, um komplexe Abfragen zu vereinfachen, gibt es einige häufige Fallstricke, die du beachten solltest. Sie umfassen die folgenden Punkte:

• Endlosschleifen in rekursiven CTEs: Wenn die Abbruchbedingung für eine rekursive CTE nicht erfüllt ist, kann dies zu einer Endlosschleife führen, wodurch die Abfrage unendlich lange läuft. Um zu verhindern, dass der rekursive CTE unendlich lange läuft, kannst du mit dem Hinweis OPTION (MAXRECURSION N) die maximale Anzahl der rekursiven Iterationen begrenzen, wobei N eine bestimmte Grenze ist.

• Überlegungen zur Leistung: Rekursive CTEs können ressourcenintensiv werden, wenn die Rekursionstiefe hoch ist oder große Datensätze verarbeitet werden. Um die Leistung zu optimieren, begrenze die in jeder Iteration verarbeiteten Daten und sorge für eine angemessene Filterung, um übermäßige Rekursionsstufen zu vermeiden.

Wann sollte man CTEs verwenden? Andere Techniken

CTEs eignen sich zwar zur Vereinfachung von Abfragen, die wiederkehrende Aufgaben beinhalten, aber auch abgeleitete Tabellen, Views und temporäre Tabellen dienen ähnlichen Zwecken. Die folgende Tabelle zeigt die Vor- und Nachteile der einzelnen Methoden und wann sie eingesetzt werden sollten.

Fazit

Um CTEs zu meistern, brauchst du Übung, wie bei allem anderen auch: Ich empfehle, den Lernpfad Associate Data Analyst in SQL von DataCamp auszuprobieren, um ein kompetenter Datenanalyst zu werden. Der Kurs Reporting in SQL hilft dir außerdem, komplexe Berichte und Dashboards für eine effektive Datenpräsentation zu erstellen. Schließlich solltest du die SQL Associate-Zertifizierung erwerben, um zu zeigen, dass du SQL zur Lösung von Geschäftsproblemen beherrschst und dich von anderen Fachleuten abhebst.