OVER-klausulen ble lagt til SQL Server «helt tilbake» i SQL Server 2005, og den ble utvidet i SQL Server 2012. Den brukes hovedsakelig sammen med «Window Functions»; det eneste unntaket er sekvensfunksjonen NESTE VERDI FOR. OVER-setningen brukes til å bestemme hvilke rader fra spørringen som brukes på funksjonen, hvilken rekkefølge de blir evaluert i av den funksjonen, og når funksjonens beregninger skal startes på nytt. Siden den brukes sammen med andre funksjoner, og denne artikkelen handler om spesifikt bare OVER-klausulen, vil disse funksjonene bare bli snakket om når den gjelder OVER-klausulen i eksemplene som er gitt.

Syntaksen til OVER-setningen er:

<function> OVER ( )

Når man ser på syntaksen, ser det ut til at alle underklausuler er valgfrie. Faktisk bestemmer hver funksjon som kan bruke OVER-klausulen hvilken av underklausulene som er tillatt, og hvilke som kreves. Avhengig av funksjonen som brukes, kan OVER-klausulen være valgfri. Det er et diagram på slutten av denne artikkelen som viser hvilke funksjoner som tillater / krever hvilke deler av OVER-setningen.

PARTITION BY-setningen brukes til å dele resultatsettet fra spørringen inn i delmengder, eller skillevegger. Hvis klausulen PARTITION BY ikke brukes, er hele resultatsettet fra spørringen partisjonen som skal brukes. Vindusfunksjonen som brukes, brukes hver partisjon separat, og beregningen som funksjonen utfører startes på nytt for hver partisjon. Du definerer et sett med verdier som bestemmer partisjonen / partisjonene du vil dele spørringen i. Disse verdiene kan være kolonner, skalarfunksjoner, skalære underspørringer eller variabler.

La oss for eksempel undersøke følgende spørsmål:

SELECT COUNT(*)FROM .sys.indexes;

Denne spørringen returnerer følgende resultatsett:

Dette er ganske enkelt antall rader som er returnert av spørringen – i dette tilfellet, antall indekser i msdb-databasen. La oss nå legge til OVER-leddet i dette spørsmålet:

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

De forkortede resultatene er:

Denne spørringen returnerer objekt-ID og indeks-ID for hver indeks, og det totale antallet indekser i resultatsettet. Siden en PARTITION BY-ledd ikke ble brukt, ble hele resultatsettet behandlet som en enkelt partisjon. Det er nå på tide å legge til PARTITION BY-leddet og se hvordan dette endrer resultatene:

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

De forkortede resultatene er:

Dette spørsmålet returnerer en rad for hver indeks, men nå angir spørringen en PARTITION BY-ledd i kolonnen object_id, slik at tellefunksjonen returnerer antall indekser på det bestemte objektet_id. ORDER BY-leddet kontrollerer rekkefølgen at radene evalueres av funksjonen. Dette vil demonstreres om kort tid. ROWS- eller RANGE-setningen bestemmer delsett av rader i partisjonen som skal brukes på funksjonen. Når du bruker ROWS eller RANGE, spesifiserer du start- og sluttpunktet til vinduet. De tillatte verdiene er:

Det er to syntakser for å spesifisere vinduet:

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

Hvis bare begynnelsesrammen er spesifisert, er standard sluttramme AKTUELL RAD.

Nøkkelordet UBEGRENSET spesifiserer begynnelsen på partisjonen (for PRECEDING), eller slutten av partisjonen (for FØLGENDE). AKTUELL RAD spesifiserer at den gjeldende raden enten er starten på vinduet eller slutten av vinduet, avhengig av hvilken vindusrammeposisjon den brukes i. «N» angir et antall rader enten før den nåværende raden (for FREMTIDENDE ), eller etter gjeldende rad (for FØLGENDE) som skal brukes til vindusrammen.

Følgende er gyldige vindusspesifikasjoner:

-- 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 å kunne bruke ROWS- eller RANGE-setningen, må du også spesifisere ORDER BY-setningen. Omvendt, hvis du bruker ORDER BY-setningen og du ikke spesifiserer en RADE- eller RANGE-setning, er standard RANGE MELLOM UGRENSET FREMTIDIG OG STRØM ROW brukes.

For å demonstrere ORDER BY- og ROWS- eller RANGE-setningene, la oss lage noen testdata: to kontoer, fire datoer per konto og et beløp for hver dato. Spørringen vil vise begge disse klausuler som brukes på forskjellige måter:

Dette spørsmålet returnerer følgende resultatsett:

The » RowNbr ”-kolonnen er brukbar g TELL-funksjonen for å returnere hvor mange rader som er i partisjonen. Partisjonen er bestilt av TranDate, og vi spesifiserer en vindusramme for alle radene fra starten av partisjonen til den nåværende raden. For den første raden er det bare en rad i vindusrammen, så verdien «1» returneres. For den andre raden er det nå to rader i vindusrammen, slik at verdien «2» returneres. Og så videre gjennom resten av radene i denne kontoen.

Siden PARTITION BY-klausulen spesifiserer kontoen, tilbakestilles funksjonsberegningene når kontoen endres, noe som kan sees ved å undersøke radene for den andre kontoen i resultatsettet. Dette er et eksempel på en «løpende» aggregering, hvor aggregeringen bygger på tidligere beregninger. Et eksempel på når du bruker dette vil være når du beregner bankkontosaldoen din etter hver transaksjon (ellers kjent som en løpende sum).

«DateCount» -kolonnen utfører en telling av hvor mange rader, partisjonert av datoen. I dette eksemplet har hver av kontoene en transaksjon på hver av de samme fire datoene, så hver dato har to transaksjoner (en for hver konto). Dette resulterer i at verdien «2» returneres for hver rad. Dette ligner på å utføre en telling som bruker GROUP BY for datoen. Forskjellen er at totalen returneres for hver rad i stedet for bare en gang for hver dato. Et eksempel av når du bruker denne metoden, vil være å vise en «Rad X på Y», eller å beregne en prosentandel av den gjeldende raden til totalt.

«Last2Count» -kolonnen utfører en telling av radene innenfor partisjonen, for den gjeldende raden og den ene raden rett foran den. For den første raden i hver konto, siden det ikke er noen rader foran den, returneres verdien «1». For de resterende radene i hver konto returneres verdien «2». Dette er et eksempel på en «flytting» eller «glidende» aggregasjon. Et eksempel på når du vil bruke denne metoden vil være å beregne en bonus basert på siste to måneders salg.

På dette tidspunktet har jeg bare vist ROWS-setningen. RANGE-setningen fungerer på en lignende måte, men i stedet for å håndtere radene på en posisjonell måte, handler den om verdier som er returnert av den raden. Det er fordi det ikke er posisjonelt at de N FØRSTE / FØLGENDE klausulene ikke kan brukes. La oss se raskt på forskjellen mellom RADER og RANGE ved å bruke dem begge i samme spørring. liste over personer (la oss kalle dem DBA), og timeprisene deres. Vær oppmerksom på at radene med RowIDs 4 & 5 og 12 & 13 har samme hastighet. Spørringen vil oppsummere prisene to ganger, en gang ved bruk av ROWS og den andre ved bruk av RANGE:

Denne spørringen gir følgende resultatsett:

I både SumByRows og SumByRange-kolonnene er OVER-setningen identisk med unntak av ROWS / RANGE-setningen. Legg også merke til at siden sluttområdet ikke ble spesifisert, er standard å bruke CURRENT ROW. Siden vi summerer lønnen fra begynnelsen av resultatet som er satt gjennom gjeldende rad, er det vi virkelig beregner en løpende sum av lønnskolonnen. I SumByRows-kolonnen beregnes verdien ved hjelp av ROWS-setningen, og vi kan se at summen av gjeldende rad er den nåværende radens lønn pluss den forrige radens totale. Imidlertid fungerer RANGE-klausulen av verdien av lønnskolonnen, så den oppsummerer alle rader med samme eller lavere lønn. Dette resulterer i at SumByRange-verdien er den samme verdien for alle rader med samme lønn.

En viktig merknad: ORDER BY-setningen i OVER-klausulen styrer bare rekkefølgen som radene i partisjonen skal brukes av vindusfunksjonen. Det kontrollerer ikke rekkefølgen på det endelige resultatsettet. Uten en ORDER BY-ledd i selve spørringen er rekkefølgen på radene ikke garantert. Du vil kanskje legge merke til at spørringen din kan komme tilbake i rekkefølgen til den sist angitte OVER-klausulen – dette skyldes måten dette for øyeblikket er implementert i SQL Server. Hvis SQL Server-teamet hos Microsoft endrer måten det fungerer på, kan det hende at det ikke lenger bestiller resultatene på den måten du for øyeblikket observerer. Hvis du trenger en bestemt rekkefølge for resultatsettet, må du oppgi en ORDER BY-ledd mot selve spørringen.

Til slutt, her er et diagram over de forskjellige funksjonene som kan bruke OVER-setningen, samt hvilke deler av paragrafen som er tillatt / påkrevd / valgfri.

R-påkrevd, O-valgfri, X-ikke tillatt