SELECT – OVER-sats (Transact-SQL)
gäller för:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft FabricSQL-databas i Microsoft Fabric
Satsen OVER avgör partitionering och ordning för en raduppsättning innan den associerade fönsterfunktionen tillämpas. Det innebär att OVER-satsen definierar ett fönster eller en användardefinierad uppsättning rader i en frågeresultatuppsättning. En fönsterfunktion beräknar sedan ett värde för varje rad i fönstret. Du kan använda OVER-satsen med funktioner för att beräkna aggregerade värden, till exempel glidande medelvärden, kumulativa aggregeringar, löpande summor eller högsta N per gruppresultat.
Transact-SQL syntaxkonventioner
Syntax
Syntax för SQL Server, Azure SQL Database och 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> }
Syntax för Parallel Data Warehouse.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Argument
Fönsterfunktioner kan ha följande argument i sin OVER-sats:
PARTITION BY som delar frågeresultatuppsättningen i partitioner.
ORDER BY som definierar den logiska ordningen för raderna i varje partition i resultatuppsättningen.
RADER eller INTERVALL som begränsar raderna i partitionen genom att ange start- och slutpunkter i partitionen. Det kräver
ORDER BYargument och standardvärdet är från början av partitionen till det aktuella elementet om argumentetORDER BYanges.
Om du inte anger något argument tillämpas fönsterfunktionerna på hela resultatuppsättningen.
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 |
PARTITION EFTER
Delar upp frågeresultatuppsättningen i partitioner. Fönsterfunktionen tillämpas på varje partition separat och beräkningen startas om för varje partition.
PARTITION BY <value_expression>
Om PARTITION BY inte anges behandlar funktionen alla rader i frågeresultatuppsättningen som en enda partition.
Funktionen tillämpas på alla rader i partitionen om du inte anger ORDER BY-sats.
PARTITION EFTER value_expression
Anger den kolumn med vilken raduppsättningen partitioneras.
value_expression kan bara referera till kolumner som görs tillgängliga av FROM-satsen.
value_expression kan inte referera till uttryck eller alias i urvalslistan.
value_expression kan vara ett kolumnuttryck, en skalär underfråga, en skalär funktion eller en användardefinierad variabel.
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 | typ | 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 ]
Definierar den logiska ordningen för raderna i varje partition i resultatuppsättningen. Det innebär att den anger den logiska ordning i vilken beräkningen av fönsterfunktionen utförs.
Om den inte anges är standardordningen
ASCoch fönsterfunktionen använder alla rader i partitionen.Om det anges och en
ROWSellerRANGEinte anges används standardRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWsom standard för fönsterramen av de funktioner som kan acceptera en valfriROWSellerRANGEspecifikation (till exempelminellermax).
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 | typ | 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
Anger en kolumn eller ett uttryck som ska sorteras på.
order_by_expression kan bara referera till kolumner som görs tillgängliga av FROM-satsen. Det går inte att ange ett heltal för att representera ett kolumnnamn eller alias.
SORTERA COLLATION_NAME
Anger att den ORDER BY åtgärden ska utföras enligt sorteringen som anges i collation_name.
collation_name kan vara antingen ett Windows-sorteringsnamn eller ett SQL-sorteringsnamn. Mer information finns i Sortering och Unicode-stöd.
COLLATE gäller endast för kolumner av typen char, varchar, ncharoch nvarchar.
ASC | DESC
Anger att värdena i den angivna kolumnen ska sorteras i stigande eller fallande ordning.
ASC är standardsorteringsordningen. Null-värden behandlas som de lägsta möjliga värdena.
RADER eller INTERVALL
gäller för: SQL Server 2012 (11.x) och senare versioner.
Ytterligare begränsar raderna i partitionen genom att ange start- och slutpunkter i partitionen. Den anger ett radintervall med avseende på den aktuella raden, antingen efter logisk association eller fysisk association. Fysisk association uppnås med hjälp av ROWS-satsen.
Satsen ROWS begränsar raderna i en partition genom att ange ett fast antal rader före eller efter den aktuella raden. Alternativt begränsar RANGE-satsen raderna i en partition logiskt genom att ange ett värdeintervall med avseende på värdet i den aktuella raden. Föregående och följande rader definieras baserat på ordningen i ORDER BY-satsen. Fönsterramen RANGE ... CURRENT ROW ... innehåller alla rader som har samma värden i ORDER BY uttryck som den aktuella raden. Till exempel innebär ROWS BETWEEN 2 PRECEDING AND CURRENT ROW att fönstret med rader som funktionen fungerar på är tre rader i storlek, från och med 2 rader som föregår till och med den aktuella raden.
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 | föregående | central | följande |
|---|---|---|---|
| 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 |
ROWS eller RANGE kräver att du anger ORDER BY-satsen. Om ORDER BY innehåller flera orderuttryck tar CURRENT ROW FOR RANGE hänsyn till alla kolumner i listan ORDER BY när den aktuella raden fastställs.
OBUNDNA FÖREGÅENDE
gäller för: SQL Server 2012 (11.x) och senare versioner.
Anger att fönstret startar på den första raden i partitionen.
UNBOUNDED PRECEDING kan bara anges som startpunkt för fönstret.
<osignerad värdespecifikation> FÖREGÅENDE
Anges med <unsigned value specification> för att ange antalet rader eller värden som ska föregå den aktuella raden. Den här specifikationen är inte tillåten för RANGE.
AKTUELL RAD
gäller för: SQL Server 2012 (11.x) och senare versioner.
Anger att fönstret startar eller slutar på den aktuella raden när det används med ROWS eller det aktuella värdet när det används med RANGE.
CURRENT ROW kan anges som både en start- och slutpunkt.
MELLAN OCH
gäller för: SQL Server 2012 (11.x) och senare versioner.
BETWEEN <window frame bound> AND <window frame bound>
Används med antingen ROWS eller RANGE för att ange de nedre (start) och övre (avslutande) gränspunkterna i fönstret.
<window frame bound> definierar gränsstartpunkten och <window frame bound> definierar gränsslutpunkten. Den övre gränsen får inte vara mindre än den nedre gränsen.
OBUNDNA FÖLJANDE
gäller för: SQL Server 2012 (11.x) och senare versioner.
Anger att fönstret slutar på den sista raden i partitionen.
UNBOUNDED FOLLOWING kan bara anges som en fönsterslutpunkt. Till exempel definierar RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ett fönster som börjar med den aktuella raden och slutar med den sista raden i partitionen.
<osignerad värdespecifikation> FÖLJANDE
Anges med <unsigned value specification> för att ange antalet rader eller värden som ska följa den aktuella raden. När <unsigned value specification> FOLLOWING anges som startpunkt för fönstret måste slutpunkten vara <unsigned value specification> FOLLOWING. Till exempel definierar ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 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. Den här specifikationen är inte tillåten för RANGE.
<osignerad heltalsliteral>
gäller för: SQL Server 2012 (11.x) och senare versioner.
En positiv heltal (inklusive 0) som anger antalet rader eller värden som ska föregå eller följa den aktuella raden eller värdet. Den här specifikationen är endast giltig för ROWS.
Anmärkningar
Fler än en fönsterfunktion kan användas i en enda fråga med en enda FROM-sats. Satsen OVER för varje funktion kan skilja sig åt i partitionering och ordning.
Om PARTITION BY inte anges behandlar funktionen alla rader i frågeresultatuppsättningen som en enda grupp.
Viktig
Om ROWS eller RANGE anges och <window frame preceding> används för <window frame extent> (kort syntax) används den här specifikationen för startpunkten för fönstrets ramgräns och CURRENT ROW används för gränsslutpunkten. Till exempel är ROWS 5 PRECEDING lika med ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Om ORDER BY inte anges används hela partitionen för en fönsterram. Detta gäller endast funktioner som inte kräver ORDER BY-satsen. Om ROWS eller RANGE inte har angetts men ORDER BY har angetts används RANGE UNBOUNDED PRECEDING AND CURRENT ROW som standard för fönsterram. Detta gäller endast funktioner som kan acceptera valfria ROWS eller RANGE specifikation. Rangordningsfunktioner kan till exempel inte acceptera ROWS eller RANGE, därför tillämpas inte den här fönsterramen trots att ORDER BY finns och ROWS eller RANGE inte är det.
Begränsningar
Satsen OVER kan inte användas med DISTINCT sammansättningar.
RANGE kan inte användas med <unsigned value specification> PRECEDING eller <unsigned value specification> FOLLOWING.
Beroende på vilken rangordning, aggregering eller analysfunktion som används med OVER-satsen kanske <ORDER BY clause> och/eller <ROWS and RANGE clause> inte stöds.
Exempel
Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.
A. Använd OVER-satsen med funktionen ROW_NUMBER
I följande exempel visas hur du använder OVER-satsen med funktionen ROW_NUMBER för att visa ett radnummer för varje rad i en partition. Den ORDER BY-sats som anges i OVER-satsen beställer raderna i varje partition efter kolumnen SalesYTD. Satsen ORDER BY i instruktionen SELECT avgör i vilken ordning hela frågeresultatuppsättningen returneras.
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
Här är resultatuppsättningen.
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. Använda OVER-satsen med aggregerade funktioner
I följande exempel används OVER-satsen med aggregerade funktioner över alla rader som returneras av frågan. I det här exemplet är det effektivare att använda OVER-satsen än att använda underfrågor för att härleda aggregerade värden.
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
Här är resultatuppsättningen.
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
I följande exempel visas hur du använder OVER-satsen med en mängdfunktion i ett beräknat värde.
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
Här är resultatuppsättningen. Aggregeringarna beräknas av SalesOrderID och Percent by ProductID beräknas för varje rad i varje 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. Producera ett glidande medelvärde och en kumulativ summa
I följande exempel används funktionerna AVG och SUM med satsen OVER för att tillhandahålla ett glidande medelvärde och en ackumulerad total total försäljning per år för varje område i tabellen Sales.SalesPerson. Data partitioneras av TerritoryID och ordnas logiskt av SalesYTD. Det innebär att funktionen AVG beräknas för varje område baserat på försäljningsåret. För TerritoryID på 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 inkluderas den tredje raden som representerar försäljning för året 2006 i beräkningen.
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;
Här är resultatuppsättningen.
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
I det här exemplet innehåller OVER-satsen inte PARTITION BY. Det innebär att funktionen tillämpas på alla rader som returneras av frågan. Den ORDER BY-sats som anges i OVER-satsen avgör den logiska ordning som funktionen AVG tillämpas på. Frågan returnerar ett glidande medelvärde av försäljningen per år för alla försäljningsterritorier som anges i WHERE-satsen. Den ORDER BY-sats som anges i SELECT-instruktionen avgör i vilken ordning frågans rader visas.
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;
Här är resultatuppsättningen.
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. Ange ROWS-satsen
gäller för: SQL Server 2012 (11.x) och senare versioner.
I följande exempel används ROWS-satsen för att definiera ett fönster där raderna beräknas som den aktuella raden och N antal rader som följer (en rad i det här exemplet).
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;
Här är resultatuppsättningen.
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
I följande exempel anges ROWS-satsen med UNBOUNDED PRECEDING. Resultatet är att fönstret börjar på den första raden i partitionen.
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;
Här är resultatuppsättningen.
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
Exempel: Analysplattformssystem (PDW)
E. Använd OVER-satsen med funktionen ROW_NUMBER
I följande exempel returneras ROW_NUMBER för säljare baserat på deras tilldelade försäljningskvot.
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;
Här är en partiell resultatuppsättning.
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. Använda OVER-satsen med aggregerade funktioner
I följande exempel visas hur du använder OVER-satsen med aggregerade funktioner. I det här exemplet är det effektivare att använda OVER-satsen än att använda underfrågor.
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;
Här är resultatuppsättningen.
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
I följande exempel visas hur du använder OVER-satsen med en mängdfunktion i ett beräknat värde. Aggregeringarna beräknas med SalesOrderNumber och procentandelen av den totala försäljningsordern beräknas för varje rad i varje 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;
Den första starten av den här resultatuppsättningen är följande:
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