APRENDER. PRATICAR. EVOLUIR.Escolher a unidade de cada linha
Antes de escrever uma função, define o que representa uma linha do resultado: movimento, conta, dia ou incidente. Uma janela acrescenta um cálculo ao detalhe que já existe. GROUP BY reduz linhas à granularidade do grupo. Num relatório fictício, uma conta com movimentos 7 e 5 pode aparecer duas vezes com total 12 em cada linha; isso é correto para detalhe enriquecido, mas não para uma lista de totais por conta. Escreve a contagem esperada de linhas antes de executar. Num ambiente com vários tenants, a identidade pode incluir tenant_id e account_id. Corrigir a partição evita misturar totais, mas não concede autorização para ler dados de outro tenant.
Empates, seleção e apresentação
ROW_NUMBER é útil para escolher uma linha quando existe um critério completo de desempate. Se a regra pede o evento mais recente e depois o maior ID, ordena por timestamp DESC e ID DESC. RANK e DENSE_RANK respondem a outra necessidade: conservar grupos empatados. Para valores 90,90,40, RANK dá 1,1,3 e DENSE_RANK dá 1,1,2. Se queres os dois maiores valores distintos, a segunda sequência exprime o contrato. Não acrescentes um ID único à ordem do ranking quando isso destruiria os empates pretendidos. Usa um ORDER BY exterior para apresentar os incidentes numa ordem estável, mantendo separadas a regra de seleção e a apresentação.
O frame define o acumulado
Uma soma ordenada não significa necessariamente uma soma linha a linha. Em PostgreSQL, o frame predefinido com ORDER BY inclui os pares do valor de ordenação atual. No exemplo executável, dois movimentos partilham o instante 10: ambos recebem 40 na coluna peer_total. Para um percurso por movimento, acrescenta o ID à ordem e define ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; obténs 20,40,45. O desempate precisa de corresponder ao contrato, não apenas a uma coluna disponível. LAST_VALUE também considera o fim do frame. Se precisas do último valor da partição em todas as linhas, explicita um frame que termine em UNBOUNDED FOLLOWING.
Séries esparsas e valores desconhecidos
LAG procura uma posição anterior na sequência de linhas, não uma data ausente. Se há observações nos dias 1 e 4, a linha anterior ao dia 4 pode ser a do dia 1. Uma média de três linhas também não corresponde automaticamente a três dias de calendário. Para um indicador diário, constrói a população temporal pretendida e junta os dados existentes. Só substituas ausência por zero quando a definição da métrica o permitir: dia sem movimento é diferente de falha de recolha. Em PostgreSQL 18, LAG não salta automaticamente valores NULL. Uma linha anterior com NULL continua a representar informação desconhecida que a regra de análise precisa de tratar.
Colocar o filtro no nível certo
A população vista pela janela já foi filtrada pelo WHERE desse nível. Se valores 40 e 60 representam a população e reténs apenas valores acima de 50 antes de calcular a soma, a percentagem de 60 passa a ser 100%. Para comparar com o total original, calcula primeiro o denominador e filtra numa consulta exterior. A mesma distinção aparece num painel de incidentes: último evento open não é igual a último evento cujo estado é open. Filtrar open antes de escolher o último evento pode ressuscitar um incidente já resolvido. Desenha duas etapas e escreve que população cada uma deve conservar antes de escolher a sintaxe.
Laboratório: provar forma e valores
Executa o exemplo numa base de aprendizagem e compara quatro propriedades: três linhas conservadas, totais com pares 40,40,45, acumulado por movimento 20,40,45 e ordem final por ID. Depois muda o valor da segunda linha para 8 e prevê o resultado antes de repetir. Acrescenta uma segunda conta e verifica se é necessário PARTITION BY; não assumes que um exemplo com uma conta prova isolamento entre contas. Para um saldo inicial, soma o acumulado dos movimentos ao saldo uma vez em cada resultado, sem transformar o saldo inicial num movimento repetido. Termina por explicar a diferença entre linha, partição, frame e ordem de apresentação a alguém da equipa.
WITH movements(id, instant, amount) AS (
VALUES (1,10,20), (2,10,20), (3,11,5)
)
SELECT id,
SUM(amount) OVER (ORDER BY instant) AS peer_total,
SUM(amount) OVER (
ORDER BY instant, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS movement_total
FROM movements
ORDER BY id;Três movimentos produzem peer_total 40,40,45 e movement_total 20,40,45. A diferença é explicada pelo frame e pelo desempate, não por alterações dos montantes.
Armadilhas comuns
Usar a ordem da janela como ordem final, acrescentar ID a um ranking que deve conservar empates ou tratar uma linha anterior como um dia anterior.
Tópicos relacionados: Juntar sem multiplicar enganos · Contar e agrupar com cuidado · Índices e planos de execução
Define primeiro a linha, a população e o desempate; depois escolhe a janela e o frame que reproduzem esse contrato.
Referência: PostgreSQL 18: Window Functions · PostgreSQL 18 reference semantics; DR SQL 2026.4; synthetic plan metrics and portable SQLite 3.51.2 examples