By Kathi Kellenberger
Évek óta használtam a ROW_NUMBER-t a T-SQL kódomban anélkül, hogy tudtam volna, hogy ez az ablakfüggvényeknek nevezett hihetetlenül hasznos függvények egyik csoportja. Csak amikor összeállítottam egy előadást az SQL Server 2012 kiadás új T-SQL funkcióiról, akkor jöttem rá, hogy annyi mindenről van szó, hogy külön előadást készítettem csak az új ablakfüggvényekről. Végül ebből az egy prezentációból két prezentáció, az Apress által kiadott könyvem és egy Pluralsight-tanfolyam lett. Mindenkinek, aki meghallgat, mesélek ezekről a nagyszerű függvényekről, és azon kapom magam, hogy napi szinten használom őket a munkám során.
Az ablakfüggvények lehetővé teszik számítások elvégzését egy sorhalmazon. Működésük hasonló az összesítő függvényekkel történő csoportosításhoz, de lehetővé tesznek egy fontos különbséget: Az aggregált lekérdezések csoportonként egy sort adnak vissza, és a részletek kiesnek az eredményekből. Az ablakfüggvényeket tartalmazó lekérdezések az ablakfüggvény kifejezés eredményével együtt az összes részlet sorát is visszaadják. Megkapja a részleteket, és megkapja az aggregátumot is.
Amikor SQL Server- és fejlesztői rendezvényeken az ablakfüggvényekről beszélek, általában a terem fele ismeri a ROW_NUMBERt. Egy maroknyi ennél többet is tud. Ez kiábrándító, hiszen ezek a hasznos függvények már évek óta rendelkezésre állnak.
A kedvenc ablakfüggvényem a LAG. A LAG lehetővé teszi, hogy az eredményhalmaz egy másik sorából bármelyik oszlopot megragadjuk. Ugyanezt más módszerekkel is el lehet érni, de a teljesítmény sokkal rosszabb. Az alábbiakban két lekérdezés következik az AdventureWorks adatbázissal szemben. Mindkét lekérdezés ugyanazt az eredményt adja. Az első egy outer join segítségével teszi ezt, míg a második a LAG előnyeit használja ki.
–Listázza az ügyfeleket a rendelés dátumával és az előző rendelés óta eltelt napokkal
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;
-Produkálja ugyanezt az eredményt a LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate),OrderDate használatával. AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
A második lekérdezés a LAG függvény használatával egyszerűbben írható és jobban teljesít. A tényleges végrehajtási terv azt mutatja, hogy az első lekérdezés a köteg erőforrásainak 99%-át veszi igénybe. A második lekérdezés az erőforrások mindössze 1%-át veszi igénybe. Íme a két végrehajtási terv, és meggyőződhet róla:
Ha nem ismeri az ablakfüggvény szintaxisát, a LAG-lekérdezés kissé zavaró lehet. Minden ablakfüggvény rendelkezik egy OVER záradékkal, amely meghatározza a számításhoz használt ablakot vagy sorok halmazát. Nem akarom összehasonlítani a rendelési dátumokat az ügyfelek között, ezért a PARTITION BY opciót használtam a sorok CustomerID szerinti csoportokra osztásához:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
Az OVER záradéknak van egy ORDER BY opciója is. Erre a LAG esetén van szükség, hogy a sorok a megfelelő sorrendben legyenek felsorakoztatva, hogy a motor a megfelelő sorokból ki tudja venni a megfelelő értékeket. A végső LAG-felhívásom a következőképpen néz ki:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
A LAG csak egy függvény a sok ablakfüggvény közül. Ezek a függvények valójában semmi olyat nem tesznek lehetővé, ami nélkülük lehetetlen lenne, de mindenképpen megkönnyítik a kódolást. Azt is meg fogja tapasztalni, hogy a lekérdezések sokszor gyorsabban futnak, ha ezekkel az új függvényekkel fogalmazza meg őket.
Az ablakfüggvényeket végül napi szinten használom. Megkönnyítik az életemet és javítják a lekérdezéseim teljesítményét. Szeretem használni őket, és önnek is kellene.
A szerzőről
Kathi Kellenberger a Linchpin People tanácsadója. Szívesen ír és beszél SQL Server-témákról, több mint két tucat cikket írt, négy könyvhöz járult hozzá, és számos SQL Server-rendezvényen tartott előadást. Szabadidejében Kathi szívesen tölti az idejét a családjával és a barátaival, szeret énekelni és kerékpározni.
Még többet megtudhat az ablakfüggvényekről és azok erejéről a nehéz lekérdezési problémák megoldásában Kathi Kellenberger Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045) című könyvéből. Kathi további könyvei: Beginning SQL Server Reporting Services (2016) és Beginning T-SQL (2014).
Vélemény, hozzászólás?