Agregaciones con OVER y PARTITION BY

En el artículo anterior vimos cómo numerar filas usando funciones de ventana. Pero la cláusula Particiones no sirven solo para crear rankings.

Su verdarea gracia es combinarlas con funciones de agregación de (SUM, AVG, COUNT, MAX, MIN), lo que nos permiten realizar cálculos complejos sobre conjuntos de datos sin necesidad de agruparlos explícitamente.

Seguramente te has encontrado con este problema: Quieres listar todos los productos con su precio, y al lado, el precio promedio de su categoría para ver si es caro o barato.

Con lo que sabíamos hasta ahora, tendrías que hacer una subconsulta compleja o un JOIN con una tabla agrupada. Con las Funciones de Agregación de Ventana, esto es muy sencillo.

Totales por grupo (PARTITION BY)

Imagina que queremos ver cada venta individual, pero también queremos saber cuánto ha vendido ese empleado en total, para calcular qué porcentaje representa esa venta específica.

Usamos

SUM(Columna) OVER (PARTITION BY Grupo ORDER BY Orden)

SELECT 
    Empleado,
    Fecha,
    Importe,
    -- Suma total de TODAS las ventas de la tabla
    SUM(Importe) OVER () as TotalGlobal,
    
    -- Suma total SOLO de este empleado
    SUM(Importe) OVER (PARTITION BY Empleado) as TotalEmpleado,
    
    -- Cálculo directo: % que representa esta venta sobre el total del empleado
    Importe / SUM(Importe) OVER (PARTITION BY Empleado) * 100 as Porcentaje
FROM Ventas;

Resultado:

Totales acumulados

Para conseguir un acumulado, simplemente añadimos ORDER BY dentro de la cláusula OVER.

Cuando usamos ORDER BY en una función de agregación, SQL Server cambia el significado de “todo el grupo” a “desde el principio del grupo hasta la fila actual”.

SELECT 
    Fecha,
    Importe,
    SUM(Importe) OVER (ORDER BY Fecha) as Acumulado
FROM VentasAnuales;

Resultado:

Promedios móviles

Un promedio móvil sirve para suavizar datos fluctuantes (como el precio de una acción o ventas diarias). Por ejemplo, calcular el promedio de los últimos 3 días.

Para esto, necesitamos definir explícitamente el “marco” de la ventana usando ROWS BETWEEN.

SELECT 
    Fecha,
    Ventas,
    AVG(Ventas) OVER (
        ORDER BY Fecha
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as MediaMovil3Dias
FROM DiarioVentas;