VÆLG – OVER-klausul (Transact-SQL)

• 08/11/2017
• 17 minutter at læse
• • V
  • L
  • c
  • j
  • M
  • +11

Gælder for: SQL Server (alle understøttede versioner) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallelt datalager

Bestemmer partitionering og rækkefølge af et rækkesæt, inden den tilknyttede vinduesfunktion anvendes. Det vil sige OVER-klausulen definerer et vindue eller brugerdefineret sæt af rækker inden for et forespørgselsresultatsæt. En vinduesfunktion beregner derefter en værdi for hver række i vinduet. Du kan bruge OVER-klausulen med funktioner til at beregne aggregerede værdier såsom glidende gennemsnit, kumulative aggregater, kørende totaler eller et øverste N pr. Grupperesultater.

• Rankingfunktioner

• Samlede funktioner

• Analytiske funktioner

• NÆSTE VÆRDI FOR funktion

Transact-SQL-syntakskonventioner

Syntaks

Argumenter

Vinduesfunktioner kan have følgende argumenter i deres OVER -klausul:

• PARTITION BY, der opdeler forespørgselsresultatet i partitioner.
• ORDER BY, der definerer den logiske rækkefølge for rækkerne inden for hver partition af resultatsættet.
• RÆKER / RÆKKE, der begrænser rækkerne i partitionen ved at angive start- og slutpunkter inden for partitionen. Det kræver ORDER BY -argument, og standardværdien er fra starten af partitionen til det aktuelle element, hvis ORDER BY -argumentet er angivet.

Hvis du ikke angiver noget argument, anvendes vinduesfunktionerne på hele resultatsættet.

objekt_id	min	maks
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Opdeler forespørgselsresultatet i partitioner. Vinduesfunktionen anvendes hver partition separat, og beregningen genstartes for hver partit ion.

Hvis PARTITION BY ikke er angivet, behandler funktionen alle rækker af forespørgselsresultatet som en enkelt partition. Funktion vil blive anvendt på alle rækker i partitionen, hvis du ikke angiver ORDER BY klausul.

PARTITION BY value_expression

Angiver den kolonne, hvormed rækkesættet er partitioneret. value_expression kan kun henvise til kolonner, der er tilgængelige med FROM-klausulen. værdi_ekspression kan ikke henvise til udtryk eller aliaser på listen. værdi_ekspression kan være et kolonneekspression, en skalarundersøgelse, en skalarfunktion eller en brugerdefineret variabel.

ORDER BY

Definerer den logiske rækkefølge for rækkerne inden for hver partition af resultatsættet. Det vil sige, det specificerer den logiske rækkefølge, i hvilken vinduesfunktionsberegningen udføres.

• Hvis den ikke er specificeret, er standardrækkefølgen ASC og vinduesfunktion bruger alle rækker i partition.
• Hvis det er angivet, og der ikke er angivet en RÆK / RÆKKE, bruges standard RANGE UNBOUNDED PRECEDING AND CURRENT ROW som standard for vindue ramme efter de funktioner, der kan acceptere valgfri ROWS / RANGE-specifikation (for eksempel min eller max).

order_by_expression
Angiver en kolonne eller et udtryk, der skal sorteres på. order_by_expression kan kun henvise til kolonner, der er tilgængelige med FROM-klausulen. Et heltal kan ikke angives til at repræsentere et kolonnenavn eller alias.

COLLATE collation_name
Angiver, at ORDER BY-operationen skal udføres i henhold til den sortering, der er angivet i collation_name. sorteringsnavn kan enten være et Windows-sorteringsnavn eller et SQL-sorteringsnavn. For mere information, se Collation and Unicode Support. COLLATE gælder kun for kolonner af typen char, varchar, nchar og nvarchar.

ASC | DESC
Angiver, at værdierne i den angivne kolonne skal sorteres i stigende eller faldende rækkefølge. ASC er standardsorteringsrækkefølgen.Nulværdier behandles som de lavest mulige værdier.

RÆKER eller RANGE

Gælder for: SQL Server 2012 (11.x) og senere.

Yderligere grænser rækkerne i partitionen ved at angive start- og slutpunkter i partitionen. Dette gøres ved at specificere en række rækker i forhold til den aktuelle række enten ved logisk tilknytning eller fysisk tilknytning. Fysisk tilknytning opnås ved at bruge ROWS-klausulen.

ROWS-klausulen begrænser rækkerne i en partition ved at angive et fast antal rækker, der går forud for eller følger den aktuelle række. Alternativt begrænser RANGE-klausulen rækkerne inden for en partition logisk ved at angive en række værdier i forhold til værdien i den aktuelle række. Forudgående og efterfølgende rækker er defineret baseret på rækkefølgen i ORDER BY-klausulen. Vinduesrammen “RANGE … CURRENT ROW …” inkluderer alle rækker, der har de samme værdier i ORDER BY-udtrykket som den aktuelle række. For eksempel betyder RÆKER MELLEM 2 FORUDGÅENDE OG LØBENDE RÆDE, at vinduet med rækker, som funktionen fungerer på, er tre rækker i størrelse, begyndende med 2 rækker forud til og med den aktuelle række.

UBEGRUNDET FORUDGÅENDE

Gælder for: SQL Server 2012 (11.x) og senere.

Angiver, at vinduet starter ved den første række i partitionen. UBEGRÆNSET FORUDGÅENDE kan kun angives som vindues startpunkt.

< usigneret værdispecifikation > FORUDGÅENDE – Specificeret med < usigneret værdispecifikation > for at angive antallet af rækker eller værdier, der går forud for den aktuelle række. Denne specifikation er ikke tilladt for RANGE.

CURRENT ROW

Gælder for: SQL Server 2012 (11.x) og nyere.

Angiver, at vinduet starter eller slutter ved den aktuelle række, når den bruges med ROWS eller den aktuelle værdi, når den bruges med RANGE. AKTUEL RÆDE kan specificeres som både start- og slutpunkt.

MELLEM OG

Gælder for: SQL Server 2012 (11.x) og senere.

Anvendes med enten ROWS eller RANGE til at specificere vinduets nedre (start) og øvre (slut) grænsepunkter. < vinduesramme bundet > definerer startpunktet for grænsen og < vinduesramme bundet > definerer grænsens slutpunkt. Den øvre grænse kan ikke være mindre end den nedre grænse.

Ubegrænset FØLGENDE

Gælder for: SQL Server 2012 (11.x) og senere.

Angiver, at vinduet slutter ved den sidste række i partitionen. UBEGRENSET FØLGENDE kan kun angives som et vinduets slutpunkt. For eksempel RANGE MELLEM AKTUEL RÆK OG UBEGRÆNSET FØLGENDE definerer et vindue, der starter med den aktuelle række og slutter med den sidste række i partitionen.

< usigneret værdispecifikation > FØLGENDE
Specificeret med < usigneret værdispecifikation > for at angive antallet af rækker eller værdier for at følge den aktuelle række. Når < usigneret værdispecifikation > FOLLOWING er angivet som vinduesstartpunkt, skal slutpunktet være < usigneret værdispecifikation > FØLGER. For eksempel definerer RÆKER MELLEM 2 FØLGENDE OG 10 FØLGENDE et vindue, der starter med den anden række, der følger den aktuelle række og slutter med den tiende række, der følger den aktuelle række. Denne specifikation er ikke tilladt for RANGE.

usigneret heltal bogstaveligt
Gælder for: SQL Server 2012 (11.x) og senere.

Er et positivt heltal bogstaveligt (inklusive 0 ), der angiver antallet af rækker eller værdier, der skal gå forud for eller følge den aktuelle række eller værdi. Denne specifikation er kun gyldig for RÆDER.

Generelle bemærkninger

Mere end en vinduesfunktion kan bruges i en enkelt forespørgsel med en enkelt FROM-sætning. OVER-klausulen for hver funktion kan variere i partitionering og rækkefølge.

Hvis PARTITION BY ikke er specificeret, behandler funktionen alle rækker i forespørgselsresultatet indstillet som en enkelt gruppe.

Vigtigt !

Hvis RÆK / RÆKKE er angivet, og < vinduesramme forud for > bruges til < vinduesrammeudstrækning > (kort syntaks), så anvendes denne specifikation til startpunktet for vinduesrammens grænse, og CURRENT ROW bruges til grænsens slutpunkt. For eksempel er “RÆKER 5 FORUDGÅENDE” lig med “RÆKER MELLEM 5 FORUDGÅENDE OG LØBENDE RÆDE”.

Begrænsninger og begrænsninger

OVER-klausulen kan ikke bruges med funktionen CHECKSUM-aggregat.

RANGE kan ikke bruges med < usigneret værdispecifikation > PRECEDING eller < usigneret værdispecifikation > FØLGENDE.

Afhængig af rangordning, samlet eller analytisk funktion brugt med OVER-klausulen, < ORDER BY-klausul > og / eller < RADER og RANGE-sætning > understøttes muligvis ikke.

Eksempler

A. Brug af OVER-klausulen med ROW_NUMBER-funktionen

Følgende eksempel viser brug af OVER-klausulen med ROW_NUMBER-funktionen til at vise et række nummer for hver række inden for en partition. ORDER BY-klausulen, der er specificeret i OVER-klausulen, ordner rækkerne i hver partition efter kolonnen SalesYTD. ORDER BY-klausulen i SELECT-sætningen bestemmer rækkefølgen, i hvilken hele forespørgselsresultatsættet returneres.

Her er resultatsættet.

B. Brug af OVER-klausulen med samlede funktioner

Følgende eksempel bruger OVER -klausulen med samlede funktioner over alle rækker, der returneres af forespørgslen. I dette eksempel er brugen af OVER -sætningen mere effektiv end at bruge underforespørgsler til at udlede de samlede værdier.

Her er resultatsættet.

Følgende eksempel viser brug af OVER -sætningen med en samlet funktion i en beregnet værdi.

Her er resultatsættet. Bemærk, at aggregaterne beregnes af SalesOrderID, og Percent by ProductID beregnes for hver linje i hver SalesOrderID.

C. Producerer et glidende gennemsnit og kumulativt total

Følgende eksempel bruger AVG- og SUM-funktionerne med OVER-klausulen til at give et glidende gennemsnit og kumulativ total af årligt salg for hvert område i Sales.SalesPerson tabel. Dataene er opdelt af TerritoryID og logisk ordnet af SalesYTD. Dette betyder, at AVG-funktionen beregnes for hvert område baseret på salgsåret. Bemærk, at der for TerritoryID 1 er der to rækker for salgsåret 2005, der repræsenterer de to sælgere med salg det år. Det gennemsnitlige salg for disse to rækker beregnes, og derefter er den tredje række, der repræsenterer salget for 2006, inkluderet i beregningen.

Her er resultatsættet.

I dette eksempel er OVER-klausulen inkluderer ikke PARTITION BY. Dette betyder, at funktionen vil blive anvendt på alle rækker, der returneres af forespørgslen. ORDER BY-klausulen, der er specificeret i OVER-klausulen, bestemmer den logiske rækkefølge, som AVG-funktionen anvendes til. Forespørgslen returnerer et glidende gennemsnit af salg efter år for alle salgsområder, der er specificeret i WHERE-klausulen. ORDER BY-klausulen, der er specificeret i SELECT-sætningen, bestemmer rækkefølgen, hvor forespørgslens rækker vises.

Her er resultatsættet.

D. Specificering af ROWS-klausulen

Gælder for: SQL Server 2012 (11.x) og nyere.

Følgende eksempel bruger ROWS-klausulen til at definere et vindue, hvor rækkerne beregnes som den aktuelle række og N antallet af rækker, der følger (1 række i dette eksempel).

Her er resultatsættet.

I det følgende eksempel er ROWS-klausulen specificeret med UNBOUNDED PRECEDING. Resultatet er, at vinduet starter ved den første række i partitionen.

Her er resultatsættet.

Eksempler: Parallelt datalager

E. Brug af OVER-klausulen med ROW_NUMBER-funktionen

Følgende eksempel returnerer ROW_NUMBER for salgsrepræsentanter baseret på deres tildelte salgskvote.

Her er et delresultatsæt.

F. Brug af OVER-klausulen med samlede funktioner

Følgende eksempler viser brug af OVER-klausulen med samlede funktioner. I dette eksempel er det mere effektivt at bruge OVER-klausulen end at bruge underforespørgsler.

Her er resultatsættet.

Følgende eksempel viser brug af OVER-klausulen med en samlet funktion i en beregnet værdi. Bemærk, at aggregaterne beregnes af SalesOrderNumber, og procentdelen af den samlede salgsordre beregnes for hver linje i hver SalesOrderNumber.

Den første start af dette resultatsæt er:

Se også

Samlede funktioner (Transact-SQL)
Analytiske funktioner (Transact-SQL)
Fremragende blogindlæg om vinduesfunktioner og OVER, på sqlmag.com, af Itzik Ben-Gan