Usando la cláusula OVER en SQL Server
La cláusula Over en una consulta determina la partición y el orden de un conjunto de filas antes de que se aplique la función de Windows asociada, es decir, la cláusula OVER define un conjunto de filas especificado por el usuario dentro de un conjunto de resultados de la consulta. Luego, una función de Windows calcula un valor para cada fila de la consulta.
Puede usar la cláusula OVER con funciones para calcular valores agregados, como promedios móviles, agregados acumulados, totales acumulados o un N superior por resultados de grupo.
Sintaxis
OVER (
[ <Partition by Expresión> ]
[ <Order by Columnas>]
[ Row or Range Cláusula]
)
Donde
[ Partition by Expresión ]
Partition by
Divide el conjunto de resultados de la consulta en particiones. La función de Window se aplica a cada partición por separado y el cálculo se reinicia para cada partición.
Expresión
Especifica la columna por la que se particiona el conjunto de filas. la Expresión solo puede hacer referencia a columnas disponibles mediante la cláusula FROM. La Expresión no puede hacer referencia a expresiones o alias en la lista de selección. La Expresión puede ser una expresión de columna, una subconsulta escalar, una función escalar o una variable definida por el usuario.
[ <Order by Columnas> ]
Especifica una columna o expresión por la que ordenar. Columnas solo puede hacer referencia a las columnas disponibles mediante la cláusula FROM. No se puede especificar un número entero para representar un nombre de columna o alias. (Ver Ordenamientos)
[ Row or Range Cláusula]
Limita aún más las filas dentro de la partición especificando puntos de inicio y finalización dentro de la partición. Esto se hace especificando un rango de filas con respecto a la fila actual, ya sea por asociación lógica o asociación física. La asociación física se logra utilizando la cláusula ROWS.
La cláusula ROWS limita las filas dentro de una partición al especificar un número fijo de filas que preceden o siguen a la fila actual. Alternativamente, la cláusula RANGE limita lógicamente las filas dentro de una partición especificando un rango de valores con respecto al valor en la fila actual.
Las filas anteriores y siguientes se definen según el orden de la cláusula ORDER BY.
Funciones Windows en SQL Server
Las funciones Windows se pueden clasificar en los siguientes grupos:
Funciones de Windows agregadas: Ver Funciones de agregado
Funciones de valores de Windows: Ver Funciones FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()
Funciones de clasificación de Windows: ROW_NUMBER(), NTILE(), RANK(), DENSE_RANK(),
Ejercicios
Usando la base de datos Northwind
use Northwind
go
Ejercicio 1
La siguiente consulta utiliza la función SUM para calcular el total de unidades compradas por cada cliente y el total general. La cláusula OVER define las particiones del cálculo. El primer cálculo se divide en cada cliente, lo que significa que la cantidad total por cliente se restablece
a cero para cada nuevo cliente. El segundo cálculo utiliza una cláusula OVER sin especificar particiones, lo que significa que el cálculo se realiza en todos los conjuntos de filas de entrada.
SELECT C.CustomerId As ‘Cód. Cliente’,
C.CompanyName As ‘Cliente’, D.Quantity As ‘Cantidad’,
SUM(D.Quantity) OVER(PARTITION BY C.CustomerID) As ‘Total Cliente’,
SUM(D.Quantity) OVER() As ‘Total General’
from Orders AS O
join Customers AS c ON O.CustomerID= C.CustomerID
join [Order Details] As D on O.OrderID = D.OrderID
Where c.CustomerID is not null
Order by c.CustomerID, D.Quantity Desc
go
El resultado se muestra en la figura.
Explicación:
El primer cliente, Alfreds Futterkiste tiene doce productos comprados (no necesariamente productos diferentes), las cantidades fueron: 40, 21, 20, 20, 16, 15, 15, 15, 6, 2, 2 y 2, las que en total suman 174 unidades.
Las órdenes y las cantidades compradas por el cliente Alfreds Futterkiste se muestran el la siguiente consulta, note que las cantidades son las mismas de la consulta usando Over.
select
O.OrderId As ‘Nº Orden’,
D.ProductID As ‘Cód. Producto’,
D.Quantity As ‘Cantidad’
from Orders As O
join [Order Details] As D on O.OrderID = D.OrderID
where CustomerID = ‘ALFKI’
Order by Cantidad desc
go
Ejercicio 2
La consulta muestra los empleados y la cantidad de órdenes atendidas
por cada empleado, también el total general de órdenes atendidas.
SELECT distinct
E.EmployeeID As ‘Cód. Empleado’,
E.LastName + Space(1) + E.FirstName As ‘Empleado’,
Count(O.OrderID) OVER(PARTITION BY E.EmployeeID ) AS ‘Cantidad Órdenes’,
Count(O.OrderID) OVER() As ‘Total Órdenes’
FROM Employees As E
join Orders AS O ON E.EmployeeID = O.EmployeeID
WHERE O.ShippedDate is not null
ORDER BY E.EmployeeID
go
El resultado se muestra en la figura
La misma consulta se puede obtener usando agrupamientos y para el total de las órdenes una subconsulta.
SELECT E.EmployeeID As ‘Cód. Empleado’,
E.LastName + Space(1) + E.FirstName As ‘Empleado’,
Count(O.OrderID) As ‘Cantidad Órdenes’,
(Select Count(O.OrderID) from Orders As O) As ‘Total Órdenes’
FROM Employees As E
join Orders AS O ON E.EmployeeID = O.EmployeeID
WHERE O.ShippedDate is not null
Group by E.EmployeeID, E.LastName + Space(1) + E.FirstName
ORDER BY E.EmployeeID
go
Importante:
Compare los tiempos de los planes de ejecución estimados y seleccione el que tenga el menor tiempo.
Para mayor información ver:
Agrupamientos en SQL Server
Subconsultas
Comparando Planes de ejecución estimados
Ejercicio 3
La consulta muestra los productos y la cantidad de unidades vendidas por cada uno así como la cantidad total por categoría.
Select distinct
C.CategoryID As ‘Cód. Categoría’,
C.CategoryName As ‘Categoría’,
P.ProductID As ‘Cód. Producto’,
P.ProductName As ‘Descripción’,
sum(D.Quantity) over(partition by P.ProductID)As ‘Total Unidades’,
sum(D.Quantity) over() As ‘Total General’
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
join [Order Details] As D on P.ProductID = D.ProductID
Order by C.CategoryID, [Total Unidades] desc
go
El resultado se muestra en la figura
Note que se muestra por cada categoría el orden de los productos más vendidos.
Ejercicio 4
Usando ordenamiento en la cláusula Over, además de agrupar una cantidad específica de filas. La consulta muestra los productos totalizando las cantidades de las tres últimas órdenes, las dos precedentes sumadas a la cantidad de la venta actual. Esto es posible utilizando el operador between, especificando la cantidad de órdenes precedentes (2 para el ejercicio) y la cantidad de la orden actual (current row).
Select
P.ProductID As ‘Cód. Producto’,
P.ProductName As ‘Descripción’,
D.Quantity As ‘Unidades’,
Month(O.OrderDate) As ‘Mes’,
sum(D.Quantity) over(partition by P.ProductID
order by Month(O.OrderDate)
rows between 2 preceding and current row )As ‘Últimas 3’,
sum(D.Quantity) over(partition by P.ProductID) As ‘Total Producto’
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
Order by P.ProductID, Mes
go
El resultado se muestra en la figura
Explicación:
El producto con código 1, Chai, se vendió cuatro veces en el mes de enero (número 1), el primer registro es de 10 unidades, en la columna «Últimas 3» aparece el mismo valor, para el segundo registro, cuya venta es de 24, en la columna «Últimas 3» aparece 34 que es el resultado de sumar las 24 de esa venta mas las 10 anteriores, el tercer registro que aparece se muestra una venta de 4 unidades, en la columna «Últimas 3» aparece 38, que es el resultado de sumar 10 + 24 de las dos precedentes y la actual de 4. El cuarto registro suma las dos precedentes de 24 + 4 y la venta de 80, el resultado es 108.