Von Kathi Kellenberger
Ich hatte jahrelang ROW_NUMBER in meinem T-SQL-Code verwendet, ohne zu wissen, dass es zu einer Gruppe von unglaublich nützlichen Funktionen gehört, den sogenannten Window Functions. Erst als ich eine Präsentation über neue T-SQL-Funktionen in SQL Server 2012 zusammenstellte, wurde mir klar, dass es so viel zu besprechen gab, dass ich eine separate Präsentation nur über die neuen Fensterfunktionen erstellte. Aus dieser einen Präsentation wurden schließlich zwei Präsentationen, mein von Apress veröffentlichtes Buch und ein Pluralsight-Kurs. Ich habe jedem, der es hören wollte, von diesen großartigen Funktionen erzählt, und ich verwende sie täglich bei der Arbeit.
Fensterfunktionen ermöglichen es Ihnen, Berechnungen über eine Reihe von Zeilen durchzuführen. Sie ähneln in ihrer Funktionsweise der Gruppierung mit Aggregatfunktionen, ermöglichen aber einen wichtigen Unterschied: Aggregatabfragen geben eine Zeile pro Gruppe zurück, und die Details werden in den Ergebnissen eliminiert. Abfragen mit Fensterfunktionen geben alle Detailzeilen zusammen mit dem Ergebnis des Ausdrucks der Fensterfunktion zurück. Sie haben die Details und erhalten auch das Aggregat.
Wenn ich auf SQL Server- und Entwicklerveranstaltungen über Fensterfunktionen spreche, ist in der Regel etwa die Hälfte der Anwesenden mit ROW_NUMBER vertraut. Eine Handvoll kennt mehr als das. Das ist enttäuschend, da diese nützlichen Funktionen seit Jahren verfügbar sind.
Meine Lieblingsfensterfunktion heißt LAG. Mit LAG können Sie eine beliebige Spalte aus einer anderen Zeile der Ergebnismenge übernehmen. Sie können das Gleiche mit anderen Methoden erreichen, aber die Leistung ist viel schlechter. Es folgen zwei Abfragen an die AdventureWorks-Datenbank. Beide Abfragen liefern das gleiche Ergebnis. Die erste erfolgt über einen Outer-Join, während die zweite die Vorteile von LAG nutzt.
–Liste der Kunden mit Bestelldaten und den Tagen seit der vorherigen Bestellung
SELECT CustomerID, OrderDate,
DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder
FROM Sales.SalesOrderHeader AS SOH
OUTER APPLY (
SELECT TOP(1) OrderDate AS PrevOrder
FROM Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
AND OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) AS OAPPLY
ORDER BY CustomerID, OrderDate;
–Ergeben die gleichen Ergebnisse mit LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Die zweite Abfrage, die die LAG-Funktion verwendet, ist einfacher zu schreiben und hat eine bessere Leistung. Der tatsächliche Ausführungsplan zeigt, dass die erste Abfrage 99 % der Ressourcen im Stapel verbraucht. Die zweite Abfrage benötigt nur 1 % der Ressourcen. Hier sind die beiden Ausführungspläne, und Sie können sich selbst davon überzeugen:
Wenn Sie mit der Syntax der Fensterfunktionen nicht vertraut sind, kann die LAG-Abfrage etwas verwirrend sein. Alle Fensterfunktionen haben eine OVER-Klausel, die das Fenster oder den Satz von Zeilen für die Berechnung definiert. Ich möchte die Bestelldaten der Kunden nicht miteinander vergleichen, daher habe ich die Option PARTITION BY verwendet, um die Zeilen in Gruppen nach Kunden-ID aufzuteilen:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
Die OVER-Klausel hat auch eine Option ORDER BY. Dies ist im Falle von LAG erforderlich, damit die Zeilen in der richtigen Reihenfolge angeordnet werden, damit die Engine die entsprechenden Werte aus den richtigen Zeilen abrufen kann. Mein endgültiger LAG-Aufruf sieht wie folgt aus:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG ist nur eine Funktion von vielen Fensterfunktionen. Mit diesen Funktionen können Sie zwar nichts tun, was ohne sie nicht möglich wäre, aber sie machen die Codierung definitiv einfacher. Sie werden auch feststellen, dass Ihre Abfragen oft schneller laufen, wenn Sie diese neuen Funktionen verwenden.
Ich verwende Fensterfunktionen täglich. Sie machen mir das Leben leichter und verbessern die Leistung meiner Abfragen. Ich benutze sie gerne, und das sollten Sie auch.
Über den Autor
Kathi Kellenberger ist Beraterin bei Linchpin People. Sie schreibt und spricht gerne über SQL Server-Themen und hat bereits mehr als zwei Dutzend Artikel verfasst, an vier Büchern mitgewirkt und bei vielen SQL Server-Veranstaltungen Vorträge gehalten. In ihrer Freizeit verbringt Kathi gerne Zeit mit Familie und Freunden, singt und fährt Rad.
Lernen Sie noch mehr über Fensterfunktionen und ihre Leistungsfähigkeit bei der Lösung schwieriger Abfrageprobleme in Kathi Kellenbergers Buch Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Zu Kathis anderen Büchern gehören Beginning SQL Server Reporting Services (2016) und Beginning T-SQL (2014).
Schreibe einen Kommentar