SQL ABS: Absolute Value, Deltas, and Tolerance Checks

ABS возвращает модуль числа — его величину без знака. Звучит тривиально, но именно эта функция отвечает на вопрос «насколько далеко», когда направление не важно: расхождение между ожидаемой и фактической суммой, разница зарплат, отклонение от целевого значения.

Модуль и величина

ABS(x) отбрасывает знак: отрицательное становится положительным, ноль и положительное остаются как есть.

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

Тип результата совпадает с типом аргумента: для integer вернётся integer, для numeric — numeric, для double precision — float. Это удобно, когда вы не хотите неявных приведений и потери точности в денежных колонках.

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

Здесь amount для возвратов хранится со знаком минус, а отчёту нужна именно величина возврата, без направления денежного потока.

Дельта и расстояние между значениями

Самое частое применение — расстояние между двумя числами: ABS(a - b). Порядок аргументов перестаёт иметь значение, потому что модуль симметричен.

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

Та же идея для зарплат: насколько оклад сотрудника отклоняется от среднего по отделу, без учёта того, выше он или ниже.

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

ABS(a - b) — это одномерное евклидово расстояние. На нём строятся отчёты о расхождениях, сверки и поиск «ближайших» записей по числовому полю.

Проверка с допуском в WHERE

Сравнивать float на точное равенство опасно: накопленная ошибка округления почти всегда сделает a = b ложным. Правильный приём — проверять, что разница укладывается в небольшой допуск (epsilon).

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

Так же сверяют две суммы, которые в теории должны совпадать, а на практике расходятся на копейки из-за округлений.

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

• Ловушка: условие ABS(amount - 100) <= 0.01 не использует обычный индекс по amount, потому что колонка обёрнута в функцию. Для диапазона быстрее писать amount BETWEEN 99.99 AND 100.01 — это sargable-предикат, который ложится на B-tree индекс.
• На целых числах ABS не теряет точности, но помните про переполнение: ABS от минимального значения int4 выйдет за границу типа и вызовет ошибку.

Связка с SIGN

ABS отвечает на вопрос «насколько», а SIGN — «в какую сторону». Вместе они полностью описывают отклонение: модуль и направление.

-- 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 возвращает -1, 0 или 1, поэтому исходное значение всегда можно восстановить: value = SIGN(value) * ABS(value). Это удобно при сортировке сначала по величине отклонения, а потом по знаку.

Различия между СУБД минимальны, но есть нюансы:

• В PostgreSQL и MySQL имя одно — ABS. В ClickHouse функция называется abs (в нижнем регистре, как все функции), результат на знаковых типах остаётся знаковым.
• В ClickHouse abs от беззнакового целого вернёт то же беззнаковое значение — отрицательных там не бывает.
• ABS(NULL) всегда даёт NULL. Если допуск считается по колонке, где встречается NULL, оберните её в COALESCE, иначе строка молча выпадет из результата проверки.

Итог: ABS — это «расстояние без направления». Берите его для дельт и сверок, добавляйте SIGN, когда нужно направление, и переписывайте предикаты в BETWEEN, когда важна скорость и индекс.

Хорошая привычка: в отчётах показывайте рядом и модуль, и знак отклонения. Так вы не потеряете направление ошибки, но сможете сортировать и фильтровать по реальной величине расхождения.