Данные построчно правильные, а итог неверный: ошибки JOIN и гранулярности в аналитике

Для отчётов интернет‑магазина собирают общую витрину заказов, товарных позиций и платежей. Нужны стоимость заказов, сумма успешных оплат, разрезы по категориям и сегментам клиентов.

При первой сверке суммы завышены, хотя число уникальных заказов совпадает с источником.

После исправления JOIN общий итог сходится, но распределение по группам ещё может быть неверным.

Возьмём три условных заказа выбранного периода.

1. Платежи считаем по этим заказам, даже если оплата произошла позже.

2. Заказы и платежи загружены полностью, суммы указаны в рублях, возвратов нет.

3. Неоплаченные заказы сохраняем в отчёте, сегмент клиента определяем на момент заказа.

В orders хранится одна строка на заказ, в order_items на товарную позицию, в payments одна актуальная запись на платёж.

У таблиц собственные первичные ключи, суммы не содержат NULL.

По исходным данным ожидаем три заказа общей стоимостью 2600 рублей и успешные платежи на 1000 рублей.

Правильные значения участвуют в сумме несколько раз

Гранулярность, или grain, определяет, что представляет одна строка данных. Заказ и платёж имеют разную гранулярность. Общий order_id позволяет связать записи, но не делает их уровень детализации одинаковым.

Первую версию витрины собирают прямым соединением трёх таблиц и считают общие показатели. Статус succeeded обозначает успешный платёж.

SELECT
    COUNT(DISTINCT o.order_id) AS orders_count,
    SUM(o.order_total) AS orders_total,
    SUM(p.amount) AS paid_total
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id
LEFT JOIN payments p
    ON p.order_id = o.order_id
   AND p.status = 'succeeded';

Запрос вернёт три заказа, стоимость 5600 рублей и платежи на 2000 рублей. Для заказа 101 до агрегирования получатся следующие строки.

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

Такое размножение строк называют fan‑out. Уже один JOIN “один ко многим” повторяет показатели родительской таблицы. Здесь две дочерние таблицы дополнительно перемножились внутри заказа. Суммы взяты из источника, неверно их участие в расчёте.

COUNT(DISTINCT order_id) вернул правильное число разных идентификаторов. На соседние SUM этот DISTINCT не влияет.

Удалять повторы нужно с учётом ключа

У четырёх комбинаций заказа 101 разные пары item_id и payment_id. С этими полями в выборке SELECT DISTINCT сохранит все строки. Повторяется стоимость заказа, а строки результата различаются.

SUM(DISTINCT o.order_total) оставит два значения, 1000 и 600, и вернёт 1600 рублей. Заказы 101 и 102 стоят одинаково, но оба должны участвовать в расчёте.

Внутри агрегата DISTINCT отбирает уникальные значения выражения, а идентификатор заказа в это выражение не входит.

Выборка уникальных пар (order_id, order_total) восстановит стоимость заказов, если каждому заказу соответствует одна стоимость. Для платежей нужен другой ключ, payment_id.

Если источник присылает версии платежей, сначала выбирают актуальную по правилам источника, затем отбирают успешные. Иначе в расчёте может остаться устаревший статус.

Для общих показателей достаточно заказов и платежей, агрегированных по order_id. Назовём результат с одной строкой на заказ order_metrics; дальше добавим к нему исторический сегмент.

WITH payments_by_order AS (
    SELECT order_id, SUM(amount) AS paid_total
    FROM payments
    WHERE status = 'succeeded'
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.customer_id,
    o.ordered_at,
    o.order_total,
    COALESCE(p.paid_total, 0) AS paid_total
FROM orders o
LEFT JOIN payments_by_order p ON p.order_id = o.order_id;

Получились три строки, стоимость заказов 2600 и платежи на 1000 рублей. При нескольких независимых дочерних таблицах их показатели сначала рассчитывают раздельно, затем соединяют на совместимом уровне. Товарный разрез потребует собственной выборки из позиций.

Разрез по категориям требует правила расчёта

У заказа 101 позиция на 600 рублей относится к одежде, на 400 к аксессуарам. Заказ входит в несколько категорий, категория объединяет много заказов. Это связь «многие ко многим», или many‑to‑many.

Стоимость товаров можно посчитать из позиций. Но платёж относится к заказу целиком, поэтому неизвестно, какую позицию оплатили конкретными 600 рублями. Если присоединить агрегированные платежи обратно к позициям, сумма 1000 попадёт в обе категории. Последующий JOIN с более детальными данными снова размножит показатель.

Для денежных итогов, которые должны складываться по категориям, нужно согласованное правило. Например, распределять оплату пропорционально стоимости позиций. Для заказа 101 веса распределения составят 0,6 и 0,4 и дадут в сумме единицу. Оплата распределится на 600 рублей одежды и 400 рублей аксессуаров. Сама связь через order_id этого правила не задаёт.

Заказ 101 допустимо учитывать в количестве заказов каждой категории. Тогда сумма количеств по категориям не обязана совпадать с общим числом заказов. Прежде чем сверять группы с общим итогом, нужно определить, должны ли их значения складываться.

Почему LEFT JOIN всё‑таки теряет заказы

Перенос условия на статус платежа из ON в WHERE меняет состав отчёта.

SELECT o.order_id, p.payment_id, p.amount
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.status = 'succeeded';

Останется заказ 101 с двумя платежами. Неуспешный платёж заказа 102 не пройдёт фильтр.

Для заказа 103 JOIN создаст строку с NULL в полях платежа, но выражение NULL = 'succeeded' не даёт TRUE, поэтому она тоже исчезнет.

Чтобы сохранить заказы, статус проверяют в условии ON либо внутри подзапроса платежей. Только заказы с успешной оплатой можно отобрать через EXISTS без размножения строк.

Добавление OR p.payment_id IS NULL вернёт заказ 103, но не 102. Неуспешный платёж уже соединился с заказом 102, его идентификатор заполнен. После удаления этой строки новая строка с NULL не появится.

При группировке по заказам у заказа 103 COUNT(*) будет равен единице: строка после JOIN существует. COUNT(p.payment_id) вернёт ноль, а SUM(p.amount) вернёт NULL. Поэтому в order_metrics отсутствие успешных платежей заменено нулём через COALESCE.

Это допустимо при полных данных и обязательной сумме платежа. При задержке загрузки ноль ещё не означает отсутствие оплаты; полноту источника проверяют отдельно.

Общая сумма сошлась, а сегменты остались неверными

Теперь добавим сегмент клиента. Заказ 101 сделал клиент 7 в 2026-06-30 10:00:00. В справочнике dim_customer хранится история его изменений.

При SCD Type 2 изменения справочника сохраняют отдельными версиями строк. customer_id обозначает клиента, суррогатный ключ customer_sk идентифицирует версию.

Соединение по customer_id найдёт обе версии и удвоит сумму заказа 101. Выбор последней версии уберёт повтор, но отнесёт июньский заказ к vip. Заказ останется в единственном экземпляре, его стоимость не изменится, однако сегмент будет неверным.

Текущий сегмент подходит для анализа прошлых покупок сегодняшней VIP‑аудитории. У нас нужна версия на момент заказа, ordered_at. Предположим, что valid_from и valid_to отражают время действия атрибута, все временные метки приведены к UTC.

Соединим подготовленный order_metrics с историей клиента. Результат назовём report_orders.

SELECT
    m.order_id,
    m.order_total,
    m.paid_total,
    d.customer_sk,
    d.segment
FROM order_metrics m
LEFT JOIN dim_customer d
    ON d.customer_id = m.customer_id
   AND m.ordered_at >= d.valid_from
   AND (m.ordered_at < d.valid_to OR d.valid_to IS NULL);

Начало интервала включено, конец исключён. Заказ ровно в 2026-07-01 00:00:00 попадёт только в новую версию. Предикат BETWEEN включает обе границы, поэтому у соседних периодов с заполненными концами на стыке возможны два совпадения. Открытый конец учитывается отдельным условием для NULL.

Для каждого заказа проверяем число версий через COUNT(d.customer_sk). Две и более означают неоднозначную историю или ошибку JOIN; ноль требует разбора отсутствующего соответствия.

Даже при одинаковых сегментах в двух версиях сумма посчитается дважды. Периоды клиента не должны пересекаться.

Если правильный customer_sk уже записан в факте заказа при загрузке, достаточно соединения по нему.

Поздний справочник уточняет отчёт

Заказ 102 принадлежит клиенту 8, у которого сегмент basic не менялся. Заказ 103 сделал клиент 9 второго июля, но его запись ещё не попала в справочник. Это late‑arriving dimension, запоздавшее измерение.

INNER JOIN исключит из отчёта 600 рублей. LEFT JOIN сохранит заказ с неопределённым сегментом. Для этого среза стоимость заказов распределится на 2000 рублей в basic и 600 рублей в группе «Сегмент не определён». Общая стоимость останется 2600 рублей.

При дозагрузке выясняется, что клиент 9 относится к vip с первого июля. После обновления справочника и пересчёта отчёта получаем следующий результат. Текущий сегмент приведён для сравнения.

Правильная стоимость заказов по историческим сегментам составляет 2000 рублей для basic и 600 для vip. Соединение с текущими версиями дало бы 1000 и 1600 рублей соответственно. В обоих случаях сохранились бы три заказа, стоимость 2600 рублей и платежи на 1000 рублей. Все общие показатели сходятся, хотя 1000 рублей отнесены к другой группе.

Если в факте обязателен ключ измерения, можно создать предварительную запись клиента с известным исходным ключом, а атрибуты заполнить позже. Уточнение прошлых интервалов SCD Type 2 требует повторного сопоставления затронутых заказов и пересчёта итогов. Причины неопределённого сегмента контролируют отдельно: ожидаемую задержку, отсутствие ключа или ошибку сопоставления.

Какие проверки оставить рядом с витриной

Сверку начинаем с участия каждого исходного заказа. Если дважды учесть заказ 101 и потерять заказ 102, сохранятся три строки на 2600 рублей. Проверка по идентификаторам обнаружит оба отклонения.

SELECT o.order_id, COUNT(r.order_id) AS copies
FROM orders o
LEFT JOIN report_orders r ON r.order_id = o.order_id
GROUP BY o.order_id
HAVING COUNT(r.order_id) <> 1;

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

Запрос обнаруживает пропавшие и повторно включённые исходные заказы; суммы и исторические атрибуты проверяются отдельно.

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

Если SQL‑запрос возвращает строки без ошибок, это ещё не гарантирует корректность итогового отчёта.

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

• 30 сентября в 20:00. «Подзапросы или CTE — как сделать сложный запрос понятным». Записаться

• 20 октября в 20:00. «Метрики качества данных и стратегия внедрения». Записаться

Полный список бесплатных уроков сентября собрали в дайджесте.