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_id | reported_pct |
|---|---|
| 1 | 40 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
Проблема не в исполнении 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_id | orders_n | visits_n | conversion_pct |
|---|---|---|---|
| 1 | 4 | 10 | 40 |
| 2 | 0 | 0 | NULL |
| 3 | NULL | 5 | NULL |
| 4 | 2 | 0 | NULL |
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_id | billed_kopecks | paid_kopecks | assumed_due | known_due |
|---|---|---|---|---|
| 1 | 1500 | 1500 | 0 | 0 |
| 2 | 800 | NULL | 800 | NULL |
| 3 | 0 | 0 | 0 | 0 |
Задолженность 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;
| invoice_rows | known_payments | known_sum | displayed_zero |
|---|---|---|---|
| 2 | 0 | NULL | 0 |
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_integer_division | sqlite_real_division | explicit_missing |
|---|---|---|
| NULL | NULL | NULL |
Похожие, но иные задачи: когда поле отсутствует, пустое или равно нулю; выбор знаменателя конверсии; условная сумма и нули. В тренажёре P077 «Сумма без NULL» закрепляет COALESCE, а P080 «Безопасная средняя скидка» — использование NULLIF для пустого знаменателя. Это две разные части разобранного приёма.
Справка SQLite: COALESCE и NULLIF, операторы деления, SUM.
Если нужна последовательная практика на MySQL 8, а не на SQLite в браузере, есть отдельный курс SQL на Stepik. Его синтаксис и поведение функций следует проверять по документации MySQL 8.