Почему пустая сумма — не нулевая продажа

Отчёт насчитал четыре заполненных поля из пяти. Проверим, сколько из них настоящие суммы, а сколько — пропуски или пустые строки.

SQL Studio / Разборы SQL / Почему пустая сумма — не нулевая продажа

В отчёте о продажах пять строк. Проверка COUNT(amount) показала четыре «заполненные суммы», хотя одна из этих четырёх записей — пустая строка, ещё одна состоит только из пробелов. Считать их нулевой выручкой нельзя. В SQLite NULL означает отсутствие значения, '' — известную строку длины ноль, а 0 — числовое значение ноль. У них разные правила сравнения и подсчёта.

Пять строк, но четыре типа состояния

Вместо сторонней базы зададим данные внутри запроса. quote() показывает литерал, а typeof() — тип значения в SQLite. Последняя строка специально содержит три пробела: визуально она тоже пустая, но это не ''.

WITH v(id, amount) AS (
  VALUES (1, NULL), (2, ''), (3, 0), (4, 120), (5, '   ')
)
SELECT id, quote(amount) AS stored, typeof(amount) AS kind
FROM v ORDER BY id;
Исходные данные: quote() позволяет различить пробелы и пустую строку
idstoredkind
1NULLnull
2''text
30integer
4120integer
5' 'text

Столбец намеренно содержит значения разных типов. В реальной числовой колонке следует не хранить пустую строку, а проверять качество импорта. Здесь смешение нужно только для демонстрации: один и тот же экранный «пропуск» может иметь несколько причин.

Правдоподобный неверный отчёт

Аналитик считает все не-NULL записи заполненными, а затем ищет настоящие нулевые продажи. Запрос выполняется, но заголовок «заполнено» вводит в заблуждение:

WITH v(id, amount) AS (
  VALUES (1, NULL), (2, ''), (3, 0), (4, 120), (5, '   ')
)
SELECT COUNT(*) AS rows_n, COUNT(amount) AS not_null_n,
       SUM(CASE WHEN amount = 0 THEN 1 ELSE 0 END) AS zero_n
FROM v;
Что показывает упрощённый счётчик
rows_nnot_null_nzero_n
541

COUNT(amount)=4 действительно исключил только NULL. Он не утверждает, что четыре суммы пригодны для денежного расчёта. amount = 0 нашёл ровно числовой ноль, а пустой текст в этом наборе не стал нулём.

Разделим пропуски, пустой текст и ноль

Для аудита качества введём отдельные числители. Условие TRIM считает пробелы пустым текстом по нашей бизнес-договорённости; без неё строки из пробелов должны быть отдельной категорией. Для данного смешанного учебного набора тип ограничен через typeof().

WITH v(id, amount) AS (
  VALUES (1, NULL), (2, ''), (3, 0), (4, 120), (5, '   ')
)
SELECT COUNT(*) AS rows_n,
       SUM(amount IS NULL) AS unknown_n,
       SUM(typeof(amount) = 'text' AND trim(amount) = '') AS blank_n,
       SUM(typeof(amount) = 'integer' AND amount = 0) AS zero_n,
       SUM(typeof(amount) = 'integer' AND amount > 0) AS known_positive_n
FROM v;
Контрольное разложение всех пяти строк
rows_nunknown_nblank_nzero_nknown_positive_n
51211

Проверка суммы категорий даёт 1 + 2 + 1 + 1 = 5. В настоящем отчёте такое равенство — простая проверка полноты классификации. Не переносите проверку typeof(amount)='integer' на данные с допустимыми вещественными суммами без уточнения модели.

Граница: почему = NULL не работает

SELECT NULL = NULL AS equal_null, NULL IS NULL AS is_null,
       '' IS NULL AS empty_is_null, 0 IS NULL AS zero_is_null,
       length('') AS empty_length, length(NULL) AS unknown_length;
Результат сравнений в SQLite
equal_nullis_nullempty_is_nullzero_is_nullempty_lengthunknown_length
NULL1000NULL

Выражение NULL = NULL даёт NULL (не «ложь» и не «истину»), поэтому в WHERE оно не отбирает строк. Проверка IS NULL возвращает 1; пустая строка и 0 ею не становятся. LENGTH('') — 0, LENGTH(NULL) — NULL.

Для поиска отсутствия связи по ключу, а не классификации поля, см. ловушку NOT IN с NULL. Если нужно различать строки и уникальных клиентов, полезнее COUNT и COUNT DISTINCT. Отдельная задача P066 — сессии клиентов, включая нули проверяет смысл нулевой группы после LEFT JOIN.

Первоисточники: SQLite: выражения, IS и NULL; COUNT.


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