av Kathi Kellenberger
Jag hade använt ROW_NUMBER i min T-SQL-kod i åratal utan att inse att det är en av en grupp otroligt användbara funktioner som kallas fönsterfunktioner. Först när jag sammanställde en presentation om nya T-SQL-funktioner i SQL Server 2012-versionen insåg jag att det fanns så mycket att prata om att jag skapade en separat presentation bara om de nya fönsterfunktionerna. Så småningom förvandlades den ena presentationen till två presentationer, min bok som publicerats av Apress och en Pluralsight-kurs. Jag har berättat för alla som vill lyssna om dessa fantastiska funktioner, och jag finner mig själv använda dem dagligen på jobbet.
Fönsterfunktioner gör att du kan utföra beräkningar över en uppsättning rader. De fungerar på samma sätt som gruppering med aggregeringsfunktioner, men de möjliggör en viktig skillnad: Aggregerade frågor returnerar en rad per grupp, och detaljerna elimineras i resultaten. Förfrågningar med fönsterfunktioner returnerar alla detaljrader tillsammans med resultatet av fönsterfunktionsuttrycket. Du har detaljerna och får aggregatet också.
När jag talar om fönsterfunktioner på SQL Server- och utvecklarevenemang brukar ungefär halva rummet känna till ROW_NUMBER. En handfull känner till mer än så. Det är en besvikelse eftersom dessa användbara funktioner har funnits tillgängliga i flera år.
Min favoritfönsterfunktion heter LAG. LAG gör det möjligt att ta med vilken kolumn som helst från en annan rad i resultatuppsättningen. Du kan åstadkomma samma sak med andra metoder, men prestandan är mycket sämre. Nedan följer två förfrågningar mot AdventureWorks-databasen. Båda dessa frågor ger samma resultat. Den första gör det genom en outer join, medan den andra utnyttjar LAG.
–Lista kunderna med beställningsdatum och dagarna sedan föregående beställning
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;
–Skapa samma resultat genom att använda 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;
Den andra frågan med LAG-funktionen är enklare att skriva och fungerar bättre. Den faktiska exekveringsplanen visar att den första frågan tar 99 % av resurserna i partiet i anspråk. Den andra frågan tar bara 1 % av resurserna i anspråk. Här är de två exekveringsplanerna och du kan själv se det:
Om du inte är bekant med syntaxen för fönsterfunktioner kan LAG-frågan vara lite förvirrande. Alla fönsterfunktioner har en OVER-klausul som definierar fönstret, eller uppsättningen av rader, för beräkningen. Jag vill inte jämföra beställningsdatum mellan kunderna, så jag använde alternativet PARTITION BY för att dela upp raderna i grupper efter CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
Den här OVER-klausulen har också ett ORDER BY-alternativ. Detta krävs i fallet med LAG, så att raderna kommer att ställas upp i rätt ordning för att motorn ska kunna hämta respektive värden från rätt rader. Mitt slutliga LAG-anrop ser ut på följande sätt:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG är bara en funktion av många fönsterfunktioner. Dessa funktioner låter dig faktiskt inte göra något som är omöjligt utan dem, men de gör definitivt kodningen enklare. Du kommer också att upptäcka att dina frågor många gånger går snabbare när de formuleras med hjälp av dessa nya funktioner.
Jag slutar med att använda fönsterfunktioner dagligen. De gör mitt liv enklare och förbättrar prestandan i mina frågor. Jag älskar att använda dem och det borde du också göra.
Om författaren
Kathi Kellenberger är konsult hos Linchpin People. Hon tycker om att skriva och tala om SQL Server-ämnen och har skrivit över två dussin artiklar, bidragit till fyra böcker och presenterat på många SQL Server-evenemang. På sin fritid umgås Kathi gärna med familj och vänner, sjunger och cyklar.
Lär dig ännu mer om fönsterfunktioner och deras kraft när det gäller att lösa svåra frågeproblem i Kathi Kellenbergers bok Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Kathis andra böcker inkluderar Beginning SQL Server Reporting Services (2016) och Beginning T-SQL (2014).
Lämna ett svar