Af Kathi Kellenberger
Jeg havde brugt ROW_NUMBER i min T-SQL-kode i årevis uden at vide, at det er en af en gruppe utroligt nyttige funktioner kaldet vinduesfunktioner. Først da jeg sammensatte en præsentation om nye T-SQL-funktioner i SQL Server 2012-udgaven, gik det op for mig, at der var så meget at tale om, at jeg lavede en separat præsentation kun om de nye vinduesfunktioner. Denne ene præsentation blev i sidste ende til to præsentationer, min bog, der er udgivet af Apress, og et Pluralsight-kursus. Jeg har fortalt alle, der vil lytte, om disse fantastiske funktioner, og jeg bruger dem dagligt på mit arbejde.
Vinduesfunktioner giver dig mulighed for at udføre beregninger over et sæt rækker. De svarer i funktionsmåde til gruppering med aggregerede funktioner, men de muliggør en vigtig forskel: Aggregerede forespørgsler returnerer én række pr. gruppe, og detaljerne elimineres i resultaterne. Forespørgsler med vinduesfunktioner returnerer alle detailrækkerne sammen med resultatet af vinduesfunktionsudtrykket. Du har detaljerne og får også aggregatet.
Når jeg taler om vinduesfunktioner ved SQL Server- og udviklerarrangementer, kender normalt omkring halvdelen af salen ROW_NUMBER. En håndfuld vil kende til mere end det. Det er skuffende, da disse nyttige funktioner har været tilgængelige i årevis.
Min yndlingsvinduesfunktion hedder LAG. LAG giver dig mulighed for at hente en hvilken som helst kolonne fra en anden række i resultatmængden. Du kan opnå det samme med andre metoder, men ydelsen er meget dårligere. Følgende er to forespørgsler mod AdventureWorks-databasen. Begge forespørgsler giver det samme resultat. Den første gør det gennem et outer join, mens den anden udnytter LAG.
–Liste over kunder med ordredatoer og dage siden den foregående ordre
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.Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
AND OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) AS OAPPLY
ORDER BY CustomerID, OrderDate;
–Det samme resultat opnås ved at bruge LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Den anden forespørgsel, der anvender LAG-funktionen, er enklere at skrive og fungerer bedre. Den faktiske udførelsesplan viser, at den første forespørgsel tager 99 % af ressourcerne i batchen. Den anden forespørgsel tager kun 1 % af ressourcerne. Her er de to eksekveringsplaner, og du kan selv se det:
Hvis du ikke er bekendt med syntaksen for window-funktioner, kan LAG-forespørgslen være en smule forvirrende. Alle vinduesfunktioner vil have en OVER-klausul, der definerer vinduet eller rækken af rækker til beregningen. Jeg ønsker ikke at sammenligne ordredatoer på tværs af kunder, så jeg brugte PARTITION BY-muligheden til at opdele rækkerne i grupper efter CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
Den OVER-klausul har også en ORDER BY-mulighed. Dette er påkrævet i tilfælde af LAG, så rækkerne bliver stillet op i den korrekte rækkefølge, så motoren kan hente de respektive værdier fra de korrekte rækker. Min endelige LAG-invokation ser således ud:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG er blot én funktion blandt mange vinduesfunktioner. Disse funktioner lader dig faktisk ikke gøre noget, som er umuligt uden dem, men de gør helt sikkert kodningen nemmere. Du vil også opdage, at dine forespørgsler mange gange kører hurtigere, når de er formuleret ved hjælp af disse nye funktioner.
Jeg ender med at bruge vinduesfunktioner dagligt. De gør mit liv lettere og forbedrer ydelsen af mine forespørgsler. Jeg elsker at bruge dem, og det bør du også gøre.
Om forfatteren
Kathi Kellenberger er konsulent hos Linchpin People. Hun nyder at skrive og holde foredrag om SQL Server-emner og har skrevet over to dusin artikler, bidraget til fire bøger og præsenteret ved mange SQL Server-arrangementer. I sin fritid nyder Kathi at tilbringe tid med familie og venner, synge og cykle.
Lær endnu mere om vinduesfunktioner og deres styrke til at løse vanskelige forespørgselsproblemer i Kathi Kellenbergers bog Expert T-SQL Window Functions in SQL Server (ISBN 978148484211045). Kathis andre bøger omfatter Beginning SQL Server Reporting Services (2016) og Beginning T-SQL (2014).
Skriv et svar