OVER-lauseke lisättiin SQL Serveriin ”takaisin” SQL Server 2005: ssä, ja sitä laajennettiin SQL: ssä Server 2012. Sitä käytetään pääasiassa ”Window Functions” -ikkunassa; ainoa poikkeus on järjestysfunktio NEXT VALUE FOR. OVER-lauseketta käytetään määrittämään, mitä kyselyn rivejä käytetään funktiossa, missä järjestyksessä ne arvioidaan funktiolla ja milloin funktion laskelmat on aloitettava uudelleen. Koska sitä käytetään yhdessä muiden toimintojen kanssa, ja tässä artikkelissa on kyse vain OVER-lauseesta, näistä toiminnoista puhutaan vain, koska se liittyy annettujen esimerkkien OVER-lauseeseen.

OVER-lauseke on:

<function> OVER ( )

Kun tarkastellaan syntaksia, näyttää siltä, että kaikki alilauseet ovat valinnaisia. Itse asiassa jokainen funktio, joka voi käyttää OVER-lauseketta, määrittää, mitkä alakappaleista ovat sallittuja ja mitkä. Käytetystä toiminnosta riippuen itse OVER-lause voi olla valinnainen. Tämän artikkelin lopussa on kaavio, joka näyttää, mitkä toiminnot sallivat / vaativat OVER-lauseen osia.

PARTITION BY -lauseketta käytetään jakamaan kyselyn tulosjoukko datajoukkoihin tai osiot. Jos PARTITION BY -lauseketta ei käytetä, koko kyselyn tulosjoukko on käytetty osio. Käytettyä ikkunatoimintoa sovelletaan kuhunkin osioon erikseen, ja laskenta, jonka toiminto suorittaa, aloitetaan uudelleen kullekin osiolle. Määrität joukon arvoja, jotka määrittävät osiot, joille kysely jaetaan. Nämä arvot voivat olla sarakkeita, skalaarifunktioita, skalaarikyselyjä tai muuttujia.

Tarkastellaan esimerkiksi seuraavaa kyselyä:

SELECT COUNT(*)FROM .sys.indexes;

Tämä kysely palauttaa seuraavan tulosjoukon:

Tämä on yksinkertaisesti kyselyn palauttamien rivien määrä – tässä tapauksessa indeksien määrä msdb-tietokannassa. Lisätään nyt OVER-lauseke tähän kyselyyn:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Lyhennetyt tulokset ovat:

Tämä kysely palauttaa jokaisen hakemiston object_id ja index_id sekä tulosjoukon hakemistojen kokonaismäärän. Koska PARTITION BY -lauseketta ei käytetty, koko tulosjoukkoa käsiteltiin yhtenä osiona. Nyt on aika lisätä PARTITION BY -lauseke ja nähdä, miten tämä muuttaa tuloksia:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Lyhennetyt tulokset ovat:

Tämä kysely palauttaa rivin jokaiselle hakemistolle, mutta nyt kysely määrittää objektin_sarakkeen PARTITION BY -lausekkeen, joten laskutoiminto palauttaa hakemistojen määrän kyseisellä objektin_tunnuksella. ORDER BY -lauseke säätelee järjestystä, jossa funktio arvioi rivit. Tämä osoitetaan pian. ROWS- tai RANGE-lauseke määrittää osion rivien osajoukon, joka on sovellettava funktioon. Kun käytät RIVIT tai ALUE, määrität ikkunan alku- ja loppupisteen. Sallitut arvot ovat:

Ikkunan määrittämisessä on kaksi syntaksia:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Jos vain alkukehys on määritetty, oletusarvoinen lopetuskehys on NYKYINEN RIVI.

RAJOITTAMATON avainsana määrittää osion alun (ENNEN), tai loppu osio (seuraavalle). NYKYINEN RIVI määrittää, että nykyinen rivi on joko ikkunan alku tai ikkunan loppu riippuen siitä, missä ikkunakehyksen sijainnissa sitä käytetään. ”N” määrittää rivien määrän joko ennen nykyistä riviä (ENNEN ENNEN ) tai nykyisen rivin jälkeen (seuraavalle), jota käytetään ikkunakehykseen.

Seuraavat ovat kelvollisia ikkunan määrityksiä:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Jos haluat käyttää ROWS- tai RANGE-lauseketta, sinun on määritettävä myös ORDER BY -lauseke.Vastaavasti, jos käytät ORDER BY -lauseketta ja et määritä ROWS- tai RANGE-lauseketta, oletusarvo RAJATTOMAN ENNEN KÄYTTÖÄ JA VIRTAA ROW käytetään.

Osoita ORDER BY- ja ROWS- tai RANGE-lausekkeita luomalla testitiedot: kaksi tiliä, neljä päivämäärää tiliä kohden ja summa kullekin päivämäärälle. Kysely näyttää molemmat eri tavalla käytettyjä lausekkeita:

Tämä kysely palauttaa seuraavan tulosjoukon:

” RowNbr ”-saraketta käytetään g COUNT-funktio palauttaa osioiden rivien määrän. TranDate on tilannut osion, ja määritämme ikkunarungon kaikista riveistä osion alusta nykyiseen riviin. Ensimmäisellä rivillä on vain yksi rivi ikkunakehyksessä, joten arvo ”1” palautetaan. Toisella rivillä on nyt kaksi riviä ikkunakehyksessä, joten arvo ”2” palautetaan. Ja niin edelleen tämän tilin muilla riveillä.

Koska PARTITION BY -lauseke määrittää tilin, tilin muuttuessa funktiolaskelmat nollataan, mikä näkyy tarkastelemalla tulosjoukon toisen tilin rivejä. Tämä on esimerkki ”käynnissä olevasta” aggregaatista, jossa aggregaatio perustuu aikaisempiin laskelmiin. Esimerkki siitä, milloin sitä käytettäisit, olisi laskettaessa pankkitilisi saldoa jokaisen tapahtuman jälkeen (muuten kutsutaan juoksevaksi kokonaissummaksi).

”DateCount” -sarakkeessa lasketaan rivien lukumäärä päivämäärän mukaan jaettuna. Tässä esimerkissä jokaisella tilillä on tapahtuma kullakin samalla neljällä päivämäärällä, joten jokaisella päivämäärällä on kaksi tapahtumaa (yksi kullekin tilille). Tämän seurauksena arvo ”2” palautetaan jokaiselle riville. Tämä on samanlainen kuin laskennan suorittaminen, joka käyttää GROUP BY -päivää päivämääränä; ero on, että summa palautetaan jokaiselle riville eikä vain kerran kullekin päivämäärälle. milloin käytät tätä menetelmää, on näyttää ”Y-rivi X” tai laskea prosenttiosuus nykyisestä rivistä kokonaismäärään.

”Last2Count” -sarake laskee rivit osion sisällä nykyiselle riville ja sitä edeltävälle yhdelle riville. Koska jokaisen tilin ensimmäisellä rivillä ei ole yhtään sitä edeltävää riviä, arvo ”1” palautetaan. Kunkin tilin jäljellä oleville riveille palautetaan arvo ”2”. Tämä on esimerkki ”liikkuvasta” tai ”liukuvasta” aggregaatista. Esimerkki siitä, milloin käytät tätä menetelmää, on laskea bonus perustuen kahden viime kuukauden myynti.

Tässä vaiheessa olen osoittanut vain ROWS-lausekkeen. RANGE-lause toimii samalla tavalla, mutta sen sijaan, että käsittelisi rivejä sijaintitarkoituksessa, se käsittelee kyseisen rivin palauttamat arvot. Koska N PRECEDING / FOLLOWING -lauseketta ei voida käyttää paikalla, katsotaanpa nopeasti ROWS: n ja RANGE: n välinen ero käyttämällä molempia samassa kyselyssä. Tässä meillä on luettelo ihmisistä (kutsutaan heitä DBA: ksi) ja heidän tuntimaksut. Huomaa, että rivit, joissa on RowID-arvot 4 & 5 ja 12 & 13: lla on sama nopeus. Kysely tiivistää hinnat kahdesti, kerran käyttämällä ROWS ja toista RANGE:

Tämä kysely tuottaa seuraavan tulosjoukon:

Sekä SumByRows- että SumByRange-sarakkeissa OVER-lause on identtinen ROWS / RANGE-lauseketta lukuun ottamatta. Huomaa myös, että koska loppualuetta ei ole määritetty, oletusarvo on käyttää NYKYINEN RIVI. Koska laskemme yhteen palkan nykyisen rivin tuloksen alusta, laskemme todella Palkka-sarakkeen juoksevan summan. SumByRows -sarakkeessa arvo lasketaan käyttämällä ROWS-lauseketta, ja voimme nähdä, että nykyisen rivin summa on nykyisen rivin Palkka plus edellisen rivin summa. RANGE-lauseke toimii kuitenkin pois Palkka-sarakkeen arvosta, joten se tiivistää kaikki rivit samalla tai pienemmällä palkalla. Tämän seurauksena SumByRange-arvo on sama arvo kaikille riveille, joilla on sama palkka.

Yksi tärkeä huomautus: OVER-lausekkeen ORDER BY -lauseke ohjaa vain järjestystä, jossa osion rivejä käytetään ikkunatoiminnolla. Se ei hallitse lopullisten tulosjoukkojen järjestystä. Ilman ORDER BY -lausetta itse kyselyssä rivien järjestystä ei taata. Saatat huomata, että kyselysi saattaa palata viimeksi määritetyn OVER-lausekkeen mukaisessa järjestyksessä – tämä johtuu tavasta, jolla tämä on tällä hetkellä toteutettu SQL Serverissä. Jos Microsoftin SQL Server -tiimi muuttaa toimintatapaansa, se ei välttämättä enää tilaa tilauksiasi tällä tavoin. Jos tarvitset tietyn tilauksen tulosjoukolle, sinun on annettava ORDER BY -lauseke itse kyselyä vastaan.

Lopuksi tässä on kaavio useista toiminnoista, jotka voivat käyttää OVER-lauseketta, sekä mitkä lauseen osat ovat sallittuja / pakollisia / valinnaisia.

R-vaaditaan, O-valinnainen, X-ei sallittu