COALESCE и NULLIF: когда 0% вводит в заблуждение

Один период дал 40%, ещё три — 0%. Два из этих нулей получились без знаменателя. Разберём формулы и настоящие результаты SQLite.

SQL Studio / Разборы SQL / COALESCE и NULLIF: когда 0% вводит в заблуждение

В отчёте конверсия четырёх периодов выглядит как 40%, 0%, 0%, 0%. Но в двух периодах не было посещений, а в третьем неизвестно число заказов. Нули выглядят убедительно, хотя аналитически неверны. NULLIF(знаменатель, 0) оставляет отношение неопределённым при нулевой базе, а COALESCE подставляет первое не-NULL значение. Не стоит автоматически превращать неопределённую долю в 0%.

Когда COALESCE маскирует проблему с знаменателем

Четыре периода задаются через VALUES. В первом четыре заказа на десять визитов; во втором 0/0; в третьем число заказов неизвестно; в четвёртом при нуле визитов указаны два заказа — это сигнал о несогласованных данных.

WITH periods(period_id, orders_n, visits_n) AS (
  VALUES (1, 4, 10), (2, 0, 0), (3, NULL, 5), (4, 2, 0)
)
SELECT period_id,
       COALESCE(ROUND(100.0 * orders_n / NULLIF(visits_n, 0), 1), 0) AS reported_pct
FROM periods ORDER BY period_id;
Формула с ошибочной подстановкой нуля
period_idreported_pct
140
20
30
40

Проблема не в исполнении SQL: запрос синтаксически корректен. COALESCE(..., 0) стёр различие между наблюдаемыми 0% и невозможностью вычислить показатель.

Безопасный знаменатель не придумывает конверсию

WITH periods(period_id, orders_n, visits_n) AS (
  VALUES (1, 4, 10), (2, 0, 0), (3, NULL, 5), (4, 2, 0)
)
SELECT period_id, orders_n, visits_n,
       ROUND(100.0 * orders_n / NULLIF(visits_n, 0), 1) AS conversion_pct
FROM periods ORDER BY period_id;
Правильная диагностика четырёх периодов
period_idorders_nvisits_nconversion_pct
141040
200NULL
3NULL5NULL
420NULL

NULLIF(visits_n, 0) возвращает NULL при равенстве аргументов, иначе исходные визиты. Деление на NULL тоже даёт NULL. Множитель 100.0 нужен, чтобы получить дробное значение при делении целых чисел SQLite. ROUND(...,1) округляет до одного десятичного знака.

Важно: в самом SQLite даже 1 / 0 возвращает NULL, а не выбрасывает исключение. Здесь NULLIF делает договорённость о знаменателе явной и понятной в код-ревью; нельзя обещать одинаковое поведение «голого» деления во всех СУБД.

COALESCE уместен только после решения о смысле пропуска

Вторая история — счета. Для счёта №2 известно выставление 800 копеек, но поле оплаты неизвестно. Отсутствие платёжного факта в этой колонке не всегда доказывает нулевой платёж.

WITH bills(invoice_id, billed_kopecks, paid_kopecks) AS (
  VALUES (1, 1500, 1500), (2, 800, NULL), (3, 0, 0)
)
SELECT invoice_id, billed_kopecks, paid_kopecks,
       billed_kopecks - COALESCE(paid_kopecks, 0) AS assumed_due,
       billed_kopecks - paid_kopecks AS known_due
FROM bills ORDER BY invoice_id;
Неизвестный платёж против предположения о нуле
invoice_idbilled_kopeckspaid_kopecksassumed_dueknown_due
11500150000
2800NULL800NULL
30000

Задолженность 800 у второго счёта — результат допущения «неизвестно = не оплачено». Если бизнес-гарантия говорит, что отсутствие записи означает отсутствие платежа, такая подстановка оправдана; если возможен неполный импорт, долг пока неизвестен.

Пограничный случай: все платежи неизвестны

WITH bills(paid_kopecks) AS (VALUES (NULL), (NULL))
SELECT COUNT(*) AS invoice_rows, COUNT(paid_kopecks) AS known_payments,
       SUM(paid_kopecks) AS known_sum,
       COALESCE(SUM(paid_kopecks), 0) AS displayed_zero
FROM bills;
Агрегация двух NULL-записей
invoice_rowsknown_paymentsknown_sumdisplayed_zero
20NULL0

SUM по двум NULL не выдаёт доказанного нуля: результат NULL. Даже когда COALESCE(SUM(...),0) удобен для интерфейса, полезно показывать рядом COUNT(paid_kopecks), чтобы не скрывать качество исходных данных.

SELECT 1 / 0 AS sqlite_integer_division,
       1.0 / 0 AS sqlite_real_division,
       1.0 / NULLIF(0, 0) AS explicit_missing;
Особенность деления на ноль именно в SQLite
sqlite_integer_divisionsqlite_real_divisionexplicit_missing
NULLNULLNULL

Похожие, но иные задачи: когда поле отсутствует, пустое или равно нулю; выбор знаменателя конверсии; условная сумма и нули. В тренажёре P077 «Сумма без NULL» закрепляет COALESCE, а P080 «Безопасная средняя скидка» — использование NULLIF для пустого знаменателя. Это две разные части разобранного приёма.

Справка SQLite: COALESCE и NULLIF, операторы деления, SUM.


Если нужна последовательная практика на MySQL 8, а не на SQLite в браузере, есть отдельный курс SQL на Stepik. Его синтаксис и поведение функций следует проверять по документации MySQL 8.