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_id | phone | utm_source | status | |
|---|---|---|---|---|
| 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 |
Ловушка: 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_n | wrong_null_pct |
|---|---|
| 6 | 100,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;
| field | rows_n | missing_n | null_pct |
|---|---|---|---|
| phone | 6 | 4 | 66,7 |
| 6 | 3 | 50,0 | |
| utm_source | 6 | 3 | 50,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_n | unreachable_n | unreachable_pct |
|---|---|---|
| 6 | 2 | 33,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_n | missing_n | null_pct |
|---|---|---|
| 0 | 0 | NULL |
Не заменяйте этот NULL на «0%» автоматически: отсутствие наблюдений не является доказательством полной заполненности. Для отдельного отчёта о статусах считайте долю на каждой явно определённой популяции, не смешивая весь CRM-справочник со свежими входящими лидами.
Небольшое упражнение
Добавьте лид 7 с одним телефоном и пустыми email, utm_source. Как изменятся числитель, знаменатель и доля недоступных для связи? Сначала запишите дробь вручную, затем измените одну строку в VALUES и сравните результат. Почему отчёт «доля пустых телефонов» и отчёт «доля недоступных лидов» реагируют на эту строку по-разному?
Документация SQLite: COUNT, AVG, SUM и NULL. Примеры рассчитаны для SQLite WASM; курс Stepik использует MySQL 8. В обоих случаях правила NULL нужно проверять в контексте конкретной СУБД.
Если нужна последовательная программа упражнений на MySQL 8, есть отдельный курс на Stepik; для проверки всех примеров этой статьи достаточно бесплатного SQL Studio.