OVER (cláusula 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

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

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

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

Vea también

Referencia