SELECT : cláusula OVER (Transact-SQL)
Se aplica a: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Punto de conexión de análisis SQL en Microsoft Fabric Warehouse en Microsoft Fabric Base de datos SQL de Microsoft Fabric
La OVER cláusula determina la creación de particiones y el orden de un conjunto de filas antes de aplicar la función de ventana asociada. Es decir, la OVER cláusula define una ventana o un conjunto especificado por el usuario de filas dentro de un conjunto de resultados de consulta. Una función de ventana calcula entonces un valor para cada fila de la ventana. Puede usar la OVER cláusula con funciones para calcular valores agregados como medias móviles, agregados acumulativos, totales en ejecución o N principales por grupo.
Convenciones de sintaxis de Transact-SQL
Sintaxis
Sintaxis para SQL Server, Azure SQL Database y Azure Synapse Analytics.
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> }
Sintaxis para el almacenamiento de datos paralelo.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Argumentos
Las funciones de ventana podrían tener los siguientes argumentos en su cláusula OVER:
PARTITION BY, que divide el conjunto de resultados de la consulta en particiones.
ORDER BY, que define el orden lógico de las filas dentro de cada partición del conjunto de resultados.
ROWS o RANGE que limita las filas dentro de la partición especificando los puntos inicial y final dentro de la partición. Requiere el argumento
ORDER BYy el valor predeterminado es desde el inicio de la partición al elemento actual si se especifica el argumentoORDER BY.
Si no especifica ningún argumento, las funciones de ventana se aplican en todo el conjunto de resultados.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | min. | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTITION BY
Divide el conjunto de resultados de la consulta en particiones. La función se aplica a cada partición por separado y el cálculo se reinicia para cada partición.
PARTITION BY <value_expression>
Si PARTITION BY no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como una sola partición.
La función se aplica en todas las filas de la partición si no se especifica ORDER BY la cláusula .
PARTITION BY value_expression
Especifica la columna a partir de la cual se particiona el conjunto de filas. value_expression solo puede hacer referencia a las columnas disponibles por la FROM cláusula . value_expression no puede hacer referencia a expresiones ni alias en la lista de selección. value_expression puede ser una expresión de columna, una subconsulta escalar, una función escalar o una variable definida por el usuario.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | type | min. | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Define el orden lógico de las filas dentro de cada partición del conjunto de resultados. Es decir, especifica el orden lógico en el que se realiza el cálculo de la función de ventana.
Si no se especifica, el orden predeterminado es
ASCy la función window usa todas las filas de la partición.Si se especifica y
ROWSno se especifica oRANGE, el valor predeterminadoRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWse usa como valor predeterminado para el marco de ventana, mediante las funciones que pueden aceptar una especificación oRANGEopcionalROWS(por ejemplo,minomax).
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | type | min. | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Especifica la columna o expresión según la cual se va a realizar la ordenación. order_by_expression solo puede hacer referencia a las columnas disponibles por la FROM cláusula . No se puede especificar un entero para representar un nombre de columna o alias.
COLLATE collation_name
Especifica que la ORDER BY operación debe realizarse según la intercalación especificada en collation_name. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información, consulte Soporte técnico de intercalación y Unicode. COLLATE solo es aplicable a las columnas de tipo char, varchar, nchar y nvarchar.
ASC | DESC
Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente. ASC es el criterio de ordenación predeterminado. Los valores NULL se tratan como los valores más bajos posibles.
ROWS o RANGE
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
Limita aún más las filas de la partición especificando los puntos inicial y final. Especifica un intervalo de filas con respecto a la fila actual, ya sea por asociación lógica o asociación física. La asociación física se logra mediante la ROWS cláusula .
La ROWS cláusula limita las filas dentro de una partición especificando un número fijo de filas anteriores o después de la fila actual. Como alternativa, la RANGE cláusula limita lógicamente las filas dentro de una partición especificando un intervalo de valores con respecto al valor de la fila actual. Las filas anteriores y siguientes se definen en función del orden de la ORDER BY cláusula . El marco RANGE ... CURRENT ROW ... de ventana incluye todas las filas que tienen los mismos valores en la ORDER BY expresión que la fila actual. Por ejemplo, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa que la ventana de filas en la que opera la función es de tres filas de tamaño, empezando por 2 filas anteriores hasta e incluyendo la fila actual.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | precedentes | central | siguientes |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS o RANGE requiere que especifique la ORDER BY cláusula . Si ORDER BY contiene varias expresiones de orden, CURRENT ROW FOR RANGE tiene en cuenta todas las columnas de la ORDER BY lista al determinar la fila actual.
UNBOUNDED PRECEDING
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
Especifica que la ventana comienza en la primera fila de la partición. UNBOUNDED PRECEDING solo se puede especificar como punto de inicio de la ventana.
<especificación de valor sin signo> PRECEDING
Se especifica con <unsigned value specification> para indicar el número de filas o valores que preceden a la fila actual. Esta especificación no está permitida para RANGE.
CURRENT ROW
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
Especifica que la ventana se inicia o termina en la fila actual cuando se usa con ROWS o con el valor actual cuando se usa con RANGE. CURRENT ROW se puede especificar como punto inicial y final.
BETWEEN AND
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
BETWEEN <window frame bound> AND <window frame bound>
Se usa con ROWS o RANGE para especificar los puntos de límite inferiores (iniciales) y superiores (finales) de la ventana. <window frame bound> define el punto de partida del límite y <window frame bound> define el punto de conexión de límite. El límite superior no puede ser menor que el límite inferior.
UNBOUNDED FOLLOWING
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
Especifica que la ventana termina en la última fila de la partición. UNBOUNDED FOLLOWING solo se puede especificar como un punto de conexión de ventana. Por ejemplo, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING define una ventana que comienza con la fila actual y termina con la última fila de la partición.
<especificación de valor sin signo> FOLLOWING
Se especifica con <unsigned value specification> para indicar el número de filas o valores que siguen a la fila actual. Cuando <unsigned value specification> FOLLOWING se especifica como punto de inicio de la ventana, el punto final debe ser <unsigned value specification> FOLLOWING. Por ejemplo, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING define una ventana que comienza con la segunda fila que sigue a la fila actual y termina con la décima fila que sigue a la fila actual. Esta especificación no está permitida para RANGE.
<literal entero sin signo>
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
Literal entero positivo (incluido 0) que especifica el número de filas o valores que preceden o siguen la fila o el valor actual. Esta especificación solo es válida para ROWS.
Comentarios
Se puede usar más de una función de ventana en una sola consulta con una sola FROM cláusula. La OVER cláusula para cada función puede diferir en la creación de particiones y la ordenación.
Si PARTITION BY no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un único grupo.
Importante
Si ROWS se especifica o RANGE y <window frame preceding> se usa para <window frame extent> (sintaxis corta), esta especificación se usa para el punto inicial del límite del marco de ventana y CURRENT ROW se usa para el punto final del límite. Por ejemplo, ROWS 5 PRECEDING es igual a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Si ORDER BY no se especifica, se usa toda la partición para un marco de ventana. Esto solo se aplica a las funciones que no requieren la ORDER BY cláusula . Si ROWS o RANGE no se especifica pero ORDER BY se especifica, RANGE UNBOUNDED PRECEDING AND CURRENT ROW se usa como valor predeterminado para el marco de ventana. Esto solo se aplica a las funciones que tienen pueden aceptar especificaciones o RANGE opcionalesROWS. Por ejemplo, las funciones de clasificación no pueden aceptar ROWS o RANGE, por lo tanto, este marco de ventana no se aplica aunque ORDER BY esté presente o ROWS RANGE no.
Limitaciones
La OVER cláusula no se puede usar con las DISTINCT agregaciones.
RANGE no se puede usar con <unsigned value specification> PRECEDING o <unsigned value specification> FOLLOWING.
Dependiendo de la clasificación, el agregado o la función analítica usada con la OVER cláusula <ORDER BY clause> o puede <ROWS and RANGE clause> que no se admita.
Ejemplos
Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se puede descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.
A Uso de la cláusula OVER con la función ROW_NUMBER
En el ejemplo siguiente se muestra el uso de la cláusula con ROW_NUMBER la OVER función para mostrar un número de fila para cada fila dentro de una partición. La cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición por la columna SalesYTD. La ORDER BY cláusula de la SELECT instrucción determina el orden en el que se devuelve todo el conjunto de resultados de la consulta.
USE AdventureWorks2022;
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
Este es el conjunto de resultados.
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. Uso de la cláusula OVER con funciones de agregado
En el ejemplo siguiente se utiliza la cláusula OVER con funciones de agregado en todas las filas devueltas por la consulta. En este ejemplo, el uso de OVER es más eficaz que usar subconsultas para obtener los valores agregados.
USE AdventureWorks2022;
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
Este es el conjunto de resultados.
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.
USE AdventureWorks2022;
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
Este es el conjunto de resultados. Los agregados se calculan mediante SalesOrderID y Percent by ProductID se calcula para cada línea de cada 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
C. Generar un promedio móvil y un total acumulado
En el ejemplo siguiente se usan las AVG funciones y SUM con la OVER cláusula para proporcionar una media móvil y un total acumulado de ventas anuales para cada territorio de la Sales.SalesPerson tabla. Se crean particiones de los datos por TerritoryID y se ordenan lógicamente por SalesYTD. Esto significa que la AVG función se calcula para cada territorio en función del año de ventas. Para TerritoryID 1, hay dos filas para el año 2005 de ventas que representan las dos personas de ventas con ventas ese año. Las ventas medias de estas dos filas se calculan y, a continuación, la tercera fila que representa las ventas del año 2006 se incluye en el cálculo.
USE AdventureWorks2022;
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;
Este es el conjunto de resultados.
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
En este ejemplo, la OVER cláusula no incluye PARTITION BY. Esto significa que la función se aplica a todas las filas devueltas por la consulta. La ORDER BY cláusula especificada en la OVER cláusula determina el orden lógico al que se aplica la AVG función. La consulta devuelve una media móvil de ventas por año para todos los territorios de ventas especificados en la WHERE cláusula . La ORDER BY cláusula especificada en la SELECT instrucción determina el orden en el que se muestran las filas de la consulta.
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;
Este es el conjunto de resultados.
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
D. Especificación de la cláusula ROWS
Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.
En el ejemplo siguiente se usa la ROWS cláusula para definir una ventana sobre la que se calculan las filas como la fila actual y el número N de filas siguientes (una fila en este ejemplo).
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;
Este es el conjunto de resultados.
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 ROWS cláusula se especifica con UNBOUNDED PRECEDING. El resultado es que la ventana comienza en la primera fila de la partición.
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;
Este es el conjunto de resultados.
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: Sistema de la plataforma de análisis (PDW)
E. Uso de la cláusula OVER con la función ROW_NUMBER
En este ejemplo se devuelve ROW_NUMBER para los representantes de ventas en función de su cuota de ventas asignada.
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.
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. Uso de la cláusula OVER con funciones de agregado
En los ejemplos siguientes se muestra el uso de la OVER cláusula con funciones de agregado. En este ejemplo, el uso de la OVER cláusula es más eficaz que usar subconsultas.
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;
Este es el conjunto de resultados.
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. Los agregados se calculan por SalesOrderNumber y el porcentaje del pedido de ventas total se calcula para cada línea de cada SalesOrderNumber.
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;
El primer inicio de este conjunto de resultados es el siguiente:
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