SELECT : cláusula OVER (Transact-SQL)

  • Artículo

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 Almacenamiento en 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 BY y el valor predeterminado es desde el inicio de la partición al elemento actual si se especifica el argumento ORDER 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 ASC y la función window usa todas las filas de la partición.

  • Si se especifica y ROWS no se especifica o RANGE , el valor predeterminado RANGE UNBOUNDED PRECEDING AND CURRENT ROW se usa como valor predeterminado para el marco de ventana, mediante las funciones que pueden aceptar una especificación o RANGE opcional ROWS (por ejemplo, min o max).

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 pueden 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

Contenido relacionado