OVER-klausulen blev tilføjet til SQL Server “langt tilbage” i SQL Server 2005, og den blev udvidet i SQL Server 2012. Det bruges overvejende sammen med “Vinduesfunktioner”; den eneste undtagelse er sekvensfunktionen NÆSTE VÆRDI FOR. OVER-klausulen bruges til at bestemme, hvilke rækker fra forespørgslen der anvendes på funktionen, hvilken rækkefølge de evalueres i af denne funktion, og hvornår funktionens beregninger skal genstartes. Da den bruges sammen med andre funktioner, og denne artikel kun specifikt handler om OVER-klausulen, vil disse funktioner kun blive talt om, da den vedrører OVER-klausulen i de givne eksempler.

Syntaksen for OVER-klausulen er:

<function> OVER ( )

Når man ser på syntaksen, ser det ud til, at alle underklausuler er valgfri. Faktisk bestemmer hver funktion, der kan bruge OVER-klausulen, hvilke af underklausulerne der er tilladt, og hvilke der kræves. Afhængigt af den anvendte funktion kan OVER-klausulen være valgfri. Der er et diagram i slutningen af denne artikel, der viser, hvilke funktioner der tillader / kræver hvilke dele af OVER-klausulen.

PARTITION BY-klausulen bruges til at opdele resultatsættet fra forespørgslen i datadele, eller skillevægge. Hvis PARTITION BY-klausulen ikke bruges, er hele resultatsættet fra forespørgslen den partition, der skal bruges. Den anvendte vinduesfunktion anvendes på hver partition separat, og den beregning, som funktionen udfører, genstartes for hver partition. Du definerer et sæt værdier, der bestemmer den eller de partitioner, som forespørgslen skal opdeles i. Disse værdier kan være kolonner, skalære funktioner, skalære underforespørgsler eller variabler.

Lad os for eksempel undersøge følgende forespørgsel:

SELECT COUNT(*)FROM .sys.indexes;

Denne forespørgsel returnerer følgende resultatsæt:

Dette er simpelthen antallet af rækker, som forespørgslen returnerer – i dette tilfælde antallet af indekser i msdb-databasen. Lad os nu tilføje OVER-klausulen til denne forespørgsel:

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

De forkortede resultater er:

Denne forespørgsel returnerer objekt_id og indeks_id for hvert indeks og det samlede antal indekser i resultatsættet. Da en PARTITION BY-klausul ikke blev brugt, blev hele resultatsættet behandlet som en enkelt partition. Det er nu tid til at tilføje PARTITION BY-klausulen og se, hvordan dette ændrer resultaterne:

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

De forkortede resultater er:

Denne forespørgsel returnerer en række for hvert indeks, men nu angiver forespørgslen en PARTITION BY-klausul i kolonnen object_id, så tællefunktionen returnerer antallet af indekser på det bestemte objekt_id. ORDER BY-klausulen styrer rækkefølgen, hvor rækkerne evalueres af funktionen. Dette vil snart blive demonstreret. ROWS- eller RANGE-udtrykket bestemmer delsættet af rækker inden for den partition, der skal anvendes på funktionen. Når du bruger ROWS eller RANGE, angiver du start- og slutpunktet for vinduet. De tilladte værdier er:

Der er to syntakser til angivelse af vinduet:

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

Hvis bare startrammen er angivet, er standardrammen for slutningen CURRENT ROW.

Det ubegrænsede nøgleord angiver begyndelsen på partitionen (for PRECEDING) eller slutningen af partitionen (til FØLGENDE). AKTUEL RÆDE angiver, at den aktuelle række enten er starten på vinduet eller slutningen af vinduet, afhængigt af hvilken vinduesrammeposition det bruges i. “N” angiver et antal rækker enten før den aktuelle række (for FORUDGÅENDE ) eller efter den aktuelle række (til FØLGENDE), der skal bruges til vinduesrammen.

Følgende er gyldige vinduesspecifikationer:

-- 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

For at kunne bruge ROWS- eller RANGE-klausulen skal du også specificere ORDER BY-klausulen. Omvendt, hvis du bruger ORDER BY-klausulen, og du ikke angiver en ROWS- eller RANGE-klausul, er standard RANGE MELLEM UBEGRUNDET FORUDGÅENDE OG AKTUELLE ROW bruges.

For at demonstrere ORDER BY- og ROWS- eller RANGE-klausulerne, lad os oprette nogle testdata: to konti, fire datoer pr. Konto og et beløb for hver dato. Forespørgslen viser begge disse klausuler, der bruges på forskellige måder:

Denne forespørgsel returnerer følgende resultatsæt:

” RowNbr ”-søjlen er brugt g COUNT-funktionen for at returnere, hvor mange rækker der er i partitionen. Partitionen er bestilt af TranDate, og vi specificerer en vinduesramme for alle rækkerne fra starten af partitionen gennem den aktuelle række. For den første række er der kun én række i vinduesrammen, så værdien “1” returneres. For den anden række er der nu to rækker i vinduesrammen, så værdien “2” returneres. Og så videre gennem resten af rækkerne i denne konto.

Da PARTITION BY-klausulen specificerer kontoen, nulstilles funktionsberegningerne, når kontoen ændres, hvilket kan ses ved at undersøge rækkerne for den anden konto i resultatsættet. Dette er et eksempel på en “kørende” aggregering, hvor aggregeringen bygger på tidligere beregninger. Et eksempel på, hvornår du vil bruge dette, er at beregne din bankkontosaldo efter hver transaktion (ellers kendt som en løbende total).

Kolonnen “DateCount” udfører en optælling af, hvor mange rækker, der er opdelt efter datoen. I dette eksempel har hver af kontiene en transaktion på hver af de samme fire datoer, så hver dato har to transaktioner (en for hver konto). Dette resulterer i, at værdien “2” returneres for hver række. Dette svarer til at udføre en optælling, der bruger GROUP BY til datoen. Forskellen er, at det samlede antal returneres for hver række i stedet for kun en gang for hver dato. Et eksempel af hvornår du ville bruge denne metode, ville være at vise en “række X af Y” eller at beregne en procentdel af den aktuelle række til det samlede beløb.

Kolonnen “Last2Count” udfører et antal af rækkerne inden for partitionen for den aktuelle række og den ene række umiddelbart forud for den. For den første række på hver konto returneres værdien “1”, da der ikke er nogen rækker, der går forud for den. For de resterende rækker på hver konto returneres en værdi på “2”. Dette er et eksempel på en “bevægelig” eller “glidende” sammenlægning. Et eksempel på, hvornår du bruger denne metode, er at beregne en bonus baseret på sidste to måneders salg.

På dette tidspunkt har jeg kun vist ROWS-klausulen. RANGE-klausulen fungerer på en lignende måde, men i stedet for at håndtere rækkerne på en positionel måde beskæftiger den sig med værdier, der returneres af den række. Det er fordi det ikke er positionelt, at de N FORUDgående / FØLGENDE klausuler ikke kan bruges. Lad os se hurtigt på forskellen mellem RÆKER og RÆKKE ved at bruge dem begge i samme forespørgsel. Her har vi en liste over personer (lad os kalde dem DBA’er) og deres timesatser. Bemærk, at rækkerne med RowIDs 4 & 5 og 12 & 13 har samme hastighed. Forespørgslen opsummerer hastighederne to gange, én gang ved brug af ROWS og den anden ved hjælp af RANGE:

Denne forespørgsel producerer følgende resultatsæt:

I både kolonnerne SumByRows og SumByRange er OVER-klausulen identisk med undtagelse af ROWS / RANGE-klausulen. Bemærk også, at da slutområdet ikke blev specificeret, er standard at bruge CURRENT ROW. Da vi summerer lønnen fra begyndelsen af det resultat, der er angivet gennem den aktuelle række, beregner vi virkelig det samlede løn i kolonnen Løn. I kolonnen SumByRows beregnes værdien ved hjælp af ROWS-klausulen, og vi kan se, at summen af den aktuelle række er den aktuelle ræks løn plus den foregående ræks samlede. RANGE-klausulen fungerer dog ud fra værdien af kolonnen Løn, så den opsummerer alle rækker med den samme eller lavere løn. Dette resulterer i, at SumByRange-værdien er den samme værdi for alle rækker med samme løn.

En vigtig note: ORDER BY-klausulen i OVER-klausulen styrer kun rækkefølgen, hvor rækkerne i partitionen skal bruges ved vinduesfunktionen. Det styrer ikke rækkefølgen af det endelige resultatsæt. Uden en ORDER BY-klausul i selve forespørgslen er rækkefølgen af rækkerne ikke garanteret. Du bemærker muligvis, at din forespørgsel muligvis vender tilbage i rækkefølgen af den sidst angivne OVER-klausul – dette skyldes den måde, som dette i øjeblikket er implementeret i SQL Server. Hvis SQL Server-teamet hos Microsoft ændrer den måde, det fungerer på, bestiller det muligvis ikke længere dine resultater på den måde, som du i øjeblikket observerer. Hvis du har brug for en bestemt ordre til resultatsættet, skal du angive en ORDER BY-klausul mod selve forespørgslen.

Endelig er her et diagram over de forskellige funktioner, der kan bruge OVER-klausulen samt hvilke dele af klausulen er tilladt / krævet / valgfri.

R-krævet, O-valgfri, X-ikke tilladt