Freigeben über


OVER-Klausel (Transact-SQL)

Aktualisiert: 17. Juli 2006

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 von der FROM-Klausel verfügbar gemacht werden. Ein Verweis auf Ausdrücke oder Aliase in der Auswahlliste ist nicht möglich. <value_expression> kann ein Spaltenausdruck, eine skalare Unterabfrage, eine Skalarfunktion 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).

    ms189461.note(de-de,SQL.90).gifWichtig:
    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

Siehe auch

Verweis

Rangfolgefunktionen (Transact-SQL)
Aggregatfunktionen (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version Verlauf

17. Juli 2006

Neuer Inhalt:
  • Beispiele wurden hinzugefügt.