Compartir a través de


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

Icono de vínculo a temasConvenciones 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).

    Nota importanteImportante

    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