Funções de janela no SQL Server: ordem e quadro corretos

Funções de janela acrescentam um saldo acumulado, um valor anterior ou uma classificação a cada linha sem reduzir o resultado a uma linha por grupo. A sintaxe curta pode esconder decisões importantes. Qual lançamento vem primeiro? Movimentos do mesmo dia compartilham um saldo? Registros ocultos pelo relatório continuam participando do cálculo?

Separe partição, ordenação e quadro. PARTITION BY define grupos independentes. ORDER BY dentro de OVER estabelece a sequência de cálculo. Nas funções que oferecem esse recurso, o quadro determina quais linhas contribuem para o resultado atual. O ORDER BY final controla apenas a apresentação e não substitui essas escolhas.

Tornar os empates visíveis

O exemplo contém intencionalmente dois lançamentos na mesma data.

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 a conta 1, DatePeerTotal retorna 80, 80 e 130. Um agregado ordenado sem quadro explícito utiliza por padrão um quadro RANGE que inclui os pares com o mesmo valor de ordenação. Assim, os dois lançamentos de 1º de janeiro já recebem o total do dia inteiro.

RunningBalance retorna 100, 80 e 130. O quadro ROWS acumula na sequência única PostedOn, EntryId. Adicionar apenas ROWS não resolve uma ordenação ambígua entre lançamentos empatados. Se o negócio exige uma sequência contábil diferente do identificador gerado, armazene essa sequência e use-a no cálculo.

FinalEntryAmount retorna 50 em todas as linhas da conta 1. O quadro chega explicitamente ao fim da partição. LAST_VALUE com um quadro que termina na linha atual frequentemente devolve o valor atual ou o último dos seus pares, e não o último valor de toda a conta.

A conta 2 é calculada separadamente e resulta em 7. Sem PARTITION BY, contas diferentes seriam misturadas. Um total geral aparentemente correto não comprova a correção dos saldos de cada linha.

Definir o efeito dos filtros

Esta consulta seleciona o lançamento mais recente de cada conta. O identificador decrescente desempata datas iguais.

;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;

O filtro de rn fica na consulta externa porque o resultado da janela não está disponível no WHERE do mesmo nível. A consulta externa também permite limitar o período exibido sem eliminar o histórico necessário para calcular saldos.

Filtrar o diário para 2 de janeiro antes de somar produz 50 para a conta 1, em vez do saldo completo de 130. Quando o relatório exige saldo inicial, calcule sobre o histórico necessário antes de filtrar a exibição, ou obtenha a abertura separadamente e acrescente os movimentos do período. A segunda estratégia pode reduzir trabalho, mas ambas as partes precisam usar uma visão consistente dos dados.

LAG significa a linha anterior na ordem definida, não necessariamente o dia anterior. Datas ausentes não criam registros automaticamente. Uma comparação diária pode exigir agregação por dia e uma tabela calendário que represente dias sem movimentos.

Observar o custo verdadeiro

Um índice iniciado pelas chaves de partição e seguido pelas chaves de ordenação pode reduzir operações de classificação. Inclua apenas as colunas adicionais justificadas pelo acesso. Janelas que exigem ordens incompatíveis ainda podem precisar de múltiplas classificações.

Verifique quantidades reais de linhas, operações que derramam para disco, concessões de memória e o volume que chega aos operadores de janela. Poucas linhas visíveis podem depender de muito histórico. Um TOP externo não torna automaticamente barato um cálculo que percorre a partição inteira.

Teste horários duplicados, várias contas, valores negativos, uma conta com somente uma linha e um período exibido que começa depois do primeiro lançamento. Se Amount aceita NULL, determine se um valor desconhecido pode ser ignorado ou deve indicar erro de qualidade. O SQL deve deixar claro qual sequência foi usada e quais movimentos participam de cada resultado.

Referências técnicas: Microsoft Learn: OVER clause · Microsoft Learn: ROW_NUMBER · Microsoft Learn: LAST_VALUE.