OVER (cláusula de Transact-SQL)

Determina las particiones y el orden del conjunto de filas antes de que se aplique la función de ventana asociada.

Se aplica a:

Funciones de ventana de categoría

Funciones de ventana de agregado. Para obtener más información, vea Funciones de agregado (Transact-SQL).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Ranking Window Functions 
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions 
< OVER_CLAUSE > :: = 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

Argumentos

• PARTITION BY
  Divide el conjunto de resultados en particiones. La función se aplica a cada partición por separado y el cálculo se reinicia para cada partición.

• value_expression
  Especifica la columna a partir de la cual se particiona el conjunto de filas generado por la cláusula FROM correspondiente. value_expression sólo puede hacer referencia a las columnas disponibles en la cláusula FROM. value_expression no puede hacer referencia a expresiones ni alias de 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.

• <Cláusula ORDER BY>
  Especifica el orden en que se debe aplicar la función de categoría. Para obtener más información, vea ORDER BY (cláusula de Transact-SQL).

  Importante
  Cuando se utiliza en el contexto de una función de categoría, la <cláusula ORDER BY> sólo puede hacer referencia a columnas disponibles a través de la cláusula FROM. No puede especificarse un entero para representar la posición del nombre o el alias de una columna en la lista de selección. La <cláusula ORDER BY> no se puede utilizar con funciones de agregado.

Notas

Las funciones están definidas en el estándar SQL de la ISO. SQL Server proporciona funciones de clasificación y agregado. Una ventana es un conjunto de filas especificado por el usuario. Una función de ventana calcula un valor para cada fila en un conjunto de resultados derivado de la ventana.

Se puede utilizar más de una función de categoría o agregado en una única consulta con una única cláusula FROM. Sin embargo, la cláusula OVER de cada función puede diferir en particiones y también en orden. No se puede utilizar la cláusula OVER con la función de agregado CHECKSUM.

Ejemplos

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

Las funciones de categoría: ROW_NUMBER, DENSE_RANK, RANK, NTILE utilizan la cláusula OVER. En el siguiente ejemplo se muestra el uso de la cláusula OVER con ROW_NUMBER.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
GO

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

En el ejemplo siguiente se muestra el uso de la cláusula OVER con funciones de agregado. En este ejemplo, es más eficaz utilizar la cláusula OVER que subconsultas.

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

Éste 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 AdventureWorks;
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

Éste es el conjunto de resultados. Tenga en cuenta que los agregados se calculan mediante SalesOrderID y se calcula Percent by ProductID 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.43
43664	773	1	14	7.14
43664	778	1	14	7.14

Vea también

Referencia

Funciones de agregado (Transact-SQL)