Как разделить SQL-запрос на проверяемые шаги с помощью CTE

Когда запрос одновременно соединяет таблицы, считает суммы, вычитает возвраты и сразу фильтрует клиентов, ошибка прячется не в синтаксисе, а в логике. Вы видите неправильный итог, но не понимаете, на каком шаге он появился. CTE помогает разложить такой запрос на именованные этапы: сначала подготовить строки заказа, потом посчитать итог по клиенту, потом выбрать нужную группу. Полезный эффект здесь не в «магии оптимизации», а в том, что каждый этап можно отдельно прочитать и проверить как обычный SELECT.

Ниже разберем один узкий вопрос: как посчитать клиентов по заказам с возвратами так, чтобы промежуточный результат был проверяемым. Пример написан для PostgreSQL, но сама логика пригодится и в других SQL-диалектах с поддержкой CTE.

Сначала разделите не запрос, а вопрос к данным

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

Такой порядок важен из-за типичной ошибки: если просто соединить заказы с таблицей возвратов, где у одного заказа может быть несколько строк возврата, сумма заказа может задвоиться. Тогда проблема выглядит как «агрегация работает странно», хотя настоящая причина — слишком ранний JOIN. Разделение на CTE помогает увидеть этот момент отдельно.

Ещё один плюс — явные названия шагов. Вместо одного большого блока со смешанными вычислениями у вас появляются этапы вроде return_totals, prepared_orders, client_totals. Такие имена полезны не только читателю, но и вам самому, когда нужно вернуться к запросу через неделю и быстро понять, где считается что именно.

Учебный запрос с именованными шагами

Учебный пример. Среда выполнения: PostgreSQL. В примере у одного заказа есть два возврата отдельными строками. Это специально, чтобы показать, почему возвраты лучше сначала агрегировать по order_id, а не сразу суммировать после соединения.

WITH
orders AS (
    SELECT *
    FROM (VALUES
        (1, 'A', 120.00),
        (2, 'A', 80.00),
        (3, 'B', 200.00),
        (4, 'B', 90.00),
        (5, 'C', 150.00),
        (6, 'D', 60.00)
    ) AS t(order_id, client_id, order_amount)
),
returns AS (
    SELECT *
    FROM (VALUES
        (101, 2, 80.00),
        (102, 3, 20.00),
        (103, 3, 30.00),
        (104, 5, 150.00)
    ) AS t(return_id, order_id, return_amount)
),
return_totals AS (
    SELECT
        order_id,
        SUM(return_amount) AS returned_amount
    FROM returns
    GROUP BY order_id
),
prepared_orders AS (
    SELECT
        o.order_id,
        o.client_id,
        o.order_amount,
        COALESCE(r.returned_amount, 0.00) AS returned_amount,
        o.order_amount - COALESCE(r.returned_amount, 0.00) AS net_order_amount
    FROM orders o
    LEFT JOIN return_totals r ON r.order_id = o.order_id
),
client_totals AS (
    SELECT
        client_id,
        COUNT(*) AS orders_count,
        SUM(order_amount) AS gross_amount,
        SUM(returned_amount) AS returned_amount,
        SUM(net_order_amount) AS net_amount
    FROM prepared_orders
    GROUP BY client_id
),
target_clients AS (
    SELECT
        client_id,
        orders_count,
        gross_amount,
        returned_amount,
        net_amount
    FROM client_totals
    WHERE net_amount >= 100.00
)
SELECT *
FROM target_clients
ORDER BY net_amount DESC, client_id;

Здесь у каждого CTE одна задача. return_totals превращает несколько строк возврата по одному заказу в одну строку. prepared_orders добавляет к заказу сумму возврата и чистую сумму заказа. client_totals считает итог по клиенту. target_clients делает последний смысловой шаг — оставляет только нужную группу.

Такое разделение дисциплинирует вычисления. Если итог неверный, вы ищете ошибку не во всем запросе сразу, а в одном конкретном слое. Это заметно быстрее, чем читать длинный запрос с вложенными подзапросами и пытаться мысленно удержать все агрегаты одновременно.

Как проверить промежуточные таблицы, а не верить итоговой цифре

Практический прием простой: вы оставляете тот же блок WITH, но меняете финальный SELECT. Например, сначала посмотрите, что получилось в return_totals. Для этого достаточно выполнить SELECT * FROM return_totals ORDER BY order_id;. Ожидаемый результат такой:

Если здесь уже не 50, а две строки по заказу 3, значит вы еще не свернули возвраты до нужного уровня. Дальше полезно проверить prepared_orders. На этом шаге должны появиться все заказы, включая те, у которых возвратов нет. Именно поэтому в запросе стоит LEFT JOIN и COALESCE.

После этого имеет смысл проверить client_totals. Здесь уже легко сверить арифметику вручную: у клиента A валовая сумма 200, возвраты 80, чистая сумма 120; у B — 290, 50 и 240; у C — 150, 150 и 0; у D — 60, 0 и 60. Значит финальный фильтр net_amount >= 100 должен вернуть только A и B. Это и есть проверяемый результат: не просто «запрос отработал», а «каждый слой дал ожидаемые строки».

Какие ошибки такой разбор помогает поймать

Первая частая ошибка — задвоение суммы заказа из-за нескольких строк возврата. Если бы мы сразу соединили orders и returns, заказ 3 клиента B появился бы дважды: один раз с возвратом 20 и второй раз с возвратом 30. Тогда сумма заказов по B стала бы 490 вместо 290. Когда возвраты вынесены в отдельный CTE и заранее собраны по order_id, эту проблему видно и исправить ее проще.

Вторая ошибка — пропажа заказов без возвратов. Если на шаге подготовки использовать INNER JOIN, заказы 1, 4 и 6 исчезнут совсем. Для финансового итога это уже другой смысл задачи. Проверка CTE на уровне строк сразу показывает, остались ли «обычные» заказы в наборе или нет.

Третья ошибка — неправильная работа с пустыми значениями. Без COALESCE выражение order_amount - returned_amount даст NULL для заказов без возвратов. Дальше это начнет искажать агрегаты или заставит вас ловить проблему уже на последнем шаге. Когда этап подготовки выделен отдельно, такие строки легко заметить по колонке net_order_amount.

Наконец, CTE хорошо помогает проверить граничные случаи. В учебных данных есть полный возврат, где чистая сумма заказа равна нулю. Если по вашей бизнес-логике такие клиенты должны остаться в промежуточной таблице, но отфильтроваться только в конце, текущая структура как раз это и показывает. Если в реальных данных бывают возвраты больше суммы заказа, такую ситуацию лучше проверять отдельным условием на этапе подготовки, а не замечать постфактум в итоговом отчете.

Почему читаемость не гарантирует ускорение

CTE полезен прежде всего как способ разложить сложный запрос на более простые части. В документации PostgreSQL WITH описан как механизм, который позволяет оформлять вспомогательные выражения для одного основного запроса, и это хорошо совпадает с учебным подходом из примера выше. Но из этого не следует автоматический выигрыш по скорости.

План выполнения зависит от того, как конкретная СУБД обрабатывает CTE. В PostgreSQL нерекурсивный CTE без побочных эффектов может быть встроен в основной запрос, если используется один раз. А если один и тот же CTE используется несколько раз, система может вычислить его отдельно и затем читать как промежуточный результат. Иногда это удобно, иногда — нет. Поэтому фраза «разбил на CTE и стало быстрее» без плана выполнения ничего не доказывает.

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

Если хотите отработать именно такие задачи на программе, во внутренней карточке каталога курса по SQL сохранено описание модуля про подзапросы, CTE и объединение таблиц, а на официальной странице курса эта тема тоже указана в программе. Для себя полезно проверить три вещи: есть ли отдельные задания на разбиение запроса по шагам, можно ли тренироваться самостоятельно на промежуточных выборках и кто проверяет, что итог совпал с ожидаемой арифметикой по каждому этапу.