CTE в SQL: как найти ошибку отчёта по шагам

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

SQL Studio / Разборы SQL / CTE не исправил отчёт. Как найти ошибку по шагам?

Число заказов в отчёте увеличилось с четырёх до пяти, но сумма осталась 22 000 копеек. Ошибка не в SUM: после отбора доставок один заказ с NULL подменили нулём и засчитали как заказ с известной стоимостью. Разложим расчёт на три обычных CTE: отбор доставок → проверка стоимости → итог по клиенту. Так можно увидеть, на каком шаге изменилась единица подсчёта.

Что именно считаем

Метрика здесь не «все доставленные заказы», а доставленные заказы с известной суммой и их общая стоимость в копейках. 0 — известная стоимость, NULL — сумма неизвестна. Считаем также число клиентов, у которых нашёлся хотя бы один такой заказ. Это учебный пример проверки расчёта, не определение бухгалтерской выручки или поступления денег.

Исходные строки для отчёта
order_idclient_idstatusamount_kopecks
2011delivered10000
2021delivered5000
2032delivered7000
2042cancelled9000
2053deliveredNULL
2063processing2000
2074delivered0

Ошибка в CTE: отсутствующую сумму приняли за ноль

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

WITH orders(order_id, client_id, status, amount_kopecks) AS (
  VALUES (201, 1, 'delivered', 10000),
         (202, 1, 'delivered', 5000),
         (203, 2, 'delivered', 7000),
         (204, 2, 'cancelled', 9000),
         (205, 3, 'delivered', NULL),
         (206, 3, 'processing', 2000),
         (207, 4, 'delivered', 0)
),
delivered AS (
  SELECT order_id, client_id, amount_kopecks
  FROM orders WHERE status = 'delivered'
),
priced AS (
  SELECT order_id, client_id,
         COALESCE(amount_kopecks, 0) AS amount_kopecks
  FROM delivered
),
by_client AS (
  SELECT client_id, COUNT(*) AS orders_n,
         SUM(amount_kopecks) AS total_kopecks
  FROM priced GROUP BY client_id
)
SELECT COALESCE(SUM(orders_n), 0) AS orders_n,
       COALESCE(SUM(total_kopecks), 0) AS total_kopecks,
       COUNT(*) AS customers_n
FROM by_client;
Ошибочный итог: сумма совпала, количество — нет
orders_ntotal_kopeckscustomers_n
5220004

Результат выглядит правдоподобно: сумма 22 000 правильная, но число заказов завышено на один, а число клиентов — тоже на один. Причина видна только при проверке данных между этапами.

Исправление: три CTE с понятным контрактом

Этап delivered содержит все доставки, priced — только строки с известной суммой, by_client — одну итоговую строку на клиента. Финальный SELECT собирает общие показатели. Обратите внимание: ни один CTE не существует после завершения одного SQL-оператора.

WITH orders(order_id, client_id, status, amount_kopecks) AS (
  VALUES (201, 1, 'delivered', 10000),
         (202, 1, 'delivered', 5000),
         (203, 2, 'delivered', 7000),
         (204, 2, 'cancelled', 9000),
         (205, 3, 'delivered', NULL),
         (206, 3, 'processing', 2000),
         (207, 4, 'delivered', 0)
),
delivered AS (
  SELECT order_id, client_id, amount_kopecks
  FROM orders WHERE status = 'delivered'
),
priced AS (
  SELECT order_id, client_id, amount_kopecks
  FROM delivered WHERE amount_kopecks IS NOT NULL
),
by_client AS (
  SELECT client_id, COUNT(*) AS orders_n,
         SUM(amount_kopecks) AS total_kopecks
  FROM priced GROUP BY client_id
)
SELECT COALESCE(SUM(orders_n), 0) AS orders_n,
       COALESCE(SUM(total_kopecks), 0) AS total_kopecks,
       COUNT(*) AS customers_n
FROM by_client;
Исправленный итог
orders_ntotal_kopeckscustomers_n
4220003

Диагностика: проверяем число строк на каждом шаге

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

WITH orders(order_id, client_id, status, amount_kopecks) AS (
  VALUES (201, 1, 'delivered', 10000),
         (202, 1, 'delivered', 5000),
         (203, 2, 'delivered', 7000),
         (204, 2, 'cancelled', 9000),
         (205, 3, 'delivered', NULL),
         (206, 3, 'processing', 2000),
         (207, 4, 'delivered', 0)
),
delivered AS (
  SELECT order_id, client_id, amount_kopecks
  FROM orders WHERE status = 'delivered'
),
priced AS (
  SELECT order_id, client_id, amount_kopecks
  FROM delivered WHERE amount_kopecks IS NOT NULL
),
by_client AS (
  SELECT client_id, COUNT(*) AS orders_n,
         SUM(amount_kopecks) AS total_kopecks
  FROM priced GROUP BY client_id
)
SELECT (SELECT COUNT(*) FROM delivered) AS delivered_rows,
       (SELECT COUNT(*) FROM priced) AS priced_rows,
       (SELECT COUNT(*) FROM by_client) AS client_groups;
Контроль уровня детализации на этапах
delivered_rowspriced_rowsclient_groups
543

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

Проверка содержимого итогового CTE

Число групп мало — можно вывести их напрямую и проверить контрольные суммы. Нулевой заказ клиента 4 остаётся; клиент 3 с неизвестной стоимостью исключён только из этой конкретной метрики.

WITH orders(order_id, client_id, status, amount_kopecks) AS (
  VALUES (201, 1, 'delivered', 10000),
         (202, 1, 'delivered', 5000),
         (203, 2, 'delivered', 7000),
         (204, 2, 'cancelled', 9000),
         (205, 3, 'delivered', NULL),
         (206, 3, 'processing', 2000),
         (207, 4, 'delivered', 0)
),
delivered AS (
  SELECT order_id, client_id, amount_kopecks
  FROM orders WHERE status = 'delivered'
),
priced AS (
  SELECT order_id, client_id, amount_kopecks
  FROM delivered WHERE amount_kopecks IS NOT NULL
),
by_client AS (
  SELECT client_id, COUNT(*) AS orders_n,
         SUM(amount_kopecks) AS total_kopecks
  FROM priced GROUP BY client_id
)
SELECT client_id, orders_n, total_kopecks
FROM by_client ORDER BY client_id;
Три клиентские группы
client_idorders_ntotal_kopecks
1215000
217000
410

Граница: NULL и реальный нулевой заказ

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

WITH orders(order_id, client_id, status, amount_kopecks) AS (
  VALUES (1, 1, 'delivered', NULL),
         (2, 2, 'delivered', 0),
         (3, 3, 'cancelled', 1000)
),
delivered AS (
  SELECT order_id, client_id, amount_kopecks
  FROM orders WHERE status = 'delivered'
),
priced AS (
  SELECT order_id, client_id, amount_kopecks
  FROM delivered WHERE amount_kopecks IS NOT NULL
),
by_client AS (
  SELECT client_id, COUNT(*) AS orders_n,
         SUM(amount_kopecks) AS total_kopecks
  FROM priced GROUP BY client_id
)
SELECT COALESCE(SUM(orders_n), 0) AS orders_n,
       COALESCE(SUM(total_kopecks), 0) AS total_kopecks,
       COUNT(*) AS customers_n
FROM by_client;
Остался один заказ с достоверной нулевой суммой
orders_ntotal_kopeckscustomers_n
101

Короткое упражнение

Добавьте ещё одну доставку с amount_kopecks=NULL и заранее предскажите три контрольных числа. Затем измените условие так, чтобы отчёт считал все доставки, включая неизвестные суммы: какие числители и знаменатели потребуется переименовать? Для отдельной практики структуры WITH откройте P091: помесячные заказы через CTE.

Документация: SQLite — WITH, обычные CTE и materialization hints. Связанный денежный пример — оплаты минус возвраты, где важно не размножить денежные операции.

По соседству: независимые денежные итоги · зерно после JOIN · этапы перед оконной суммой.


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