Mit rekursiven CTEs (Common Table Expressions) lassen sich Abfragen elegant und lesbar schreiben. Ein Beispiel wird hier vorgestellt. Vorgegebene Wertebereiche sollen jeweils auf eine bestimmte Anzahl von Intervallen (Buckets) aufgeteilt werden. Es kann sich dabei z.B. um Kennzahlen wie Einkommen oder um das Alter von Kunden handeln. Der jeweilige Kunde kann dann (z.B. in einem Lookup-Task in SSIS) dem passenden Bucket zugeordnet werden um alle Kunden sinnvoll zu klassifizieren.
Gegeben ist eine Hilfstabelle wo für jede Kennzahl der Name, den Wertebereich (z.B. 20 – 70 für das Alter) und die Anzahl der gewünschten Buckets (z.B. 5 für das Alter, womit jedes Bucket einen Bereich von 10 Jahren abdeckt) enthält. Die Hilfstabelle „Kennzahlen_Bucketdefinition“ enthält in unserem Beispiel zwei Kennzahlen: Alter und Einkommen.
Abgespeichert wird für jedes der erzeugten Buckets ein Datensatz mit dem Start und dem Ende des Intervalls sowie die Bezeichnung der Kennzahl, um die es geht. Zusätzlich wird eine fortlaufende Nummerierung der Buckets erzeugt. In diesem Beispiel werden für jede Kennzahl zwei zusätzliche Buckets erzeugt, nämlich eines für alle Werte, die kleiner als der Startwert des Wertebereichs sind (nach unten offen) und eines für alle Werte, die größer als der Endwert des jeweiligen Wertebereichs sind (nach oben offen). Auf die Erzeugung dieser zusätzlichen Buckets kann je nach Anwendungszweck natürlich in der Realität auch verzichtet werden.
Bei der Umsetzung der Anforderung wird ein rekursiver allgemeiner Tabellenausdruck verwendet, der eine kurze und einfache Umsetzung ermöglicht. Es wird dabei zunächst die Startmenge (Rekursions-Anker) festgelegt, die aus dem jeweils ersten Bucket-Datensatz für jede Kennzahl besteht (das nach unten offene Startintervall gekennzeichnet mit NULL als Startwert). Auf diese Menge wird dann das rekursive Statement ausgeführt, das für jeden Datensatz in der Startmenge den Nachfolgedatensatz erzeugt. Darauf folgend wird für die Ergebnismenge (die erzeugten Nachfolgedatensätze) wiederrum das rekursive Statement aufgerufen. Das wird dann für jede Zwischenschritt-Ergebnismenge ausgeführt, bis nach einem Schritt keine Zeilen mehr in der Ergebnismenge sind, dann bricht die Rekursion ab. Das wird in unserem Beispiel erreicht, wenn die Bucket-Nummer den vorgegebene Wert für die maximale Anzahl an Buckets erreicht hat. Das jeweils letzte Bucket für jede Kennzahl wird als oben offen erzeugt (NULL als Endwert).
Der Abfrage für den rekursiven allgemeinen Tabellenausdruck (CTE – Common Table Expression) sieht wie folgt aus:
;WITH MyCTE AS
(
–1. Teil (Anchor): festes Statement als Start der Rekursion (erstes Bucket):
— erstes Bucket = Pufferbucket für alle Werte, die kleiner sind als die Bucket-Range; daher unten offen
SELECT
Kennzahl_Name,
BucketNr=1,
BucketStart = CONVERT(decimal(18,2),NULL),
BucketStop = CONVERT(decimal(18,2),Intervall_Start),
BucketLength = CONVERT(decimal(18,2),(Intervall_Ende-Intervall_Start)/ (Buckets_Anzahl)),
maxBuckets = Buckets_Anzahl+2
FROM Kennzahlen_Bucketdefinition
UNION ALL
–2. Teil (Recursive): dynamisches (rekursives) Statement
— letztes Bucket = Pufferbucket für alle Werte, die größer sind als die Bucket-Range; daher oben offen
SELECT
Kennzahl_Name,
BucketNr=BucketNr+1 ,
BucketStart=ISNULL(BucketStop,0),
BucketStop=CASE WHEN BucketNr+1 < maxBuckets THEN CONVERT(decimal(18,2), Bucketstop+BucketLength) ELSE NULL END,
BucketLength,
maxBuckets
FROM MyCTE WHERE BucketNr+1 <= maxBuckets
)
SELECT Kennzahl_Name,BucketNr,BucketStart,BucketStop FROM MyCTE
ORDER BY Kennzahl_Name, BucketNr
OPTION (MAXRECURSION 32767 );
Das Ergebnis des Aufruf sieht wie gewünscht aus:
:
Anmerkungen: Um Uneindeutigkeiten bezüglich des Schlüsselwortes WITH zu vermeiden, muss bei einer CTE-Definition in einem Batch (außer es ist die erste Anweisung) die Anweisung vor WITH mit einem Semikolon abgeschlossen werden. Es ist also ganz praktisch immer ein Semikolon vor WITH zu schreiben, um das nicht zu übersehen.
Der Wert für MAXRECURSION ist optional. Er gibt die maximal erlaubte Anzahl von Rekursionsschritten an. Wird der Wert nicht explizit gesetzt, gilt übrigens der serverweite Default-Wert 100. Die CTE-Abfrage würde dann beim 101-ten Rekursionsschritt eine Ausnahme auslösen. Wird der Wert auf 0 gesetzt, ist die Rekursionstiefe unbegrenzt, was aber bei fehlerhaft geschriebenen CTEs zu einer Endlosschleife führen kann.