Freigeben über


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).

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

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).

    Wichtiger HinweisWichtig

    Bei 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