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

• 08/11/2017
• 17 minuter att läsa
• • V
  • L
  • c
  • j
  • M
  • +11

Gäller för: SQL Server (alla versioner som stöds) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallellt datalager

Bestämmer partitionering och ordning av en raduppsättning innan tillhörande fönsterfunktion tillämpas. Det vill säga OVER-klausulen definierar ett fönster eller en användardefinierad uppsättning rader inom en sökresultatuppsättning. En fönsterfunktion beräknar sedan ett värde för varje rad i fönstret. Du kan använda OVER-klausulen med funktioner för att beräkna aggregerade värden som glidande medelvärden, kumulativa aggregat, löpande totalvärden eller ett högsta N per gruppresultat.

• Rankningsfunktioner

• Aggregerade funktioner

• Analytiska funktioner

• NÄSTA VÄRDE FÖR funktion

Transact-SQL-syntaxkonventioner

Syntax

Argument

Fönsterfunktioner kan ha följande argument i deras OVER -sats:

• PARTITION BY som delar frågeresultatet i partitioner.
• BESTÄLLNING AV som definierar den logiska ordningen på raderna inom varje partition av resultatuppsättningen.
• RADER / RANGE som begränsar raderna inom partitionen genom att ange start- och slutpunkter inom partitionen. Det kräver argumentet ORDER BY och standardvärdet är från början av partitionen till det aktuella elementet om ORDER BY -argumentet anges.

Om du inte anger något argument kommer fönsterfunktionerna att tillämpas på hela resultatuppsättningen.

objekt_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

DELNING AV

Delar upp frågeresultatet i partitioner. Fönsterfunktionen tillämpas på varje partition separat och beräkningen startar om för varje partit jon.

Om PARTITION BY inte anges, behandlar funktionen alla rader i frågeresultatet som en enda partition. Funktion kommer att tillämpas på alla rader i partitionen om du inte anger ORDER BY -sats.

PARTITION BY value_expression

Anger den kolumn med vilken radsatsen är partitionerad. value_expression kan endast hänvisa till kolumner som görs tillgängliga genom FROM-satsen. value_expression kan inte hänvisa till uttryck eller alias i listan. value_expression kan vara ett kolumnuttryck, skalärundersökning, skalarfunktion eller användardefinierad variabel.

BESTÄLLNING AV

Definierar den logiska ordningen på raderna inom varje partition i resultatuppsättningen. Det vill säga det anger den logiska ordning i vilken fönsterfunktionsberäkningen utförs.

• Om den inte anges är standardordningen ASC och fönstrets funktion kommer att använda alla rader i partitionen.
• Om den är angiven och en RAD / RANGE inte anges, används standard RANGE UNBOUNDED PRECEDING AND CURRENT ROW som standard för fönster ram genom funktionerna som kan acceptera valfri ROWS / RANGE-specifikation (till exempel min eller max).

order_by_expression
Anger en kolumn eller ett uttryck som ska sorteras. order_by_expression kan endast hänvisa till kolumner som görs tillgängliga genom FROM-klausulen. Ett heltal kan inte anges för att representera ett kolumnnamn eller alias.

COLLATE collation_name
Anger att operationen ORDER BY ska utföras enligt den sortering som anges i collation_name. collation_name kan antingen vara ett Windows-sorteringsnamn eller ett SQL-sorteringsnamn. Mer information finns i Collation and Unicode Support. COLLATE är endast tillämpligt för kolumner av typen char, varchar, nchar och nvarchar.

ASC | DESC
Anger att värdena i den angivna kolumnen ska sorteras i stigande eller fallande ordning. ASC är standardsorteringsordningen.Nollvärden behandlas som de lägsta möjliga värdena.

RADER eller RANGE

Gäller: SQL Server 2012 (11.x) och senare.

Ytterligare gränser raderna inom partitionen genom att ange start- och slutpunkter i partitionen. Detta görs genom att ange ett radintervall med avseende på den aktuella raden antingen genom logisk association eller fysisk association. Fysisk association uppnås genom att använda ROWS-satsen.

ROWS-satsen begränsar raderna inom en partition genom att ange ett fast antal rader som föregår eller följer den aktuella raden. Alternativt begränsar RANGE-satsen logiskt raderna inom en partition genom att ange ett värdeintervall med avseende på värdet i den aktuella raden. Före och efterföljande rader definieras baserat på ordningen i ORDER BY-klausulen. Fönsterramen ”RANGE … CURRENT ROW …” innehåller alla rader som har samma värden i ORDER BY-uttrycket som den aktuella raden. Till exempel betyder RADER MELLAN 2 INFÖRANDE OCH AKTUELL RAD att fönstret på rader som funktionen fungerar är tre rader i storlek, börjar med två rader före och med den aktuella raden.

OBEGRÄNSAD PRECEDING

Gäller: SQL Server 2012 (11.x) och senare.

Anger att fönstret startar vid den första raden i partitionen. OBEGRÄNSAD PRECEDING kan bara anges som fönsterets startpunkt.

< osignerad värdespecifikation > PRECEDING
Specificerad med < osignerad värdespecifikation > för att ange antalet rader eller värden som föregår den aktuella raden. Denna specifikation är inte tillåten för RANGE.

AKTUELL RAD

Gäller för: SQL Server 2012 (11.x) och senare.

Anger att fönstret startar eller slutar vid den aktuella raden när den används med ROWS eller det aktuella värdet när den används med RANGE. AKTUELL RAD kan anges som både start- och slutpunkt.

MELLAN OCH

Gäller: SQL Server 2012 (11.x) och senare.

Används med antingen ROWS eller RANGE för att ange fönstrets nedre (start) och övre (slut) gränspunkter. < fönsterram bunden > definierar gränsens startpunkt och < fönsterram bunden > definierar gränsens slutpunkt. Den övre gränsen kan inte vara mindre än den nedre gränsen.

OBEGRÄNSAD FÖLJANDE

Gäller: SQL Server 2012 (11.x) och senare.

Anger att fönstret slutar vid den sista raden i partitionen. OBEGRÄNSAD FÖLJNING kan bara anges som fönstrets slutpunkt. Till exempel RANGE MELLAN AKTUELL RAD OCH OGRÄNSAD FÖLJ definierar ett fönster som börjar med den aktuella raden och slutar med den sista raden i partitionen.

< osignerad värdespecifikation > FÖLJANDE
Specificerad med < osignerad värdespecifikation > för att ange antalet rader eller värden för att följa den aktuella raden. När < osignerad värdespecifikation > FÖLJANDE anges som fönstrets startpunkt måste slutpunkten vara < osignerad värdespecifikation > FÖLJER. Till exempel definierar RADER MELLAN 2 FÖLJANDE OCH 10 FÖLJANDE ett fönster som börjar med den andra raden som följer den aktuella raden och slutar med den tionde raden som följer den aktuella raden. Denna specifikation är inte tillåten för RANGE.

osignerat heltal bokstavligt
Gäller för: SQL Server 2012 (11.x) och senare.

Är ett positivt heltal bokstavligt (inklusive 0 ) som anger antalet rader eller värden som ska föregå eller följa den aktuella raden eller värdet. Denna specifikation är endast giltig för ROWS.

Allmänna anmärkningar

Mer än en fönsterfunktion kan användas i en enda fråga med en enda FROM-sats. OVER-klausulen för varje funktion kan skilja sig åt i partitionering och ordning.

Om PARTITION BY inte anges, behandlar funktionen alla rader i frågeresultatet som en enda grupp.

Viktigt !

Om RAD / RANGE anges och < fönsterram föregående > används för < fönsterramens omfattning > (kort syntax) då används denna specifikation för startpunkten för fönsterramens gräns och CURRENT ROW används för gränsens slutpunkt. Till exempel ”RAD 5 FÖRFÖRANDE” är lika med ”RADER MELLAN 5 FÖRFÖRANDE OCH AKTUELL RAD”.

Begränsningar och begränsningar

OVER-klausulen kan inte användas med funktionen CHECKSUM-aggregat.

RANGE kan inte användas med < osignerad värdespecifikation > PRECEDING eller < osignerad värdespecifikation > FÖLJER.

Beroende på rangordning, aggregat eller analytisk funktion som används med OVER-satsen, < BESTÄLLNING AV sats > och / eller < RADER och RANGE-sats > stöds kanske inte.

Exempel

A. Använda OVER-satsen med ROW_NUMBER-funktionen

Följande exempel visar hur OVER-satsen med ROW_NUMBER-funktionen används för att visa ett radnummer för varje rad inom en partition. ORDER BY-satsen som anges i OVER-satsen beställer raderna i varje partition med kolumnen SalesYTD. ORDER BY-satsen i SELECT-satsen bestämmer i vilken ordning hela sökresultatuppsättningen returneras.

Här är resultatuppsättningen.

B. Använda OVER-satsen med aggregerade funktioner

Följande exempel använder OVER -satsen med aggregerade funktioner över alla rader som returneras av frågan. I det här exemplet är användning av OVER -satsen mer effektiv än att använda underfrågor för att härleda aggregerade värden.

Här är resultatuppsättningen.

Följande exempel visar att OVER -satsen används med en aggregerad funktion i ett beräknat värde.

Här är resultatuppsättningen. Observera att aggregaten beräknas av SalesOrderID och Percent by ProductID beräknas för varje rad i varje SalesOrderID.

C. Producera ett glidande medelvärde och kumulativt totalt

Följande exempel använder AVG- och SUM-funktionerna med OVER-klausulen för att tillhandahålla ett glidande medelvärde och kumulativ total årlig försäljning för varje territorium i Sales.SalesPerson tabell. Uppgifterna är partitionerade av TerritoryID och ordnas logiskt av SalesYTD. Detta innebär att AVG-funktionen beräknas för varje territorium baserat på försäljningsåret. Observera att för TerritoryID 1 finns det två rader för försäljningsåret 2005 som representerar de två säljare med försäljning det året. Den genomsnittliga försäljningen för dessa två rader beräknas och sedan ingår den tredje raden som representerar försäljningen för år 2006 i beräkningen.

Här är resultatuppsättningen.

I det här exemplet är OVER-klausulen inkluderar inte PARTITION BY. Detta innebär att funktionen kommer att tillämpas på alla rader som returneras av frågan. ORDER BY-satsen som anges i OVER-satsen bestämmer den logiska ordning som AVG-funktionen tillämpas på. Frågan returnerar ett glidande genomsnitt av försäljning per år för alla försäljningsområden som anges i WHERE-klausulen. ORDER BY-satsen som anges i SELECT-satsen bestämmer i vilken ordning raderna i frågan ska visas.

Här är resultatuppsättningen.

D. Ange ROWS-satsen

Gäller för: SQL Server 2012 (11.x) och senare.

Följande exempel använder ROWS-satsen för att definiera ett fönster över vilket raderna beräknas som den aktuella raden och N-antalet rader som följer (1 rad i det här exemplet).

Här är resultatuppsättningen.

I följande exempel specificeras ROWS-satsen med UNBOUNDED PRECEDING. Resultatet är att fönstret börjar vid den första raden i partitionen.

Här är resultatuppsättningen.

Exempel: Parallellt datalager

E. Använda OVER-satsen med ROW_NUMBER-funktionen

Följande exempel returnerar ROW_NUMBER för säljare baserat på deras tilldelade försäljningskvot.

Här är en partiell resultatuppsättning.

F. Använda OVER-satsen med aggregerade funktioner

Följande exempel visar hur OVER-satsen används med aggregerade funktioner. I det här exemplet är det effektivare att använda OVER-klausulen än att använda underfrågor.

Här är resultatuppsättningen.

Följande exempel visar att använda OVER-satsen med en aggregerad funktion i ett beräknat värde. Observera att aggregaten beräknas av SalesOrderNumber och procenten av den totala försäljningsordern beräknas för varje rad i varje SalesOrderNumber.

Den första starten på denna resultatuppsättning är:

Se även

Aggregerade funktioner (Transact-SQL)
Analytiska funktioner (Transact-SQL)
Utmärkt blogginlägg om fönsterfunktioner och OVER, på sqlmag.com, av Itzik Ben-Gan