SQL CASE, COALESCE и NULL: как не сломать сегменты и метрики

Коротко

NULL означает отсутствие известного значения, а не пустую строку и не ноль. CASE создаёт новое значение по условиям, COALESCE выбирает первое непустое значение. Вместе они помогают сделать бизнес-правила явными и сохранить правильный знаменатель метрики.

• Для проверки NULL используй IS NULL или IS NOT NULL, а не = NULL.
• COALESCE(value, 0) превращает пропуск в ноль только там, где это действительно означает ноль.
• CASE полезен для сегментов, флагов и читаемых категорий.
• Не смешивай неизвестный канал и органический канал без отдельного решения.
• Проверяй, изменился ли знаменатель после фильтра по NULL.

NULL — не пустая строка

В таблице users у части пользователей может быть неизвестный channel. Это не то же самое, что channel = '', и не то же самое, что channel = 'organic'. NULL говорит: значение неизвестно или не было записано.

select
  count(*) filter (where channel is null) as missing_channel,
  count(*) filter (where channel = '') as empty_channel,
  count(*) as all_users
from users;

Почему channel = NULL не работает

SQL использует трёхзначную логику: условие может быть TRUE, FALSE или UNKNOWN. Сравнение любого значения с NULL не возвращает TRUE, поэтому where channel = null не найдёт пропуски. Нужна специальная проверка IS NULL.

select
  user_id,
  signup_date,
  channel
from users
where channel is null
order by signup_date, user_id;

CASE: создать сегмент из условия

CASE возвращает значение в зависимости от условий. Он подходит, когда в исходной таблице нет готовой бизнес-категории: можно разделить пользователей по каналу, возрасту когорты или активности. Важно задать ELSE, иначе все неописанные случаи станут NULL.

select
  user_id,
  channel,
  case
    when channel = 'paid' then 'paid'
    when channel in ('organic', 'referral') then 'owned'
    when channel is null then 'unknown'
    else 'other'
  end as channel_group
from users
order by user_id;

• Условия CASE проверяются сверху вниз.
• Первое совпавшее условие определяет результат.
• ELSE защищает от неожиданных NULL в новой колонке.
• Категории должны быть взаимоисключающими, если потом считаешь их сумму.

COALESCE: показать отсутствие как ноль

После LEFT JOIN у пользователя без платежа поле amount будет NULL. Для отчёта о выручке это обычно нужно показать как 0, но только после того, как ты сохранил пользователя в знаменателе. COALESCE выбирает первое значение, которое не является NULL.

select
  u.user_id,
  u.channel,
  coalesce(sum(p.amount), 0) as revenue
from users as u
left join payments as p using (user_id)
group by u.user_id, u.channel
order by revenue desc, u.user_id;

Как не потерять сегмент в GROUP BY

GROUP BY соберёт все NULL-значения в одну группу. Это полезно для контроля качества, но не всегда хорошо для бизнес-отчёта. Сначала покажи unknown отдельно, оцени размер группы и только потом решай, можно ли распределить её по другим категориям.

select
  coalesce(channel, 'unknown') as channel_group,
  count(*) as users_count
from users
group by coalesce(channel, 'unknown')
order by users_count desc;

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

Большинство проблем возникает не из-за синтаксиса, а из-за подмены смысла. Ноль, неизвестность и отсутствие строки могут выглядеть одинаково в таблице, но приводят к разным продуктовым выводам.

• Проверять NULL через = NULL.
• Использовать COALESCE для дат и создавать ложные значения.
• Забыть ELSE в CASE и получить новые пропуски.
• Отфильтровать NULL до расчёта и уменьшить знаменатель.
• Склеить unknown с organic без проверки качества канала.
• Считать сумму сегментов и не заметить, что часть строк ушла в NULL.

NULL в долях, средних и сравнениях

NULL не равен нулю и не равен пустой строке. В арифметике он обычно распространяется: сумма с NULL может стать NULL, а деление на NULL не даёт долю. count(column) пропускает NULL, count(*) считает строку. Поэтому запрос может выглядеть корректно, но использовать разные базы в числителе и знаменателе.

Перед coalesce задай смысл отсутствия. Для скидки NULL иногда означает «скидки не было», и ноль оправдан. Для revenue NULL означает «сумма неизвестна», и подстановка нуля занизит итог. Для channel лучше использовать unknown, чтобы группа не исчезла из отчёта.

В ratio-метриках используй nullif(denominator, 0). Это возвращает NULL для пустой базы и позволяет отчёту показать «нет наблюдений», а не бесконечность или искусственный ноль. Рядом с формулой подпиши, что именно исключено из знаменателя.

select
  channel,
  count(*) filter (where status = 'paid') as paid_orders,
  count(*) as all_orders,
  round(
    count(*) filter (where status = 'paid')::numeric
      / nullif(count(*), 0),
    4
  ) as paid_share
from orders
group by channel
order by channel;

CASE как контракт сегмента

CASE часто начинается как маленькое условие и превращается в определение бизнес-сегмента. Запиши порядок правил от специфичного к общему и добавь else unknown, если новые значения должны быть заметны. else low смешивает реальные низкие значения с невалидными строками.

Проверь границы на контрольных значениях: ровно 0, ровно 1000, NULL, отрицательное значение и новая категория. Если условия пересекаются, первый подходящий WHEN выигрывает. Это должно быть осознанным приоритетом, а не случайностью расположения строк.

Если один и тот же CASE используется в нескольких отчётах, вынеси его в view или справочник. Иначе продуктовая команда будет получать разные сегменты для одинакового пользователя из-за небольшой разницы в SQL.

select
  user_id,
  case
    when revenue is null then 'unknown'
    when revenue < 0 then 'invalid'
    when revenue >= 5000 then 'high'
    when revenue >= 1500 then 'medium'
    else 'low'
  end as revenue_segment
from user_revenue;

Мини-практика и следующий шаг

Найди пользователей с неизвестным channel, затем создай channel_group через CASE. После этого присоедини payments и выведи ноль для пользователей без платежей. В конце сравни сумму пользователей по группам с общим количеством строк.