SELECT - OVER-Klausel (Transact-SQL)SELECT - OVER Clause (Transact-SQL)

GILT FÜR: jaSQL Server (ab 2008) jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Bestimmt die Partitionierung und Reihenfolge eines Rowsets vor der Anwendung der zugehörigen Fensterfunktion.Determines the partitioning and ordering of a rowset before the associated window function is applied. Demnach definiert die OVER-Klausel ein Fenster oder eine benutzerdefinierte Reihe von Zeilen innerhalb eines Abfrageresultsets.That is, the OVER clause defines a window or user-specified set of rows within a query result set. Eine Fensterfunktion berechnet dann einen Wert für jede Zeile im Fenster.A window function then computes a value for each row in the window. Sie können die OVER-Klausel mit Funktionen verwenden, um aggregierte Werte wie gleitende Durchschnitte, kumulierte Aggregate, laufende Gesamtbeträge oder Ergebnisse vom Typ "Erste n pro Gruppe" berechnen.You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

Themenlinksymbol Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse  
  
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 for Parallel Data Warehouse  
  
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )  

ArgumenteArguments

PARTITION BYPARTITION BY
Teilt das Abfrageresultset in Partitionen.Divides the query result set into partitions. Die Fensterfunktion wird auf jede Partition einzeln angewendet, und die Berechnung wird für jede Partition neu gestartet.The window function is applied to each partition separately and computation restarts for each partition.

value_expressionvalue_expression
Gibt die Spalte an, nach der das Rowset partitioniert wird.Specifies the column by which the rowset is partitioned. value_expression kann nur auf von der FROM-Klausel bereitgestellte Spalten verweisen.value_expression can only refer to columns made available by the FROM clause. value_expression kann in der Auswahlliste nicht auf Ausdrücke oder Aliase verweisen.value_expression cannot refer to expressions or aliases in the select list. value_expression kann ein Spaltenausdruck, eine skalare Unterabfrage, eine Skalarfunktion oder eine benutzerdefinierte Variable sein.value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY-Klausel><ORDER BY clause>
Definiert die logische Reihenfolge der Zeilen innerhalb jeder Partition des Resultsets.Defines the logical order of the rows within each partition of the result set. Demnach gibt sie die logische Reihenfolge an, in der die Fensterfunktionsberechnung ausgeführt wird.That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expressionorder_by_expression
Gibt eine Spalte oder einen Ausdruck für die Sortierung an.Specifies a column or expression on which to sort. order_by_expression kann nur auf von der FROM-Klausel bereitgestellte Spalten verweisen.order_by_expression can only refer to columns made available by the FROM clause. Eine ganze Zahl kann nicht angegeben werden, um einen Spaltennamen oder einen Alias darzustellen.An integer cannot be specified to represent a column name or alias.

COLLATE collation_nameCOLLATE collation_name
Gibt an, dass der ORDER BY-Vorgang gemäß der in collation_name angegebenen Sortierung ausgeführt werden soll.Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name kann entweder der Name einer Windows-Sortierreihenfolge oder ein SQL-Sortierungsname sein.collation_name can be either a Windows collation name or a SQL collation name. Weitere Informationen finden Sie unter Collation and Unicode Support.For more information, see Collation and Unicode Support. COLLATE ist nur für Spalten vom Typ char, varchar, nchar und nvarchar anwendbar.COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESCASC | DESC
Gibt an, dass die Werte in der angegebenen Spalte in aufsteigender oder absteigender Reihenfolge sortiert werden sollen.Specifies that the values in the specified column should be sorted in ascending or descending order. ASC ist die Standardsortierreihenfolge.ASC is the default sort order. NULL-Werte werden als die niedrigsten Werte behandelt, die möglich sind.Null values are treated as the lowest possible values.

ROWS | RANGEROWS | RANGE
Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Grenzt die Zeilen innerhalb der Partition weiter ein, indem Start- und Endpunkte innerhalb der Partition angegeben werden.Further limits the rows within the partition by specifying start and end points within the partition. Dies erfolgt durch die Angabe einer Reihe von Zeilen unter Berücksichtigung der aktuellen Zeile – entweder anhand der logischen oder physischen Zuordnung.This is done by specifying a range of rows with respect to the current row either by logical association or physical association. Die physische Zuordnung wird erreicht, indem die ROWS-Klausel verwendet wird.Physical association is achieved by using the ROWS clause.

Die ROWS-Klausel schränkt die Zeilen innerhalb einer Partition ein, indem eine feste Anzahl an Zeilen angegeben wird, die der aktuellen Zeile vorausgehen oder dieser folgen.The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternativ schränkt die RANGE-Klausel die Zeilen innerhalb einer Partition logisch ein, indem eine Reihe von Werten unter Berücksichtigung des Werts der aktuellen Zeile angegeben wird.Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. Vorausgehende und folgende Zeilen werden auf Grundlage der Reihenfolge in der ORDER BY-Klausel definiert.Preceding and following rows are defined based on the ordering in the ORDER BY clause. Der Fensterrahmen „RANGE ... CURRENT ROW ...“ enthält alle Zeilen, die im ORDER BY-Ausdruck über die gleichen Werte wie die aktuelle Zeile verfügen.The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. Beispielsweise bedeutet ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, dass das Zeilenfenster, über das die Funktion ausgeführt wird, drei Zeilen umfasst – beginnend mit zwei vorausgehenden Zeilen bis zur und einschließlich der aktuellen Zeile.For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.

Hinweis

ROWS oder RANGE erfordert die Angabe der ORDER BY-Klausel.ROWS or RANGE requires that the ORDER BY clause be specified. Wenn ORDER BY mehrere Reihenfolgenausdrücke enthält, berücksichtigt CURRENT ROW FOR RANGE beim Ermitteln der aktuellen Zeile alle Spalten in der ORDER BY-Liste.If ORDER BY contains multiple order expressions, CURRENT ROW FOR RANGE considers all columns in the ORDER BY list when determining the current row.

UNBOUNDED PRECEDINGUNBOUNDED PRECEDING
Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Gibt an, dass das Fenster bei der ersten Zeile der Partition startet.Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING kann nur als Fensterstartpunkt angegeben werden.UNBOUNDED PRECEDING can only be specified as window starting point.

<unsigned value specification> PRECEDING<unsigned value specification> PRECEDING
Wird mit <unsigned value specification> angegeben, um die Anzahl der Zeilen oder Werte anzugeben, die der aktuellen Zeile vorausgehen sollen.Specified with <unsigned value specification>to indicate the number of rows or values to precede the current row. Diese Spezifikation ist für RANGE nicht zulässig.This specification is not allowed for RANGE.

CURRENT ROWCURRENT ROW
Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Gibt an, dass das Fenster bei Verwendung mit ROWS oder auf Basis des aktuellen Werts bei Verwendung mit RANGE bei der aktuellen Zeile startet oder endet.Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW kann als Start- und Endpunkt angegeben werden.CURRENT ROW can be specified as both a starting and ending point.

BETWEEN <window frame bound > AND <window frame bound >BETWEEN <window frame bound > AND <window frame bound >
Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Wird mit ROWS oder RANGE verwendet, um den unteren Grenzpunkt (Startpunkt) oder oberen Grenzpunkt (Endpunkt) des Fensters anzugeben.Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <window frame bound> definiert den Startgrenzpunkt, und <window frame bound> definiert den Endgrenzpunkt.<window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. Die Obergrenze kann nicht kleiner als die Untergrenze sein.The upper bound cannot be smaller than the lower bound.

UNBOUNDED FOLLOWINGUNBOUNDED FOLLOWING
Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Gibt an, dass das Fenster bei der letzten Zeile der Partition endet.Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING kann nur als Fensterendpunkt angegeben werden.UNBOUNDED FOLLOWING can only be specified as a window end point. Beispielsweise definiert RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ein Fenster, das mit der aktuellen Zeile startet und mit der letzten Zeile der Partition endet.For example RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current row and ends with the last row of the partition.

<unsigned value specification> FOLLOWING<unsigned value specification> FOLLOWING
Wird mit <unsigned value specification> angegeben, um die Anzahl der Zeilen oder Werte anzugeben, die der aktuellen Zeile folgen sollen.Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. Wenn <unsigned value specification> FOLLOWING als Fensterstartpunkt angegeben ist, muss der Endpunkt <unsigned value specification>FOLLOWING lauten.When <unsigned value specification> FOLLOWING is specified as the window starting point, the ending point must be <unsigned value specification>FOLLOWING. Beispielsweise definiert ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING ein Fenster, das mit der zweiten Zeile startet, die der aktuellen Zeile folgt, und mit der zehnten Zeile endet, die der aktuellen Zeile folgt.For example, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row that follows the current row and ends with the tenth row that follows the current row. Diese Spezifikation ist für RANGE nicht zulässig.This specification is not allowed for RANGE.

unsigned integer literalunsigned integer literal
Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Ist ein positives ganzzahliges Literal (einschließlich 0), das die Anzahl an Zeilen oder Werten angibt, die der aktuellen Zeile oder dem aktuellen Wert vorausgehen oder folgen.Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. Diese Spezifikation ist nur für ROWS gültig.This specification is valid only for ROWS.

Allgemeine HinweiseGeneral Remarks

Mehrere Fensterfunktionen können in einer einzelnen Abfrage mit einer einzelnen FROM-Klausel verwendet werden.More than one window function can be used in a single query with a single FROM clause. Die OVER-Klausel für jede Funktion kann sich in der Partitionierung und Reihenfolge unterscheiden.The OVER clause for each function can differ in partitioning and ordering.

Wird PARTITION BY nicht angegeben, verarbeitet die Funktion alle Zeilen des Abfrageresultsets als einzelne Gruppe.If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Wichtig!Important!

Bei Angabe von ROWS/RANGE und Verwendung von <window frame preceding> für <window frame extent> (kurze Syntax) wird diese Spezifikation für den Startgrenzpunkt des Fensterrahmens und CURRENT ROW für den Endgrenzpunkt verwendet.If ROWS/RANGE is specified and <window frame preceding> is used for <window frame extent> (short syntax) then this specification is used for the window frame boundary starting point and CURRENT ROW is used for the boundary ending point. Beispielsweise ist „ROWS 5 PRECEDING“ gleich „ROWS BETWEEN 5 PRECEDING AND CURRENT ROW“.For example "ROWS 5 PRECEDING" is equal to "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW".

Hinweis

Wenn ORDER BY nicht angegeben ist, wird die ganze Partition für einen Fensterrahmen verwendet.If ORDER BY is not specified entire partition is used for a window frame. Dies gilt nur für Funktionen, die keine ORDER BY-Klausel erfordern.This applies only to functions that do not require ORDER BY clause. Wenn ROWS/RANGE nicht angegeben und ORDER BY angegeben ist, wird RANGE UNBOUNDED PRECEDING AND CURRENT ROW für Fensterrahmen als Standard verwendet.If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. Dies gilt nur für Funktionen, die eine optionale ROWS/RANGE-Spezifikation akzeptieren.This applies only to functions that have can accept optional ROWS/RANGE specification. Beispielsweise können Rangfolgefunktionen ROWS/RANGE nicht akzeptieren. Daher wird dieser Fensterrahmen nicht übernommen, obwohl ORDER BY angegeben und ROWS/RANGE nicht angegeben ist.For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.

EinschränkungenLimitations and Restrictions

Die OVER-Klausel kann nicht mit der CHECKSUM-Aggregatfunktion verwendet werden.The OVER clause cannot be used with the CHECKSUM aggregate function.

RANGE kann nicht mit <unsigned value specification> PRECEDING oder <unsigned value specification> FOLLOWING verwendet werden.RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.

Je nach Rangfolge-, Aggregat- oder Analysefunktion, die mit der OVER-Klausel verwendet wird, wird die <ORDER BY-Klausel> bzw. die <ROWS/RANGE-Klausel> u.U. nicht unterstützt.Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.

BeispieleExamples

A.A. Verwenden der OVER-Klausel mit der ROW_NUMBER-FunktionUsing the OVER clause with the ROW_NUMBER function

Das folgende Beispiel zeigt die Verwendung der OVER-Klausel mit der ROW_NUMBER-Funktion, um eine Zeilennummer für jede Zeile innerhalb einer Partition anzuzeigen.The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. Durch die ORDER BY-Klausel in der OVER-Klausel werden die Zeilen in jeder Partition nach der Spalte SalesYTD sortiert.The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. Die ORDER BY-Klausel in der SELECT-Anweisung bestimmt die Reihenfolge, in der das gesamte Abfrageresultset zurückgegeben wird.The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.

USE AdventureWorks2012;  
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  

Im Folgenden finden Sie das Resultset.Here is the result set.

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.B. Verwenden der OVER-Klausel mit AggregatfunktionenUsing the OVER clause with aggregate functions

Im folgenden Beispiel wird die OVER-Klausel mit Aggregatfunktionen für alle von der Abfrage zurückgegebenen Zeilen verwendet.The following example uses the OVER clause with aggregate functions over all rows returned by the query. In diesem Beispiel ist die Verwendung der OVER-Klausel effizienter als die Verwendung von Unterabfragen, um die Aggregatwerte abzuleiten.In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

USE AdventureWorks2012;  
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  

Im Folgenden finden Sie das Resultset.Here is the result set.

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.The following example shows using the OVER clause with an aggregate function in a calculated value.

USE AdventureWorks2012;  
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  

Im Folgenden finden Sie das Resultset.Here is the result set. Beachten Sie, dass die Aggregate nach SalesOrderID berechnet werden und Percent by ProductID für jede Zeile von SalesOrderID berechnet wird.Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each 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  
  
 (20 row(s) affected)  

C.C. Erzeugen eines gleitenden Durchschnitts und kumulierten GesamtbetragsProducing a moving average and cumulative total

Im folgenden Beispiel werden die AVG- und SUM-Funktion mit der OVER-Klausel verwendet, um einen gleitenden Durchschnitt und kumulierten Gesamtbetrag von jährlichen Verkäufen für jedes Gebiet in der Sales.SalesPerson-Tabelle bereitzustellen.The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson table. Die Daten werden nach TerritoryID partitioniert und logisch nach SalesYTD sortiert.The data is partitioned by TerritoryID and logically ordered by SalesYTD. Folglich wird die AVG-Funktion auf Grundlage des Verkaufsjahres für jedes Gebiet berechnet.This means that the AVG function is computed for each territory based on the sales year. Beachten Sie, dass für TerritoryID 1 zwei Zeilen für das Verkaufsjahr 2005 vorhanden sind, die die beiden Vertriebsmitarbeiter mit dem Umsatz aus diesem Jahr darstellen.Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. Der durchschnittliche Umsatz für diese zwei Zeilen wird berechnet, und anschließend wird die dritte Zeile, die den Umsatz für das Jahr 2006 darstellt, in die Berechnung einbezogen.The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

USE AdventureWorks2012;  
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;  

Im Folgenden finden Sie das Resultset.Here is the result set.

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  
  
(10 row(s) affected)  
  

In diesem Beispiel ist PARTITION BY nicht in der OVER-Klausel enthalten.In this example, the OVER clause does not include PARTITION BY. Folglich wird die Funktion auf alle von der Abfrage zurückgegebenen Zeilen angewendet.This means that the function will be applied to all rows returned by the query. Die in der OVER-Klausel angegebene ORDER BY-Klausel bestimmt die logische Reihenfolge, auf die die AVG-Funktion angewendet wird.The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. Die Abfrage gibt einen gleitenden Durchschnitt der Jahresumsätze für alle Vertriebsgebiete zurück, die in der WHERE-Klausel angegeben sind.The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. Die in der SELECT-Anweisung angegebene ORDER BY-Klausel bestimmt die Reihenfolge, in der die Zeilen der Abfrage angezeigt werden.The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

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;  

Im Folgenden finden Sie das Resultset.Here is the result set.

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  
(10 row(s) affected)  

D.D. Angeben der ROWS-KlauselSpecifying the ROWS clause

Gilt für: SQL Server 2012 (11.x)SQL Server 2012 (11.x) bis SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Im folgenden Beispiel wird die ROWS-Klausel verwendet, um ein Fenster zu definieren, über das die Zeilen als aktuelle Zeile und die N-Anzahl an Zeilen, die folgen, berechnet werden (eine Zeile in diesem Beispiel).The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).

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;  

Im Folgenden finden Sie das Resultset.Here is the result set.

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 angegeben.In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. Das Ergebnis ist, dass das Fenster bei der ersten Zeile der Partition startet.The result is that the window starts at the first row of the partition.

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;  

Im Folgenden finden Sie das Resultset.Here is the result set.

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: Parallel Data WarehouseParallel Data WarehouseExamples: Parallel Data WarehouseParallel Data Warehouse

E.E. Verwenden der OVER-Klausel mit der ROW_NUMBER-FunktionUsing the OVER clause with the ROW_NUMBER function

Im folgenden Beispiel wird ROW_NUMBER für die Vertriebsmitarbeiter (basierend auf der zugewiesenen Sollvorgabe für den Verkauf) zurückgegeben.The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

-- Uses AdventureWorks  
  
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.Here is a partial result set.

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.F. Verwenden der OVER-Klausel mit AggregatfunktionenUsing the OVER clause with aggregate functions

In den folgenden Beispielen wird die Verwendung der OVER-Klausel mit Aggregatfunktionen dargestellt.The following examples show using the OVER clause with aggregate functions. In diesem Beispiel ist die Verwendung der OVER-Klausel effizienter als die Verwendung von Unterabfragen.In this example, using the OVER clause is more efficient than using subqueries.

-- Uses AdventureWorks  
  
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;  

Im Folgenden finden Sie das Resultset.Here is the result set.

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.The following example shows using the OVER clause with an aggregate function in a calculated value. Beachten Sie, dass die Aggregate nach SalesOrderNumber berechnet werden und der Prozentsatz der Auftragssumme für jede Zeile von SalesOrderNumber berechnet wird.Notice that the aggregates are calculated by SalesOrderNumber and the percentage of the total sales order is calculated for each line of each SalesOrderNumber.

-- Uses AdventureWorks  
  
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;  

Das erste Ergebnis dieses Resultsets lautet wie folgt:The first start of this result set is:

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  

Weitere InformationenSee Also

Aggregate Functions (Transact-SQL) (Aggregatfunktionen (Transact-SQL)) Aggregate Functions (Transact-SQL)
Analytische Funktionen (Transact-SQL) Analytic Functions (Transact-SQL)
Hilreicher Blogbeitrag zu Fensterfunktionen und OVER unter sqlmag.com von Itzik Ben-GanExcellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan