Нет строки или нет продаж: проверяем матрицу товар–магазин в SQL

В отчёте магазина нет продаж товара B. Это может означать нулевой спрос, отсутствие товара в ассортименте или неполную выгрузку. Чтобы не свести разные ситуации к одному нулю, построим ожидаемую матрицу товар–магазин и сравним её с полученными данными в SQLite.

Откуда берётся ожидаемая комбинация

В примере два активных магазина и два обязательных товара. Значит, ожидаются четыре комбинации. CROSS JOIN строит их независимо от фактических продаж. В реальном ассортименте не каждый товар должен присутствовать во всех точках: вместо полного произведения нужен справочник разрешённых товар–магазин с периодом действия.

Источник отчёта в нашем договоре обязан передать строку для каждой ожидаемой комбинации, даже если продажи равны нулю. Поэтому отсутствие строки — нарушение полноты, а не нулевые продажи. Если исходный источник содержит только фактические чеки, такого договора нет: отсутствие операции можно трактовать нулём лишь после подтверждения полной выгрузки периода.

Одна строка delivered уже является итогом по магазину и товару за закрытый период. Перед соединением проверьте уникальность этого ключа. Для сырых чеков сначала понадобится агрегация, но она не создаст подтверждённые нули: их происхождение должно быть объяснено отдельно.

Сохраняем пропуск после LEFT JOIN

Присоединяем доставленные итоги к ожидаемой матрице. По отсутствию ключа правой таблицы определяем missing. Для присутствующей строки с qty=0 ставим zero, для положительной — sales. Не применяем COALESCE(qty,0) до классификации, иначе потеряем различие между пропуском и нулём.

У результата появляются три разных маршрута: missing возвращается владельцу данных, zero идёт в анализ спроса и наличия, sales участвует в обычном сравнении. Отрицательные количества потребуют отдельного правила возвратов; в примере источник передаёт неотрицательные продажи.

WITH shops(shop) AS (VALUES (1),(2)),
products(sku) AS (VALUES ('A'),('B')),
delivered(shop,sku,qty) AS (VALUES (1,'A',10),(1,'B',0),(2,'A',5)),
expected AS (SELECT shop,sku FROM shops CROSS JOIN products)
SELECT e.shop,e.sku,d.qty,
 CASE WHEN d.shop IS NULL THEN 'missing'
 WHEN d.qty=0 THEN 'zero' ELSE 'sales' END AS status
FROM expected e LEFT JOIN delivered d ON d.shop=e.shop AND d.sku=e.sku
ORDER BY e.shop,e.sku;

Ожидаемые строки результата в порядке столбцов SELECT:

[[1, "A", 10, "sales"], [1, "B", 0, "zero"], [2, "A", 5, "sales"], [2, "B", null, "missing"]]

Что удалось обнаружить

Магазин 1 подтвердил нулевые продажи B. Для магазина 2 строка B вообще не поступила. Сумма имеющихся продаж равна 15, но полнота матрицы — три из четырёх ожидаемых строк. Поэтому корректная сумма ещё не делает отчёт полным. В шапке регулярной выгрузки полезно показывать число ожидаемых и полученных комбинаций.

По missing проверьте экспорт магазина 2, фильтр по товару B и дату действия ассортиментной матрицы. Если B действительно перестал быть обязательным, исправляется справочник ожиданий. Если пропала строка экспорта, исправляется поставка данных. Заполнять её нулём без выяснения причины означает скрыть проблему.

Как расширить проверку

Дополнительно найдите неожиданные комбинации: строки delivered, которых нет в expected. Это могут быть продажи снятого с ассортимента товара или неверный код магазина. Для такой проверки используйте NOT EXISTS в обратную сторону. Полнота состоит не только из отсутствующих ожидаемых, но и из лишних неожиданных данных.

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

Сверка по ключам дополняет контроль общей суммы: Как сверить два SQL-отчёта: одинаковые суммы могут скрывать ошибки.

Следующий уровень SQL-практики

В «Продвинутом SQL» вы учитесь работать с несколькими источниками, пропусками и проверками качества данных. Запросы проходят автоматическую проверку на разных наборах в SQLite. Затем вы применяете приёмы в многошаговых кейсах и итоговой аналитической витрине магазина. Откройте программу «Продвинутого SQL».