Funciones de ventana en SQL Server: orden y marco correctos

Las funciones de ventana añaden un saldo acumulado, un valor anterior o una posición a cada fila sin reducir el resultado a una fila por grupo. Su brevedad puede esconder decisiones del negocio. ¿Qué movimiento es anterior? ¿Comparten saldo los movimientos del mismo día? ¿Participan en el cálculo las filas que el informe no muestra?

Distingue partición, orden y marco. PARTITION BY separa grupos independientes. ORDER BY dentro de OVER establece la secuencia del cálculo. Para las funciones que lo admiten, el marco define qué filas contribuyen al resultado actual. El ORDER BY final solo controla la presentación y no sustituye ninguna de esas decisiones.

Hacer visibles los empates

El ejemplo incluye deliberadamente dos movimientos con la misma fecha.

DECLARE @Ledger table (
    AccountId int, EntryId int, PostedOn date, Amount decimal(12,2)
);
INSERT @Ledger VALUES
(1, 1, '20250101', 100.00),
(1, 2, '20250101', -20.00),
(1, 3, '20250102', 50.00),
(2, 4, '20250101', 7.00);

SELECT AccountId, EntryId, Amount,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn
    ) AS DatePeerTotal,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningBalance,
    LAST_VALUE(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS FinalEntryAmount
FROM @Ledger
ORDER BY AccountId, PostedOn, EntryId;

Para la cuenta 1, DatePeerTotal devuelve 80, 80 y 130. Un agregado ordenado sin marco explícito utiliza por defecto un marco RANGE que incluye las filas empatadas en el valor de ordenación. Ambos movimientos del 1 de enero ven así el total de todo ese día.

RunningBalance devuelve 100, 80 y 130. El marco ROWS acumula siguiendo la secuencia única PostedOn, EntryId. Añadir ROWS sin un orden determinista no decide qué movimiento empatado viene primero. Si el negocio necesita una secuencia de contabilización diferente del identificador, almacénala y úsala expresamente.

FinalEntryAmount devuelve 50 en todas las filas de la cuenta 1. El marco llega de forma explícita hasta el final de la partición. LAST_VALUE con un marco que termina en la fila actual suele devolver el valor actual o el último de sus iguales, no necesariamente el último de toda la cuenta.

La cuenta 2 se calcula de forma independiente y obtiene 7. Omitir PARTITION BY mezclaría cuentas distintas. Un total general correcto no garantiza que todos los saldos por fila sean correctos.

Entender lo que elimina un filtro

Esta consulta selecciona el último movimiento de cada cuenta. El identificador descendente resuelve los empates de fecha.

;WITH Ranked AS (
    SELECT AccountId, EntryId, PostedOn, Amount,
        ROW_NUMBER() OVER (
            PARTITION BY AccountId
            ORDER BY PostedOn DESC, EntryId DESC
        ) AS rn
    FROM @Ledger
)
SELECT AccountId, EntryId, PostedOn, Amount
FROM Ranked
WHERE rn = 1
ORDER BY AccountId;

El filtro sobre rn debe estar fuera porque el resultado de ventana no está disponible en WHERE al mismo nivel. Una consulta exterior también permite mostrar un intervalo limitado conservando los movimientos anteriores para calcular el saldo.

Si filtras primero el diario al 2 de enero, el acumulado de la cuenta 1 será 50 en vez de 130. Cuando hace falta un saldo inicial, calcula sobre el historial necesario antes de filtrar la presentación, o calcula por separado la apertura y añade los movimientos del periodo. La segunda opción puede reducir trabajo, pero ambos cálculos deben utilizar una visión coherente de los datos.

LAG significa la fila anterior según el orden definido, no el día natural anterior. Las fechas ausentes no generan filas por sí mismas. Una comparación diaria puede requerir agrupar por día y utilizar una tabla calendario para representar días sin actividad.

Medir el coste de la pregunta real

Un índice que empiece por las claves de partición y continúe con las de ordenación puede reducir los pasos de ordenación. Incluye únicamente las columnas adicionales justificadas por la carga. Ventanas con órdenes incompatibles pueden seguir necesitando varias ordenaciones.

Revisa filas reales, derrames de ordenación a disco, concesiones de memoria y volumen que entra en los operadores de ventana. Un resultado pequeño puede exigir procesar mucho historial. Un TOP exterior no garantiza que un cálculo sobre toda la partición sea barato.

Prueba marcas temporales duplicadas, varias cuentas, importes negativos, una cuenta con una sola fila y un periodo visible que empiece después del primer movimiento. Si Amount permite NULL, decide si un importe desconocido debe omitirse o señalar un problema de calidad. La consulta correcta permite explicar claramente de dónde sale cada saldo y qué movimientos contribuyen a él.

Referencias técnicas: Microsoft Learn: OVER clause · Microsoft Learn: ROW_NUMBER · Microsoft Learn: LAST_VALUE.