OVER-Klausel (Transact-SQL)
Bestimmt die Partitionierung und Reihenfolge des Rowsets vor der Anwendung der zugehörigen Fensterfunktion.
Betrifft:
Fensterrangfunktionen
Fensteraggregatfunktionen Weitere Informationen finden Sie unter Aggregatfunktionen (Transact-SQL).
.gif "Themenlink (Symbol)")
Syntax
Ranking Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
<ORDER BY_Clause> )
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
Argumente
PARTITION BY
Unterteilt das Resultset in Partitionen. Die Fensterfunktion wird auf jede Partition einzeln angewendet, und die Berechnung wird für jede Partition neu gestartet.value_expression
Gibt die Spalte an, nach der das von der entsprechenden FROM-Klausel erstellte Rowset partitioniert wird. value_expression kann nur auf Spalten verweisen, die durch die FROM-Klausel verfügbar gemacht werden. value_expression kann nicht auf Ausdrücke und Aliasse in der Auswahlliste verweisen. value_expression kann ein Spaltenausdruck, eine skalare Unterabfrage, eine skalare Funktion oder eine benutzerdefinierte Variable sein.<ORDER BY-Klausel>
Gibt die Reihenfolge an, in der die Fensterrangfunktion angewendet wird. Weitere Informationen finden Sie unter ORDER BY-Klausel (Transact-SQL)..gif "Wichtiger Hinweis")
WichtigBei Verwendung im Kontext einer Fensterrangfunktion kann die <ORDER BY-Klausel> nur auf Spalten verweisen, die durch die FROM-Klausel verfügbar gemacht werden. Eine ganze Zahl kann nicht angegeben werden, um die Position des Namens oder des Alias einer Spalte in der Auswahlliste darzustellen. <ORDER BY-Klausel> kann nicht mit Fensteraggregatfunktionen verwendet werden.
Hinweise
Fensterfunktionen werden im ISO SQL-Standard definiert. In SQL Server werden Fensterrang- und Fensteraggregatfunktionen bereitgestellt. Ein Fenster stellt eine vom Benutzer angegebene Gruppe von Zeilen dar. Mit einer Fensterfunktion wird ein Wert für jede Zeile in einem Resultset berechnet, das aus dem Fenster abgeleitet wird.
Es können mehrere Fensterrang- oder -aggregatfunktionen in einer Abfrage mit einer FROM-Klausel verwendet werden. Die OVER-Klausel für die einzelnen Funktionen kann sich jedoch in der Partitionierung und auch der Reihenfolge unterscheiden. Die OVER-Klausel kann nicht mit der CHECKSUM-Aggregatfunktion verwendet werden.
Beispiele
A. Verwenden der OVER-Klausel mit der ROW_NUMBER-Funktion
In jeder Rangfolgefunktion, ROW_NUMBER, DENSE_RANK, RANK und NTILE, wird die OVER-Klausel verwendet. Im folgenden Beispiel wird die Verwendung der OVER-Klausel mit ROW_NUMBER dargestellt.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
B. Verwenden der OVER-Klausel mit Aggregatfunktionen
In den folgenden Beispielen wird die Verwendung der OVER-Klausel mit Aggregatfunktionen dargestellt. In diesem Beispiel ist die Verwendung der OVER-Klausel effizienter als die Verwendung von Unterabfragen.
USE AdventureWorks;
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
Dies ist das Resultset.
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 AdventureWorks;
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
Dies ist das Resultset. Beachten Sie, dass die Aggregate nach SalesOrderID berechnet werden und Percent by ProductID für jede Zeile von SalesOrderID berechnet wird.
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.43 |
43664 |
773 |
1 |
14 |
7.14 |
43664 |
778 |
1 |
14 |
7.14 |