.png)
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).
- 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.
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.
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 |
