Door Kathi Kellenberger
Ik gebruik ROW_NUMBER al jaren in mijn T-SQL code zonder me te realiseren dat het een van een groep ongelooflijk handige functies is die window functies worden genoemd. Pas bij het samenstellen van een presentatie over nieuwe T-SQL functies in de SQL Server 2012 release realiseerde ik me dat er zoveel was om over te praten dat ik een aparte presentatie alleen over de nieuwe window functies heb gemaakt. Uiteindelijk veranderde die ene presentatie in twee presentaties, mijn boek gepubliceerd door Apress, en een Pluralsight cursus. Ik heb iedereen die maar wil luisteren verteld over deze geweldige functies, en ik gebruik ze dagelijks op mijn werk.
Vensterfuncties stellen je in staat om berekeningen uit te voeren over een set rijen. Ze zijn in werking vergelijkbaar met groeperen met aggregate functies, maar ze maken een belangrijk verschil: Aggregate queries retourneren één rij per groep, en de details worden geëlimineerd in de resultaten. Queries met window functies geven alle detail rijen terug samen met het resultaat van de window functie expressie. Je hebt detail, en krijgt het aggregaat ook.
Wanneer ik op SQL Server en ontwikkelaarsevenementen spreek over window functies, is meestal ongeveer de helft van de zaal bekend met ROW_NUMBER. Een handjevol kent er meer dan dat. Dat is teleurstellend omdat deze handige functies al jaren beschikbaar zijn.
Mijn favoriete window functie heet LAG. Met LAG kun je een kolom uit een andere rij in de result set pakken. Je kunt hetzelfde bereiken met andere methoden, maar de performance is veel slechter. Hieronder volgen twee queries tegen de AdventureWorks database. Beide queries geven hetzelfde resultaat. De eerste doet dat via een outer join, terwijl de tweede gebruik maakt van LAG.
–Lijst van klanten met besteldata en het aantal dagen sinds de vorige bestelling
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;
-Dezelfde resultaten worden verkregen met 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;
De tweede query met de LAG-functie is eenvoudiger te schrijven en presteert beter. Het feitelijke uitvoeringsplan laat zien dat de eerste query 99% van de bronnen in de batch in beslag neemt. De tweede query neemt slechts 1% van de bronnen in beslag. Hier zijn de twee uitvoeringsplannen, en u kunt het zelf zien:
Als u niet bekend bent met de syntaxis van windowfuncties, kan de LAG-query een beetje verwarrend zijn. Alle windowfuncties hebben een OVER-clausule die het venster, of de reeks rijen, voor de berekening definieert. Ik wil de besteldata niet vergelijken tussen klanten, dus heb ik de optie PARTITION BY gebruikt om de rijen te verdelen in groepen op basis van KlantID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
De OVER-clausule heeft ook een ORDER BY-optie. Dit is nodig in het geval van LAG, zodat de rijen in de juiste volgorde komen te staan zodat de engine de respectievelijke waarden uit de juiste rijen kan halen. Mijn uiteindelijke LAG-aanroep ziet er als volgt uit:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG is slechts één functie van vele vensterfuncties. Met deze functies kunt u eigenlijk niets doen wat zonder deze functies onmogelijk is, maar ze maken het coderen beslist gemakkelijker. U zult ook merken dat uw query’s vaak sneller worden uitgevoerd wanneer u deze nieuwe functies gebruikt.
Ik gebruik de window-functies uiteindelijk dagelijks. Ze maken mijn leven gemakkelijker en verbeteren de prestaties van mijn query’s. Ik gebruik ze graag, en dat zou jij ook moeten doen.
Over de auteur
Kathi Kellenberger is consultant bij Linchpin People. Ze houdt van schrijven en spreken over SQL Server onderwerpen. Ze heeft meer dan twee dozijn artikelen geschreven, bijgedragen aan vier boeken en presentaties gegeven op vele SQL Server evenementen. In haar vrije tijd brengt Kathi graag tijd door met familie en vrienden, zingt en fietst.
Lees nog meer over window functies en hun kracht bij het oplossen van lastige query problemen in Kathi Kellenberger’s boek Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Kathi’s andere boeken zijn onder andere Beginning SQL Server Reporting Services (2016) en Beginning T-SQL (2014).
Geef een antwoord