By Kathi Kellenberger
Olin käyttänyt ROW_NUMBERia T-SQL-koodissani vuosia ymmärtämättä, että se on yksi uskomattoman hyödyllisistä funktioista, joita kutsutaan ikkunafunktioiksi. Vasta kootessani esitystä SQL Server 2012 -julkaisun uusista T-SQL-ominaisuuksista tajusin, että puhuttavaa oli niin paljon, että loin erillisen esityksen vain uusista ikkunafunktioista. Lopulta tuosta yhdestä esityksestä tuli kaksi esitystä, Apressin julkaisema kirjani ja Pluralsight-kurssi. Olen kertonut kaikille kuulijoille näistä upeista funktioista, ja huomaan käyttäväni niitä päivittäin työssäni.
Ikkunafunktioiden avulla voit suorittaa laskutoimituksia joukolle rivejä. Ne ovat toiminnaltaan samanlaisia kuin ryhmittely aggregaattifunktioilla, mutta ne mahdollistavat tärkeän eron: Aggregaattikyselyt palauttavat yhden rivin per ryhmä, ja yksityiskohdat poistetaan tuloksista. Ikkunafunktioita sisältävät kyselyt palauttavat kaikki yksityiskohtaiset rivit yhdessä ikkunafunktion lausekkeen tuloksen kanssa. Saat yksityiskohdat, ja saat myös aggregaatin.
Kun puhun SQL Server- ja kehittäjätapahtumissa ikkunafunktioista, yleensä noin puolet salista tuntee ROW_NUMBERin. Kourallinen tietää enemmänkin. Se on pettymys, koska nämä hyödylliset funktiot ovat olleet saatavilla jo vuosia.
Lempi-ikkunafunktioni on nimeltään LAG. LAG:n avulla voit napata minkä tahansa sarakkeen tulosjoukon toiselta riviltä. Voit saavuttaa saman asian muilla menetelmillä, mutta suorituskyky on paljon huonompi. Seuraavassa on kaksi kyselyä AdventureWorks-tietokantaan. Molemmat kyselyt antavat saman tuloksen. Ensimmäinen tekee sen ulomman liitoksen avulla, kun taas toinen hyödyntää LAG:ia.
–Luettelo asiakkaista, joilla on tilauspäivämäärät ja edellisen tilauksen jälkeiset päivät
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;
-Tuottaa samat tulokset käyttämällä 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;
Toinen LAG-funktiota käyttävä kysely on yksinkertaisempi kirjoittaa ja toimii paremmin. Todellinen suoritussuunnitelma osoittaa, että ensimmäinen kysely vie 99 % erän resursseista. Toinen kysely vie vain 1 % resursseista. Tässä on kaksi suoritussuunnitelmaa, ja voit itse nähdä:
Jos et tunne ikkunafunktion syntaksia, LAG-kysely voi olla hieman hämmentävä. Kaikissa ikkunafunktioissa on OVER-lauseke, joka määrittelee laskennan ikkunan tai rivijoukon. En halua vertailla tilauspäivämääriä eri asiakkaiden kesken, joten käytin PARTITION BY -vaihtoehtoa riveiden jakamiseen ryhmiin CustomerID:n mukaan:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
OVER-lausekkeessa on myös ORDER BY -vaihtoehto. Tätä tarvitaan LAG-lausekkeen tapauksessa, jotta rivit ovat oikeassa järjestyksessä, jotta moottori voi napata vastaavat arvot oikeilta riveiltä. Lopullinen LAG-kutsuni näyttää seuraavalta:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG on vain yksi funktio monista ikkunafunktioista. Näiden funktioiden avulla ei oikeastaan voi tehdä mitään sellaista, mikä olisi mahdotonta ilman niitä, mutta ne ehdottomasti helpottavat koodausta. Huomaat myös, että monesti kyselyt toimivat nopeammin, kun ne muotoillaan käyttämällä näitä uusia funktioita.
Päädyn käyttämään ikkunafunktioita päivittäin. Ne helpottavat elämääni ja parantavat kyselyjeni suorituskykyä. Käytän niitä mielelläni, ja sinunkin pitäisi käyttää niitä.
Tietoa kirjoittajasta
Kathi Kellenberger on Linchpin People -yrityksen konsultti. Hän kirjoittaa ja puhuu mielellään SQL Server -aiheista, ja hän on kirjoittanut yli kaksi tusinaa artikkelia, osallistunut neljän kirjan kirjoittamiseen ja esitellyt monissa SQL Server -tapahtumissa. Vapaa-ajallaan Kathi viettää mielellään aikaa perheen ja ystävien kanssa, laulaa ja harrastaa pyöräilyä.
Opi vielä enemmän ikkunafunktioista ja niiden tehosta vaikeiden kyselyongelmien ratkaisemisessa Kathi Kellenbergerin kirjasta Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Kathin muita kirjoja ovat Beginning SQL Server Reporting Services (2016) ja Beginning T-SQL (2014).
Vastaa