ABS no SQL: valor absoluto, diferencas e checagens com tolerancia

ABS retorna o valor absoluto de um numero: a sua magnitude sem o sinal. Parece trivial, mas e a funcao que responde "quao distante" quando a direcao nao importa: a diferenca entre o total esperado e o real, uma diferenca salarial, um desvio em relacao a uma meta.

Magnitude e valor absoluto

ABS(x) descarta o sinal: um negativo vira positivo, enquanto zero e positivos ficam como estao.

SELECT ABS(-7),     -- 7
       ABS(7),      -- 7
       ABS(0);      -- 0

O tipo do resultado acompanha o tipo do argumento: um integer continua integer, um numeric continua numeric e um double precision continua float. Isso ajuda quando voce quer evitar conversoes implicitas e perda de precisao em colunas monetarias.

-- magnitude of a signed balance adjustment
SELECT id, amount, ABS(amount) AS magnitude
FROM orders
WHERE status = 'refund';

Aqui as linhas de estorno guardam amount como negativo, mas o relatorio quer o tamanho do estorno, nao a direcao do fluxo de caixa.

Diferenca e distancia entre valores

O uso mais comum e a distancia entre dois numeros: ABS(a - b). A ordem dos argumentos deixa de importar, porque o valor absoluto e simetrico.

-- how far each order amount sits from the user's average
SELECT o.id,
       o.amount,
       ABS(o.amount - avg_amount) AS delta
FROM orders o
JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
) a ON a.user_id = o.user_id;

A mesma ideia vale para salarios: a que distancia o pagamento de um funcionario esta da media do seu departamento, sem importar se e maior ou menor.

SELECT name, dept, salary,
       ABS(salary - AVG(salary) OVER (PARTITION BY dept)) AS gap
FROM employees
ORDER BY gap DESC;

ABS(a - b) e uma distancia euclidiana de uma dimensao. Ela sustenta relatorios de divergencias, conciliacoes e buscas do "registro mais proximo" sobre um campo numerico.

Checagens com tolerancia no WHERE

Comparar valores float por igualdade exata e arriscado: o erro de arredondamento acumulado quase sempre fara a = b ser falso. O certo e checar se a diferenca cabe dentro de uma tolerancia pequena (epsilon).

-- find orders whose amount is within 0.01 of a target
SELECT id, amount
FROM orders
WHERE ABS(amount - 100.00) <= 0.01;

O mesmo padrao concilia dois totais que em teoria deveriam coincidir, mas que na pratica divergem por um centavo por causa do arredondamento.

SELECT o.id
FROM orders o
JOIN ledger l ON l.order_id = o.id
WHERE ABS(o.amount - l.posted_amount) > 0.005;   -- flag real mismatches

• Pegadinha: o predicado ABS(amount - 100) <= 0.01 nao consegue usar um indice comum sobre amount, porque a coluna esta envolvida numa funcao. Para uma faixa, amount BETWEEN 99.99 AND 100.01 e mais rapido: e um predicado sargavel que se apoia num indice B-tree.
• Com inteiros ABS nao perde precisao, mas atencao ao overflow: ABS do valor minimo de int4 cai fora da faixa do tipo e levanta um erro.

Combinando com SIGN

ABS responde "quanto" e SIGN responde "para que lado". Juntos descrevem por completo um desvio: magnitude mais direcao.

-- magnitude and direction of deviation from a target salary
SELECT name,
       salary,
       ABS(salary - 50000)  AS gap,
       SIGN(salary - 50000) AS direction   -- -1 below, 0 equal, 1 above
FROM employees;

SIGN retorna -1, 0 ou 1, entao o valor original sempre pode ser reconstruido: value = SIGN(value) * ABS(value). Isso e conveniente quando voce ordena primeiro pelo tamanho do desvio e depois pelo sinal.

As diferencas entre engines sao pequenas, mas vale conhecer algumas:

• No PostgreSQL e no MySQL o nome e apenas ABS. No ClickHouse a funcao se escreve abs (em minusculas, como todas), e o resultado sobre tipos com sinal continua com sinal.
• No ClickHouse, abs de um inteiro sem sinal retorna o mesmo valor sem sinal: la nao ha negativos para inverter.
• ABS(NULL) sempre da NULL. Se a tolerancia for calculada sobre uma coluna que pode conter NULL, envolva-a em COALESCE, ou a linha some em silencio da checagem.

Resumindo: ABS e "distancia sem direcao". Use-o para diferencas e conciliacoes, acrescente SIGN quando precisar da direcao e reescreva os predicados como BETWEEN quando velocidade e indice importarem.