CTEs

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.

Referenzen:

• Offizielle Dokumentation von Microsoft