100 000 строк и пропавшие клиенты: как NULL незаметно изменил результат SQL-запроса

Краткая аннотация

В исходной таблице было чуть больше 100 тысяч клиентов, но после обычного фильтра из расчёта исчезли несколько тысяч записей. Дубликатов не было, JOIN не использовался, запрос выполнялся без ошибок. Причиной оказался NULL в одном поле статуса и трёхзначная логика SQL. Разбираю ошибку на конкретном примере и показываю, почему она пережила первые проверки.

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

В исходной таблице было 100 438 строк. После фильтра осталось 93 614. Закрытых клиентов при этом было только 2 187.

Не хватало ещё 4 637 записей.

Сначала я решил, что где-то появился дополнительный фильтр. Потом проверил дубли, даты, идентификаторы и даже загрузку из CRM. Всё сходилось. Клиенты существовали в таблице, но один короткий SQL-фильтр просто переставал их видеть.

Запрос выглядел настолько обычным, что я долго не считал его подозрительным

Упрощённо условие выглядело так: SELECT customer_id, status FROM customers WHERE status <> 'closed';

Логика на человеческом языке очевидная: выбрать всех клиентов, статус которых не равен closed.

Я ожидал получить:

100 438 − 2 187 = 98 251 строк.

Но запрос возвращал 93 614.

Разница составляла ровно 4 637 строк.

Я сгруппировал данные по статусу и получил такую картину: active 71 842 new 12 604 paused 9 168 closed 2 187 NULL 4 637 —————————- total 100 438

Вот здесь причина стала видна.

Все 4 637 пропавших клиентов имели NULL в поле status.

Я много раз видел подобные ошибки, но именно эта была неприятна тем, что SQL не делал ничего странного. Он выполнял условие строго по своим правилам. Ошибочным было моё представление о том, что именно означает оператор <> при наличии неизвестного значения.

NULL — это не пустая строка и не ещё одно значение

Если поле содержит active, выражение status <> 'closed'

даёт TRUE.

Если содержит closed, результат — FALSE.

С NULL всё иначе.

SQL не интерпретирует NULL как текст NULL, ноль, пустую строку или отдельную категорию. В данном случае это отсутствие известного значения.

Поэтому вопрос NULL <> 'closed'

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

Нельзя.

Результатом становится не TRUE и не FALSE, а UNKNOWN.

А оператор WHERE оставляет только строки, для которых условие стало TRUE.

Получается: active <> closed → TRUE paused <> closed → TRUE closed <> closed → FALSE NULL <> closed → UNKNOWN

FALSE удаляется.

UNKNOWN тоже удаляется.

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

Именно поэтому запрос был синтаксически правильным и одновременно давал неправильную для моей задачи выборку.

На 100 тысячах строк ошибка уже перестала быть косметической

4 637 клиентов — примерно 4,6% исходной таблицы.

Если бы я считал только количество пользователей, расхождение довольно быстро бросилось бы в глаза. Но настоящий отчёт был сложнее.

Клиенты группировались по каналам привлечения, регионам и активности. NULL распределялся между сегментами неравномерно.

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

Получалось, что ошибка не просто уменьшала итоговое количество на 4,6%. Она меняла структуру данных.

В одном условном канале было: 12 400 клиентов всего 1 860 клиентов со status IS NULL

После фильтра status <> ’closed’ эти 1 860 записей исчезали.

В другом канале NULL было всего около 1%.

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

Я посчитал долю исключённых NULL отдельно по каждому источнику.

Именно после этого стало понятно, почему агрегированный показатель казался более-менее правдоподобным, а отдельные сегменты выглядели странно.

Проверки, которые помогли быстро локализовать расхождение

• сравнил COUNT(*) до и после фильтра;
• отдельно посчитал количество строк каждого статуса;
• вынес status IS NULL в отдельный запрос;
• проверил долю NULL не только по всей таблице, но и внутри сегментов;
• сравнил ID клиентов до фильтра и после него;
• убедился, что потерянные ID полностью совпадают с группой NULL.

После такой проверки никакой мистики уже не осталось.

Разница между ожидаемым и фактическим результатом была не случайной: 4 637 потерянных строк полностью объяснялись одним состоянием поля.

Простое исправление оказалось не таким простым

Первое желание — заменить условие на: WHERE status <> 'closed' OR status IS NULL;

Для конкретной бизнес-логики это действительно было правильным вариантом.

Если неизвестный статус не означает закрытого клиента, такой клиент должен оставаться в выборке.

После исправления запрос вернул ожидаемые 98 251 запись.

Проверка сошлась: 100 438 всего − 2 187 closed = 98 251 клиентов

Но я не стал на этом останавливаться.

Можно было написать и так: WHERE COALESCE(status, 'unknown') <> 'closed';

Результат для этого набора данных получился бы тем же.

Однако COALESCE меняет семантику: мы искусственно превращаем отсутствие значения в конкретное значение unknown. Иногда это удобно, иногда — маскирует проблему качества данных.

Если NULL появился потому, что статус действительно ещё не определён, вариант допустим.

Если же статус обязан существовать для каждого клиента, превращать NULL в unknown только ради красивого запроса опасно. Ошибка загрузки останется, просто перестанет бросаться в глаза.

Поэтому я разделил два вопроса.

Первый: как правильно посчитать отчёт сейчас?

Второй: почему вообще 4 637 клиентов не имеют статуса?

И это оказались две разные задачи.

Источник NULL нашёлся не там, где я ожидал

Я сначала предположил, что пустые статусы приходят напрямую из CRM.

Оказалось, нет.

В исходной системе у большинства этих клиентов значение существовало.

NULL появлялся позже.

В аналитической модели статус подтягивался через справочник: SELECT c.customer_id, s.status_name AS status FROM customer_base c LEFT JOIN status_dictionary s ON c.status_code = s.status_code;

Сам LEFT JOIN был правильным.

Проблема находилась в справочнике.

Часть старых клиентов имела коды статусов, которых уже не было в текущей версии status_dictionary.

Например, в исторических данных встречался код 17, а действующий справочник содержал только новые значения 1–12.

LEFT JOIN сохранял клиента, но соответствующая запись справа не находилась.

В итоге: customer_id = 58142 status_code = 17 status_name = NULL

То есть NULL был не исходным состоянием клиента. Он возник как результат несовпадения данных между основной таблицей и справочником.

Это сильно изменило смысл проблемы.

Если бы я просто добавил OR status IS NULL, отчёт стал бы математически правильнее, но дефект модели данных остался бы незамеченным.

Я разделил настоящие NULL и NULL, которые создал JOIN

Для проверки я больше не смотрел только на итоговое поле status.

Я сохранил исходный код и признак совпадения со справочником.

Получился примерно такой диагностический запрос: SELECT COUNT(*) AS total_rows, SUM( CASE WHEN c.status_code IS NULL THEN 1 ELSE 0 END ) AS source_nulls, SUM( CASE WHEN c.status_code IS NOT NULL AND s.status_code IS NULL THEN 1 ELSE 0 END ) AS unmatched_codes FROM customer_base c LEFT JOIN status_dictionary s ON c.status_code = s.status_code;

После этого 4 637 записей разделились на две группы.

У 1 204 клиентов status_code действительно отсутствовал в исходной системе.

У 3 433 код существовал, но ему не находилось соответствия в справочнике.

Внешне обе группы выглядели одинаково: status = NULL

По происхождению это были совершенно разные ситуации.

Первая — неполные исходные данные.

Вторая — нарушение ссылочной целостности между таблицами.

И исправлять их одинаково было бы неправильно.

Особенно опасным NULL становится внутри NOT IN

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

И нашёл вариант гораздо неприятнее первоначального: SELECT customer_id FROM customers WHERE status_code NOT IN ( SELECT status_code FROM excluded_statuses );

Сам по себе запрос выглядит нормально.

Допустим, в excluded_statuses лежат: 8 9 NULL

Тогда SQL должен определить, что некоторый status_code = 5 не входит в этот набор.

Но логика фактически становится похожей на: 5 <> 8 AND 5 <> 9 AND 5 <> NULL

Первые две проверки дают TRUE.

Последняя — UNKNOWN.

TRUE AND TRUE AND UNKNOWN превращается в UNKNOWN.

Строка не проходит WHERE.

В зависимости от запроса один NULL внутри подзапроса способен привести к тому, что NOT IN вернёт совсем не тот результат, который ожидается.

После этого случая я стал относиться к NOT IN заметно осторожнее.

Что я теперь отдельно проверяю при работе с NULL

• могут ли NULL появиться в исходном столбце;
• может ли NULL быть создан после LEFT JOIN;
• есть ли NULL внутри подзапросов для NOT IN;
• что бизнес-смысл считает неизвестным значением;
• должен NULL попадать в расчёт или исключаться;
• есть ли у поля ограничение NOT NULL, если отсутствие значения невозможно по модели;
• меняется ли доля NULL между сегментами или периодами;
• не используется ли COALESCE только для того, чтобы скрыть дефект данных.

Здесь для меня важен именно последний пункт.

COALESCE — полезная функция. Но она очень легко превращается в способ сделать плохие данные внешне аккуратными.

COUNT тоже может дать ложное ощущение, что всё в порядке

В том же отчёте я проверял количество записей и сначала получил ещё одну неочевидную разницу. SELECT COUNT(*) FROM customers;

возвращал 100 438.

А: SELECT COUNT(status) FROM customers;

возвращал 95 801.

Разница — те самые 4 637 NULL.

Причина проста: COUNT(*) считает строки, а COUNT(column) считает только ненулевые значения конкретного столбца.

На небольшой таблице это очевидно.

В большом отчёте выражение легко спрятать внутри нескольких CTE, JOIN и группировок. В итоге один аналитик считает клиентов через COUNT(*), другой — через COUNT(status), третий — через COUNT(DISTINCT customer_id).

Все три результата могут выглядеть разумно.

Но отвечать они будут на разные вопросы.

После этой истории я стараюсь не использовать слово количество без уточнения того, что именно считается.

Строки?

Ненулевые статусы?

Уникальные клиенты?

Клиенты после фильтра?

Разница в одном выражении способна изменить итог на несколько процентов.

После исправления я повторил расчёт целиком, а не только проблемное место

Это оказалось важным.

Я мог просто получить правильное количество — 98 251 — и закрыть задачу.

Вместо этого заново прогнал весь отчёт по сегментам.

До исправления один из каналов содержал 10 112 клиентов.

После — 11 972.

Рост составил примерно 18%.

Другой сегмент изменился меньше чем на процент.

Это подтвердило, что исходная ошибка была неравномерной и действительно могла влиять на сравнение каналов.

Дальше я восстановил старые коды в историческом справочнике и отдельно обозначил клиентов с реально отсутствующим статусом.

После этого количество NULL снизилось с 4 637 до 1 204.

То есть большую часть проблемы удалось исправить не условием в отчёте, а на уровне модели данных.

Это для меня главный результат всего разбора.

Если необычный NULL возникает после JOIN, правильнее сначала понять его происхождение, а уже потом решать, как с ним обращаться в бизнес-логике.

Вывод

В этой истории не было сложного SQL.

Не было тяжёлой оптимизации, хитрого оконного выражения или ошибки в СУБД.

Было обычное условие: status <> 'closed'

И 4 637 клиентов, которые из-за него исчезли из расчёта.

Самая неприятная часть заключалась в том, что запрос не выглядел ошибочным. Он успешно выполнялся, возвращал десятки тысяч строк и давал вполне правдоподобные цифры.

Настоящая причина стала понятна только после того, как я перестал воспринимать NULL как ещё одно значение.

SQL работает с ним через отдельную логику: TRUE, FALSE и UNKNOWN. А WHERE оставляет только TRUE.

Но ещё полезнее оказалось пойти на один уровень глубже.

Часть NULL действительно существовала в исходных данных. Большинство появилось позже из-за несовпадения со справочником.

Если бы я ограничился исправлением фильтра, отчёт сошёлся бы, а дефект данных продолжил жить дальше.

Теперь при неожиданном сокращении выборки я проверяю не только фильтры и JOIN. Я отдельно считаю NULL до каждого преобразования и после него.

Сто тысяч строк для базы — небольшой объём.

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