SELECT – OVER Clause (Transact-SQL) (Norsk)

• 08/11/2017
• 17 minutter å lese
• • V
  • L
  • c
  • j
  • M
  • +11

Gjelder: SQL Server (alle støttede versjoner) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallell datalager

Bestemmer partisjonering og bestilling av et radsett før den tilhørende vindusfunksjonen brukes. Det vil si at OVER-leddet definerer et vindu eller brukerdefinert sett med rader i et spørresultatsett. En vindusfunksjon beregner deretter en verdi for hver rad i vinduet. Du kan bruke OVER-setningen med funksjoner til å beregne aggregerte verdier som glidende gjennomsnitt, kumulative aggregater, løpende summer eller topp N per grupperesultater.

• Rangering funksjoner

• Aggregerte funksjoner

• Analytiske funksjoner

• NESTE VERDI FOR funksjon

Transact-SQL-syntakskonvensjoner

Syntaks

Argumenter

Vindusfunksjoner kan ha følgende argumenter i OVER -klausulen:

• PARTITION BY som deler spørringsresultatet satt i partisjoner.
• ORDER BY som definerer den logiske rekkefølgen på radene i hver partisjon av resultatsettet.
• RADER / RANGE som begrenser radene i partisjonen ved å spesifisere start- og sluttpunkter i partisjonen. Det krever ORDER BY argument og standardverdien er fra starten av partisjonen til det nåværende elementet hvis ORDER BY argumentet er spesifisert.

Hvis du ikke spesifiserer noe argument, blir vindusfunksjonene brukt på hele resultatsettet.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

DELING BY

Deler søkeresultatet satt i partisjoner. Vinduefunksjonen brukes hver partisjon separat, og beregningen starter på nytt for hver partit ion.

Hvis PARTITION BY ikke er spesifisert, behandler funksjonen alle radene i spørringsresultatet som er satt som en enkelt partisjon. Funksjon vil bli brukt på alle radene i partisjonen hvis du ikke spesifiserer ORDER BY klausul.

PARTITION BY value_expression

Spesifiserer kolonnen som radsett er partisjonert med. value_expression kan bare henvise til kolonner som er gjort tilgjengelige av FROM-setningen. value_expression kan ikke referere til uttrykk eller aliaser i listen. value_expression kan være et kolonneuttrykk, skalarundersøking, skalarfunksjon eller brukerdefinert variabel.

BESTILL AV

Definerer den logiske rekkefølgen på radene i hver partisjon av resultatsettet. Det vil si at den spesifiserer den logiske rekkefølgen som beregningen av vindusfunksjonen utføres i.

• Hvis den ikke er spesifisert, er standardrekkefølgen ASC og vindusfunksjon vil bruke alle radene i partisjonen.
• Hvis den er spesifisert, og RADER / RANGE ikke er spesifisert, brukes standard RANGE UNBOUNDED PRECEDING AND CURRENT ROW som standard for vindu ramme etter funksjonene som kan akseptere valgfri ROWS / RANGE-spesifikasjon (for eksempel min eller max).

order_by_expression
Spesifiserer en kolonne eller et uttrykk som skal sorteres. order_by_expression kan bare referere til kolonner gjort tilgjengelig av FROM-setningen. Et helt tall kan ikke spesifiseres for å representere et kolonnenavn eller alias.

SOLLER Sorteringsnavn – Angir at ORDER BY-operasjonen skal utføres i henhold til sorteringen som er angitt i sorteringsnavn. sorteringsnavn kan enten være et Windows-sorteringsnavn eller et SQL-sorteringsnavn. For mer informasjon, se Collation and Unicode Support. COLLATE gjelder bare for kolonner av typen char, varchar, nchar og nvarchar.

ASC | DESC
Angir at verdiene i den angitte kolonnen skal sorteres i stigende eller synkende rekkefølge. ASC er standard sorteringsrekkefølge.Nullverdier behandles som lavest mulige verdier.

Rader eller rekkevidde

Gjelder: SQL Server 2012 (11.x) og senere.

Ytterligere grenser radene i partisjonen ved å spesifisere start- og sluttpunkter i partisjonen. Dette gjøres ved å spesifisere en rekke rader med hensyn til gjeldende rad, enten ved logisk tilknytning eller fysisk tilknytning. Fysisk tilknytning oppnås ved å bruke ROWS-setningen.

ROWS-setningen begrenser radene i en partisjon ved å spesifisere et fast antall rader som går før eller følger den nåværende raden. Alternativt begrenser RANGE-setningen logisk radene i en partisjon ved å spesifisere et verdiområde med hensyn til verdien i den nåværende raden. Foregående og påfølgende rader er definert basert på rekkefølgen i ORDER BY-setningen. Vindusrammen «RANGE … CURRENT ROW …» inkluderer alle rader som har de samme verdiene i ORDER BY-uttrykket som den gjeldende raden. RADER MELLOM 2 FREMGÅENDE OG AKTUELL RAD betyr for eksempel at vinduet med rader som funksjonen opererer på er tre rader i størrelse, og starter med to rader før og med gjeldende rad.

UBEGRENSET FREMTID

Gjelder: SQL Server 2012 (11.x) og senere.

Angir at vinduet starter på den første raden i partisjonen. UBEGRENSET PRECEDING kan bare spesifiseres som startpunkt for vinduet.

< usignert verdispesifikasjon > PRECEDING
spesifisert med < usignert verdispesifikasjon > for å indikere antall rader eller verdier som skal gå foran den nåværende raden. Denne spesifikasjonen er ikke tillatt for RANGE.

AKTUELL RAD

Gjelder: SQL Server 2012 (11.x) og nyere.

Spesifiserer at vinduet starter eller slutter på gjeldende rad når den brukes med ROWS eller gjeldende verdi når den brukes med RANGE. AKTUELL RAD kan spesifiseres som både start- og sluttpunkt.

MELLOM OG

Gjelder: SQL Server 2012 (11.x) og senere.

Brukes med enten ROWS eller RANGE for å spesifisere vinduets nedre (start) og øvre (slutt) grensepunkt. < vindusramme bundet > definerer startpunktet for grensen og < vindusramme bundet > definerer grensepunktet. Den øvre grensen kan ikke være mindre enn den nedre grensen.

UBEGRENSET FØLGENDE

Gjelder for: SQL Server 2012 (11.x) og senere.

Spesifiserer at vinduet slutter på den siste raden av partisjonen. UBEGRENSET FØLGING kan bare angis som et vinduets sluttpunkt. For eksempel RANGE MELLOM AKTUELL RAD OG UBEGRENSET FØLGENDE definerer et vindu som starter med den nåværende raden og slutter med den siste raden i partisjonen.

< usignert verdispesifikasjon > FØLGER
Spesifisert med < usignert verdispesifikasjon > for å indikere antall rader eller verdier for å følge gjeldende rad. Når < usignert verdispesifikasjon > FØLGENDE er spesifisert som vinduets startpunkt, må sluttpunktet være < usignert verdispesifikasjon > FØLGER. RADER MELLOM 2 FØLGER OG 10 FØLGER definerer for eksempel et vindu som starter med den andre raden som følger den gjeldende raden og slutter med den tiende raden som følger den gjeldende raden. Denne spesifikasjonen er ikke tillatt for RANGE.

usignert heltall bokstavelig
Gjelder: SQL Server 2012 (11.x) og senere.

Er et positivt heltall bokstavelig (inkludert 0 ) som spesifiserer antall rader eller verdier som skal gå foran eller følge gjeldende rad eller verdi. Denne spesifikasjonen er bare gyldig for ROWS.

Generelle merknader

Mer enn én vindusfunksjon kan brukes i et enkelt spørsmål med en enkelt FROM-ledd. OVER-klausulen for hver funksjon kan variere i partisjonering og rekkefølge.

Hvis PARTISJON BY ikke er spesifisert, behandler funksjonen alle radene i søkeresultatet som er satt som en enkelt gruppe.

Viktig !

Hvis RADER / OMRÅDE er spesifisert og < vindusramme foran > brukes til < vindusrammeutstrekning > (kort syntaks) så brukes denne spesifikasjonen for startpunktet for vindusrammens grense og CURRENT ROW brukes for grensens sluttpunkt. For eksempel er «ROWS 5 PRECEDING» lik «ROWS MELLOM 5 PRECEDING AND CURRENT ROW».

Begrensninger og begrensninger

OVER-leddet kan ikke brukes med funksjonen CHECKSUM-aggregat.

RANGE kan ikke brukes med < usignert verdispesifikasjon > PRECEDING eller < usignert verdispesifikasjon > FØLGER.

Avhengig av rangering, samlet eller analytisk funksjon som brukes med OVER-setningen, < BESTILL AV BY-ledd > og / eller < RADER og RANGE-ledd > støttes kanskje ikke.

Eksempler

A. Bruke OVER-setningen med ROW_NUMBER-funksjonen

Følgende eksempel viser bruk av OVER-setningen med ROW_NUMBER-funksjonen for å vise et radnummer for hver rad i en partisjon. ORDER BY-leddet spesifisert i OVER-leddet ordner radene i hver partisjon etter kolonnen SalesYTD. ORDER BY-setningen i SELECT-setningen bestemmer rekkefølgen som hele spørringsresultatsettet returneres.

Her er resultatsettet.

B. Bruke OVER-leddet med samlede funksjoner

Følgende eksempel bruker OVER -satsen med samlede funksjoner over alle radene som returneres av spørringen. I dette eksemplet er bruk av OVER -satsen mer effektiv enn å bruke underspørsmål for å utlede samlede verdier.

Her er resultatsettet.

Følgende eksempel viser bruk av OVER -setningen med en samlet funksjon i en beregnet verdi.

Her er resultatsettet. Legg merke til at aggregatene beregnes av SalesOrderID og Percent by ProductID beregnes for hver linje i hver SalesOrderID.

C. Å produsere et glidende gjennomsnitt og kumulativ total

Følgende eksempel bruker funksjonene AVG og SUM med OVER-klausulen for å gi et glidende gjennomsnitt og kumulativ sum av årlig salg for hvert territorium i Sales.SalesPerson tabell. Dataene er partisjonert av TerritoryID og logisk ordnet av SalesYTD. Dette betyr at AVG-funksjonen beregnes for hvert område basert på salgsåret. Legg merke til at for TerritoryID 1 er det to rader for salgsåret 2005 som representerer de to selgerne med salg det året. Gjennomsnittlig salg for disse to radene blir beregnet, og deretter er den tredje raden som representerer salg for året 2006 inkludert i beregningen.

Her er resultatsettet.

I dette eksemplet, OVER-klausulen inkluderer ikke PARTITION BY. Dette betyr at funksjonen vil bli brukt på alle radene som returneres av spørringen. ORDER BY-leddet spesifisert i OVER-setningen bestemmer den logiske rekkefølgen AVG-funksjonen brukes på. Spørringen returnerer et glidende gjennomsnitt av salg etter år for alle salgsområder spesifisert i WHERE-setningen. ORDER BY-setningen spesifisert i SELECT-setningen bestemmer rekkefølgen som radene i spørringen vises i.

Her er resultatsettet.

D. Spesifisere ROWS-setningen

Gjelder: SQL Server 2012 (11.x) og senere.

Følgende eksempel bruker ROWS-setningen for å definere et vindu som radene beregnes som gjeldende rad og N antall rader som følger (1 rad i dette eksemplet).

Her er resultatsettet.

I det følgende eksemplet er ROWS-setningen spesifisert med UNBOUNDED PRECEDING. Resultatet er at vinduet starter på den første raden av partisjonen.

Her er resultatsettet.

Eksempler: Parallell datalager

E. Bruke OVER-setningen med ROW_NUMBER-funksjonen

Følgende eksempel returnerer ROW_NUMBER for salgsrepresentanter basert på deres tildelte salgskvote.

Her er et delvis resultatsett.

F. Bruke OVER-leddet med samlede funksjoner

Følgende eksempler viser bruk av OVER-setningen med samlede funksjoner. I dette eksemplet er bruk av OVER-klausulen mer effektiv enn å bruke underspørringer.

Her er resultatsettet.

Følgende eksempel viser bruk av OVER-setningen med en samlet funksjon i en beregnet verdi. Legg merke til at aggregatene blir beregnet av SalesOrderNumber og prosentandelen av den totale salgsordren blir beregnet for hver linje i hver SalesOrderNumber.

Den første starten på dette resultatsettet er:

Se også

Aggregerte funksjoner (Transact-SQL)
Analytiske funksjoner (Transact-SQL)
Utmerket blogginnlegg om vindusfunksjoner og OVER, på sqlmag.com, av Itzik Ben-Gan