SELECT : cláusula OVER (Transact-SQL)

Se aplica a:SQL Server Azure SQL Database Instancia administrada de Azure SQL Azure Synapse Analytics Analytics Platform System (PDW)Punto de conexión de SQL Analytics en Microsoft Fabric Almacén en Microsoft Fabric Base de datos SQL 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

Syntax

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 solo para el Sistema de Plataforma de Analítica (PDW):

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Arguments

Las funciones ventana pueden incluir los siguientes argumentos en su OVER cláusula:

PARTITION BY divide el conjunto de resultados de la consulta en particiones.
ORDER BY define el orden lógico de las filas dentro de cada partición del conjunto de resultados.
ROWS o RANGE limitan las filas dentro de la partición especificando los puntos de inicio y fin dentro de la partición. Requiere una ORDER BY discusión. Si especificas ORDER BY, el valor por defecto es desde el inicio de la partición hasta el elemento actual.

Si no especificas ningún argumento, las funciones ventana se aplican a 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

PARTICIÓN POR

Divide el conjunto de resultados de la consulta en particiones. La función ventana se aplica a cada partición por separado, y el cálculo se reinicia para cada partición.

PARTITION BY <value_expression>

Si no especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como una sola partición.

Si no especificas una ORDER BY cláusula, la función se aplica a todas las filas de la partición.

PARTICIÓN POR 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
...	...	...	...

ORDENAR POR

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. Especifica el orden lógico en que se realiza el cálculo de la función ventana.

Si no especifica un pedido, el orden predeterminado es ASC y la función de ventana usa todas las filas de la partición.
Si especifica un orden pero no especifica ROWS o RANGE, las funciones que pueden aceptar una especificación o ROWS opcional RANGE (por ejemplo, MIN o MAX) usan RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW como marco de ventana predeterminado.

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. NULL los valores son los valores más bajos posibles.

ROWS o RANGE

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

Estas opciones limitan aún más las filas dentro de la partición especificando los puntos inicial y final dentro de la partición. Especifique un intervalo de filas con respecto a la fila actual, ya sea por asociación lógica o asociación física. Para lograr la asociación física, use 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 las que opera la función es de tres filas de tamaño, empezando por dos 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	preceding	central	following
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.

SIN LÍMITES PRECEDIDOS

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

Especifica que la ventana comienza en la primera fila de la partición. Solo puede especificar UNBOUNDED PRECEDING como punto de inicio de la ventana.

<especificación de valor sin signo> PRECEDING

Especifique 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.

FILA ACTUAL

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. Puede especificar CURRENT ROW como punto inicial y final.

ENTRE Y

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.

SEGUIMIENTO SIN LÍMITES

Se aplica a: SQL Server 2012 (11.x) y versiones posteriores.

Especifica que la ventana termina en la última fila de la partición. Solo puede especificar UNBOUNDED FOLLOWING como 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

Especifique con <unsigned value specification> para indicar el número de filas o valores que se van a seguir a la fila actual. Al especificar <unsigned value specification> FOLLOWING 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.

Remarks

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 no especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como un único grupo.

Important

Si especifica ROWS o RANGE y usa <window frame preceding> para <window frame extent> (sintaxis corta), la consulta usa esta especificación para el punto inicial del límite del marco de ventana y CURRENT ROW para el punto final del límite. Por ejemplo, ROWS 5 PRECEDING es igual a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.

Si no especifica ORDER BY, se usa toda la partición para un marco de ventana. Esta regla solo se aplica a las funciones que no requieren una ORDER BY cláusula . Si no especifica ROWS o RANGE pero especifica ORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW se usa como valor predeterminado para el marco de ventana. Esta regla solo se aplica a las funciones que pueden aceptar una especificación o ROWS opcionalRANGE. Por ejemplo, las funciones de clasificación no pueden aceptar ROWS o RANGE, por lo que este marco de ventana no se aplica aunque ORDER BY esté presente o ROWSRANGE no.

Limitations

No se puede usar la OVER cláusula con las DISTINCT agregaciones.

No se puede usar RANGE con <unsigned value specification> PRECEDING o <unsigned value specification> FOLLOWING.

La compatibilidad con ORDER BY la cláusula y las ROWS cláusulas y RANGE depende de la función de clasificación, agregado o análisis que se usa con la OVER cláusula .

Consideraciones sobre el rendimiento

Con funciones de ventana, el SQL Motor de base de datos suele tener la tarea de particionar y ordenar grandes conjuntos de datos para consultas analíticas complejas. Utiliza las siguientes técnicas para mantener eficientes las consultas con funciones de ventana.

Proporcionar un índice de apoyo

Para consultas de alto valor o ejecutadas frecuentemente que usan funciones ventana, considera crear un nuevo índice no agrupado. Para beneficiar a una consulta que utilice una función ventana, la posición de las columnas clave en el nuevo índice no agrupado debe coincidir con las PARTITION BY columnas seguidas por las ORDER BY columnas, si las hay. Si hay una ORDER BY cláusula presente, el orden de las columnas clave índice también debe coincidir con el orden especificado en dicha ORDER BY cláusula. Por ejemplo:

SELECT CustomerID,
       OrderDate,
       SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM Sales.SalesOrderHeader;

Esta consulta podría beneficiarse de este índice no agrupado en rowstore:

CREATE INDEX IX_SalesOrderHeader_Customer_OrderDate
    ON Sales.SalesOrderHeader (CustomerID, OrderDate)
    INCLUDE (TotalDue);

Beneficio de la ejecución en modo batch

Los operadores de Agregado de Ventanas pueden funcionar más rápido en modo lote que en modo fila. Con el procesamiento por lotes, los operadores de consulta trabajan en lotes de filas en lugar de una fila a la vez. El modo por lotes es posible en los siguientes casos:

La consulta hace referencia a una tabla que tiene un índice de almacén de columnas. Para obtener más información, consulte Índices de almacén de columnas: rendimiento de consultas.
La consulta se ejecuta en tablas de rowstore (heap o árbol B+) con nivel de compatibilidad de bases de datos 150 o superior, a partir de SQL Server 2019 (15.x).

No puedes forzar que una consulta use el modo por lotes. El SQL Motor de base de datos lo utiliza cuando es posible y se considera beneficioso. Un conjunto de operadores de plan de ejecución puede usar el modo batch tanto para objetos rowstore como columnstore. Para confirmar que se utiliza el modo por lotes, busque el operador en el plan de ejecución real, como Agregado de Ventanas, y verifique que la propiedad del Actual Execution Mode operador sea Batch. Para más información sobre operadores de planes de ejecución, consulte Referencia de operadores de planos de exhibición lógicos y físicos.

Evita derrames de clasificación

Una cardinalidad subestimada puede hacer que la operación de ordenamiento requiera más memoria en tiempo de ejecución. Este proceso puede aumentar el coste de consultas. Para mitigar derrames:

Asegúrate de que las estadísticas cubran las columnas de partición y ordenación. Asegúrate de que estas estadísticas estén actualizadas. Para obtener más información, consulte Estadísticas.
Asegúrate de que la Retroalimentación de Cesión de Memoria esté habilitada. La retroalimentación de la concesión de memoria ayuda al Optimizador de Consultas a ajustar las concesiones de memoria en ejecuciones posteriores. Para asegurar que tus cargas de trabajo sean automáticamente elegibles para recibir retroalimentación de la beca de memoria, utiliza el nivel de compatibilidad con bases de datos 140 o superior. Cuando está habilitada, esta configuración aparece como habilitada en sys.database_scoped_configurations.
Reduce el número de filas de entrada con WHERE filtros.

Examples

Los ejemplos de código de este artículo usan la base de datos de ejemplo de AdventureWorks2025 o AdventureWorksDW2025, que puede descargar de la página principal de ejemplos de Microsoft SQL Server y proyectos de comunidad.

A. Uso de la cláusula OVER con la función ROW_NUMBER

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

USE AdventureWorks2025;
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. La consulta particiona los datos por TerritoryID y ordena lógicamente por SalesYTD. Este uso de OVER significa que la AVG función se calcula para cada territorio en función del año de venta. Para TerritoryID de 1, existen dos filas para el año 2022de ventas , que representa a los dos vendedores 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 2023 se incluye en el cálculo.

USE AdventureWorks2025;
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        2021        559,697.56           559,697.56           559,697.56
287              NULL        2023        519,905.93           539,801.75           1,079,603.50
285              NULL        2024        172,524.45           417,375.98           1,252,127.95
283              1           2022        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2022        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2023        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2022        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2022        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2022        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2022        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        2021        559,697.56           559,697.56           559,697.56
275              2           2022        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2022        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2022        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2022        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2022        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2022        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2023        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2023        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2024        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           2021        1,079,603.50
287              NULL        519,905.93           2023        692,430.38
285              NULL        172,524.45           2024        172,524.45
283              1           1,573,012.94         2022        2,925,590.07
280              1           1,352,577.13         2022        2,929,139.33
284              1           1,576,562.20         2023        1,576,562.20
275              2           3,763,178.18         2022        3,763,178.18
277              3           3,189,418.37         2022        3,189,418.37
276              4           4,251,368.55         2022        6,709,904.17
281              4           2,458,535.62         2022        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           2021        559,697.56
287              NULL        519,905.93           2023        1,079,603.50
285              NULL        172,524.45           2024        1,252,127.95
283              1           1,573,012.94         2022        1,573,012.94
280              1           1,352,577.13         2022        2,925,590.07
284              1           1,576,562.20         2023        4,502,152.27
275              2           3,763,178.18         2022        3,763,178.18
277              3           3,189,418.37         2022        3,189,418.37
276              4           4,251,368.55         2022        4,251,368.55
281              4           2,458,535.62         2022        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

Comentarios

¿Le ha resultado útil esta página?

Last updated on 2026-06-24

SELECT : cláusula OVER (Transact-SQL)

Syntax

Arguments

PARTICIÓN POR

PARTICIÓN POR value_expression

ORDENAR POR

order_by_expression

COLLATE collation_name

{ ASC | DESC }

ROWS o RANGE

SIN LÍMITES PRECEDIDOS

<especificación de valor sin signo> PRECEDING

FILA ACTUAL

ENTRE Y

SEGUIMIENTO SIN LÍMITES

<especificación de valor sin signo> FOLLOWING

<literal entero sin signo>

Remarks

Limitations

Consideraciones sobre el rendimiento

Proporcionar un índice de apoyo

Beneficio de la ejecución en modo batch

Evita derrames de clasificación

Examples

A. Uso de la cláusula OVER con la función ROW_NUMBER

B. Uso de la cláusula OVER con funciones de agregado

C. Generar un promedio móvil y un total acumulado

D. Especificación de la cláusula ROWS

Ejemplos: Sistema de la plataforma de análisis (PDW)

E. Uso de la cláusula OVER con la función ROW_NUMBER

F. Uso de la cláusula OVER con funciones de agregado

Contenido relacionado

Comentarios

Recursos adicionales