De Kathi Kellenberger
Am folosit ani de zile ROW_NUMBER în codul meu T-SQL fără să-mi dau seama că face parte dintr-un grup de funcții incredibil de utile numite funcții de fereastră. Abia când am alcătuit o prezentare despre noile funcții T-SQL din versiunea SQL Server 2012 mi-am dat seama că era atât de mult de discutat încât am creat o prezentare separată doar despre noile funcții de fereastră. În cele din urmă, acea prezentare s-a transformat în două prezentări, în cartea mea publicată de Apress și într-un curs Pluralsight. Am vorbit oricui m-a ascultat despre aceste funcții grozave și mă trezesc că le folosesc zilnic la locul de muncă.
Funcțiile de fereastră vă permit să efectuați calcule pe un set de rânduri. Ele sunt similare în operare cu gruparea cu funcții de agregare, dar permit o diferență importantă: Interogările agregate returnează un rând pe grup, iar detaliile sunt eliminate în rezultate. Interogările care implică funcții de fereastră returnează toate rândurile de detaliu împreună cu rezultatul expresiei funcției de fereastră. Aveți detalii și obțineți și agregarea.
Când vorbesc la evenimente SQL Server și pentru dezvoltatori despre funcțiile fereastră, de obicei, aproximativ jumătate din sală este familiarizată cu ROW_NUMBER. O mână de oameni vor ști despre mai mult decât atât. Acest lucru este dezamăgitor, deoarece aceste funcții utile sunt disponibile de ani de zile.
Funcția mea preferată de fereastră se numește LAG. LAG vă permite să luați orice coloană dintr-un alt rând din setul de rezultate. Puteți realiza același lucru cu alte metode, dar performanța este mult mai slabă. În continuare sunt două interogări împotriva bazei de date AdventureWorks. Ambele interogări dau același rezultat. Prima face acest lucru printr-o îmbinare exterioară, în timp ce a doua profită de LAG.
–Înumeră clienții cu datele comenzilor și zilele scurse de la comanda precedentă
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;
– Produce aceleași rezultate folosind LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate),OrderDate) AS DaysSinceLinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Cea de-a doua interogare care utilizează funcția LAG este mai simplu de scris și funcționează mai bine. Planul de execuție real arată că prima interogare ocupă 99% din resursele din lot. A doua interogare ocupă doar 1% din resurse. Iată cele două planuri de execuție și vă puteți convinge singuri:
Dacă nu sunteți familiarizați cu sintaxa funcției window, interogarea LAG poate fi puțin confuză. Toate funcțiile de fereastră vor avea o clauză OVER care definește fereastra, sau setul de rânduri, pentru calcul. Nu vreau să compar datele comenzilor între clienți, așa că am folosit opțiunea PARTITION BY pentru a împărți rândurile în grupuri în funcție de CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
Clauza OVER are, de asemenea, o opțiune ORDER BY. Aceasta este necesară în cazul LAG, astfel încât rândurile să fie aliniate în ordinea corectă pentru ca motorul să preia valorile respective din rândurile lor corecte. Invocarea mea finală LAG arată după cum urmează:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG este doar o funcție din mai multe funcții de fereastră. Aceste funcții nu vă permit de fapt să faceți nimic care este imposibil fără ele, dar cu siguranță ușurează codarea. Veți descoperi, de asemenea, că de multe ori interogările dvs. rulează mai repede atunci când sunt formulate folosind aceste noi funcții.
Am ajuns să folosesc zilnic funcțiile de fereastră. Ele îmi fac viața mai ușoară și îmbunătățesc performanța interogărilor mele. Îmi place să le folosesc, iar dumneavoastră ar trebui să faceți la fel.
Despre autor
Kathi Kellenberger este consultant la Linchpin People. Îi place să scrie și să vorbească pe teme SQL Server, a scris peste două duzini de articole, a contribuit la patru cărți și a prezentat la multe evenimente SQL Server. În timpul liber, lui Kathi îi place să petreacă timp cu familia și prietenii, să cânte și să meargă cu bicicleta.
Învățați și mai multe despre funcțiile de fereastră și puterea lor în rezolvarea problemelor dificile de interogare din cartea lui Kathi Kellenberger, Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Printre celelalte cărți ale lui Kathi se numără Beginning SQL Server Reporting Services (2016) și Beginning T-SQL (2014).
.
Lasă un răspuns