Доля NULL в SQL: как измерить пропуски и оценить их риск

Телефоны пропущены у четырёх лидов из шести, но отчёт показывает 100%. Найдём ошибку в AVG и проверим, что означает эта доля для продаж.

SQL Studio / Разборы SQL / Качество заполнения

В отчёте по лидам аналитик получил «100% телефонов отсутствует». Проверка исходных строк показала четыре пустых телефона из шести, то есть 66,7%. Ошибка оказалась не в данных: выражение AVG(CASE WHEN phone IS NULL THEN 1 END) игнорировало все заполненные телефоны. Для доли NULL используйте COUNT(*) - COUNT(phone) в числителе или AVG(CASE WHEN phone IS NULL THEN 1.0 ELSE 0.0 END), а знаменателем оставляйте все строки выбранной аудитории.

Данные перед агрегацией: шесть лидов, три поля

В приложении для заявок по каждому lead_id пришли email, phone, utm_source и status. Данные ниже вымышленные; каждый SQL-блок содержит собственный WITH ... VALUES, поэтому запускается отдельно в бесплатной SQLite-песочнице. NULL означает отсутствие значения, не пустую строку и не ноль.

Фрагмент источника перед проверкой
lead_idemailphoneutm_sourcestatus
1a@xNULLadsnew
2NULL+7001NULLqualified
3b@xNULLNULLnew
4NULLNULLorganicwon
5c@x+7002adswon
6NULLNULLNULLnew

Ловушка: AVG без ELSE 0 завышает пропуски

Здесь CASE возвращает 1 для четырёх пустых телефонов, а для остальных двух возвращает NULL. AVG в SQLite пропускает NULL, поэтому знаменатель становится 4, а не 6:

WITH leads(lead_id, email, phone, utm_source, status) AS (
  VALUES (1,'a@x',NULL,'ads','new'),
         (2,NULL,'+7001',NULL,'qualified'),
         (3,'b@x',NULL,NULL,'new'),
         (4,NULL,NULL,'organic','won'),
         (5,'c@x','+7002','ads','won'),
         (6,NULL,NULL,NULL,'new')
)
SELECT COUNT(*) AS rows_n,
       ROUND(100.0*AVG(CASE WHEN phone IS NULL THEN 1 END),1)
         AS wrong_null_pct
FROM leads;
Неправильный профиль
rows_nwrong_null_pct
6100,0

Если использовать AVG(CASE WHEN phone IS NULL THEN 1.0 ELSE 0.0 END), знаменателем станут все шесть строк. В отчёте обязательно сохраняйте также счётчик пропусков: один процент без размера выборки вводит в заблуждение.

Правильный профиль: COUNT, доля и сортировка по риску

Для каждого поля считаем COUNT(*) и COUNT(column). Второе количество исключает NULL; разность равна числу пропусков. Из промежуточной одной строки profile строим вертикальный список полей через UNION ALL; сортировка по доле помогает увидеть проблемные места, но ещё не определяет важность поля.

WITH leads(lead_id, email, phone, utm_source, status) AS (
  VALUES (1,'a@x',NULL,'ads','new'),
         (2,NULL,'+7001',NULL,'qualified'),
         (3,'b@x',NULL,NULL,'new'),
         (4,NULL,NULL,'organic','won'),
         (5,'c@x','+7002','ads','won'),
         (6,NULL,NULL,NULL,'new')
),
profile AS (
  SELECT COUNT(*) AS rows_n,
         COUNT(*)-COUNT(email) AS email_null,
         COUNT(*)-COUNT(phone) AS phone_null,
         COUNT(*)-COUNT(utm_source) AS utm_null
  FROM leads
),
missing AS (
  SELECT 'email' AS field, rows_n, email_null AS missing_n FROM profile
  UNION ALL SELECT 'phone', rows_n, phone_null FROM profile
  UNION ALL SELECT 'utm_source', rows_n, utm_null FROM profile
)
SELECT field, rows_n, missing_n,
       ROUND(100.0*missing_n/NULLIF(rows_n,0),1) AS null_pct
FROM missing
ORDER BY null_pct DESC, field;
Ранжирование NULL по полям
fieldrows_nmissing_nnull_pct
phone6466,7
email6350,0
utm_source6350,0

Результат можно проверить вручную: phone пуст у 1, 3, 4 и 6; email — у 2, 4 и 6; utm_source — у 2, 3 и 6. Это профиль *заполненности*, а не автоматическое решение удалить 66,7% строк. Поля имеют разную бизнес-обязательность: например, у органического лида отсутствие UTM может быть ожидаемым, а отсутствие обоих каналов связи — уже препятствие для обработки заявки.

Проверка, которая полезнее общего рейтинга

Команда продаж может связаться с человеком, если указан хотя бы email или телефон. Поэтому для этой задачи важно пересечение пропусков, а не высокая доля NULL отдельного поля. Следующий запрос помечает только лиды, у которых отсутствуют оба контакта:

WITH leads(lead_id, email, phone, utm_source, status) AS (
  VALUES (1,'a@x',NULL,'ads','new'),
         (2,NULL,'+7001',NULL,'qualified'),
         (3,'b@x',NULL,NULL,'new'),
         (4,NULL,NULL,'organic','won'),
         (5,'c@x','+7002','ads','won'),
         (6,NULL,NULL,NULL,'new')
)
SELECT COUNT(*) AS rows_n,
       COALESCE(SUM(
         CASE WHEN email IS NULL AND phone IS NULL THEN 1 ELSE 0 END
       ),0) AS unreachable_n,
       ROUND(100.0*AVG(
         CASE WHEN email IS NULL AND phone IS NULL THEN 1.0 ELSE 0.0 END
       ),1) AS unreachable_pct
FROM leads;
Недоступные для связи лиды
rows_nunreachable_nunreachable_pct
6233,3

Это лиды 4 и 6. Лид 2 без email, но с телефоном, остаётся доступным. Отдельный бизнес-вопрос: должен ли вообще попадать в воронку лид со статусом won, если у него нет контактов? Можно начать расследование с конкретных идентификаторов, а не объявлять любую nullable-колонку дефектом.

Пустая таблица: ноль строк — не 0% пропусков

Если фильтр отчёта выбрал пустой сегмент, знаменатель равен нулю. COUNT(*) вернёт 0; SUM без COALESCE вернёт NULL; AVG тоже вернёт NULL. Поэтому нулевое количество пропусков можно выводить как 0, а долю оставлять неопределённой:

WITH leads(lead_id, email, phone, utm_source, status) AS (
  VALUES (1,'a@x',NULL,'ads','new'),
         (2,NULL,'+7001',NULL,'qualified'),
         (3,'b@x',NULL,NULL,'new'),
         (4,NULL,NULL,'organic','won'),
         (5,'c@x','+7002','ads','won'),
         (6,NULL,NULL,NULL,'new')
)
SELECT COUNT(*) AS rows_n,
       COALESCE(SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END),0)
         AS missing_n,
       ROUND(100.0*AVG(
         CASE WHEN phone IS NULL THEN 1.0 ELSE 0.0 END
       ),1) AS null_pct
FROM leads WHERE 1=0;
Контроль сегмента без строк
rows_nmissing_nnull_pct
00NULL

Не заменяйте этот NULL на «0%» автоматически: отсутствие наблюдений не является доказательством полной заполненности. Для отдельного отчёта о статусах считайте долю на каждой явно определённой популяции, не смешивая весь CRM-справочник со свежими входящими лидами.

Небольшое упражнение

Добавьте лид 7 с одним телефоном и пустыми email, utm_source. Как изменятся числитель, знаменатель и доля недоступных для связи? Сначала запишите дробь вручную, затем измените одну строку в VALUES и сравните результат. Почему отчёт «доля пустых телефонов» и отчёт «доля недоступных лидов» реагируют на эту строку по-разному?

По теме: COUNT и пропуски · условная агрегация CASE · какой знаменатель у конверсии.

Документация SQLite: COUNT, AVG, SUM и NULL. Примеры рассчитаны для SQLite WASM; курс Stepik использует MySQL 8. В обоих случаях правила NULL нужно проверять в контексте конкретной СУБД.

Начните с задачи P038 «Визиты без клиента», где нужно посчитать настоящий SQL NULL. Затем расширьте проверку до доли пропусков, как показано выше.


Если нужна последовательная программа упражнений на MySQL 8, есть отдельный курс на Stepik; для проверки всех примеров этой статьи достаточно бесплатного SQL Studio.