Что такое ABS в SQL: модуль числа и расстояние без знака

ABS возвращает модуль числа — то есть величину без знака.

Проще говоря, ABS отвечает на вопрос:

насколько большое значение, если не важно, плюс это или минус?

Например:

• ABS(-7) вернёт 7;
• ABS(7) тоже вернёт 7;
• ABS(0) вернёт 0.

В обычной жизни это похоже на расстояние. Если вы отошли от дома на 3 километра на север или на 3 километра на юг, направление разное, но расстояние одинаковое — 3 километра.

В SQL ABS нужен именно для таких задач: найти величину расхождения, отклонение от плана, разницу между ожидаемой и фактической суммой, расстояние от среднего значения или ошибку округления.

Что делает ABS

ABS(x) убирает знак числа.

Если число отрицательное, оно становится положительным. Если число уже положительное или равно нулю, оно не меняется.

SELECT
  ABS(-7) AS a,
  ABS(7) AS b,
  ABS(0) AS c;

Результат:

a	b	c
7	7	0

Разберём:

ABS(-7) возвращает 7, потому что модуль отрицательного числа — это его величина без минуса.

ABS(7) возвращает 7, потому что положительное число уже показывает свою величину.

ABS(0) возвращает 0, потому что у нуля нет ни плюса, ни минуса.

Зачем нужен модуль

Главная идея ABS — отделить величину от направления.

Например, есть денежные операции:

id	amount	status
1	1200	paid
2	-500	refund
3	-300	refund

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

SELECT
  id,
  amount,
  ABS(amount) AS refund_amount
FROM orders
WHERE status = 'refund'
ORDER BY id;

Результат:

id	amount	refund_amount
2	-500	500
3	-300	300

amount показывает направление денежного потока: минус означает возврат.

refund_amount показывает величину возврата: сколько именно вернули.

Это два разных смысла, и оба могут быть полезны.

ABS для разницы между двумя числами

Самый частый приём — считать расстояние между двумя значениями:

ABS(a - b)

Порядок чисел уже не важен.

SELECT
  ABS(100 - 80) AS a,
  ABS(80 - 100) AS b;

Результат:

a	b
20	20

Без ABS в одном случае получилось бы 20, а в другом -20.

С ABS мы говорим SQL:

мне не важно, больше первое число или второе; мне важно, насколько они отличаются.

Пример: отклонение заказа от среднего

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

Сначала посчитаем среднюю сумму заказов по пользователю, а потом сравним с каждым заказом.

SELECT
  o.id,
  o.user_id,
  o.amount,
  ABS(o.amount - a.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
ORDER BY delta DESC;

Результат может быть таким:

id	user_id	amount	delta
14	2	900	420
8	1	100	250
11	1	380	30

delta показывает, насколько заказ далеко от обычного поведения пользователя.

Если средний заказ пользователя равен 350, то заказ на 100 отличается на 250, а заказ на 600 тоже отличается на 250. Направление разное, но расстояние одинаковое.

Пример: отклонение зарплаты от среднего по отделу

ABS хорошо сочетается с оконными функциями.

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

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

Результат:

name	dept	salary	gap
Anna	QA	120000	35000
Max	QA	60000	25000
Ivan	Dev	180000	20000

Здесь gap — это не «зарплата выше или ниже средней», а именно расстояние от средней.

Если нужно понять направление, одного ABS уже недостаточно. Об этом чуть ниже.

ABS для поиска ближайшего значения

Иногда нужно найти запись, которая ближе всего к целевому значению.

Например, хотим найти заказы, которые ближе всего к сумме 1000.

SELECT
  id,
  amount,
  ABS(amount - 1000) AS distance
FROM orders
ORDER BY distance
LIMIT 5;

Результат может быть таким:

id	amount	distance
10	1000	0
21	995	5
7	1012	12
18	980	20
5	1030	30

Чем меньше distance, тем ближе заказ к целевой сумме.

Это очень удобный шаблон:

ORDER BY ABS(value - target)

Он помогает искать ближайшую цену, ближайший возраст, ближайший рейтинг, ближайшую координату на одной оси или ближайшее значение метрики.

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

В реальных данных часто нужно проверять не точное равенство, а близость.

Например, заказ должен быть на 100.00, но из-за округлений сумма может получиться 99.999 или 100.004. Особенно часто такое встречается с дробными числами и вычислениями.

Плохая идея — сравнивать такие значения строго:

SELECT
  id,
  amount
FROM orders
WHERE amount = 100.00;

Если в данных есть маленькая погрешность, строка не попадёт в результат.

Лучше проверять, что разница укладывается в допустимый предел.

SELECT
  id,
  amount
FROM orders
WHERE ABS(amount - 100.00) <= 0.01;

Такой запрос означает:

найди заказы, где сумма отличается от 100.00 не больше чем на 0.01.

То есть подойдут и 99.99, и 100.00, и 100.01.

Пример сверки двух сумм

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

Но на практике могут быть расхождения: округления, комиссии, ошибки интеграции.

SELECT
  o.id,
  o.amount,
  l.posted_amount,
  ABS(o.amount - l.posted_amount) AS diff
FROM orders o
JOIN ledger l ON l.order_id = o.id
WHERE ABS(o.amount - l.posted_amount) > 0.005
ORDER BY diff DESC;

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

Это полезный подход для сверок:

• сумма заказа против суммы оплаты;
• ожидаемый баланс против фактического;
• расчёт в приложении против расчёта в базе;
• плановая метрика против реальной.

ABS и индексы: важная ловушка

У ABS есть неочевидный минус: если обернуть колонку в функцию, обычный индекс по этой колонке может не использоваться так эффективно.

Например, условие выглядит красиво:

SELECT
  id,
  amount
FROM orders
WHERE ABS(amount - 100) <= 0.01;

Но для базы это уже не простой поиск по amount. Она должна вычислить выражение для строк.

Если у вас есть обычный B-tree индекс по amount, часто лучше переписать условие как диапазон:

SELECT
  id,
  amount
FROM orders
WHERE amount BETWEEN 99.99 AND 100.01;

Смысл тот же: найти значения около 100 с допуском 0.01.

Но для индекса это понятнее: колонка amount не спрятана внутри функции.

Такой предикат называют sargable: база может удобно использовать индекс для поиска диапазона.

ABS и NULL

Если передать в ABS значение NULL, результат тоже будет NULL.

SELECT
  ABS(NULL) AS value;

Результат:

value
NULL

Это нормальное поведение SQL: если значение неизвестно, то и модуль неизвестен.

В запросах это может быть незаметной ловушкой.

SELECT
  id,
  ABS(amount - expected_amount) AS diff
FROM payments;

Если amount или expected_amount равны NULL, то diff тоже будет NULL.

А в фильтре такие строки могут молча исчезнуть:

SELECT
  id,
  amount,
  expected_amount
FROM payments
WHERE ABS(amount - expected_amount) > 1;

Если разница равна NULL, условие не станет истинным. WHERE пропускает только строки, где условие равно true.

Если по бизнес-логике пустые значения нужно считать нулём, используйте COALESCE:

SELECT
  id,
  ABS(COALESCE(amount, 0) - COALESCE(expected_amount, 0)) AS diff
FROM payments;

Но заменять NULL на ноль нужно осторожно. Иногда пустое значение означает не «ноль», а «данных нет». Это разные вещи.

ABS и SIGN: величина и направление

ABS отвечает на вопрос:

насколько сильно значение отклонилось?

А SIGN отвечает на вопрос:

в какую сторону?

SIGN возвращает:

• -1, если число отрицательное;
• 0, если число равно нулю;
• 1, если число положительное.

Например, посмотрим отклонение зарплаты от целевого значения 50000.

SELECT
  name,
  salary,
  ABS(salary - 50000) AS gap,
  SIGN(salary - 50000) AS direction
FROM employees
ORDER BY gap DESC;

Результат:

name	salary	gap	direction
Anna	70000	20000	1
Max	35000	15000	-1
Ivan	50000	0	0

Как читать:

• у Anna зарплата выше цели на 20000;
• у Max зарплата ниже цели на 15000;
• у Ivan зарплата ровно равна цели.

ABS дал величину отклонения.

SIGN сохранил направление.

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

Можно ли восстановить знак

Для числа, которое не равно NULL, идея такая:

SIGN(value) * ABS(value)

вернёт исходное значение.

Проверим:

SELECT
  value,
  SIGN(value) AS direction,
  ABS(value) AS magnitude,
  SIGN(value) * ABS(value) AS restored_value
FROM (
  VALUES
    (-10),
    (0),
    (15)
) AS t(value);

Результат:

value	direction	magnitude	restored_value
-10	-1	10	-10
0	0	0	0
15	1	15	15

ABS хранит величину.

SIGN хранит направление.

Вместе они описывают число полностью.

Сортировка по величине отклонения

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

Например, есть плановая и фактическая сумма:

SELECT
  id,
  planned_amount,
  actual_amount,
  actual_amount - planned_amount AS signed_diff,
  ABS(actual_amount - planned_amount) AS abs_diff
FROM reports
ORDER BY abs_diff DESC;

Результат:

id	planned_amount	actual_amount	signed_diff	abs_diff
4	1000	700	-300	300
2	1000	1250	250	250
7	1000	900	-100	100

Если сортировать по обычной разнице, отрицательные и положительные отклонения будут вести себя по-разному.

А сортировка по ABS показывает самые большие расхождения первыми.

Это особенно удобно для аудита, сверок и поиска аномалий.

ABS и переполнение целых чисел

На обычных числах ABS ведёт себя предсказуемо. Но у целых типов есть границы.

Например, у типа integer в PostgreSQL есть минимальное значение. Его модуль не помещается обратно в тот же тип.

Условно говоря, если тип может хранить значения от -2147483648 до 2147483647, то модуль -2147483648 должен стать 2147483648. Но такого положительного значения в integer уже нет.

В PostgreSQL такой случай может привести к ошибке переполнения.

SELECT ABS(-2147483648::integer) AS value;

Для большинства бытовых запросов вы с этим не столкнётесь. Но если работаете с крайними значениями целых типов, лучше помнить: модуль минимального отрицательного числа может выйти за границу типа.

Тип результата

Обычно ABS возвращает значение того же числового типа, с которым работает.

Если передать целое число, результат будет целым.

Если передать numeric, результат останется numeric.

Если передать double precision, результат будет значением с плавающей точкой.

Это удобно для денежных расчётов: если сумма хранится в точном типе numeric, ABS не обязан превращать её в неточный тип.

SELECT
  id,
  amount,
  ABS(amount) AS magnitude
FROM transactions;

Если amount имеет тип numeric, то и magnitude будет точным числовым значением.

Различия между СУБД

В PostgreSQL и MySQL функция называется ABS.

SELECT ABS(-42) AS value;

Результат:

value
42

В ClickHouse обычно пишут функцию в нижнем регистре:

SELECT abs(-42) AS value;

Результат тот же:

value
42

Практический смысл во всех этих системах одинаковый: получить величину числа без знака.

Но с типами и крайними значениями лучше быть внимательным. Например, у знаковых целых типов возможны граничные случаи с минимальным отрицательным значением, а у беззнаковых типов отрицательных значений вообще быть не может.

Когда использовать ABS

ABS нужен, когда направление не важно, а важна величина.

Например:

• найти разницу между ожидаемой и фактической суммой;
• проверить, укладывается ли значение в допустимую погрешность;
• отсортировать записи по величине отклонения;
• найти ближайшее значение к целевому;
• показать размер возврата без минуса;
• сравнить зарплату со средней по отделу;
• найти большие расхождения в сверке.

Хороший мысленный тест:

мне важен знак или только размер расхождения?

Если важен только размер — берите ABS.

Если важен и размер, и направление — используйте ABS вместе с SIGN или показывайте рядом обычную разницу.

Частые ошибки с ABS

Первая ошибка — терять направление там, где оно важно.

SELECT
  ABS(actual_amount - planned_amount) AS diff
FROM reports;

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

Лучше вывести оба значения:

SELECT
  actual_amount - planned_amount AS signed_diff,
  ABS(actual_amount - planned_amount) AS abs_diff
FROM reports;

Вторая ошибка — использовать ABS в фильтре и забывать про индекс.

SELECT
  id,
  amount
FROM orders
WHERE ABS(amount - 100) <= 0.01;

Для производительности часто лучше диапазон:

SELECT
  id,
  amount
FROM orders
WHERE amount BETWEEN 99.99 AND 100.01;

Третья ошибка — забывать про NULL.

SELECT
  ABS(amount - expected_amount) AS diff
FROM payments;

Если одно из значений неизвестно, результат тоже будет неизвестен.

Четвёртая ошибка — считать ABS заменой обычной разницы. Это не замена, а другой смысл. Обычная разница говорит «куда и насколько», а ABS говорит только «насколько».

Главное из статьи

ABS возвращает модуль числа — величину без знака.

SELECT ABS(-7) AS value;

Результат:

value
7

Чаще всего ABS используют для расстояния между двумя числами:

SELECT ABS(actual_amount - expected_amount) AS diff
FROM payments;

Если нужно проверить значение с допуском, можно написать так:

SELECT
  id,
  amount
FROM orders
WHERE ABS(amount - 100.00) <= 0.01;

Но для индекса по колонке часто лучше диапазон:

SELECT
  id,
  amount
FROM orders
WHERE amount BETWEEN 99.99 AND 100.01;

ABS убирает знак, поэтому направление отклонения теряется. Если направление важно, показывайте рядом обычную разницу или используйте SIGN.

SELECT
  salary - 50000 AS signed_gap,
  ABS(salary - 50000) AS gap,
  SIGN(salary - 50000) AS direction
FROM employees;

Главная мысль: ABS — это расстояние без направления. Он помогает находить расхождения, допуски, ближайшие значения и величину ошибки, но знак нужно сохранять отдельно, если он важен для смысла отчёта.