SIGN in SQL: the Sign of a Number and Branching by Direction

Спросите аналитика, что важнее в метрике — насколько она изменилась или в какую сторону, и в девяти случаях из десяти услышите про сторону. Выручка качнулась вверх или вниз? Отклонение от плана положительное или отрицательное? Платёж — это списание или возврат? Во всех этих вопросах величина уходит на второй план, а на первый выходит направление. Именно здесь и пригождается SIGN: он отдаёт знак числа — -1 для отрицательных, 0 для нуля и 1 для положительных. Функция крошечная, почти незаметная в списке встроенных, но она переводит вопрос «насколько» в вопрос «куда» и схлопывает километры неуклюжих CASE в один короткий вызов.

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

Сигнатура проще некуда: на вход одно число, на выходе ровно одно из трёх значений — -1, 0 или 1.

SELECT SIGN(-42)   AS neg,   -- -1
       SIGN(0)     AS zero,  --  0
       SIGN(17.5)  AS pos;   --  1

Тип результата повторяет тип аргумента: подаёте numeric — получаете numeric, подаёте double precision — получаете double precision. Деталь мелкая, но в выражениях, где вы потом домножаете знак на что-то ещё, она избавляет от лишних приведений типа. А если на вход пришёл NULL, то и на выходе будет NULL: это не четвёртое скрытое состояние, а обычное распространение неопределённости, как и везде в SQL. Запомните это сразу — позже именно NULL подкинет самый неприятный сюрприз.

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

SELECT id,
       amount,
       SIGN(amount) AS direction  -- -1 refund, 0 zero, 1 charge
FROM orders;

Ветвление по направлению изменения

Но настоящий хлеб SIGN — это не одиночное число, а разница между двумя. Классифицировать зазор как «вверх», «вниз» или «без движения» — ровно его работа, и делает он её без единого условного оператора. Возьмём типичную задачу: для каждого заказа понять, больше он предыдущего заказа этого же клиента или меньше. Считать руками лесенку из CASE WHEN ... > ... THEN не хочется. Сравним сумму с предыдущим заказом того же пользователя через оконную функцию и получим направление тренда одной аккуратной колонкой:

SELECT id,
       user_id,
       amount,
       SIGN(amount - LAG(amount) OVER (
           PARTITION BY user_id ORDER BY created_at
       )) AS trend  -- -1 down, 0 flat, 1 up
FROM orders;

Три значения легко развернуть обратно в человеческие подписи через CASE — и заметьте, насколько он короче, чем три полноценных сравнения с границами. Только держите в голове: ветка WHEN 0 ловит точное равенство, а не «почти столько же». Для денег с фиксированной точностью это ровно то, что нужно; для дробных вычислений к этому нюансу мы ещё вернёмся.

SELECT id,
       CASE SIGN(amount - 100)
           WHEN -1 THEN 'below target'
           WHEN  0 THEN 'on target'
           WHEN  1 THEN 'above target'
       END AS bucket
FROM orders;

SIGN и ABS: направление отдельно, величина отдельно

Любое число раскладывается на множители как x = SIGN(x) * ABS(x). Знак и модуль — это два независимых ответа на два разных вопроса: «в какую сторону» и «насколько сильно». Это разделение — главный приём, ради которого стоит держать SIGN под рукой, потому что обычно эти два ответа нужны вам в разных колонках, а не слепленными в одну. Скажем, посчитать одним проходом, сколько денег ушло в возвраты и сколько пришло платежами, не разбивая выборку на два запроса:

SELECT SUM(CASE WHEN SIGN(amount) = 1 THEN amount ELSE 0 END) AS charged,
       SUM(CASE WHEN SIGN(amount) = -1 THEN ABS(amount) ELSE 0 END) AS refunded
FROM orders;

Та же связка выручает, когда сортируешь по близости к цели: упорядочиваем по ABS (как далеко ушли), а знак держим в отдельной колонке, чтобы было видно, в какую сторону зарплата отклоняется от ориентира отдела:

SELECT name,
       dept,
       salary,
       SIGN(salary - 60000) AS side,   -- which way off
       ABS(salary - 60000)  AS gap     -- how far off
FROM employees
ORDER BY gap DESC;

Прелесть в том, что «как далеко» и «в какую сторону» больше не дерутся за одну колонку — у каждого вопроса свой ответ. Сортировка по gap ставит наверх самые крупные отклонения, а side тут же подсказывает, кому недоплачивают, а кому переплачивают, — и всё это без вложенных условий и временных таблиц.

Численная устойчивость и подводные камни

Сам по себе SIGN надёжен как камень: ему нечего терять в точности и негде переполниться, ведь результат — всего три значения. Вся опасность прячется не в функции, а в том, что вы ей скармливаете. И ловушки эти не теоретические — на них спотыкаются ровно тогда, когда отчёт уже ушёл заказчику.

• Ноль с плавающей точкой. В цепочке вычислений ожидаемый 0.0 нередко приходит как мизерный 1e-18 — и тогда SIGN бодро вернёт 1 вместо 0, а ваша «нулевая» ветка тихо останется пустой. Если она важна, округляйте до того, как берёте знак: SIGN(ROUND(x::numeric, 6)).
• NULL проваливается мимо всех веток. В CASE SIGN(x) с NULL не совпадёт ни -1, ни 0, ни 1 — добавляйте явную проверку через IS NULL или замыкающий ELSE, иначе строка молча выпадет в NULL.
• Целочисленное деление. SIGN(a / b) для целых сначала делит нацело, и SIGN(3 / 4) — это SIGN(0) = 0, хотя по смыслу там явная единица. Приводите к дробному типу до деления, а не после.

Различия между движками невелики, но знать их полезно:

• PostgreSQL возвращает тот же числовой тип, что и аргумент.
• MySQL функция зовётся так же, SIGN(), но всегда отдаёт целое -1/0/1.
• ClickHouse предлагает sign() в нижнем регистре с тем же поведением -1/0/1.

-- Pitfall: integer division hides the real sign
SELECT SIGN(3 / 4)               AS wrong,  -- 0
       SIGN(3.0 / 4)             AS right_; -- 1

Запомните тождество x = SIGN(x) * ABS(x) — оно снимает половину вопросов про «вверх или вниз» без единого вложенного CASE. Держите его в голове рядом с правилом «сначала приведи тип, потом считай знак», следите за нулём с плавающей точкой и не забывайте про NULL — и SIGN из неприметной однострочной функции превратится в один из самых тихих и надёжных инструментов вашего набора для аналитики направлений.