SELECT - OVER-Klausel (Transact-SQL)
Gilt für: 
SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric
Die OVER Klausel bestimmt die Partitionierung und Sortierung eines Rowsets, bevor die zugeordnete Fensterfunktion angewendet wird. Das heißt, die OVER Klausel definiert ein Fenster oder einen vom Benutzer angegebenen Satz von Zeilen innerhalb eines Abfrageergebnissatzes. Eine Fensterfunktion berechnet dann einen Wert für jede Zeile im Fenster. Sie können die OVER Klausel mit Funktionen verwenden, um aggregierte Werte zu berechnen, z. B. gleitende Mittelwerte, kumulierte Aggregate, laufende Summen oder top N pro Gruppenergebnis.
Transact-SQL-Syntaxkonventionen
Syntax
Syntax für SQL Server, Azure SQL-Datenbank und 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 ] )
Argumente
Fensterfunktionen können die folgenden Argumente in ihrer OVER-Klausel aufweisen:
PARTITION BY, das das Abfrageresultset in Partitionen unterteilt.
ORDER BY, das die logische Reihenfolge der Zeilen innerhalb jeder Partition des Resultsets definiert.
ZEILEN oder BEREICH , die die Zeilen innerhalb der Partition einschränken, indem Start- und Endpunkte innerhalb der Partition angegeben werden. Es erfordert ein
ORDER BY-Argument und der Standardwert verläuft vom Start der Partition bis zum aktuellen Element, wenn dasORDER BY-Argument angegeben ist.
Wenn Sie kein Argument angeben, werden die Fensterfunktionen auf das gesamte Resultset angewendet.
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 BY
Teilt das Abfrageresultset in Partitionen. Die Fensterfunktion wird auf jede Partition einzeln angewendet, und die Berechnung wird für jede Partition neu gestartet.
PARTITION BY <value_expression>
Wenn PARTITION BY nicht angegeben, behandelt die Funktion alle Zeilen des Abfrageergebnissatzes als einzelne Partition.
Die Funktion wird auf alle Zeilen in der Partition angewendet, wenn Sie keine Klausel angeben ORDER BY .
PARTITION BY value_expression
Gibt die Spalte an, nach der das Rowset partitioniert wird. value_expression können nur auf Spalten verweisen, die von der FROM Klausel zur Verfügung gestellt werden. value_expression können nicht auf Ausdrücke oder Aliase in der Auswahlliste verweisen. value_expression kann ein Spaltenausdruck, eine skalare Unterabfrage, eine Skalarfunktion oder eine benutzerdefinierte Variable sein.
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 | E | 3 | 98 |
| 5 | E | 3 | 98 |
| ... | ... | ... | ... |
| 98 | E | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Definiert die logische Reihenfolge der Zeilen innerhalb jeder Partition des Resultsets. Demnach gibt sie die logische Reihenfolge an, in der die Fensterfunktionsberechnung ausgeführt wird.
Wenn nicht angegeben, wird
ASCdie Standardreihenfolge verwendet, und die Fensterfunktion verwendet alle Zeilen in der Partition.Wenn angegeben und ein
ROWSoderRANGEnicht angegeben wird, wird der StandardwertRANGE UNBOUNDED PRECEDING AND CURRENT ROWals Standard für den Fensterrahmen verwendet, von den Funktionen, die eine optionaleROWSoderRANGESpezifikation akzeptieren können (zmin. B. odermax).
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 | E | 3 | 3 |
| 5 | E | 3 | 5 |
| 6 | E | 3 | 6 |
| ... | ... | ... | |
| 97 | E | 3 | 97 |
| 98 | E | 3 | 98 |
| ... | ... | ... |
order_by_expression
Gibt eine Spalte oder einen Ausdruck für die Sortierung an. order_by_expression kann nur auf Spalten verweisen, die von der FROM Klausel zur Verfügung gestellt werden. Eine ganze Zahl kann nicht angegeben werden, um einen Spaltennamen oder Alias darzustellen.
COLLATE collation_name
Gibt an, dass der ORDER BY Vorgang gemäß der in collation_name angegebenen Sortierung ausgeführt werden soll. collation_name kann entweder der Name einer Windows-Sortierreihenfolge oder ein SQL-Sortierungsname sein. Weitere Informationen finden Sie unter Sortierungs- und Unicode-Support. COLLATEgilt nur für Spalten vom Typ Char, varchar, nchar und nvarchar.
ASC | DESC
Gibt an, dass die Werte in der angegebenen Spalte in aufsteigender oder absteigender Reihenfolge sortiert werden sollen. ASC ist die Standardsortierreihenfolge. NULL-Werte werden als die niedrigsten Werte behandelt, die möglich sind.
ROWS oder RANGE
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
Grenzt die Zeilen innerhalb der Partition weiter ein, indem Start- und Endpunkte innerhalb der Partition angegeben werden. Er gibt einen Zeilenbereich in Bezug auf die aktuelle Zeile entweder durch logische Zuordnung oder physische Zuordnung an. Die physische Zuordnung wird mithilfe der ROWS Klausel erreicht.
Die ROWS Klausel begrenzt die Zeilen innerhalb einer Partition, indem eine feste Anzahl von Zeilen vor oder nach der aktuellen Zeile angegeben wird. Alternativ beschränkt die RANGE Klausel die Zeilen innerhalb einer Partition logisch, indem sie einen Wertebereich in Bezug auf den Wert in der aktuellen Zeile angeben. Vorhergehende und folgende Zeilen werden basierend auf der Reihenfolge in der ORDER BY Klausel definiert. Der Fensterrahmen RANGE ... CURRENT ROW ... enthält alle Zeilen mit denselben Werten im ORDER BY Ausdruck wie die aktuelle Zeile. Bedeutet beispielsweise, dass das Fenster der Zeilen, auf denen die Funktion ausgeführt wird, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW drei Zeilen in der Größe beträgt, beginnend mit 2 Zeilen vor und einschließlich der aktuellen Zeile.
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 | preceding | central | following |
|---|---|---|---|
| 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 oder RANGE erfordert, dass Sie die ORDER BY Klausel angeben. Wenn ORDER BY mehrere Reihenfolgenausdrücke enthalten sind, werden alle Spalten in der ORDER BY Liste berücksichtigt, CURRENT ROW FOR RANGE wenn sie die aktuelle Zeile bestimmen.
UNBOUNDED PRECEDING
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
Gibt an, dass das Fenster bei der ersten Zeile der Partition startet. UNBOUNDED PRECEDING kann nur als Startpunkt des Fensters angegeben werden.
<unsigned value specification> PRECEDING
Angegeben, mit <unsigned value specification> dem die Anzahl der Zeilen oder Werte angegeben wird, die der aktuellen Zeile vorangestellt werden sollen. Diese Spezifikation ist nicht zulässig.RANGE
CURRENT ROW
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
Gibt an, dass das Fenster bei Verwendung mit ROWS oder dem aktuellen Wert bei Verwendung mit RANGEder aktuellen Zeile beginnt oder endet. CURRENT ROW kann sowohl als Ausgangspunkt als auch als Endpunkt angegeben werden.
BETWEEN AND
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
BETWEEN <window frame bound> AND <window frame bound>
Wird entweder mit ROWS oder RANGE zum Angeben der unteren (Anfang) und oberen (endenden) Begrenzungspunkte des Fensters verwendet. <window frame bound> definiert den Grenzbeginn und <window frame bound> definiert den Grenzendpunkt. Die obere Grenze darf nicht kleiner als die untere Grenze sein.
UNBOUNDED FOLLOWING
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
Gibt an, dass das Fenster bei der letzten Zeile der Partition endet. UNBOUNDED FOLLOWING kann nur als Fensterendpunkt angegeben werden. Definiert beispielsweise ein Fenster, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING das mit der aktuellen Zeile beginnt und mit der letzten Zeile der Partition endet.
<unsigned value specification> FOLLOWING
Wird mit <unsigned value specification> angegeben, um die Anzahl der Zeilen oder Werte anzugeben, die der aktuellen Zeile folgen sollen. Wenn <unsigned value specification> FOLLOWING als Startpunkt des Fensters angegeben wird, muss der Endpunkt sein <unsigned value specification> FOLLOWING. Definiert beispielsweise ein Fenster, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING das mit der zweiten Zeile beginnt, die auf die aktuelle Zeile folgt, und endet mit der zehnten Zeile, die auf die aktuelle Zeile folgt. Diese Spezifikation ist nicht zulässig.RANGE
<nicht signiertes ganzzahliges Literal>
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
Ein positives ganzzahliges Literal (einschließlich 0), das die Anzahl der Zeilen oder Werte angibt, die der aktuellen Zeile oder dem aktuellen Wert vorangestellt oder folgen sollen. Diese Spezifikation gilt nur für ROWS.
Hinweise
Mehrere Fensterfunktionen können in einer einzelnen Abfrage mit einer einzelnen FROM Klausel verwendet werden. Die OVER Klausel für jede Funktion kann sich bei der Partitionierung und Sortierung unterscheiden.
Wenn PARTITION BY nicht angegeben, behandelt die Funktion alle Zeilen des Abfrageergebnissatzes als einzelne Gruppe.
Wichtig
Wenn ROWS oder RANGE angegeben und <window frame preceding> für <window frame extent> (kurze Syntax) verwendet wird, wird diese Spezifikation für den Anfangspunkt der Fensterrahmenbegrenzung verwendet und CURRENT ROW für den Begrenzungsendpunkt verwendet. Ist z. B ROWS 5 PRECEDING . gleich ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Wenn ORDER BY nicht angegeben, wird die gesamte Partition für einen Fensterrahmen verwendet. Dies gilt nur für Funktionen, die keine Klausel erfordern ORDER BY . Wenn ROWS oder RANGE nicht angegeben, aber ORDER BY angegeben wird, RANGE UNBOUNDED PRECEDING AND CURRENT ROW wird als Standard für Fensterrahmen verwendet. Dies gilt nur für Funktionen, die optional ROWS oder RANGE spezifikationsfrei akzeptieren können. Beispielsweise können Bewertungsfunktionen nicht akzeptieren ROWS oder RANGE, daher wird dieser Fensterrahmen nicht angewendet, obwohl ORDER BY vorhanden und ROWS nicht RANGE .
Begrenzungen
Die OVER Klausel kann nicht mit den DISTINCT Aggregationen verwendet werden.
RANGE kann nicht mit <unsigned value specification> PRECEDING oder <unsigned value specification> FOLLOWING verwendet werden.
Abhängig von der Rangfolge, Aggregat- oder Analysefunktion, die mit der OVER Klausel verwendet wird, <ORDER BY clause> und/oder die <ROWS and RANGE clause> möglicherweise nicht unterstützt wird.
Beispiele
Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.
A. Verwenden der OVER-Klausel mit der ROW_NUMBER-Funktion
Das folgende Beispiel zeigt die Verwendung der Klausel mit ROW_NUMBER der OVER Funktion zum Anzeigen einer Zeilennummer für jede Zeile innerhalb einer Partition. Durch die ORDER BY-Klausel in der OVER-Klausel werden die Zeilen in jeder Partition nach der Spalte SalesYTD sortiert. Die ORDER BY Klausel in der SELECT Anweisung bestimmt die Reihenfolge, in der das gesamte Abfrageergebnissatz zurückgegeben wird.
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
Hier sehen Sie das Ergebnis.
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. Verwenden der OVER-Klausel mit Aggregatfunktionen
Im folgenden Beispiel wird die OVER-Klausel mit Aggregatfunktionen für alle von der Abfrage zurückgegebenen Zeilen verwendet. In diesem Beispiel ist die Verwendung der OVER-Klausel effizienter als die Verwendung von Unterabfragen, um die Aggregatwerte abzuleiten.
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
Hier sehen Sie das Ergebnis.
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
Im folgenden Beispiel wird die Verwendung der OVER-Klausel mit einer Aggregatfunktion in einem berechneten Wert dargestellt.
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
Hier sehen Sie das Ergebnis. Die Aggregate werden berechnet, SalesOrderID und die Percent by ProductID werden für jede Zeile jeder Zeile SalesOrderIDberechnet.
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. Produzieren eines gleitenden Mittelwerts und kumulierter Summe
Im folgenden Beispiel werden die AVG Und-Funktionen SUM mit der OVER Klausel verwendet, um einen gleitenden Durchschnitt und einen kumulierten Jahresumsatz für jedes Gebiet in der Sales.SalesPerson Tabelle bereitzustellen. Die Daten werden nach TerritoryID partitioniert und logisch nach SalesYTD sortiert. Dies bedeutet, dass die AVG Funktion für jedes Gebiet basierend auf dem Umsatzjahr berechnet wird. Für TerritoryID 1 gibt es zwei Zeilen für das Umsatzjahr 2005 , die die beiden Vertriebsmitarbeiter mit dem Umsatz in diesem Jahr darstellen. Der durchschnittliche Umsatz für diese beiden Zeilen wird berechnet, und dann wird die dritte Zeile, die den Umsatz für das Jahr 2006 darstellt, in die Berechnung einbezogen.
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;
Hier sehen Sie das Ergebnis.
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 diesem Beispiel enthält PARTITION BYdie OVER Klausel nicht . Dies bedeutet, dass die Funktion auf alle Zeilen angewendet wird, die von der Abfrage zurückgegeben werden. Die ORDER BY in der OVER Klausel angegebene Klausel bestimmt die logische Reihenfolge, auf die die AVG Funktion angewendet wird. Die Abfrage gibt einen gleitenden Durchschnitt der Umsätze nach Jahr für alle in der WHERE Klausel angegebenen Vertriebsgebiete zurück. Die ORDER BY in der SELECT Anweisung angegebene Klausel bestimmt die Reihenfolge, in der die Zeilen der Abfrage angezeigt werden.
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;
Hier sehen Sie das Ergebnis.
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: Angeben der ROWS-Klausel
Gilt für: SQL Server 2012 (11.x) und höhere Versionen.
Im folgenden Beispiel wird die ROWS Klausel verwendet, um ein Fenster zu definieren, über das die Zeilen als aktuelle Zeile und die N-Anzahl der nachfolgenden Zeilen berechnet werden (eine Zeile in diesem Beispiel).
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;
Hier sehen Sie das Ergebnis.
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
Im folgenden Beispiel wird die ROWS Klausel mit UNBOUNDED PRECEDING. Das Ergebnis ist, dass das Fenster bei der ersten Zeile der Partition startet.
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;
Hier sehen Sie das Ergebnis.
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
Beispiele: Analytics-Plattformsystem (PDW)
E. Verwenden der OVER-Klausel mit der ROW_NUMBER-Funktion
Im folgenden Beispiel wird ROW_NUMBER für die Vertriebsmitarbeiter (basierend auf der zugewiesenen Sollvorgabe für den Verkauf) zurückgegeben.
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;
Dies ist ein Auszug aus dem Resultset.
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. Verwenden der OVER-Klausel mit Aggregatfunktionen
Die folgenden Beispiele zeigen die Verwendung der OVER Klausel mit Aggregatfunktionen. In diesem Beispiel ist die Verwendung der OVER Klausel effizienter als die Verwendung von Unterabfragen.
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;
Hier sehen Sie das Ergebnis.
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
Im folgenden Beispiel wird die Verwendung der OVER-Klausel mit einer Aggregatfunktion in einem berechneten Wert dargestellt. Die Aggregate werden berechnet, SalesOrderNumber und der Prozentsatz der Gesamtverkaufsbestellung wird für jede Zeile jeder SalesOrderNumberberechnet.
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;
Der erste Start dieses Resultsets lautet wie folgt:
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