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 un conjunto relacionado sin convertirlo en una sola fila. La cláusula OVER define esa ventana 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 hace explícito que el marco 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, el marco predeterminado 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.