Was sind CTEs?
CTE ist die Abkürzung für “Common Table Expression”. Ihr könnt sie euch vorstellen wie ein benanntes Zwischenergebnis, auf das ihr in eurer Abfrage immer wieder zugreifen könnt. Wichtig dabei ist, dass eine CTE eine Tabelle definiert und dass in dieser Tabelle alle Spalten benannt sein müssen. Die CTE enthält also ein SELECT
-Statement, bei dem alle abgeleiteten Spalten einen Alias haben müssen.
Wo verwende ich CTEs?
Es gibt immer wieder Szenarien, in denen sich die Verwendung von CTEs anbietet. Nicht immer ist die CTE die beste und schnellste Lösung eine Abfrage zu schreiben. Aber selbst, wenn sie die Performance einer Abfrage nicht verbessert, erhöht sie doch meistens die Lesbarkeit.
Sehr sinnvoll sind CTEs an Stellen, wo ihr sonst Nested Queries verwenden würdet, also an Stellen wo sich die Granularität von verschiedenen Elementen von Abfragen unterscheidet.
Sehen wir uns ein Beispiel an. Dazu fragen wir die [Sales].[Orders]
-Tabelle der WWI-Datenbank ab. Wir möchten nun zunächst herausfinden, welche Kunden an einem Tag mehrfach bei demselben Sales-Angestellten bestellt haben. Das ist eine einfache GROUP BY
Abfrage:
SELECT [CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,count(distinct OrderID) as OrdersAtDateBySalesPerson FROM [WideWorldImporters].[Sales].[Orders] group by CustomerId, SalespersonPersonID, OrderDate order by 4 desc
Schreiben wir nun im nächsten Schritt die gesamte Anzahl an Bestellungen des jeweiligen Kunden mit auf die Zeile. Der Naive Ansatz hierfür wäre, eine Window-Function zu verwenden und die Bestellungen analog zur Zählweise in der Gruppierung zu zählen:
SELECT [CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,COUNT(distinct OrderID) as OrdersAtDateBySalesPerson ,COUNT(distinct OrderID) OVER (partition by CustomerID) as TotalOrders FROM [WideWorldImporters].[Sales].[Orders] group by CustomerId, SalespersonPersonID, OrderDate order by 4 desc
Dieser Ansatz schlägt allerdings fehl, denn die Verwendung von Distinct
in Funktionen mit Window ist nicht erlaubt: Use of DISTINCT is not allowed with the OVER clause.
Wenn gegeben ist, dass es pro Order immer nur eine Zeile in der Tabelle gibt, dann wäre das Resultat dasselbe, versuchen wir es also wie folgt:
SELECT [CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,COUNT(OrderID) as OrdersAtDateBySalesPerson ,COUNT(OrderID) OVER (partition by CustomerID) as TotalOrders FROM [WideWorldImporters].[Sales].[Orders] group by CustomerId, SalespersonPersonID, OrderDate order by 4 desc
Hier bekommen wir die Nachricht, dass die Verwendung der OrderID
-Spalte nicht zulässig ist, da sie nicht im Group By
enthalten ist: Column 'WideWorldImporters.Sales.Orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
.
Auch dieses Problem können wir umgehen: die Zeilen können wir nämlich auch einfach mit einem COUNT(1)
zählen:
SELECT [CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,COUNT(1) as OrdersAtDateBySalesPerson ,COUNT(1) OVER (partition by CustomerID) as TotalOrders FROM [WideWorldImporters].[Sales].[Orders] group by CustomerId, SalespersonPersonID, OrderDate order by 4 desc
Nun möchten wir aber nicht mehr die Gesamtzahl Bestellungen wissen, sondern einen Kunden-Rang, der sich aus dieser Anzahl berechnet:
SELECT [CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,COUNT(1) as OrdersAtDateBySalesPerson ,DENSE_RANK() OVER (ORDER BY COUNT(1) OVER (partition by CustomerID)) as CustRank FROM [WideWorldImporters].[Sales].[Orders] group by CustomerId, SalespersonPersonID, OrderDate order by 4 desc
Und spätestens hier sind wir mit den Window-Funktionen am Ende, denn diese dürfen sich nicht gegenseitig enthalten: Windowed functions cannot be used in the context of another windowed function or aggregate.
Die Lösung für dieses Problem bieten CTEs: Ähnlich wie wir in einer View die Gesamtzahl an Bestellungen für den Kunden zählen und diese dann mit der Abfrage Joinen könnten, können wir das mit einer CTE tun. Dafür schreiben wir zunächst die CTE. Diese erkennt ihr immer daran, dass sie mit dem Schlüsselwort WITH
beginnt. Diese CTE joinen wir dann mit unserer Abfrage, die den Rang der Zeilen berechnet:
WITH cte_rank as ( SELECT [CustomerID] ,COUNT(1) as TotalOrders FROM [WideWorldImporters].[Sales].[Orders] GROUP BY CustomerID ) SELECT o.[CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,COUNT(1) OVER (PARTITION BY o.CustomerID, SalespersonPersonID, OrderDate) as OrdersAtDateBySalesPerson ,DENSE_RANK() OVER (ORDER BY r.TotalOrders) FROM [WideWorldImporters].[Sales].[Orders] o LEFT JOIN cte_rank r ON o.CustomerID = r.CustomerID ORDER BY 4 DESC
Hier ersetzt die CTE also ein geschachteltes SELECT
Statement oder eine View, die wir sonst ebenfalls für diese Zwecke verwenden könnten.
Ähnlich können CTEs auch verwendet werden, um auf die Ergebnisse von ROW_NUMBER
oder RANK
-Zeilen zugreifen zu können, wenn wir nämlich nur die erste Bestellung eines Kunden am Tag sehen möchten, können wir das wie folgt machen: wir nummerieren die Bestellungen an diesem Tag durch und wählen diejenige mit der Zeilennummer 1 aus.
WITH cte_rownumbers AS ( select [CustomerID] ,[OrderDate] ,ROW_NUMBER() OVER (PARTITION BY [CustomerID], [OrderDate] ORDER BY [OrderDate]) as RowNumber FROM [WideWorldImporters].[Sales].[Orders] ) SELECT * FROM cte_rownumbers WHERE RowNumber = 1
Auf diese Art könnt Ihr übrigens auch wunderbar Doubletten aus euren Tabellen löschen.
2 Gedanken zu „CTEs“
Ha, wenn in Beitrag 1 schon Window Functions als “naiver Ansatz” bezeichnet werden, bin ich echt gespannt auf die nächsten 52 Posts! 😉 Weiter so, Jungs!
Danke Dir! Das Ziel ist schon, alles möglichst “anfängerfreundlich” zu gestalten. Ich hoffe, dass uns das gelingt.