APRENDER. PRATICAR. EVOLUIR.Agregações condicionais sem perder população
Um relatório pode precisar do total de pedidos e de subtotais por estado na mesma linha. FILTER limita as entradas da sua agregação; não substitui o WHERE que filtra toda a consulta. Com três settled, dois failed e um pending, os indicadores são seis no total, três settled e dois failed. Verifica também expressões CASE: COUNT conta tanto 1 como 0 porque ambos são não nulos. Para contar falhas, usa uma expressão que só produza valor nas falhas, soma indicadores 1 e 0 ou aplica FILTER. Depois compara a soma dos grupos com o total apenas se as categorias forem realmente disjuntas e cobrirem toda a população.
Denominadores, precisão e médias
Uma média só é interpretável com a população que a sustenta. Dois pedidos com média 10 ms e oito com média 30 ms produzem média conjunta 26 ms, não 20 ms. Guarda soma e contagem quando precisas de combinar grupos; percentis não podem ser combinados pela mesma fórmula. NULL também muda o denominador: AVG de 20,NULL,40 dá 30, enquanto substituir NULL por zero dá 20. Em PostgreSQL, a divisão entre inteiros pode descartar a fração. Converte um operando antes da divisão quando o contrato exige um resultado decimal. Define ainda o tratamento de denominador zero em vez de deixar a apresentação inventar uma taxa.
Reconciliação: presença antes da diferença
Uma diferença numérica igual a zero não prova por si só uma correspondência válida. Primeiro determina se a chave existe nas duas origens; depois verifica se os montantes necessários são conhecidos; só então compara valores. No exemplo, ID 1 existe nas duas origens e difere em 10, ID 2 existe apenas no ledger com montante desconhecido, ID 3 existe apenas no statement e ID 4 corresponde com diferença zero. A coluna amount_unknown refere-se a montantes de linhas presentes; a ausência de uma linha é descrita separadamente em coverage. Esta separação evita que COALESCE fabrique zeros e transforme duas lacunas de informação numa reconciliação aparentemente bem-sucedida.
Identidade, multiplicidade e relações
Dois pagamentos com montante igual podem ser operações distintas. UNION elimina linhas iguais na projeção, enquanto UNION ALL conserva ocorrências. Se selecionares apenas amount, perdes a identidade que permitiria distinguir pagamentos legítimos de retransmissões. EXCEPT também tem uma diferença de conjuntos e uma variante ALL que conserva multiplicidades em PostgreSQL. Nas junções, observa cada relação um-para-muitos: duas comissões e três exceções podem gerar seis linhas para uma conta. Agrega cada relação à granularidade necessária antes de juntar os resultados. SUM(DISTINCT amount) não é uma correção geral, porque duas comissões legítimas podem ter o mesmo montante. Reconcilia também contagens e chaves, além dos totais.
CTEs como etapas verificáveis
Usa CTEs para dar nomes às etapas do raciocínio: população elegível, totais por conta, correspondências e classificação final. Mede a quantidade de IDs em cada fronteira. Se há doze antes do filtro e nove depois, examina as três identidades removidas antes de mudar um índice. Legibilidade não garante desempenho. Em PostgreSQL 18, uma CTE SELECT sem efeitos laterais pode ser integrada na consulta principal; múltiplas referências e opções de materialização influenciam o plano. NOT MATERIALIZED pode permitir filtros antecipados e também repetir cálculo. Compara resultados e planos num ambiente autorizado com dados representativos. Não transformes um comportamento observado num plano específico numa regra universal para todas as consultas.
Laboratório: explicar todas as exceções
Executa a reconciliação do exemplo e confirma as quatro chaves esperadas. Para cada linha, explica coverage, amount_unknown e difference separadamente. Acrescenta uma linha statement para ID 2 com montante 0: a cobertura passa a both, mas o montante do ledger continua desconhecido e a diferença não deve tornar-se zero. Depois introduz duas comissões iguais para uma conta e compara a soma antes e depois de juntar detalhes. Para relatórios diários UTC, usa limites que incluam o início e excluam o fim, evitando contar a meia-noite em dois dias. Fecha a investigação com uma pequena tabela de resultados previstos, observados e pressupostos, que outra pessoa consiga reproduzir.
WITH ledger(id, amount) AS (
VALUES (1,100), (2,NULL), (4,70)
), statement(id, amount) AS (
VALUES (1,90), (3,50), (4,70)
)
SELECT COALESCE(l.id,s.id) AS id,
CASE WHEN l.id IS NULL THEN 'missing_ledger'
WHEN s.id IS NULL THEN 'missing_statement'
ELSE 'both' END AS coverage,
CASE WHEN (l.id IS NOT NULL AND l.amount IS NULL)
OR (s.id IS NOT NULL AND s.amount IS NULL)
THEN 1 ELSE 0 END AS amount_unknown,
CASE WHEN l.id IS NOT NULL AND s.id IS NOT NULL
AND l.amount IS NOT NULL AND s.amount IS NOT NULL
THEN l.amount - s.amount ELSE NULL END AS difference
FROM ledger l FULL JOIN statement s ON s.id = l.id
ORDER BY COALESCE(l.id,s.id);A reconciliação separa uma diferença de 10, uma origem ausente com montante desconhecido, outra chave sem par e uma correspondência com diferença zero.
Armadilhas comuns
Confundir ausência com zero, somar valores repetidos por JOIN, usar média de médias sem pesos ou presumir que qualquer CTE melhora o plano.
Tópicos relacionados: Juntar sem multiplicar enganos · Contar e agrupar com cuidado · Índices e planos de execução
Prova primeiro a população, a identidade e a granularidade; só depois interpreta totais, percentagens e diferenças.
Referência: PostgreSQL 18: Table Expressions · PostgreSQL 18 reference semantics; DR SQL 2026.4; synthetic plan metrics and portable SQLite 3.51.2 examples