Mit Common Table Expressions (CTEs) wartbares SQL schreiben
SQL ist ein zentrales Werkzeug für Entwickler, um komplexe Datenabfragen zu realisieren. Jedoch kann das Schreiben von effizientem und wartbarem SQL eine Herausforderung darstellen, vor allem wenn es um umfangreiche Abfragen geht. Common Table Expressions (CTEs) können hier Abhilfe schaffen.
In diesem Beitrag werden CTEs vorgestellt und gezeigt, wie mit ihrer Hilfe die Lesbarkeit und Wartbarkeit von SQL-Statements verbessert werden kann.
Das Problem
Ebenso wie der in Java, Python oder jeder anderen Programmiersprache verfasste Code, sind auch SQL-Statements Bestandteil einer Anwendung und diese sollten daher denselben Qualitätsansprüchen unterliegen. Das Ziel sollte es daher sein, auch SQL so zu formulieren, dass es lesbar und leicht anpassbar ist. Gerade bei größeren Statements gegen mehrere Tabellen wird es ohne CTEs jedoch schnell unübersichtlich. Hier ein Beispiel.
Aus den Tabellen Kunde und Auftäge sollen alle Kunden ausgegeben werden, die 2023 ihren ersten Auftrag aufgegeben haben.
Zu jedem dieser Kunden soll der gesamte Auftragswert sowie die Anzahl an Großaufträgen (Wert >= 1000) ermittelt werden.
Eine mögliche Abfrage könnte etwa so formuliert werden:
SELECT Kunde.Id,
Kunde.Name,
SUM(Auftrag.Wert) AS Gesamtwert2023,
COUNT(
CASE WHEN Auftrag.Wert >= 1000 THEN 1 END
) AS AnzahlGroßeAuftraege
FROM Kunde
JOIN
Auftrag ON Kunde.Id = Auftrag.KundenId
WHERE Auftrag.Datum >= '2023-01-01'
AND Auftrag.Datum < '2024-01-01'
AND Kunde.Id IN (SELECT KundenId
FROM Auftrag
GROUP BY KundenId
HAVING MIN(Datum) >= '2023-01-01')
GROUP BY Kunde.Id, Kunde.Name;Bereits bei dieser eher einfachen Abfrage sind einige Zeilen SQL notwendig um zum gewünschten Ergebnis zu gelangen. Gerade die Subquery zum Einschränken der Kunden-IDs vermischt mit den Einschränkungen zur Auswahl der relevanten Aufträge erschwert das Lesen der Abfrage. Hinzu kommt die notwendige Gruppierung, um die Aggregationsfunktionen anwenden zu können.
Common Table Expressions
WITH cte_name AS (SELECT ...)
SELECT * FROM cte_name;Common Table Expressions (CTEs) wurden mit SQL:1999 in den Standard aufgenommen. Mit ihrer Hilfe können Teile eines SQL-Statements ähnlich der Deklaration einer Variablen explizit benannt und wiederverwendbar gemacht werden. Im Gegensatz zu einer View ist der Scope einer CTE jedoch auf das nachfolgende Statement beschränkt. Aus diesem Grund wurden CTEs ursprünglich auch als Statement Scoped Views bezeichnet.
Eine CTE steht vor dem eigentlichen Statement und wird mit dem Schlüsselwort WITH eingeleitet.
Anschließend folgt der Name, den die CTE tragen soll, das Schlüsselwort AS und schließlich das Statement, das benannt werden soll.
Weitere CTEs können durch ein Komma getrennt angehängt werden.
Das WITH muss dazu nicht wiederholt werden.
Heute unterstützen praktisch alle gängigen RDBMS die oben gezeigte Form von CTEs. Je nach System können zudem herstellerspezifische Features hinzukommen.
Wartbarkeit verbessern
Mit diesem Wissen kann nun das Statement von oben überarbeitet werden. Dabei gibt es eine Vielzahl von Möglichkeiten das primäre Statement in CTEs zu unterteilen.
WITH Neukunden2023 AS (SELECT KundenId
FROM Auftrag
GROUP BY KundenId
HAVING MIN(Datum) >= '2023-01-01')
SELECT Kunde.Id,
Kunde.Name,
SUM(Auftrag.Wert) AS Gesamtwert2023,
COUNT(
CASE WHEN Auftrag.Wert >= 1000 THEN 1 END
) AS AnzahlGroßeAuftraege
FROM Kunde
JOIN
Auftrag ON Kunde.Id = Auftrag.KundenId
WHERE Auftrag.Datum >= '2023-01-01'
AND Auftrag.Datum < '2024-01-01'
AND Kunde.Id IN (SELECT * FROM Neukunden2023)
GROUP BY Kunde.Id;Im ersten Schritt kann die Subquery in eine CTE ausgelagert werden.
Dadurch wird der WHERE-Teil des primären Statements wesentlich übersichtlicher und durch die Benennung der CTE als Neukunden2023 wird die Bedeutung dieses Teil des Statements klar kommuniziert.
WITH Neukunden2023 AS (SELECT KundenId
FROM Auftrag
GROUP BY KundenId
HAVING MIN(Datum) >= '2023-01-01'),
Umsatzstatistik2023 AS (
SELECT KundenId,
SUM(Auftrag.Wert) AS Gesamtwert2023,
COUNT(
CASE WHEN Auftrag.Wert >= 1000 THEN 1 END
) AS AnzahlGroßeAuftraege
FROM auftrag
WHERE Datum >= '2023-01-01'
AND Datum < '2024-01-01'
GROUP BY KundenId
)
SELECT Id, Name, Gesamtwert2023, AnzahlGroßeAuftraege
FROM Kunde JOIN Umsatzstatistik2023 ON Id = KundenId
WHERE Id IN (SELECT * FROM Neukunden2023);Als nächste Maßnahme kann das primäre Statement durch eine zweite CTE Umsatzstatistik2023 weiter vereinfacht werden.
In diese wird die Auswahl der relevanten Aufträge sowie die Berechnung der Aggregationsfunktionen ausgelagert.
Das primäre Statement besteht nun lediglich noch aus der Auswahl der gewünschten Spalten und dem Referenzieren der CTEs. Beginnt man an dieser Stelle mit dem Lesen des Codes, ist sofort klar, was dieser bezwecken soll: Informationen über die Umsätze von Neukunden aus 2023 ermitteln. Nur bei Bedarf müssen die Implementierungsdetails in den CTEs betrachtet werden.
In der Praxis erweisen sich Common Table Expressions somit als wertvolles Werkzeug, um die Klarheit und Struktur von SQL-Statements zu verbessern. Gezielt eingesetzt können sie die Lesbarkeit und Wartbarkeit von Abfragen erhöhen.
Ausblick: Rekursive CTEs
In dieser einfachen Form ermöglichen es CTEs Statements auf andere Arten zu formulieren. Sie können aber auch verwendet werden, um rekursive Anweisungen zu formulieren, was ohne CTEs nur über Umwege möglich ist. Das kann beispielweise beim Abfragen von hierarchischen Daten praktisch sein.
WITH RECURSIVE ZahlenVonEinsBisZehn AS (
SELECT 1 AS n -- Ausgangspunkt
UNION ALL
SELECT n + 1
FROM ZahlenVonEinsBisZehn -- Rekursion
WHERE n < 10 -- Abbruchbedingung
)
SELECT * FROM ZahlenVonEinsBisZehn;Eine rekursive CTE wird mit WITH RECURSIVE eingeleitet.
Das innere Statement besteht typischerweise aus einem nicht-rekursiven Ausgangspunkt der per UNION mit einem rekursiven Teil vereinigt wird.
Im rekursiven Teil kann die eigene CTE referenziert werden, worüber auf einen Snapshot des vorangegangenen Schrittes zugegriffen werden kann.
So können, wie im obigen Beispiel, die Zahlen eins bis zehn rekursiv erzeugt werden.
Zum Thema SQL bieten wir sowohl Beratung, Entwicklungsunterstützung als auch passende Schulungen an:
Auch für Ihren individuellen Bedarf können wir Workshops und Schulungen anbieten. Sprechen Sie uns gerne an.