Das Konstrukt With… wurde mit der SQL-Server-Version 2005 in die Abfragesprache Transact-SQL eingeführt. In der Abfragesprache für multidimensionale Datenbanken (MDX) war es bereits seit längerem verfügbar und wird dort sehr häufig eingesetzt. Deshalb stellen wir das With-Konstrukt kurz vor und werden zwei Anwendungsmöglichkeiten aufzeigen, die in den Projekten für Kunden von Bissantz & Company GmbH häufig anzutreffen sind.

Das With-Konstrukt hat für die Abfragesprache Transact-SQL zwei Bedeutungen. Als “trivial” ist die erste Bedeutung zu bezeichnen: Ein bestimmtes SQL-Statement, das in einem größeren SQL-Statement mehrere Male als Sub-Select verwendet wird, wird am Anfang definiert und dann im Haupt-Select eingesetzt. In diesem Fall erspart das With-Konstrukt also das Erstellen einer View, die dann ausschließlich in einer anderen View benutzt wird. Um dies zu demonstrieren ist folgendes Beispiel ausgewählt: das Dekumulieren.

Die andere Bedeutung des With-Konstruktes ist die Möglichkeit, rekursive Abfragen zu erstellen. Das heißt, eine Abfrage, die sich selbst als Datengrundlage im From-Bereich nutzt. Solche Abfragen werden zum Beispiel im Zusammenhang mit hierarchisch geordneten Daten benutzt, in der die Hierarchie-Tiefe variabel sein muss.

Auch in OLAP-Datenbanken gibt es natürlich die Möglichkeit, Hierarchien mit variabler Hierarchie-Tiefe aufzubauen. Dies ist sehr bequem, da man sich um Hierarchie-Tiefen keine Gedanken mehr machen muss. Die Parent-Child-Hierarchien haben aber auch Nachteile. So kann pro Dimension immer nur eine Parent-Child-Hierarchie aufgebaut werden. Außerdem sind solchen Hierarchien nicht sehr performant, sodass in den “best practices” von Microsoft sogar empfohlen wird, nur eine Parent-Child-Struktur pro Datenbank zu nutzen.

Häufig liegen die Daten im Quellsystem in einer Parent-Child-Struktur vor, aber können durchaus in eine normale Hierarchie überführt werden, da die maximale Hierarchie-Tiefe bekannt ist oder sich nur sehr selten ändert. Zum Überführen von Parent-Child-Strukturen in “normale” Hierarchien ist die Information notwendig, welches maximale Level die einzelnen Elemente der Parent-Child-Struktur erreichen. An der Stelle kann uns das With-Konstrukt unterstützen.

Nutzung des With-Konstruktes um häufig genutzte Sub-Selects zu definieren

Nicht ganz einfach ist, solche “häufig genutzten Sub-Selects” zu erkennen. Sub-Selects müssen nicht absolut identisch sein, damit sich das With-Konstrukt lohnt. Wichtig ist, dass der FROM-Bereich weitgehend übereinstimmt. Alles andere, z.B. die einzelnen auszugebenen Spalten, oder ein ‚Group By’ kann möglicherweise genauso gut im Haupt-Select definiert werden. Hier sollte man Nutzen und Aufwand genau abwägen.

With Name_With_Construct (col1, col2) As
( Select col1, col2 From ...
)
Select * From Name_With_Construct

Das With-Konstrukt besteht aus dem Wort With gefolgt von einem Namen, mit dem dann später auf das With-Konstrukt referenziert wird. Dann folgen in Klammern die Spalten und nach dem Wörtchen ‚as’ das Select-Statement (auch in Klammern), das durch das With-Konstrukt gekapselt werden soll. Danach beginnt das Haupt-Select, in dem auf das With-Konstrukt verwiesen wird.

Beim Beispiel für eine “triviale” Nutzung sollen bereits vorhandene Summen wieder in Bestandteile zerlegt werden. OLAP-Datenbanken sind wahre Meister im Aggregieren. Es ist aber komplex, einer OLAP-Datenbank bereits aggregierte Werte zu übergeben und der OLAP-Datenbank das Aggregieren zu verbieten (…und statt dessen den zeitlich letzten Wert zu nehmen). Daher werden solche Quelldaten nach Möglichkeit relational ‚dekumuliert’ (also die Summierung rückgängig gemacht) und dann in die OLAP-Datenbank exportiert. Grundlage für dieses Beispiel ist eine Tabelle mit Kontensalden:

AccountID   PC_ID       Cutoff_Date             Balance

———– ———– ———————– ———————

2000        1           2009-01-01 00:00:00.000 100,00

2000        1           2009-02-01 00:00:00.000 120,00

2000        1           2009-03-01 00:00:00.000 150,00

2000        1           2009-04-01 00:00:00.000 0,00

Neben dem Konto (AccountID), dem Stichtag und dem Saldo ist noch eine zusätzliche Spalte ProfitCenter vorhanden. Üblicherweise kommen noch mehr Dimensionsspalten in einer Fakten-Tabelle vor. Die Aufgabe ist nun, jeden Datensatz mit seinem zeitlich direkten Vorgänger zu verknüpfen, um die Differenz zwischen beiden Salden berechnen zu können. Da der Vorgänger in diesem Fall nicht direkt ermittelt werden kann (z.B. Stichtag – 1), ist eine weitere Vereinbarung notwendig: Bevor ein Konto gelöscht wird, ist ein Datensatz mit Balance = 0 notwendig. Sonst kann man nicht unterscheiden, ob ein Datensatz für einen bestimmten Stichtag nicht vorhanden ist, weil sich der Saldo nicht geändert hat, oder weil das Konto gelöscht wurde.

Für die Aufgabe wird eine Unterabfrage dreimal benötigt: Für die Daten des “aktuellen” Stichtages, für die Daten des Vorgänger-Stichtages und zur Ermittlung des Vorgänger-Stichtages. Diese drei müssen miteinander verknüpft werden. Um den Aufwand beim Verknüpfen möglichst gering zu halten, werden alle fixen (von der Zeit unabhängigen) Dimensions-Schlüssel (hier AccountID und PC_ID) zu einem Schlüssel zusammengefasst:

Select AccountID, PC_ID, Cutoff_Date, Balance,
               PC_ID * 10000 + AccountID OBJ_KEY
From T_D_Balances

Folgendes Statement entsteht bei der Nutzung von With:

With bals (AccountID, PC_ID, Cutoff_Date, Balance, OBJ_KEY) As
(      Select
                   AccountID, PC_ID, Cutoff_Date, Balance,
                   PC_ID * 10000 + AccountID OBJ_KEY
       From T_D_Balances
)
Select x.AccountID, x.PC_ID, x.Cutoff_Date, x.Balance - ISNULL(b.Balance, 0) diff
From bals x
Left Join bals b
       On x.OBJ_KEY = b.OBJ_KEY
       And b.Cutoff_Date =
                       (      Select Max(CutOff_Date)
                              From bals
                              Where OBJ_KEY = x.OBJ_KEY
                              And CutOff_Date < x.Cutoff_Date
                       )
Order By CutOff_Date

x ist dabei die Quelle für die aktuellen Daten, damit verknüpft ist b als Quelle für die Vorgängerdaten. Die Verknüpfung ist eindeutig über den neu erzeugten, zusammengesetzten Schlüssel und den Stichtag. Zur Ermittlung des richtigen Stichtages muss in einer weiteren Unterabfrage der höchste Stichtag unterhalb des Stichtages der Quelle für die aktuellen Daten (x.CutOff_Date) herausgefunden werden. Alle drei Quellen beziehen sich auf das With-Konstrukt am Anfang. Dieses Statement ist sehr übersichtlich, durch die Nutzung des With-Konstruktes.

Ergebnis des SQL-Statements ist:

AccountID   PC_ID       Cutoff_Date             diff

———– ———– ———————– ———————

2000        1           2009-01-01 00:00:00.000 100,00

2000        1           2009-02-01 00:00:00.000 20,00

2000        1           2009-03-01 00:00:00.000 30,00

2000        1           2009-04-01 00:00:00.000 -150,00

Rekursive Abfragen mit dem With-Konstrukt

Bei einer rekursiven Abfrage wird das With-Konstrukt bereits in sich selbst wieder benutzt. Dies geschieht immer durch ein Union-Select im With-Konstrukt, das aus einer initialen Abfrage und einer Abfrage besteht, die auf das With-Konstrukt verweist:

With w_tab (x) As
(      Select 1
       Union all
       Select w_tab.x + 1 From w_tab
)
Select * From w_tab

Die initiale Abfrage ist “Select 1″. Der zweite Teil fügt dem ersten Teil dessen Nachfolger hinzu. Ergebnis ist ein Spalte mit den Zahlen 1, 2, 3, 4 … . Da in diesem einfachen Demonstrationsbeispiel auf ein Ende der Rekursion verzichtet wurde, müsste die Abfrage in alle Ewigkeit weiterlaufen. Allerdings gibt es im SQL-Server die Option MAXRECURSION, die standardmäßig auf 100 eingestellt ist. Das heißt, nach 100 Rekursionen läuft die Abfrage auf den Fehler:

Meldung 530, Ebene 16, Status 1, Zeile 1
Die Anweisung wurde beendet.
Die maximale Rekursionstiefe 100 wurde vor Abschluss der Anweisung erreicht.

Solch tiefe Rekursionen sind absolut unüblich, sollte aber trotzdem einmal Bedarf an tieferen Rekursionen bestehen, kann man die Option MAXRECURSION der Abfrage hinzufügen:

With w_tab (x) As
(      Select 1
       Union all
       Select w_tab.x + 1 from w_tab
)
Select * From w_tab
Option (MAXRECURSION 10000);

Diese Abfrage wird auch auf einen Fehler laufen, aber jetzt erst nach 10.000 Rekursionen. Es ist also sehr wichtig bei Rekursionen, eine Abbruchbedingung mit anzugeben (Where-Bedingung):

With w_tab (x) As
(      Select 1
       Union All
       Select w_tab.x + 1 From w_tab Where x < 50
)
Select * From w_tab

Im praktischen Beispiel soll den Elementen einer Parent-Child-Hierarchie ein „Maximales Level“ zugeordnet werden.

Folgende Parent-Child-Hierarchie ist als Quell-Tabelle vorhanden:

Die Hierarchie ist in einer PC-Tabelle mit zwei Spalten abgelegt:

Parent      Child

———– ———–

1           2

2           3

2           4

3           5

1           6

2           7

…

Für viele Aufgabestellungen ist es notwendig, herauszufinden, welches maximale Level die einzelnen Elemente einer Parent-Child-Struktur haben. Dieses kann über ein rekursives With-Statement ermittelt werden:

With
l5 (PNr, Nr, Level) as
(      Select Parent, Child, 5 Level From Tab_ParentChild
       Where Child Not In (Select Parent From Tab_ParentChild)
),
x (Nr, Level) As
(      Select Nr, Level from l5
       Union All
       Select pc.Parent, x.Level - 1 Level
       From Tab_ParentChild pc Join x
              On pc.child = x.Nr
)
Select * From x

Das With-Konstrukt l5 ist ein With für Sub-Selects. Das With-Konstrukt x ist die rekursive Abfrage. Das initiale Select holt alle Kinder aus der Tabelle, die nie selbst Eltern sind (dies wird noch in l5 gefiltert). Diesen Elementen ist die unterste Hierarchiestufe 5 zugeordnet. Im rekursiven Teil der Abfrage wird der Parent des Elementes gesucht und ihm die Hierarchiestufe “Level des Kindes minus 1″ zugewiesen. Da in diesem Fall schnell keine Eltern mehr in der Tabelle Tab_Parentchild zu finden sind, braucht man sich hier nicht explizit um einen Abbruch der Rekursion kümmern. Ergebnis sind die Level der Elemente:

Nr          Level

———– ———–

6           5

7           5

8           5

9           5

10          5

13          5

14          5

15          5

17          5

16          4

12          4

11          3

12          4

11          3

11          4

…

Dabei werden manchen Elementen mehrere Level zugewiesen (siehe Element 11). Aufgabe war jedoch, nur die höchste Stufe jedes Elements zu suchen. Daher wird noch auf die Elemente gruppiert und das höchste Level pro Element ermittelt:

With
l5 (PNr, Nr, Level) As
(      Select Parent, Child, 5 Level From Tab_ParentChild
       Where Child Not In (Select Parent From Tab_ParentChild)
),
x (Nr, Level) As
(      Select Nr, Level From l5
       Union All
       Select pc.Parent, x.Level - 1 Level
       From Tab_ParentChild pc Join x 
              On pc.child = x.Nr
)
Select Nr, MIN(Level) ML From x Group By Nr

Das Ergebnis ist jetzt:

Nr          ML

———– ———–

1           1

2           2

3           3

4           4

5           4

6           5

7           5

8           5

9           5

10          5

11          3

12          4

…

Wie man sieht, können genau wie in MDX mehrere With-Konstrukte untereinander angelegt werden. Das Wort With kommt dabei nur am Anfang, die With-Konstrukte sind durch Komma miteinander getrennt.