SELECT – OVER Clause (Transact-SQL)

• 08/11/2017
• 17 minuten om te lezen
• • V
  • L
  • c
  • j
  • M
  • +11

Is van toepassing op: SQL Server (alle ondersteunde versies) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

Bepaalt de partitionering en volgorde van een rijenset voordat de bijbehorende vensterfunctie wordt toegepast. Dat wil zeggen, de OVER-clausule definieert een venster of een door de gebruiker gespecificeerde set rijen binnen een queryresultaatset. Een vensterfunctie berekent vervolgens een waarde voor elke rij in het venster. U kunt de OVER-clausule gebruiken met functies om geaggregeerde waarden te berekenen, zoals voortschrijdende gemiddelden, cumulatieve aggregaten, lopende totalen of een top N per groepsresultaten.

• Rangorde-functies

• Geaggregeerde functies

• Analytische functies

• VOLGENDE WAARDE VOOR functie

Transact-SQL-syntaxisconventies

Syntaxis

Argumenten

Vensterfuncties kunnen de volgende argumenten hebben in hun OVER -clausule:

• PARTITION BY die de set met queryresultaten in partities verdeelt.
• ORDER BY dat de logische volgorde van de rijen binnen elke partitie van de resultatenset definieert.
• ROWS / RANGE die de rijen binnen de partitie beperkt door begin- en eindpunten binnen de partitie te specificeren. Het vereist ORDER BY argument en de standaardwaarde is vanaf het begin van de partitie tot het huidige element als het ORDER BY argument is gespecificeerd.

Als u geen argument opgeeft, worden de vensterfuncties toegepast op de gehele resultaatset.

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

PARTITIE DOOR

Verdeelt de queryresultaatset in partities. De vensterfunctie wordt op elke partitie afzonderlijk toegepast en de berekening wordt voor elke partitie opnieuw gestart ion.

Als PARTITION BY niet is gespecificeerd, behandelt de functie alle rijen van de query-resultatenset als een enkele partitie. De functie wordt toegepast op alle rijen in de partitie als u de clausule ORDER BY niet specificeert.

PARTITION BY value_expression

Specificeert de kolom waarmee de rijenset wordt gepartitioneerd. waarde_expressie kan alleen verwijzen naar kolommen die beschikbaar zijn gemaakt door de FROM-component. waarde_expressie kan niet verwijzen naar uitdrukkingen of aliassen in de keuzelijst. waarde_expressie kan een kolomexpressie, scalaire subquery, scalaire functie of door de gebruiker gedefinieerde variabele zijn.

ORDER BY

Bepaalt de logische volgorde van de rijen binnen elke partitie van de resultatenset. Dat wil zeggen, het specificeert de logische volgorde waarin de berekening van de vensterfunctie wordt uitgevoerd.

• Als het niet is gespecificeerd, is de standaardvolgorde ASC en window-functie zal alle rijen in partitie gebruiken.
• Als het gespecificeerd is, en er is geen ROWS / RANGE gespecificeerd, dan wordt standaard RANGE UNBOUNDED PRECEDING AND CURRENT ROW gebruikt als standaard voor window frame door de functies die optionele ROWS / RANGE-specificatie kunnen accepteren (bijvoorbeeld min of max).

order_by_expression
Specificeert een kolom of uitdrukking waarop moet worden gesorteerd. order_by_expression kan alleen verwijzen naar kolommen die beschikbaar zijn gemaakt door de FROM-component. Een geheel getal kan niet worden opgegeven om een kolomnaam of alias weer te geven.

COLLATE collation_name
Specificeert dat de ORDER BY-bewerking moet worden uitgevoerd volgens de sortering die is opgegeven in collatie_name. collatienaam kan een Windows-sorteernaam of een SQL-sorteernaam zijn. Zie Sorteren en Unicode-ondersteuning voor meer informatie. COLLATE is alleen van toepassing op kolommen van het type char, varchar, nchar en nvarchar.

ASC | DESC
Geeft aan dat de waarden in de opgegeven kolom in oplopende of aflopende volgorde moeten worden gesorteerd. ASC is de standaard sorteervolgorde.Null-waarden worden behandeld als de laagst mogelijke waarden.

RIJEN of BEREIK

Van toepassing op: SQL Server 2012 (11.x) en later.

Verdere limieten de rijen binnen de partitie door begin- en eindpunten binnen de partitie op te geven. Dit wordt gedaan door een reeks rijen op te geven met betrekking tot de huidige rij, hetzij door logische associatie of fysieke associatie. Fysieke associatie wordt bereikt door de ROWS-clausule te gebruiken.

De ROWS-component beperkt het aantal rijen binnen een partitie door een vast aantal rijen op te geven voorafgaand aan of volgend op de huidige rij. Als alternatief beperkt de clausule RANGE de rijen binnen een partitie op logische wijze door een reeks waarden op te geven met betrekking tot de waarde in de huidige rij. Voorafgaande en volgende rijen worden gedefinieerd op basis van de volgorde in de ORDER BY-clausule. Het vensterframe “RANGE … CURRENT ROW …” bevat alle rijen die dezelfde waarden hebben in de ORDER BY-expressie als de huidige rij. RIJEN TUSSEN 2 VOORGAANDE EN HUIDIGE RIJ betekent bijvoorbeeld dat het venster met rijen waarop de functie werkt, drie rijen groot is, beginnend met 2 rijen voorafgaand aan en inclusief de huidige rij.

UNBOUNDED PRECEDING

Van toepassing op: SQL Server 2012 (11.x) en later.

Specificeert dat het venster begint op de eerste rij van de partitie. UNBOUNDED PRECEDING kan alleen worden opgegeven als beginpunt van het venster.

< unsigned value specificatie > PRECEDING
Gespecificeerd met < niet-ondertekende waardenspecificatie > om het aantal rijen of waarden aan te geven dat voorafgaat aan de huidige rij. Deze specificatie is niet toegestaan voor RANGE.

CURRENT ROW

Van toepassing op: SQL Server 2012 (11.x) en later.

Specificeert dat het venster start of eindigt bij de huidige rij bij gebruik met ROWS of de huidige waarde bij gebruik met RANGE. CURRENT ROW kan worden opgegeven als zowel een begin- als een eindpunt.

TUSSEN EN

Is van toepassing op: SQL Server 2012 (11.x) en later.

Wordt gebruikt met ROWS of RANGE om de onderste (begin) en bovenste (eind) grenspunten van het venster te specificeren. < raamkozijn gebonden > definieert het beginpunt van de begrenzing en < raamkozijn gebonden > definieert het eindpunt van de grens. De bovengrens mag niet kleiner zijn dan de ondergrens.

ONGEBONDEN VOLGEND

Van toepassing op: SQL Server 2012 (11.x) en hoger.

Specificeert dat het raam eindigt op de laatste rij van de partitie. UNBOUNDED FOLLOWING kan alleen worden opgegeven als een venstereindpunt. BEREIK TUSSEN CURRENT ROW AND UNBOUNDED FOLLOWING definieert bijvoorbeeld een venster dat begint met de huidige rij en eindigt met de laatste rij van de partitie.

< unsigned value specificatie > VOLGEND
Gespecificeerd met < niet-ondertekende waarde specificatie > om het aantal rijen of waarden om de huidige rij te volgen. Wanneer < niet-ondertekende waarde specificatie > FOLLOWING is opgegeven als het beginpunt van het venster, moet het eindpunt < niet-ondertekende waarde specificatie > VOLGEND. RIJEN TUSSEN 2 VOLGENDE EN 10 VOLGENDE definieert bijvoorbeeld een venster dat begint met de tweede rij die volgt op de huidige rij en eindigt met de tiende rij die volgt op de huidige rij. Deze specificatie is niet toegestaan voor RANGE.

niet-ondertekende letterlijke gehele getallen
Van toepassing op: SQL Server 2012 (11.x) en later.

Is een positieve letterlijke gehele waarde (inclusief 0 ) die het aantal rijen of waarden specificeert dat voorafgaat aan of volgt op de huidige rij of waarde. Deze specificatie is alleen geldig voor ROWS.

Algemene opmerkingen

Er kunnen meer dan één vensterfunctie worden gebruikt in een enkele query met een enkele FROM-component. De OVER-clausule voor elke functie kan verschillen in partitionering en volgorde.

Als PARTITION BY niet is opgegeven, behandelt de functie alle rijen van de queryresultatenet als een enkele groep.

Belangrijk !

Als ROWS / RANGE is opgegeven en < raamkozijn voorafgaand aan > wordt gebruikt voor < vensterframe-omvang > (korte syntaxis), dan wordt deze specificatie gebruikt voor het beginpunt van de raamframebegrenzing en wordt HUIDIGE RIJ gebruikt voor het eindpunt van de grens. Bijvoorbeeld “RIJEN 5 VOORAFGAAND” is gelijk aan “RIJEN TUSSEN 5 VOORGAANDE EN HUIDIGE RIJ”.

Beperkingen en beperkingen

De clausule OVER kan niet worden gebruikt met de statistische functie CHECKSUM.

RANGE kan niet worden gebruikt met < niet-ondertekende waarde specificatie > VOORAFGAAND of < niet-ondertekende waarde specificatie > VOLGENDE.

Afhankelijk van de rangschikking, geaggregeerd of analytisch functie gebruikt met de OVER-clausule, < ORDER BY-clausule > en / of de < ROWS en RANGE-clausule > worden mogelijk niet ondersteund.

Voorbeelden

A. De clausule OVER gebruiken met de functie ROW_NUMBER

Het volgende voorbeeld toont het gebruik van de clausule OVER met de functie ROW_NUMBER om een rijnummer weer te geven voor elke rij binnen een partitie. De ORDER BY-component gespecificeerd in de OVER-clausule rangschikt de rijen in elke partitie op basis van de kolom SalesYTD. De ORDER BY-clausule in de SELECT-instructie bepaalt de volgorde waarin de volledige queryresultatenet wordt geretourneerd.

Hier is de resultatenset.

B. De clausule OVER gebruiken met geaggregeerde functies

In het volgende voorbeeld wordt de clausule OVER gebruikt met geaggregeerde functies voor alle rijen die door de query worden geretourneerd. In dit voorbeeld is het gebruik van de OVER -clausule efficiënter dan het gebruik van subquerys om de geaggregeerde waarden af te leiden.

Hier is de resultatenset.

Het volgende voorbeeld toont het gebruik van de OVER -clausule met een geaggregeerde functie in een berekende waarde.

Hier is de resultaatset. Merk op dat de aggregaten worden berekend door SalesOrderID en de Percent by ProductID wordt berekend voor elke regel van elk SalesOrderID.

C. Een voortschrijdend gemiddelde en cumulatief totaal produceren

In het volgende voorbeeld worden de AVG- en SUM-functies met de OVER-clausule gebruikt om een voortschrijdend gemiddelde en cumulatief totaal van jaarlijkse verkopen voor elk gebied in de Sales.SalesPerson tafel. De gegevens zijn gepartitioneerd op TerritoryID en logisch geordend op SalesYTD. Dit betekent dat de AVG-functie voor elke regio wordt berekend op basis van het verkoopjaar. Merk op dat voor TerritoryID 1, er twee rijen zijn voor het verkoopjaar 2005 die de twee verkopers met verkopen dat jaar vertegenwoordigen. De gemiddelde verkopen voor deze twee rijen worden berekend en vervolgens wordt de derde rij die de verkopen voor het jaar 2006 vertegenwoordigt, meegenomen in de berekening.

Hier is de resultaatset.

In dit voorbeeld, de OVER-clausule omvat geen PARTITION BY. Dit betekent dat de functie wordt toegepast op alle rijen die door de query worden geretourneerd. De ORDER BY-clausule die is opgegeven in de OVER-clausule, bepaalt de logische volgorde waarop de AVG-functie wordt toegepast. De query retourneert een voortschrijdend gemiddelde van de verkopen per jaar voor alle verkoopregios die zijn opgegeven in de WHERE-clausule. De ORDER BY-component die is opgegeven in de SELECT-instructie, bepaalt de volgorde waarin de rijen van de query worden weergegeven.

Hier is de resultatenset.

D. Specificeren van de ROWS-clausule

Van toepassing op: SQL Server 2012 (11.x) en later.

In het volgende voorbeeld wordt de ROWS-clausule gebruikt om een venster te definiëren waarover de rijen worden berekend als de huidige rij en het N aantal rijen dat volgt (1 rij in dit voorbeeld).

Hier is de resultatenset.

In het volgende voorbeeld wordt de clausule ROWS gespecificeerd met UNBOUNDED PRECEDING. Het resultaat is dat het venster begint bij de eerste rij van de partitie.

Hier is de resultatenset.

Voorbeelden: Parallel Data Warehouse

E. De OVER-clausule gebruiken met de ROW_NUMBER-functie

Het volgende voorbeeld retourneert de ROW_NUMBER voor verkoopvertegenwoordigers op basis van hun toegewezen verkoopquotum.

Hier is een gedeeltelijke resultaatset.

F. Het gebruik van de OVER-clausule met geaggregeerde functies

De volgende voorbeelden tonen het gebruik van de OVER-clausule met geaggregeerde functies. In dit voorbeeld is het gebruik van de OVER-clausule efficiënter dan het gebruik van subquerys.

Hier is de resultaatset.

Het volgende voorbeeld toont het gebruik van de OVER-clausule met een geaggregeerde functie in een berekende waarde. Merk op dat de aggregaten worden berekend door SalesOrderNumber en het percentage van de totale verkooporder wordt berekend voor elke regel van elke SalesOrderNumber.

De eerste start van deze resultatenset is:

Zie ook

Geaggregeerde functies (Transact-SQL)
Analytische functies (Transact-SQL)
Uitstekende blogpost over vensterfuncties en OVER, op sqlmag.com, door Itzik Ben-Gan