OVER y PARTITION BY: calcular sin perder el detalle

Una agregación con GROUP BY reduce varias filas a un resumen. Eso funciona cuando solo necesitamos el total, pero no cuando queremos conservar cada partida y mostrar junto a ella el total de su pedido.

Las funciones de ventana calculan sobre filas relacionadas sin reducirlas a una sola fila. La ventana es el conjunto de filas que participa en el cálculo de la fila actual. La cláusula OVER la define y PARTITION BY permite dividirla en grupos independientes.

La idea

Una función de ventana devuelve un resultado para cada fila. Puede calcular un total, promedio, posición o acumulado utilizando otras filas como contexto.

PARTITION BY reinicia el cálculo cuando cambia el grupo. Si lo omitimos, la ventana puede abarcar todo el resultado.

Sintaxis

Funcion() OVER
(
    PARTITION BY ColumnaDeGrupo
    ORDER BY ColumnaDeOrden
)

No todas las funciones requieren las dos partes. Un total por grupo puede utilizar solo PARTITION BY; un acumulado necesita además un orden.

Ejemplo: total del pedido en cada partida

SELECT
    pd.IdPedido,
    pd.NumeroPartida,
    pd.IdProducto,
    pd.Cantidad * pd.PrecioUnitario AS ImportePartida,
    SUM(pd.Cantidad * pd.PrecioUnitario) OVER
    (
        PARTITION BY pd.IdPedido
    ) AS ImportePedido
FROM dbo.PedidoDetalle AS pd
ORDER BY
    pd.IdPedido,
    pd.NumeroPartida;

Las dos partidas del pedido 1 permanecen visibles. El total de 728 se calcula sobre su partición y aparece junto a cada una.

Comparar una fila con el promedio de su grupo

SELECT
    p.IdProducto,
    p.IdCategoria,
    p.Nombre,
    p.Precio,
    AVG(p.Precio) OVER
    (
        PARTITION BY p.IdCategoria
    ) AS PrecioPromedioCategoria
FROM dbo.Producto AS p
WHERE p.Activo = 1;

Podemos conservar el precio individual y observar al mismo tiempo el promedio de la categoría.

Construir un acumulado

El orden se vuelve parte del cálculo cuando cada fila debe incorporar las anteriores:

SELECT
    m.IdProducto,
    m.FechaMovimiento,
    m.IdMovimiento,
    m.TipoMovimiento,
    m.Cantidad,
    SUM
    (
        CASE
            WHEN m.TipoMovimiento = 'E' THEN m.Cantidad
            ELSE -m.Cantidad
        END
    ) OVER
    (
        PARTITION BY m.IdProducto
        ORDER BY m.FechaMovimiento, m.IdMovimiento
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ExistenciaCalculada
FROM dbo.MovimientoInventario AS m
ORDER BY
    m.IdProducto,
    m.FechaMovimiento,
    m.IdMovimiento;

La especificación ROWS define el marco: la porción de la ventana incluida en el cálculo de la fila actual. En este caso avanza fila por fila. El identificador desempata movimientos con la misma fecha.

Qué conviene comprobar

PARTITION BY agrupa para el cálculo, pero no ordena la salida. Si el orden final importa, todavía necesitamos un ORDER BY exterior.

También debemos distinguir la ventana completa del marco de filas. Al agregar ORDER BY dentro de OVER, SQL Server aplica un marco predeterminado que puede no representar el acumulado que imaginamos, especialmente cuando existen empates. Es preferible escribir ROWS de forma explícita.

Relacionado con

• GROUP BY: resume grupos y reduce el número de filas.
• ROW_NUMBER, RANK y DENSE_RANK: asignan posiciones dentro de una ventana.
• ORDER BY: define la secuencia del cálculo y, por separado, la presentación final.
• CTE: permite filtrar posteriormente los resultados de una función de ventana.