SELECT - sobre (cláusula de Transact-SQL)SELECT - OVER Clause (Transact-SQL)

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síAzure SQL DatabasesíAzure SQL Data Warehouse síAlmacenamiento de datos paralelos THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Determina las particiones y el orden de un conjunto de filas antes de que se aplique la función de ventana asociada.Determines the partitioning and ordering of a rowset before the associated window function is applied. Es decir, la cláusula OVER define una ventana o un conjunto de filas definido por el usuario en un conjunto de resultados de la consulta.That is, the OVER clause defines a window or user-specified set of rows within a query result set. Una función de ventana calcula entonces un valor para cada fila de la ventana.A window function then computes a value for each row in the window. Puede utilizar la cláusula OVER con funciones para calcular valores agregados tales como medias móviles, agregados acumulados, totales acumulados o N elementos superiores por resultados del grupo.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.

SintaxisSyntax

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

ArgumentosArguments

PARTITION BYPARTITION BY
Divide el conjunto de resultados de la consulta en particiones.Divides the query result set into partitions. La función se aplica a cada partición por separado y el cálculo se reinicia para cada partición.The window function is applied to each partition separately and computation restarts for each partition.

value_expressionvalue_expression
Especifica la columna a partir de la cual se particiona el conjunto de filas.Specifies the column by which the rowset is partitioned. value_expression solo puede hacer referencia a las columnas disponibles en la cláusula FROM.value_expression can only refer to columns made available by the FROM clause. value_expression no puede hacer referencia a expresiones ni alias en la lista de selección.value_expression cannot refer to expressions or aliases in the select list. value_expression puede ser una expresión de columna, una subconsulta escalar, función escalar o variable definida por el usuario.value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<Cláusula ORDER BY ><ORDER BY clause>
Define el orden lógico de las filas dentro de cada partición del conjunto de resultados.Defines the logical order of the rows within each partition of the result set. Es decir, especifica el orden lógico en el que se realiza el cálculo de la función de ventana.That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expressionorder_by_expression
Especifica la columna o expresión según la cual se va a realizar la ordenación.Specifies a column or expression on which to sort. order_by_expression solo puede hacer referencia a las columnas disponibles en la cláusula FROM.order_by_expression can only refer to columns made available by the FROM clause. No se puede especificar un número entero para representar un nombre de columna o alias.An integer cannot be specified to represent a column name or alias.

COLLATE collation_nameCOLLATE collation_name
Especifica que la operación ORDER BY debe realizarse según la intercalación especificada en collation_name.Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL.collation_name can be either a Windows collation name or a SQL collation name. Para más información, consulte Compatibilidad con la intercalación y Unicode.For more information, see Collation and Unicode Support. COLLATE sólo es aplicable para las columnas de tipo char, varchar, nchar, y nvarchar.COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESCASC | DESC
Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente.Specifies that the values in the specified column should be sorted in ascending or descending order. ASC es el criterio de ordenación predeterminado.ASC is the default sort order. Los valores NULL se tratan como los valores más bajos posibles.Null values are treated as the lowest possible values.

ROWS | RANGEROWS | RANGE
Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

Limita aún más las filas de la partición especificando los puntos inicial y final.Further limits the rows within the partition by specifying start and end points within the partition. Para ello, se especifica un rango de filas con respecto a la fila actual mediante asociación lógica o asociación física.This is done by specifying a range of rows with respect to the current row either by logical association or physical association. La asociación física se realiza mediante la cláusula ROWS.Physical association is achieved by using the ROWS clause.

La cláusula ROWS restringe las filas dentro de una partición especificando un número fijo de filas delante y detrás de la fila actual.The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. La cláusula RANGE también puede restringir lógicamente las filas de una partición especificando un rango de valores con respecto al valor de la fila actual.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. Las filas precedentes y siguientes se definen en función de la ordenación de la cláusula ORDER BY.Preceding and following rows are defined based on the ordering in the ORDER BY clause. El marco de ventana "RANGE …The window frame “RANGE … CURRENT ROW …"CURRENT ROW …” incluye todas las filas que tienen los mismos valores en la expresión ORDER BY como la fila actual.includes all rows that have the same values in the ORDER BY expression as the current row. Por ejemplo, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW indica que la ventana de filas en la que opera la función tiene un tamaño de tres filas, con dos filas delante hasta e inclusive la fila actual.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.

Nota

ROWS o RANGE requieren que se especifique la cláusula ORDER BY.ROWS or RANGE requires that the ORDER BY clause be specified. Si ORDER BY contiene varias expresiones de orden, CURRENT ROW FOR RANGE considera todas las columnas de la lista ORDER BY al determinar la fila actual.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
Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

Especifica que la ventana comienza en la primera fila de la partición.Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING solo se puede especificar como punto inicial de la ventana.UNBOUNDED PRECEDING can only be specified as window starting point.

<especificación de valor sin signo > PRECEDING<unsigned value specification> PRECEDING
Especificado con <especificación de valor sin signo > para indicar el número de filas o valores que preceden a la fila actual.Specified with <unsigned value specification>to indicate the number of rows or values to precede the current row. Esta especificación no se permite para RANGE.This specification is not allowed for RANGE.

CURRENT ROWCURRENT ROW
Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

Especifica que la ventana comienza o termina en la fila actual cuando se utiliza con ROWS, o el valor actual cuando se utiliza con RANGE.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 se puede especificar como punto inicial o final.CURRENT ROW can be specified as both a starting and ending point.

ENTRE <límite del marco de ventana > AND <límite del marco de ventana >BETWEEN <window frame bound > AND <window frame bound >
Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

Se utiliza con ROWS o RANGE para especificar los puntos de límite inferior (inicio) y superior (final) de la ventana.Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <límite del marco de ventana > define el punto inicial del límite y <límite del marco de ventana > define el punto final.<window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. El límite superior no puede ser menor que el límite inferior.The upper bound cannot be smaller than the lower bound.

UNBOUNDED FOLLOWINGUNBOUNDED FOLLOWING
Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

Especifica que la ventana termina en la última fila de la partición.Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING solo se puede especificar como punto final de una ventana.UNBOUNDED FOLLOWING can only be specified as a window end point. Por ejemplo, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING define una ventana que empieza en la fila actual y termina en la última fila de la partición.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.

<especificación de valor sin signo > siguiente<unsigned value specification> FOLLOWING
Especificado con <especificación de valor sin signo > para indicar el número de filas o valores detrás de la fila actual.Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. Cuando <especificación de valor sin signo > siguiente se especifica que la ventana de punto de partida, el punto final debe ser <especificación de valor sin signo > siguiente.When <unsigned value specification> FOLLOWING is specified as the window starting point, the ending point must be <unsigned value specification>FOLLOWING. Por ejemplo, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING define una ventana que empieza en la segunda fila a partir de la fila actual y termina en la décima fila a partir de la fila actual.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. Esta especificación no se permite para RANGE.This specification is not allowed for RANGE.

literal entero sin signounsigned integer literal
Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

Es un literal entero positivo (incluido el 0) que especifica el número de filas o de valores delante o detrás de la fila o el valor actual.Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. Esta especificación es válida solamente para ROWS.This specification is valid only for ROWS.

Notas generalesGeneral Remarks

Se pueden utilizar varias funciones de ventana en una sola consulta con una única cláusula FROM.More than one window function can be used in a single query with a single FROM clause. La cláusula OVER de cada función puede diferir en particiones y también en orden.The OVER clause for each function can differ in partitioning and ordering.

Si no se especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como un único grupo.If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Importante:Important!

Si se especifica ROWS/RANGE y <marco de ventana precedente > se utiliza para <extensión de marco de ventana > (sintaxis abreviada), a continuación, esta especificación se utiliza para el punto inicial del límite del marco de ventana y CURRENT ROW se usa para el final del límite punto.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. Por ejemplo, “ROWS 5 PRECEDING” es igual a “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.For example “ROWS 5 PRECEDING” is equal to “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Nota

Si no se especifica ORDER BY, se utiliza la partición completa para el marco de ventana.If ORDER BY is not specified entire partition is used for a window frame. Esto se aplica únicamente a las funciones que no requieren la cláusula ORDER BY.This applies only to functions that do not require ORDER BY clause. Si no se especifica ROWS/RANGE pero sí ORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW se utiliza como valor predeterminado para el marco de ventana.If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. Esto se aplica solamente a las funciones que pueden aceptar la especificación opcional de ROWS/RANGE.This applies only to functions that have can accept optional ROWS/RANGE specification. Por ejemplo, las funciones de clasificación no pueden aceptar ROWS/RANGE; por lo tanto, este marco de ventana no se aplica aunque se especifique ORDER BY y no se especifique ROWS/RANGE.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.

Limitaciones y restriccionesLimitations and Restrictions

No se puede utilizar la cláusula OVER con la función de agregado CHECKSUM.The OVER clause cannot be used with the CHECKSUM aggregate function.

INTERVALO no se puede usar con <especificación de valor sin signo > PRECEDING o <especificación de valor sin signo > siguiente.RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.

Dependiendo de la función de clasificación, agregada o analítica utilizada con la cláusula OVER, <cláusula ORDER BY > o <ROWS y RANGE cláusula > puede no ser compatible.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.

EjemplosExamples

A.A. Utilizar la cláusula OVER con la función ROW_NUMBERUsing the OVER clause with the ROW_NUMBER function

En el ejemplo siguiente se muestra cómo usar la cláusula OVER con la función ROW_NUMBER para mostrar un número de fila para cada fila de una partición.The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. La cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición por la columna SalesYTD.The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. La cláusula ORDER BY en la instrucción SELECT determina el orden en que se devuelve el conjunto completo de resultados de la consulta.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  

El conjunto de resultados es el siguiente.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. Utilizar la cláusula OVER con funciones de agregadoUsing the OVER clause with aggregate functions

En el ejemplo siguiente se utiliza la cláusula OVER con funciones de agregado en todas las filas devueltas por la consulta.The following example uses the OVER clause with aggregate functions over all rows returned by the query. En este ejemplo, el uso de OVER es más eficaz que usar subconsultas para obtener los valores agregados.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  

El conjunto de resultados es el siguiente.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  

En el ejemplo siguiente se muestra el uso de la cláusula OVER con una función de agregado en un valor calculado.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  

El conjunto de resultados es el siguiente.Here is the result set. Tenga en cuenta que los agregados se calculan mediante SalesOrderID y se calcula Percent by ProductID para cada línea de cada SalesOrderID. 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. Producir una media móvil y un total acumulativoProducing a moving average and cumulative total

En el ejemplo siguiente se usan las funciones AVG y SUM con la cláusula OVER para proporcionar una media móvil y un total acumulado de ventas anuales para cada territorio de la tabla Sales.SalesPerson.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. Se crean particiones de los datos por TerritoryID y se ordenan lógicamente por SalesYTD.The data is partitioned by TerritoryID and logically ordered by SalesYTD. Esto significa que la función AVG se calcula para cada territorio en función del año de ventas.This means that the AVG function is computed for each territory based on the sales year. Observe que para TerritoryID 1, solo hay dos filas para el año de ventas 2005, que representan los dos vendedores con ventas durante ese año.Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. Se calculan las ventas medias de estas dos filas y la tercera fila que representa las ventas durante el año 2006 se incluye en el cálculo.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;  

El conjunto de resultados es el siguiente.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)  

En este ejemplo, la cláusula OVER no incluye PARTITION BY.In this example, the OVER clause does not include PARTITION BY. Esto significa que la función se aplicará a todas las filas devueltas por la consulta.This means that the function will be applied to all rows returned by the query. La cláusula ORDER BY especificada en la cláusula OVER determina el orden lógico al que se aplica la función AVG.The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. La consulta devuelve una media móvil de ventas por año para todos los territorios de ventas especificados en la cláusula WHERE.The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. La cláusula ORDER BY especificada en la instrucción SELECT determina el orden en que se muestran las filas de la consulta.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;  

El conjunto de resultados es el siguiente.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. Especificar la cláusula ROWSSpecifying the ROWS clause

Se aplica a: desde SQL Server 2012SQL Server 2012 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

En el ejemplo siguiente se utiliza la cláusula ROWS para definir una ventana en la que se calculan las filas que la fila actual y la N número de filas que van a continuación (1 fila en este ejemplo).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;  

El conjunto de resultados es el siguiente.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  

En el ejemplo siguiente, la cláusula ROWS se especifica con UNBOUNDED PRECEDING.In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. El resultado es que la ventana comienza en la primera fila de la partición.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;  

El conjunto de resultados es el siguiente.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  

Ejemplos: Almacenamiento de datos paralelosParallel Data WarehouseExamples: Almacenamiento de datos paralelosParallel Data Warehouse

E.E. Utilizar la cláusula OVER con la función ROW_NUMBERUsing the OVER clause with the ROW_NUMBER function

En el ejemplo siguiente se devuelve el ROW_NUMBER para los representantes de ventas en función de sus cuotas de ventas asignado.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;  

A continuación se muestra un conjunto parcial de resultados.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. Utilizar la cláusula OVER con funciones de agregadoUsing the OVER clause with aggregate functions

Los ejemplos siguientes muestran el uso de la cláusula OVER con funciones de agregado.The following examples show using the OVER clause with aggregate functions. En este ejemplo, es más eficaz que usar subconsultas con la cláusula OVER.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;  

El conjunto de resultados es el siguiente.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  

En el ejemplo siguiente se muestra el uso de la cláusula OVER con una función de agregado en un valor calculado.The following example shows using the OVER clause with an aggregate function in a calculated value. Tenga en cuenta que los agregados se calculan por SalesOrderNumber y el porcentaje de la orden de venta total se calcula para cada línea de cada SalesOrderNumber.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;  

Es la primera vez que este conjunto de resultados: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  

Vea tambiénSee Also

Las funciones de agregado ( Transact-SQL ) Aggregate Functions (Transact-SQL)
Funciones analíticas ( Transact-SQL ) Analytic Functions (Transact-SQL)
Entrada de blog excelente sobre SOBREUTILIZACIÓN y funciones de ventana en sqlmag.com, Itzik Ben-GanExcellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan