OVER (cláusula de Transact-SQL)

Actualizado: 17 de julio de 2006

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 categoría

Funciones 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 según la cual se realiza la partición del conjunto de filas resultante de la cláusula FROM correspondiente. <value_expression> sólo puede hacer referencia a columnas disponibles a través de la cláusula FROM. No puede hacer referencia a expresiones o alias de la lista de selección. <value_expression> puede ser una expresión de columna, subconsulta escalar, función escalar o 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.

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.

Vea también

Referencia

Funciones de categoría (Transact-SQL)
Funciones de agregado (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios