Ikkunafunktiot (tai ikkunatoiminnot) ovat loistava tapa saada erilaisia näkökulmia tietoihin ilman, että palvelimelle tarvitsee tehdä toistuvia kutsuja kyseisiä tietoja varten. Voimme esimerkiksi kerätä sarakkeen summan ja näyttää sen vierekkäin yksityiskohtaisen tason tietojen kanssa, jolloin ”Myyntisumma” ja ”SUM(Myyntisumma)” voivat näkyä samalla rivillä. Voimme myös tehdä analyyttisiä funktioita, kuten PERCENT_RANK, ja järjestysfunktioita, kuten ROW_NUMBER, kaikki tämä muuttamatta tulosjoukon rakeisuutta tai tekemättä ylimääräisiä matkoja saman lähdetiedon hankkimiseksi uudestaan ja uudestaan.”
”Katsokaa, kuinka tasapainoilen vaivattomasti kahden Val Kilmerin munuaiskiven kanssa. Melko bully!”
Ikkunafunktiot käyttävät kaikki OVER()-lauseketta, jolla määritellään, miten funktio arvioidaan. OVER()-lauseke hyväksyy kolme eri argumenttia:
- PARTITION BY: Nollaa laskurinsa joka kerta, kun ilmoitettu(t) sarake(t) vaihtaa(vat) arvoja.
- ORDER BY: Järjestää rivit, joita funktio arvioi. Tämä ei järjestä koko tulosjoukkoa, vaan ainoastaan sen, miten funktio etenee rivien läpi.
- ROWS BETWEEN: Määrittää, miten funktion arvioimia rivejä rajoitetaan edelleen.
Kuvitellaan, että tarkastelemme yksinkertaistettuja tietoja painonnostokilpailusta. Seuraavassa on joitakin koodiesimerkkejä (teemme ne kaikki yhdellä SELECT-lauseella, koska ikkunafunktioiden lisääminen/poistaminen ei millään tavalla muuta takaisin saamiemme rivien määrää):
|
1
2
3
4
5
6
7
8
9
10
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBER listaa rivin numeron LiftID:n mukaan järjestettynä.
Laskuri nollautuu jokaisella uudella LiftDate- ja LiftPersonID-yhdistelmällä */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM laskee yhteen nostetut painot.
Ensimmäinen SUM näyttää koko tulosjoukon loppusumman.
Toinen SUM näyttää kyseisen rivin nostopäivän nostojen kokonaispainon.
Kolmas SUM näyttää kyseisen rivin nostopäivän ja henkilön nostojen kokonaispainon. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG näyttää keskimääräisen nostetun painon.
Ensimmäinen AVG näyttää keskimääräisen nostopainon kyseisen rivin nostopäivämäärän osalta.
Toinen AVG näyttää keskimääräisen nostopainon kyseisen rivin nostopäivän ja henkilön osalta. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG- ja LEAD-toiminnot mahdollistavat sen, että nykyiseltä riviltä voidaan raportoida sen takaisilla tai sitä edeltävillä riveillä olevia tietoja.
Tämä LAG-funktio palauttaa LiftWeightin 1 rivin takaa (LiftID:n mukaisessa järjestyksessä), ja jos arvoa ei löydy, se palauttaa NULL:n sijasta 0.
LEAD-funktio saa LiftWeightin 3 riviä edeltä. Koska emme määrittäneet valinnaista oletusarvoa (kuten ”0”, jonka annoimme LAG-funktiolle), se palauttaa NULL:n, jos 3 riviä eteenpäin ei ole riviä. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE JA LAST_VALUE palauttavat määritellyn sarakkeen ensimmäisen ja viimeisimmän arvon tulosjoukossa.
Tämä FIRST_VALUE-funktio palauttaa tulosjoukon ensimmäisen nostopainon.
Tämä LAST_VALUE-funktio palauttaa tulosjoukon viimeisen nostopainon.
***VAROITUS: ilman ROWS BETWEEN -merkintää LAST_VALUE-funktiossa saatat saada odottamattomia tuloksia.***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* ROWS BETWEEN:ia käyttävä SUM supistaa ikkunafunktion arvioimaa soveltamisalaa.
Funktio alkaa ja päättyy sinne, missä ROWS BETWEEN määrittelee.
Ensimmäinen SUM lisää kaikki LiftWeight-arvot riveillä nykyiseen riviin asti ja sen jälkeen.
Toinen SUM lisää kaikki LiftWeight-arvot riveillä, jotka ovat nykyisen rivin ja sitä edeltävien kolmen rivin välillä.
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
Ikkunafunktioiden harkinta
Jos sinulla ei ole SQL Server 2012:ta tai uudempaa SQL Server 2012:ta, ikkunafunktioiden kaappisi on melko tyhjä; SQL Server 2005-2008 R2 salli vain PARTITION BY:n aggregaattifunktion OVER-lausekkeessa, ja käytössäsi oli RANK() ja ROW_NUMBER(). Siinä kaikki. Jos olet kehittäjä, joka käyttää yhä jotakin näistä aiemmista versioista, tämä on vakuuttava peruste siirtyä käyttämään vuotta 2012 tai uudempaa versiota. Ajattele, kuinka paljon aikaa säästät, kun et tarvitse kirjoittaa useita CTE:itä, ja kuinka paljon nopeammin kyselyt sujuvat.
Nopeudesta puheen ollen…
Välttämällä samojen tietojen hakeminen palvelimelta vähentää taulukoiden I/O:ta. Jos käytämme indeksejä, voimme todella vähentää lukukertoja. Kompromissi on olemassa, mutta se on yleensä erittäin suotuisa. Ikkunafunktiot edellyttävät, että SQL Server rakentaa ikkunan ja laskee funktion (näkyy tehtävinä kuten Window Spool, Segment, Sequence Project ja Compute Scalar). Näin tehdessään se lisää lukuja työtaulukkoon. Tämä on kuitenkin yleensä edullisempaa kuin se, että lähdetiedot haetaan useita kertoja, aggregoidaan tarvittaessa ja liitetään yhteen. Lisäksi Worktable on olemassa tempdb:ssä, joka – mieluiten – on nopeimmalla tallennustasollasi.
Loppujen lopuksi muista, että ikkunafunktiolle asettamasi rajoitukset – PARTITION BY, ORDER BY tai ROWS BETWEEN – koskevat vain kontekstia ikkunafunktiolle eivätkä missään tapauksessa koko tulosjoukkoa. Toisin sanoen mikään, mitä käsket ikkunafunktion tehdä, ei vaikuta SELECT-lauseeseesi.