SQL Studio / Разборы SQL / CTE не исправил отчёт. Как найти ошибку по шагам?
Число заказов в отчёте увеличилось с четырёх до пяти, но сумма осталась 22 000 копеек. Ошибка не в SUM: после отбора доставок один заказ с NULL подменили нулём и засчитали как заказ с известной стоимостью. Разложим расчёт на три обычных CTE: отбор доставок → проверка стоимости → итог по клиенту. Так можно увидеть, на каком шаге изменилась единица подсчёта.
Что именно считаем
Метрика здесь не «все доставленные заказы», а доставленные заказы с известной суммой и их общая стоимость в копейках. 0 — известная стоимость, NULL — сумма неизвестна. Считаем также число клиентов, у которых нашёлся хотя бы один такой заказ. Это учебный пример проверки расчёта, не определение бухгалтерской выручки или поступления денег.
| order_id | client_id | status | amount_kopecks |
|---|---|---|---|
| 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 |
Ошибка в 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_n | total_kopecks | customers_n |
|---|---|---|
| 5 | 22000 | 4 |
Результат выглядит правдоподобно: сумма 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_n | total_kopecks | customers_n |
|---|---|---|
| 4 | 22000 | 3 |
Диагностика: проверяем число строк на каждом шаге
Не нужно отлаживать все этапы глазами одновременно. На время проверки замените только финальную выборку: те же три 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_rows | priced_rows | client_groups |
|---|---|---|
| 5 | 4 | 3 |
Пять доставленных заказов превращаются в четыре строки с известными суммами, а затем — в три клиентские группы. Если после изменения фильтра вдруг станет пять строк на этапе 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_id | orders_n | total_kopecks |
|---|---|---|
| 1 | 2 | 15000 |
| 2 | 1 | 7000 |
| 4 | 1 | 0 |
Граница: 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_n | total_kopecks | customers_n |
|---|---|---|
| 1 | 0 | 1 |
Короткое упражнение
Добавьте ещё одну доставку с amount_kopecks=NULL и заранее предскажите три контрольных числа. Затем измените условие так, чтобы отчёт считал все доставки, включая неизвестные суммы: какие числители и знаменатели потребуется переименовать? Для отдельной практики структуры WITH откройте P091: помесячные заказы через CTE.
Документация: SQLite — WITH, обычные CTE и materialization hints. Связанный денежный пример — оплаты минус возвраты, где важно не размножить денежные операции.
Все примеры выше рассчитаны на SQLite в бесплатном SQL Studio. Если нужен отдельный структурированный курс на MySQL 8, есть курс «SQL для аналитика» на Stepik; синтаксис и некоторые функции могут отличаться.