Как уверенно писать SQL для многотабличных отчётов: CTE, агрегации и предотвращение “сломанной” кратности

Многотабличные отчёты — то место, где SQL внезапно перестаёт быть «я просто соединяю таблицы» и превращается в дисциплину: нужно понимать кардинальность, контролировать кратность строк после JOIN, правильно выбирать уровень агрегации и уметь быстро проверять себя.

Одна из самых частых причин неожиданных итогов в аналитике — размножение строк из‑за JOIN и последующая «неправильная» агрегация. Итоговые суммы, средние, количество заказов или активных пользователей внезапно оказываются завышенными/заниженными, хотя каждый JOIN вроде бы логичен.

В этой статье разберём типовые сценарии, покажем, как строить запросы с CTE, где именно размещать агрегации, как предотвращать «сломанные» кратности и как делать контрольные выборки, чтобы поймать ошибку ещё до того, как она попадёт в отчёт.

Почему ломается кратность: JOIN как операция над мультимножествами

В теории реляционных баз JOIN — это сопоставление строк по условию. На практике важно другое: если в одной из таблиц на ключ приходится несколько строк, то JOIN выдаст произведение кратностей.

Пример: один заказ — много позиций

Пусть у нас есть:

• orders — по 1 строке на заказ
• order_items — по несколько строк на заказ (позиции)
• payments — по несколько строк на заказ (оплаты/платежи)

Если мы сделаем JOIN orders → order_items → payments и начнём суммировать payments.amount, мы рискуем получить кратность:

• 1 заказ
• N позиций в order_items
• M платежей в payments

При таком JOIN каждая сумма платежа повторится N раз, потому что она «растягивается» на каждую позицию заказа.

Отсюда типовая формула ошибки:

1. вы подключили «размножающую» таблицу (многострочную по ключу),
2. выполнили JOIN,
3. суммировали поля после размножения.

Это и есть «сломанная кратность».

Принцип №1: агрегация должна происходить до размножающих JOIN

Самый надёжный подход в отчётах: прежде чем JOIN “умножит” строки, агрегируйте там, где нужно считать метрику.

Иными словами:

• если вам нужно total_items по заказу — агрегируйте order_items до JOIN с orders
• если вам нужно total_paid по заказу — агрегируйте payments до JOIN

Тогда при финальном JOIN вы соединяете таблицы, где ключ уникален (или по крайней мере у вас контролируемая кратность).

Ниже — базовая заготовка на CTE.

Паттерн “агрегируй сначала, соединяй потом”

WITH
items AS (
    SELECT
        oi.order_id,
        COUNT(*) AS items_count,
        SUM(oi.quantity * oi.unit_price) AS items_amount
    FROM order_items oi
    GROUP BY oi.order_id
),
paid AS (
    SELECT
        p.order_id,
        SUM(p.amount) AS paid_amount,
        MAX(p.paid_at) AS last_paid_at
    FROM payments p
    GROUP BY p.order_id
)
SELECT
    o.order_id,
    o.customer_id,
    items.items_count,
    items.items_amount,
    COALESCE(paid.paid_amount, 0) AS paid_amount
FROM orders o
LEFT JOIN items ON items.order_id = o.order_id
LEFT JOIN paid  ON paid.order_id = o.order_id;

Здесь ключ order_id становится “почти уникальным” внутри CTE: каждая агрегация выдаёт ровно одну строку на заказ. Финальный JOIN уже не ломает суммы.

Принцип №2: выберите уровень агрегации (и не смешивайте его)

Ошибки часто происходят не только из-за JOIN, но и из-за того, что агрегации выполняются на разных уровнях “по смыслу”.

Типичный анти-паттерн

Представьте, что вы хотите отчёт по клиентам: сколько заказов и на какую сумму они оплатили. Если вы делаете JOIN на уровне строк (items, payments, ...) и агрегируете уже в конце, уровень агрегации съезжает с “заказа” на “строку итоговой таблицы”.

Пример (ошибка: сумма платежей умножается на количество позиций):

SELECT
    o.customer_id,
    COUNT(DISTINCT o.order_id) AS orders_cnt,
    SUM(p.amount) AS paid_amount_wrong
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments p     ON p.order_id  = o.order_id
GROUP BY o.customer_id;

COUNT(DISTINCT o.order_id) местами спасает количество заказов, но не спасает сумму, потому что p.amount повторяется при умножении на oi.

Как правильно

Разнести агрегации по смысловым сущностям: “оплата по заказу” и “позиции по заказу”, затем агрегировать по клиенту уже на уровне заказов.

WITH
paid AS (
    SELECT order_id, SUM(amount) AS paid_amount
    FROM payments
    GROUP BY order_id
),
orders_base AS (
    SELECT
        o.order_id,
        o.customer_id
    FROM orders o
)
SELECT
    ob.customer_id,
    COUNT(*) AS orders_cnt,
    SUM(COALESCE(p.paid_amount, 0)) AS paid_amount
FROM orders_base ob
LEFT JOIN paid p ON p.order_id = ob.order_id
GROUP BY ob.customer_id;

Если вам нужны дополнительные метрики по позициям — добавляйте ещё один CTE уровня “по заказу”, а затем суммируйте по клиентам.

CTE как инструмент контроля: делайте запрос читаемым и проверяемым

CTE (WITH) — это не “сахар”, а способ:

1. разложить расчёт по шагам,
2. зафиксировать уровень агрегации,
3. упростить отладку,
4. повторно использовать промежуточные результаты (в разумных пределах).

Практика: имена CTE как контракты

Хорошая привычка — называть CTE так, чтобы было ясно, что в ней гарантируется.

• items_by_order — в ней одна строка на order_id
• paid_by_order — одна строка на order_id
• orders_in_period — множество строк заказов (без агрегации)

Тогда читатель понимает риск кратности заранее.

CASE STUDY: отчёт по заказам с несколькими “размножителями”

Рассмотрим задачу ближе к реальности:

Нужно вывести по каждому заказу:

• число позиций
• сумма позиций
• сумма оплат
• количество платежей
• последняя дата оплаты

Таблицы:

• orders (order_id, customer_id, created_at, status)
• order_items (order_id, product_id, quantity, unit_price)
• payments (payment_id, order_id, amount, paid_at)

Ошибочный подход

SELECT
    o.order_id,
    COUNT(oi.product_id) AS items_cnt,
    SUM(oi.quantity * oi.unit_price) AS items_amount,
    SUM(p.amount) AS paid_amount,
    COUNT(p.payment_id) AS payments_cnt,
    MAX(p.paid_at) AS last_paid_at
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY o.order_id;

Если для заказа есть N позиций и M платежей, то:

• SUM(oi.quantity * oi.unit_price) станет завышенной: каждая позиция повторится M раз
• SUM(p.amount) станет завышенной: каждый платёж повторится N раз
• COUNT будет отравлен кратностью

То есть ошибка в обе стороны.

Корректное решение

Делаем два CTE “по заказу” и соединяем их с orders.

WITH
items_by_order AS (
    SELECT
        oi.order_id,
        COUNT(*) AS items_cnt,
        SUM(oi.quantity * oi.unit_price) AS items_amount
    FROM order_items oi
    GROUP BY oi.order_id
),
payments_by_order AS (
    SELECT
        p.order_id,
        COUNT(*) AS payments_cnt,
        SUM(p.amount) AS paid_amount,
        MAX(p.paid_at) AS last_paid_at
    FROM payments p
    GROUP BY p.order_id
)
SELECT
    o.order_id,
    i.items_cnt,
    i.items_amount,
    COALESCE(p.paid_amount, 0) AS paid_amount,
    COALESCE(p.payments_cnt, 0) AS payments_cnt,
    p.last_paid_at
FROM orders o
LEFT JOIN items_by_order i ON i.order_id = o.order_id
LEFT JOIN payments_by_order p ON p.order_id = o.order_id;

Теперь уровень агрегации согласован: обе CTE дают одну строку на order_id, а финальный JOIN не меняет количество строк.

Проблема “LEFT JOIN + NULLы + фильтры в WHERE”: осторожно с логикой

Даже если вы правильно агрегировали, легко сломать результат фильтром в WHERE.

Типичная ошибка: фильтруют поле из правой таблицы после LEFT JOIN, тем самым фактически превращают LEFT JOIN в INNER JOIN.

Пример

Хотим оставить все заказы, но только оплаты в периоде учитывать в сумме. Нельзя писать:

SELECT ...
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.paid_at >= '2026-01-01';

Это уберёт заказы без оплат в периоде.

Правильные варианты

Вариант 1: фильтровать в CTE:

WITH payments_by_order AS (
    SELECT
        p.order_id,
        SUM(p.amount) AS paid_amount
    FROM payments p
    WHERE p.paid_at >= DATE '2026-01-01'
    GROUP BY p.order_id
)
SELECT ...
FROM orders o
LEFT JOIN payments_by_order p ON p.order_id = o.order_id;

Вариант 2: фильтровать внутри SUM/COUNT (если СУБД и бизнес логика позволяют):

SELECT
    o.order_id,
    SUM(CASE WHEN p.paid_at >= DATE '2026-01-01' THEN p.amount ELSE 0 END) AS paid_amount
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
GROUP BY o.order_id;

Вариант 1 обычно проще отлаживать и согласуется с принципом “агрегация до JOIN”.

Проверка через контрольные выборки: как поймать сломанную кратность

Существует дисциплина: до того как “доверять” отчёту, вы проверяете допущения о кратности и уровне агрегации.

Ниже — практические проверки, которые занимают минуты.

Контроль 1: сколько строк рождается после JOIN?

Если вы ожидаете 1 строку на заказ, но видите больше — кратность сломана.

SELECT
    o.order_id,
    COUNT(*) AS row_cnt_after_joins
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments p     ON p.order_id = o.order_id
GROUP BY o.order_id
HAVING COUNT(*) > 1
ORDER BY row_cnt_after_joins DESC
LIMIT 50;

Если для большинства заказов row_cnt_after_joins равно N*M, значит вы “скрестили” две многострочные таблицы на уровне строк.

Контроль 2: сравните суммы “до” и “после” по тестовому заказу

Возьмите один конкретный order_id из “подозрительных”, например с большим числом позиций и платежей, и сравните:

• сумму позиций напрямую из order_items
• сумму позиций через ваш финальный запрос

Прямой запрос:

SELECT
    oi.order_id,
    COUNT(*) AS items_cnt,
    SUM(oi.quantity * oi.unit_price) AS items_amount
FROM order_items oi
WHERE oi.order_id = 12345
GROUP BY oi.order_id;

А теперь посмотрим, что выдаёт ваш отчёт для этого order_id. Если отличается — проблема в кратности или в месте агрегации.

Контроль 3: контролируйте инварианты

Для ряда метрик есть очевидные “инварианты”, которые можно использовать как тест.

Пример:

• payments_cnt не может быть меньше COUNT(payment_id) из payments для этого заказа.
• orders_cnt при подсчёте заказов с уникальным order_id не должен расти от добавления JOIN-полей, если вы считаете корректно.

Предотвращение “сломанной” кратности: чеклист на этапе проектирования запроса

Перед тем как писать финальный SELECT, пройдитесь по пунктам.

1) Задайте вопрос: какая таблица размножит строки?

Любая таблица, где на ключ order_id приходится больше одной строки, потенциально размножает.

• order_items — размножает
• payments — размножает
• events (события) — почти всегда размножает

Если у вас в запросе одновременно два (или три) размножителя, не агрегируйте “в конце” по строкам без контроля.

2) Синхронизируйте уровень агрегации

Если финальный отчёт “по заказам” — ваши агрегации должны давать одну строку на order_id до объединений.

Если финальный отчёт “по клиентам” — агрегируйте сначала по заказам (внутри CTE), затем по клиентам.

3) Старайтесь, чтобы ключи в JOIN имели ожидаемую уникальность

Это не всегда возможно обеспечить физически, но логически вы должны знать, сколько строк будет у каждого ключа.

Практический способ: в CTE аггрегируйте по ключу.

4) Не смешивайте фильтры “после LEFT JOIN” с логикой включения

Фильтруйте правую таблицу либо в CTE, либо учитывайте в CASE/условиях внутри агрегации.

5) Делайте контрольные выборки на “сложных” примерах

Берите:

• заказ с максимальным количеством позиций
• заказ с максимальным количеством платежей
• пару заказов из разных статусов

Если эти “сложные” кейсы дают корректные метрики — вероятнее, что и остальные будут корректными.

Улучшенная архитектура запроса: слои отчёта

Полезно мыслить в слоях, особенно когда отчёт растёт.

Пример слоистой архитектуры для отчёта “по клиентам за период”:

1. выбрать заказы за период
2. агрегировать позиции по заказам
3. агрегировать оплаты по заказам
4. соединить по заказам
5. агрегировать по клиентам

Пример шаблона

WITH
orders_in_period AS (
    SELECT
        o.order_id,
        o.customer_id
    FROM orders o
    WHERE o.created_at >= DATE '2026-01-01'
      AND o.created_at <  DATE '2026-02-01'
),
items_by_order AS (
    SELECT
        oi.order_id,
        COUNT(*) AS items_cnt,
        SUM(oi.quantity * oi.unit_price) AS items_amount
    FROM order_items oi
    GROUP BY oi.order_id
),
payments_by_order AS (
    SELECT
        p.order_id,
        COUNT(*) AS payments_cnt,
        SUM(p.amount) AS paid_amount
    FROM payments p
    WHERE p.paid_at >= DATE '2026-01-01'
      AND p.paid_at <  DATE '2026-02-01'
    GROUP BY p.order_id
),
orders_enriched AS (
    SELECT
        op.order_id,
        op.customer_id,
        COALESCE(i.items_cnt, 0) AS items_cnt,
        COALESCE(i.items_amount, 0) AS items_amount,
        COALESCE(p.payments_cnt, 0) AS payments_cnt,
        COALESCE(p.paid_amount, 0) AS paid_amount
    FROM orders_in_period op
    LEFT JOIN items_by_order i ON i.order_id = op.order_id
    LEFT JOIN payments_by_order p ON p.order_id = op.order_id
)
SELECT
    customer_id,
    COUNT(*) AS orders_cnt,
    SUM(items_cnt) AS total_items_cnt,
    SUM(items_amount) AS total_items_amount,
    SUM(paid_amount) AS total_paid_amount
FROM orders_enriched
GROUP BY customer_id;

Такой запрос:

• устойчив к росту числа “размножающих” таблиц (добавляете ещё один слой-CTE),
• легче отлаживается (каждый CTE можно отдельно проверить),
• снижает риск “тихой” порчи метрик.

Типичные ошибки, которые встречаются постоянно

Ошибка 1: DISTINCT вместо правильной агрегации суммы

COUNT(DISTINCT order_id) помогает только для счётчиков заказов. Но суммы (SUM(amount)) от повторов не защищены.

Ошибка 2: SUM по полю из размножающей таблицы “в финале”

Если финальный SELECT агрегирует по клиентам/категориям/дням, а в FROM есть order_items и payments как строки, сумма почти наверняка “утяжелится” кратностью.

Ошибка 3: фильтрация правой таблицы в WHERE после LEFT JOIN

Результат станет выборкой только тех заказов, у которых есть оплаты/события в периоде — даже если бизнес этого не требовал.

Ошибка 4: агрегации с разными ключами

Например:

• items_by_order агрегировали по order_id
• а paid_by_order по customer_id (или наоборот)
  В итоге вы “соединяете несопоставимое”.

Ошибка 5: непроверенная предпосылка “ключ уникален”

Иногда считают, что payments имеет 1 строку на order_id. В реальности там история платежей. SQL не обязан угадывать вашу интуицию — он просто перемножит кратности.

Как учиться на практике: маленькие эксперименты с данными

Если вы хотите уверенно писать такие запросы, важен не столько “запоминалочный” синтаксис, сколько привычка:

1. взять один реальный отчёт,
2. найти место, где join добавляет таблицу-наполнения,
3. переписать запрос по паттерну “агрегация до JOIN”,
4. сравнить результаты на контрольных ключах.

Часто достаточно 2–3 таких итераций, чтобы мозг перестроился: вы начинаете видеть кратность как первую сущность, которую нужно контролировать.

Если вы только стартуете и хотите системно разобраться с базовыми конструкциями SQL (включая JOIN и агрегации) — хороший следующий шаг может быть курс «SQL – для начинающих!», но дальше всё равно придётся практиковать именно контроль кратности и уровней агрегации на ваших данных.

Выводы: надёжный SQL для отчётов — это контроль кратности и уровня агрегации

Многотабличные отчёты обычно “ломаются” по одной причине: JOIN размножает строки, а затем вы агрегируете поля “в конце”, уже на испорченном уровне кардинальности. В результате суммы и счётчики становятся непредсказуемыми.

Чтобы писать SQL уверенно и получать корректные итоги:

• агрегируйте до размножающих JOIN (через CTE, чтобы зафиксировать уровень “по заказу”, “по пользователю” и т.д.);
• согласуйте уровень агрегации с логикой отчёта;
• аккуратно фильтруйте LEFT JOIN (лучше переносить фильтры в CTE);
• применяйте проверки: “сколько строк рождается после JOIN”, сравнение сумм на конкретных ключах;
• держите запрос в слоях, чтобы отладка была быстрой и результат — проверяемым.

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

Если нужно, могу предложить набор упражнений (с данными и задачами) под вашу схему отчётов: например, “заказы → позиции → возвраты/сторнирования” или “клиенты → активности → подписки” — там кратность проявляется особенно показательно.