Kürzlich stellte sich mir die Anforderung, aus einer in den SQL Server importierten, hierarchischen Excel-Liste eine Parent-Child Dimension für einen Cube zu generieren.
Das Problem an der Sache?
Die einzigen Infos, um den jeweils übergeordneten Knoten in der Hierarchie ausfindig zu machen, sind die Sortierung aus der Excel Liste sowie das Level auf dem der Knoten sitzt. Um den Sachverhalt zu veranschaulichen, hier eine Liste, wie sie importiert werden würde. Sie zeigt die Mitarbeiterhierarchie einer Firma vom Chef in der ersten Spalte (Level 1) bis zum Praktikanten in der letzten Spalte (Level 4).
Nach dem Importvorgang sieht die Tabelle im SQL Server wie folgt aus:
Um im Anschluss die Spalte ParentID zu füllen, benötigen wir also ein Update Statement, welches aus den gegebenen Informationen eindeutig den Vaterknoten für jedes Element herausfindet. Hier bietet sich direkt ein SELF JOIN an:
UPDATE d
SET d.ParentID = s.ID
FROM dbo.Employees d
LEFT JOIN dbo.Employees s on d.Level > s.Level AND d.Sortierung > s.Sortierung
Beim Ausführen dieser Abfrage sehen wir aber auch sofort das Problem an dem Statement. Zum Beispiel wird der Algorithmus für Bastian Steger, den Praktikanten auf Level 4 insgesamt 8 Väter feststellen, da alle Mitarbeiter ab Level 3 über ihm die Kriterien erfüllen. Der JOIN wird dann zusätzliche Zeilen generieren, die wir nicht wollen. Die einzige Information, welche wir noch nicht genutzt haben, ist der Fakt, dass nur der erste der 8 festgestellten möglichen Väter von Bastian Steger der Richtige sein kann. Der SELF JOIN scheidet damit zur Lösung des Problems aus, da wir diese Information dort nicht sinnvoll verarbeiten können.
Es gibt jedoch 2 T-SQL Funktionen, welche genau dies können. Das OUTER APPLY-Statement kann zu jeder Zeile des Ursprungsstatement jede beliebige Tabellenwertfunktion ausführen und gibt NULL zurück, wenn es kein Ergebnis findet. Es verhält sich damit ähnlich dem LEFT JOIN, jedoch ist kein explizites Joinen der Zeilen mehr notwendig, da die Zuordnung von Zeilen der linken Tabelle zu denen der rechten Tabelle implizit schon durch die Funktion abgewickelt wird. Durch die Möglichkeit, auch die Spalten der Ursprungszeile zu nutzen, zu der die Funktion gerade ausgeführt wird, ist APPLY somit das ideale Instrument für unser Problem:
UPDATE d
SET d.ParentID = s.ID
FROM dbo.Employees d
OUTER APPLY
(SELECT TOP 1 t.ID
FROM dbo.Employees t
WHERE
d.Level > t.Level and d.Sortierung > t.Sortierung
ORDER BY t.Sortierung DESC) s
Für alle Nutzer ab SQL Server 2012 gibt es sogar noch eine weitere Variante, denn diese Version führte die analytischen Fensterfunktionen ein, welche im Vergleich meist deutlich performanter sind als konventioneller Code. Die LAG-Funktion bietet genau dieselben Vorteile wie APPLY, benötigt aber i.A. keine zusätzlichen Resultsets, sondern greift nur auf Zeilen des bestehenden Sets zu. Das finale Statement mit LAG sieht so aus:
UPDATE d
SET d.ParentID = s.ParentID
FROM dbo.Employees d
inner join
(
SELECT e1.ID,
LAG(e1.ID, e1.Sortierung – (SELECT MAX(e2.Sortierung) FROM dbo.Employees e2 WHERE e2.Level < e1.Level AND e2.Sortierung < e1.Sortierung)) OVER(ORDER BY Sortierung) as ParentID
FROM dbo.Employees e1
) s on d.ID = s.ID