SELECT - OVER-component (Transact-SQL)
van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft FabricWarehouse in Microsoft FabricSQL-database in Microsoft Fabric
De OVER component bepaalt de partitionering en volgorde van een rijenset voordat de bijbehorende vensterfunctie wordt toegepast. Dat wil gezegd, definieert de OVER-component een venster of door de gebruiker opgegeven set rijen in een queryresultatenset. Een vensterfunctie berekent vervolgens een waarde voor elke rij in het venster. U kunt de component OVER met functies gebruiken om geaggregeerde waarden te berekenen, zoals zwevende gemiddelden, cumulatieve aggregaties, lopende totalen of top N per groepsresultaat.
Transact-SQL syntaxisconventies
Syntaxis
Syntaxis voor SQL Server, Azure SQL Database en Azure Synapse Analytics.
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ , ...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
Syntaxis voor parallel datawarehouse.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Argumenten
Vensterfuncties hebben mogelijk de volgende argumenten in hun OVER component:
PARTITION BY waarmee de queryresultatenset wordt verdeeld in partities.
ORDER BY die de logische volgorde van de rijen binnen elke partitie van de resultatenset definieert.
RIJEN of BEREIK waarmee de rijen binnen de partitie worden beperkt door begin- en eindpunten binnen de partitie op te geven. Hiervoor is
ORDER BYargument vereist en de standaardwaarde is van het begin van de partitie naar het huidige element als het argumentORDER BYis opgegeven.
Als u geen argument opgeeft, worden de vensterfuncties toegepast op de volledige resultatenset.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | Min | Max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTITIONEREN OP
Verdeelt de queryresultatenset in partities. De vensterfunctie wordt afzonderlijk toegepast op elke partitie en de berekening wordt opnieuw gestart voor elke partitie.
PARTITION BY <value_expression>
Als PARTITION BY niet is opgegeven, behandelt de functie alle rijen van de queryresultatenset als één partitie.
De functie wordt toegepast op alle rijen in de partitie als u geen ORDER BY component opgeeft.
PARTITIONEREN OP VALUE_EXPRESSION
Hiermee geeft u de kolom waarop de rijenset is gepartitioneerd.
value_expression kan alleen verwijzen naar kolommen die beschikbaar zijn gesteld door de FROM component.
value_expression kan niet verwijzen naar expressies of aliassen in de selectielijst.
value_expression kan een kolomexpressie, scalaire subquery, scalaire functie of door de gebruiker gedefinieerde variabele zijn.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | type | Min | Max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Definieert de logische volgorde van de rijen binnen elke partitie van de resultatenset. Dat wil gezegd, hiermee wordt de logische volgorde opgegeven waarin de berekening van de vensterfunctie wordt uitgevoerd.
Als dit niet is opgegeven, wordt de standaardvolgorde
ASCen gebruikt de vensterfunctie alle rijen in de partitie.Indien opgegeven en een
ROWSofRANGEniet is opgegeven, wordt standaardRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWgebruikt als de standaardinstelling voor het vensterframe, door de functies die een optioneleROWSofRANGEspecificatie kunnen accepteren (bijvoorbeeldminofmax).
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | type | Min | Max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Hiermee geeft u een kolom of expressie op waarop moet worden gesorteerd.
order_by_expression kan alleen verwijzen naar kolommen die beschikbaar zijn gesteld door de FROM component. Een geheel getal kan niet worden opgegeven om een kolomnaam of alias weer te geven.
COLLATION_NAME
Hiermee geeft u op dat de ORDER BY bewerking moet worden uitgevoerd volgens de sortering die is opgegeven in collation_name.
collation_name kan een Windows-sorteringsnaam of een SQL-sorteringsnaam zijn. Zie Sortering en Unicode-ondersteuningvoor meer informatie.
COLLATE is alleen van toepassing op kolommen van het type teken, varchar, ncharen nvarchar.
ASC | DESC
Hiermee geeft u op dat de waarden in de opgegeven kolom in oplopende of aflopende volgorde moeten worden gesorteerd.
ASC is de standaardsorteerdervolgorde. Null-waarden worden behandeld als de laagst mogelijke waarden.
RIJEN OF BEREIK
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
De rijen binnen de partitie worden verder beperkt door begin- en eindpunten binnen de partitie op te geven. Hiermee wordt een bereik van rijen opgegeven met betrekking tot de huidige rij, hetzij door logische koppeling of fysieke koppeling. Fysieke koppeling wordt bereikt met behulp van de ROWS-component.
De ROWS-component beperkt de rijen binnen een partitie door een vast aantal rijen vóór of na de huidige rij op te geven. U kunt ook de RANGE component de rijen binnen een partitie logisch beperken door een bereik met waarden op te geven met betrekking tot de waarde in de huidige rij. Voorgaande en volgende rijen worden gedefinieerd op basis van de volgorde in de ORDER BY-component. Het vensterframe RANGE ... CURRENT ROW ... bevat alle rijen met dezelfde waarden in de ORDER BY expressie als de huidige rij.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW betekent bijvoorbeeld dat het venster met rijen waarop de functie werkt drie rijen groot is, beginnend met 2 rijen die voorafgaan tot en met de huidige rij.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | voorafgaand | centraal | volgend |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
voor ROWS of RANGE moet u de ORDER BY component opgeven. Als ORDER BY meerdere orderexpressies bevat, CURRENT ROW FOR RANGE alle kolommen in de ORDER BY lijst beschouwt bij het bepalen van de huidige rij.
NIET-GEBONDEN VOORAFGAANDE
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
Hiermee geeft u op dat het venster begint bij de eerste rij van de partitie.
UNBOUNDED PRECEDING kan alleen worden opgegeven als beginpunt van het venster.
<specificatie van niet-ondertekende waarden> VOORGAANDE
Opgegeven met <unsigned value specification> om het aantal rijen of waarden aan te geven dat voorafgaat aan de huidige rij. Deze specificatie is niet toegestaan voor RANGE.
HUIDIGE RIJ
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
Hiermee geeft u op dat het venster begint of eindigt op de huidige rij wanneer het wordt gebruikt met ROWS of de huidige waarde wanneer het wordt gebruikt met RANGE.
CURRENT ROW kan worden opgegeven als een begin- en eindpunt.
TUSSEN EN
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
BETWEEN <window frame bound> AND <window frame bound>
Wordt gebruikt met ROWS of RANGE om de onderste (begin- en eindgrenspunten) van het venster op te geven.
<window frame bound> definieert het beginpunt van de grens en <window frame bound> het grenseindpunt definieert. De bovengrens mag niet kleiner zijn dan de ondergrens.
NIET-AFHANKELIJKE VOLGENDE
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
Hiermee geeft u op dat het venster eindigt op de laatste rij van de partitie.
UNBOUNDED FOLLOWING kan alleen worden opgegeven als een venstereindpunt.
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definieert bijvoorbeeld een venster dat begint met de huidige rij en eindigt met de laatste rij van de partitie.
<specificatie van niet-ondertekende waarden> FOLLOWING
Opgegeven met <unsigned value specification> om het aantal rijen of waarden aan te geven dat moet worden gevolgd door de huidige rij. Wanneer <unsigned value specification> FOLLOWING is opgegeven als het beginpunt van het venster, moet het eindpunt <unsigned value specification> FOLLOWINGzijn.
ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 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>
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
Een positieve letterlijke waarde voor gehele getallen (inclusief 0) die het aantal rijen of waarden aangeeft dat voorafgaat aan de huidige rij of waarde. Deze specificatie is alleen geldig voor ROWS.
Opmerkingen
Meer dan één vensterfunctie kan worden gebruikt in één query met één FROM component. De OVER component voor elke functie kan verschillen in partitionering en volgorde.
Als PARTITION BY niet is opgegeven, behandelt de functie alle rijen van de queryresultatenset als één groep.
Belangrijk
Als ROWS of RANGE is opgegeven en <window frame preceding> wordt gebruikt voor <window frame extent> (korte syntaxis), wordt deze specificatie gebruikt voor het beginpunt van het vensterframe en CURRENT ROW wordt gebruikt voor het eindpunt van de grens.
ROWS 5 PRECEDING is bijvoorbeeld gelijk aan ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Als ORDER BY niet is opgegeven, wordt de hele partitie gebruikt voor een vensterframe. Dit geldt alleen voor functies waarvoor geen ORDER BY-component is vereist. Als ROWS of RANGE niet is opgegeven, maar ORDER BY is opgegeven, wordt RANGE UNBOUNDED PRECEDING AND CURRENT ROW als standaard gebruikt voor vensterkaders. Dit geldt alleen voor functies die optionele ROWS of RANGE specificatie kunnen accepteren. Classificatiefuncties kunnen bijvoorbeeld geen ROWS of RANGEaccepteren, daarom wordt dit vensterframe niet toegepast, ook al is ORDER BY aanwezig en ROWS of RANGE niet.
Beperkingen
De OVER-component kan niet worden gebruikt met de DISTINCT-aggregaties.
RANGE kan niet worden gebruikt met <unsigned value specification> PRECEDING of <unsigned value specification> FOLLOWING.
Afhankelijk van de classificatie-, statistische of analytische functie die wordt gebruikt met de OVER component, worden <ORDER BY clause> en/of de <ROWS and RANGE clause> mogelijk niet ondersteund.
Voorbeelden
De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina.
Een. De OVER-component gebruiken met de functie ROW_NUMBER
In het volgende voorbeeld ziet u het gebruik van de component OVER met ROW_NUMBER functie om een rijnummer weer te geven voor elke rij binnen een partitie. De ORDER BY-component die is opgegeven in de OVER-component, ordet de rijen in elke partitie door de kolom SalesYTD. De ORDER BY-component in de instructie SELECT bepaalt de volgorde waarin de volledige queryresultatenset wordt geretourneerd.
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName,
s.SalesYTD,
a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
Dit is de resultatenset.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. De OVER-component gebruiken met statistische functies
In het volgende voorbeeld wordt de OVER-component met statistische functies gebruikt voor alle rijen die door de query worden geretourneerd. In dit voorbeeld is het gebruik van de component OVER efficiënter dan het gebruik van subquery's om de geaggregeerde waarden af te leiden.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg",
COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Count",
MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min",
MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Dit is de resultatenset.
SalesOrderID ProductID OrderQty Total Avg Count Min Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
In het volgende voorbeeld ziet u het gebruik van de OVER-component met een statistische functie in een berekende waarde.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
CAST (1. * OrderQty / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) * 100 AS DECIMAL (5, 2)) AS [Percent by ProductID]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Dit is de resultatenset. De aggregaties worden berekend door SalesOrderID en de Percent by ProductID wordt berekend voor elke regel van elke SalesOrderID.
SalesOrderID ProductID OrderQty Total Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659 776 1 26 3.85
43659 777 3 26 11.54
43659 778 1 26 3.85
43659 771 1 26 3.85
43659 772 1 26 3.85
43659 773 2 26 7.69
43659 774 1 26 3.85
43659 714 3 26 11.54
43659 716 1 26 3.85
43659 709 6 26 23.08
43659 712 2 26 7.69
43659 711 4 26 15.38
43664 772 1 14 7.14
43664 775 4 14 28.57
43664 714 1 14 7.14
43664 716 1 14 7.14
43664 777 2 14 14.29
43664 771 3 14 21.4
43664 773 1 14 7.14
43664 778 1 14 7.14
C. Een zwevend gemiddelde en cumulatief totaal produceren
In het volgende voorbeeld worden de functies AVG en SUM gebruikt met de OVER component om een zwevend gemiddelde en een cumulatief totaal van de jaarlijkse verkoop voor elk gebied in de Sales.SalesPerson tabel te bieden. De gegevens worden gepartitioneerd door TerritoryID en logisch geordend door SalesYTD. Dit betekent dat de functie AVG wordt berekend voor elk gebied op basis van het verkoopjaar. Voor TerritoryID van 1 zijn er twee rijen voor het verkoopjaar 2005 die de twee verkoopmedewerkers met de verkoop van dat jaar vertegenwoordigen. De gemiddelde verkoop voor deze twee rijen wordt berekend en vervolgens de derde rij die de verkoop voor het jaar vertegenwoordigt 2006 is opgenomen in de berekening.
USE AdventureWorks2022;
GO
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;
Dit is de resultatenset.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
In dit voorbeeld bevat de OVER-component geen PARTITION BY. Dit betekent dat de functie wordt toegepast op alle rijen die door de query worden geretourneerd. De ORDER BY-component die is opgegeven in de OVER-component bepaalt de logische volgorde waarop de AVG functie wordt toegepast. De query retourneert een zwevend gemiddelde van de verkoop per jaar voor alle verkoopregio's die zijn opgegeven in de WHERE component. De ORDER BY component die is opgegeven in de instructie SELECT bepaalt de volgorde waarin de rijen van de query worden weergegeven.
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY SalesYear;
Dit is de resultatenset.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
D. De COMPONENT ROWS opgeven
Van toepassing op: SQL Server 2012 (11.x) en latere versies.
In het volgende voorbeeld wordt de ROWS-component gebruikt om een venster te definiëren waarmee de rijen worden berekend als de huidige rij en het N aantal rijen dat volgt (één rij in dit voorbeeld).
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Dit is de resultatenset.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
In het volgende voorbeeld wordt de ROWS-component opgegeven met UNBOUNDED PRECEDING. Het resultaat is dat het venster begint bij de eerste rij van de partitie.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Dit is de resultatenset.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 6,709,904.17
Voorbeelden: Analytics Platform System (PDW)
E. De OVER-component gebruiken met de functie ROW_NUMBER
In het volgende voorbeeld wordt de ROW_NUMBER voor verkoopmedewerkers geretourneerd op basis van hun toegewezen verkoopquotum.
SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName,
LastName,
CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Hier volgt een gedeeltelijke resultatenset.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. De OVER-component gebruiken met statistische functies
In de volgende voorbeelden ziet u het gebruik van de OVER-component met statistische functies. In dit voorbeeld is het gebruik van de component OVER efficiënter dan het gebruik van subquery's.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Dit is de resultatenset.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
In het volgende voorbeeld ziet u het gebruik van de OVER-component met een statistische functie in een berekende waarde. De aggregaties worden berekend door SalesOrderNumber en het percentage van de totale verkooporder wordt berekend voor elke regel van elke SalesOrderNumber.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
De eerste start van deze resultatenset is als volgt:
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75