`SIGN` в SQL: как понять направление изменения числа

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

Выручка выросла или упала?
Заказ больше предыдущего или меньше?
Платёж — это списание или возврат?
Отклонение от плана положительное или отрицательное?

Во всех этих вопросах нас интересует знак числа: плюс, минус или ноль.

Для этого в PostgreSQL есть функция SIGN. Она берёт число и возвращает его направление:

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

То есть SIGN превращает вопрос «какое число?» в вопрос «куда оно смотрит?».

Что делает SIGN

Функция принимает одно число:

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

Результат:

neg	zero	pos
-1	0	1

Логика простая:

Значение	Результат SIGN
меньше нуля	-1
равно нулю	0
больше нуля	1

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

SELECT
  id,
  amount,
  SIGN(amount) AS direction
FROM orders;

Результат может выглядеть так:

id	amount	direction
1	2500.00	1
2	-400.00	-1
3	0.00	0

Можно читать так:

• 1 — деньги пришли;
• -1 — деньги ушли или был возврат;
• 0 — движения нет.

SIGN не говорит, насколько сумма большая. Он отвечает только на вопрос: плюс, минус или ноль.

Зачем нужен SIGN, если есть CASE

Конечно, знак числа можно определить через CASE:

SELECT
  amount,
  CASE
    WHEN amount > 0 THEN 1
    WHEN amount < 0 THEN -1
    ELSE 0
  END AS direction
FROM orders;

Но это длинно. А SIGN делает то же самое короче:

SELECT
  amount,
  SIGN(amount) AS direction
FROM orders;

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

Определяем рост и падение метрики

Главная сила SIGN раскрывается, когда мы берём не само число, а разницу между двумя числами.

Например:

current_value - previous_value

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

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

Для предыдущего заказа используем оконную функцию LAG:

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

Что здесь происходит:

1. LAG(amount) берёт сумму предыдущего заказа пользователя.
2. amount - LAG(amount) считает разницу.
3. SIGN(...) превращает эту разницу в направление.

Результат:

id	user_id	amount	trend
10	1	100.00	NULL
11	1	150.00	1
12	1	120.00	-1
13	1	120.00	0

У первой строки trend равен NULL, потому что предыдущего заказа ещё нет. Это нормальное поведение: сравнивать пока не с чем.

Дальше всё читается легко:

• 1 — сумма выросла;
• -1 — сумма снизилась;
• 0 — сумма не изменилась.

Превращаем знак в понятные подписи

Числа -1, 0, 1 удобны для расчётов, но в отчёте человеку приятнее видеть слова.

Например, сравним сумму заказа с планом 100:

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

Результат:

id	amount	bucket
1	80.00	below target
2	100.00	on target
3	135.00	above target

Обратите внимание: CASE здесь получился коротким. Мы не пишем три условия вида amount > 100, amount < 100, amount = 100. Мы один раз считаем знак разницы, а потом просто переводим его в текст.

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

Есть две разные задачи:

1. Понять, в какую сторону отклонение.
2. Понять, насколько большое отклонение.

Для первой задачи нужен SIGN.
Для второй — ABS.

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

Например:

SELECT
  salary,
  SIGN(salary - 60000) AS side,
  ABS(salary - 60000) AS gap
FROM employees;

Допустим, ориентир зарплаты — 60000.

Результат:

salary	side	gap
50000	-1	10000
60000	0	0
75000	1	15000

Здесь:

• side показывает сторону отклонения;
• gap показывает размер отклонения.

Это удобное разделение. В одной колонке мы видим «выше или ниже», в другой — «на сколько».

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

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

Так наверху окажутся самые большие отклонения, а side сразу покажет, в какую сторону они ушли.

Денежный пример: платежи и возвраты

В финансовых данных положительные и отрицательные суммы часто означают разные операции.

Например:

• положительная сумма — платёж;
• отрицательная сумма — возврат;
• ноль — техническая или пустая операция.

Посмотрим направления:

SELECT
  id,
  amount,
  CASE SIGN(amount)
    WHEN 1 THEN 'charge'
    WHEN -1 THEN 'refund'
    WHEN 0 THEN 'zero'
  END AS operation_type
FROM payments;

Результат:

id	amount	operation_type
1	1200.00	charge
2	-300.00	refund
3	0.00	zero

А теперь посчитаем отдельно сумму платежей и сумму возвратов:

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

Почему для возвратов используется ABS(amount)?

Потому что возврат хранится отрицательным числом, например -300. Но в отчёте часто хочется показать сумму возвратов как положительную величину: 300, а не -300.

Здесь SIGN отвечает за направление, а ABS — за размер.

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

Любое число можно представить так:

x = SIGN(x) * ABS(x)

Проверим на примерах:

x	SIGN(x)	ABS(x)	SIGN(x) * ABS(x)
-15	-1	15	-15
0	0	0	0
20	1	20	20

Это полезная идея для аналитики.

Когда вы смотрите на число, внутри него как будто спрятаны две части:

• знак — направление;
• модуль — сила, размер, расстояние.

SIGN достаёт первую часть. ABS достаёт вторую.

Что происходит с NULL

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

SELECT SIGN(NULL);

Результат:

NULL

Это обычное поведение SQL: неизвестное значение на входе даёт неизвестный результат на выходе.

Но здесь есть ловушка.

Допустим, вы пишете:

SELECT
  id,
  CASE SIGN(amount)
    WHEN 1 THEN 'charge'
    WHEN -1 THEN 'refund'
    WHEN 0 THEN 'zero'
  END AS operation_type
FROM payments;

Если amount равен NULL, то SIGN(amount) тоже будет NULL. Он не совпадёт ни с 1, ни с -1, ни с 0. В результате operation_type тоже станет NULL.

Если нужно обработать такой случай явно, добавьте ELSE:

SELECT
  id,
  CASE SIGN(amount)
    WHEN 1 THEN 'charge'
    WHEN -1 THEN 'refund'
    WHEN 0 THEN 'zero'
    ELSE 'unknown'
  END AS operation_type
FROM payments;

Или проверьте NULL отдельно:

SELECT
  id,
  CASE
    WHEN amount IS NULL THEN 'unknown'
    WHEN SIGN(amount) = 1 THEN 'charge'
    WHEN SIGN(amount) = -1 THEN 'refund'
    WHEN SIGN(amount) = 0 THEN 'zero'
  END AS operation_type
FROM payments;

Так отчёт не оставит пустое значение там, где лучше честно показать unknown.

Осторожно с дробными вычислениями

С обычными целыми числами всё просто. Но с вычислениями на дробных типах иногда возникает эффект «почти ноль».

Например, в результате сложной формулы вы ожидали получить 0, но из-за особенностей вычислений получилось очень маленькое число:

0.000000000000000001

Для человека это почти ноль. Но для SIGN это положительное число, значит результат будет 1.

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

Например, через округление:

SELECT
  SIGN(ROUND(delta::numeric, 6)) AS direction
FROM metrics;

Или через порог:

SELECT
  CASE
    WHEN ABS(delta) < 0.000001 THEN 0
    ELSE SIGN(delta)
  END AS direction
FROM metrics;

Второй вариант часто понятнее в аналитике: вы прямо говорите, какое отклонение считаете слишком маленьким, чтобы обращать на него внимание.

Осторожно с целочисленным делением

Ещё одна ловушка — деление целых чисел.

В PostgreSQL выражение:

SELECT 3 / 4;

для целых чисел даст 0, потому что дробная часть отбрасывается.

Поэтому такой запрос вернёт ноль:

SELECT SIGN(3 / 4) AS wrong_result;

Результат:

wrong_result
0

Хотя математически 3 / 4 — это 0.75, а знак у 0.75 положительный.

Чтобы получить правильный результат, нужно сделать деление дробным:

SELECT SIGN(3.0 / 4) AS right_result;

Результат:

right_result
1

Или явно привести тип:

SELECT SIGN(3::numeric / 4) AS right_result;

Главное правило:

Сначала приведите числа к дробному типу, потом считайте знак.

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

SELECT SIGN((3 / 4)::numeric) AS still_wrong;

Здесь сначала посчитается 3 / 4, получится 0, и только потом этот ноль превратится в numeric.

SIGN в фильтрах и группировках

SIGN можно использовать не только в SELECT, но и в группировках.

Например, сгруппируем платежи по направлению:

SELECT
  SIGN(amount) AS direction,
  COUNT(*) AS payments_count,
  SUM(amount) AS total_amount
FROM payments
GROUP BY SIGN(amount)
ORDER BY direction;

Результат:

direction	payments_count	total_amount
-1	12	-3500.00
0	3	0.00
1	85	42000.00

Такой отчёт быстро показывает, сколько было возвратов, нулевых операций и платежей.

Можно сразу сделать человекочитаемую версию:

SELECT
  CASE SIGN(amount)
    WHEN -1 THEN 'refund'
    WHEN 0 THEN 'zero'
    WHEN 1 THEN 'charge'
  END AS direction,
  COUNT(*) AS payments_count,
  SUM(ABS(amount)) AS total_abs_amount
FROM payments
GROUP BY SIGN(amount)
ORDER BY MIN(SIGN(amount));

Здесь SUM(ABS(amount)) показывает величину оборота без минуса. Это удобно, если вы хотите видеть сумму возвратов положительным числом.

Пример с планом продаж

Допустим, есть таблица sales_plan:

manager_id	actual_amount	target_amount
1	120000	100000
2	95000	100000
3	100000	100000

Нужно понять, кто выше плана, кто ниже, а кто ровно в плане.

SELECT
  manager_id,
  actual_amount,
  target_amount,
  actual_amount - target_amount AS diff,
  SIGN(actual_amount - target_amount) AS plan_direction,
  CASE SIGN(actual_amount - target_amount)
    WHEN 1 THEN 'above plan'
    WHEN 0 THEN 'on plan'
    WHEN -1 THEN 'below plan'
  END AS plan_status
FROM sales_plan;

Результат:

manager_id	actual_amount	target_amount	diff	plan_direction	plan_status
1	120000	100000	20000	1	above plan
2	95000	100000	-5000	-1	below plan
3	100000	100000	0	0	on plan

Запрос читается естественно: сначала считаем разницу, потом берём её знак.

Пример с изменением цены

Представим таблицу price_history, где хранится история цен товаров:

product_id	price	changed_at
10	100	2024-01-01
10	120	2024-01-10
10	115	2024-01-20

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

SELECT
  product_id,
  price,
  changed_at,
  price - LAG(price) OVER (
    PARTITION BY product_id
    ORDER BY changed_at
  ) AS price_diff,
  SIGN(
    price - LAG(price) OVER (
      PARTITION BY product_id
      ORDER BY changed_at
    )
  ) AS price_direction
FROM price_history;

Результат:

product_id	price	price_diff	price_direction
10	100	NULL	NULL
10	120	20	1
10	115	-5	-1

Так можно быстро построить аналитику:

• где цена выросла;
• где снизилась;
• где осталась прежней.

Если хочется убрать повторение LAG, можно вынести расчёт в CTE:

WITH price_changes AS (
  SELECT
    product_id,
    price,
    changed_at,
    price - LAG(price) OVER (
      PARTITION BY product_id
      ORDER BY changed_at
    ) AS price_diff
  FROM price_history
)
SELECT
  product_id,
  price,
  changed_at,
  price_diff,
  SIGN(price_diff) AS price_direction
FROM price_changes;

Так запрос становится чище: сначала считаем разницу, потом отдельно работаем с её знаком.

Как это выглядит в других СУБД

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

SELECT SIGN(-10);

В MySQL функция тоже называется SIGN.

SELECT SIGN(-10);

В ClickHouse используется функция sign в нижнем регистре:

SELECT sign(-10);

Идея везде одинаковая: функция возвращает направление числа — отрицательное, нулевое или положительное.

Но при переносе запросов между СУБД всё равно стоит проверять детали типов, деления и поведения выражений. Особенно если знак считается не от простого числа, а от результата формулы.

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

Забыть про NULL

SIGN(NULL) возвращает NULL, а не 0.

SELECT SIGN(NULL);

Если NULL для вашей задачи должен считаться нулём, используйте coalesce:

SELECT SIGN(coalesce(amount, 0)) AS direction
FROM payments;

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

Считать почти ноль настоящим нулём

Для дробных расчётов маленькое значение вроде 0.000000001 всё равно положительное.

SELECT SIGN(0.000000001);

Результат:

sign
1

Если нужен «технический ноль», задайте порог:

SELECT
  CASE
    WHEN ABS(delta) < 0.000001 THEN 0
    ELSE SIGN(delta)
  END AS direction
FROM metrics;

Считать знак после целочисленного деления

Неправильно:

SELECT SIGN(3 / 4) AS direction;

Результат будет 0.

Правильно:

SELECT SIGN(3.0 / 4) AS direction;

Результат будет 1.

Использовать SIGN, когда нужна величина

SIGN не показывает размер изменения.

SELECT SIGN(1000000);

и:

SELECT SIGN(1);

оба вернут 1.

Если нужно понять, насколько большое значение, используйте само число или ABS.

Главное

SIGN — маленькая функция, которая отвечает на важный аналитический вопрос: в какую сторону направлено число.

Она возвращает:

• -1 для отрицательных значений;
• 0 для нуля;
• 1 для положительных значений;
• NULL, если на вход пришёл NULL.

Чаще всего SIGN используют не просто от числа, а от разницы:

SIGN(current_value - previous_value)

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

Запомните связку:

x = SIGN(x) * ABS(x)

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