Один JOIN, который почти удвоил выручку: как ошибка в детализации данных пережила все проверки

Аннотация

В отчёте выручка внезапно выросла почти вдвое, хотя продажи не менялись. SQL выполнялся без ошибок, количество заказов совпадало, фильтры были корректными. Проблема оказалась в обычном JOIN между заказами и товарами. Разбираю, почему такое происходит, как быстро найти источник и какие проверки стоит ставить до публикации отчёта.

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

Количество заказов совпадало с CRM. Количество клиентов тоже. Фильтр по дате был правильным. Сам SQL выполнялся без ошибок. Я даже несколько минут подозревал задержку данных в CRM, потому что хотелось верить красивому графику.

В итоге причина оказалась намного проще и неприятнее. Я соединил таблицу заказов с таблицей товарных позиций и после этого посчитал сумму заказа так, будто одна строка всё ещё соответствует одному заказу.

Ошибка началась не в SUM, а в том, что поменялся grain таблицы

Исходная таблица заказов была простой. Одна строка — один заказ. В ней хранились order_id, customer_id, дата, статус и итоговая сумма заказа.

Условно было три заказа:

• заказ 101 на 10 000;
• заказ 102 на 15 000;
• заказ 103 на 20 000.

Суммарная выручка — 45 000.

Затем понадобилось добавить в отчёт категории товаров. Для этого я присоединил таблицу order_items, где одна строка уже соответствовала не заказу, а отдельной товарной позиции.

У заказа 101 было две позиции.

У заказа 102 — одна.

У заказа 103 — четыре.

После JOIN таблица перестала иметь grain один заказ на строку. Теперь grain стал одна товарная позиция на строку.

Сам запрос выглядел абсолютно нормально: SELECT o.order_id, o.revenue, i.product_id, i.category FROM orders o LEFT JOIN order_items i ON o.order_id = i.order_id

И вот здесь начинается ключевая ошибка.

Если после этого выполнить: SELECT SUM(revenue) FROM joined_data

получится уже не 45 000.

Заказ 101 попадёт в таблицу два раза, значит его 10 000 посчитаются дважды.

Заказ 103 попадёт четыре раза, значит его 20 000 посчитаются четырежды.

Итог станет 10 000 × 2 + 15 000 × 1 + 20 000 × 4 = 115 000.

JOIN ничего не сломал.

SUM тоже ничего не сломал.

Каждая операция по отдельности была корректной.

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

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

Самая неприятная часть истории была в том, что часть метрик на дашборде не изменилась вообще.

Количество заказов считалось так: COUNT(DISTINCT order_id)

Поэтому оно оставалось правильным.

Три заказа до JOIN.

Три заказа после JOIN.

Средний чек считался как: SUM(revenue) / COUNT(DISTINCT order_id)

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

Получалась очень опасная ситуация: одна метрика на отчёте выглядела правдоподобно, другая — нет, а визуально они стояли рядом и создавали ощущение согласованности.

В реальном кейсе всё было менее очевидно, чем в примере с тремя заказами. У одних заказов была одна товарная позиция, у других пять, у третьих десять. Коэффициент завышения поэтому не был ровно 2.

Он зависел от среднего количества позиций внутри заказа.

Если среднее число строк order_items на заказ равно 1,8, выручка при таком расчёте будет завышена примерно в 1,8 раза.

Если распределение сильно неоднородное, ошибка становится ещё менее предсказуемой.

Именно поэтому я теперь первым делом проверяю не итоговую сумму, а кардинальность связи.

Кардинальность JOIN важнее самого типа JOIN

Когда говорят про JOIN, обычно обсуждают INNER, LEFT, RIGHT и FULL.

На практике для аналитики часто важнее другой вопрос: сколько строк с одной стороны может соответствовать одной строке с другой.

Я для себя разделяю связи на четыре типа:

• 1:1 — одной строке слева соответствует максимум одна справа;
• 1:N — одной строке слева соответствует несколько справа;
• N:1 — много строк слева соответствуют одной справа;
• N:M — много строк с обеих сторон могут соответствовать друг другу.

В случае orders → order_items связь была 1:N.

Это означает, что JOIN почти неизбежно размножает строки родительской таблицы.

Сам по себе это не баг.

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

Чтобы быстро увидеть проблему, я начал считать число строк до и после JOIN: SELECT COUNT(*) FROM orders; SELECT COUNT(*) FROM joined_data;

Допустим, в orders было 52 418 строк.

После JOIN стало 94 736.

Это уже сигнал.

Дальше я смотрю: SELECT COUNT(DISTINCT order_id) FROM joined_data;

Если уникальных заказов по-прежнему 52 418, значит записи не потерялись. Они размножились.

Следующая проверка: SELECT COUNT(*) * 1.0 / COUNT(DISTINCT order_id) AS rows_per_order FROM joined_data;

Если получается 1,81, это означает, что в среднем на один заказ теперь приходится 1,81 строки.

И если в этой таблице просто суммировать поле уровня заказа, ошибка примерно уже видна.

Почему DISTINCT не всегда спасает

Самое очевидное исправление, которое часто приходит в голову: SUM(DISTINCT revenue)

Это почти всегда плохая идея.

Представим два разных заказа по 10 000.

Они оба существуют честно.

SUM(DISTINCT revenue) посчитает значение 10 000 только один раз.

Вместо 20 000 получится 10 000.

DISTINCT работает по значению, а не по сущности заказа.

То есть он удаляет повторяющиеся числа, а не повторяющиеся строки одной бизнес-сущности.

Правильнее сначала вернуть нужный grain.

Например: WITH order_level AS ( SELECT order_id, MAX(revenue) AS revenue FROM joined_data GROUP BY order_id ) SELECT SUM(revenue) FROM order_level

Почему MAX здесь допустим?

Потому что revenue — атрибут заказа и во всех размноженных строках одного order_id должен быть одинаковым.

Можно использовать MIN.

Можно использовать ANY_VALUE, если СУБД это поддерживает и семантика подходит.

Но главное не функция.

Главное — сначала снова получить одну строку на один заказ.

В моём отчёте проблема была сложнее из-за скидок и возвратов

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

Но у нас была ещё таблица возвратов.

И вот здесь началась уже настоящая неприятность.

Структура была примерно такая:

orders — один заказ;

order_items — несколько товарных позиций;

refunds — несколько возвратов по заказу.

Если соединить сразу все три таблицы по order_id: orders LEFT JOIN order_items LEFT JOIN refunds

получается не просто 1:N.

Получается фактически N:M внутри одного заказа.

Допустим, в заказе четыре товарные позиции и два возврата.

После JOIN может появиться восемь строк.

Каждая товарная позиция сочетается с каждой строкой возврата.

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

Это уже не случайная дубликация одной метрики.

Это fan-out.

И его намного сложнее заметить по итоговому отчёту.

Fan-out я теперь проверяю отдельно

Есть простой диагностический запрос.

До JOIN: SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS orders FROM orders;

После каждого JOIN: SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS orders FROM step_n;

Если количество уникальных заказов не изменилось, а число строк резко выросло, я сразу проверяю, не появилась ли связь 1:N.

Если после второго JOIN рост стал ещё сильнее, возможно, уже возник N:M.

Я стараюсь не добавлять несколько детальных таблиц к одной родительской таблице напрямую.

Вместо этого сначала агрегирую каждую дочернюю сущность до grain заказа.

Например: WITH items_agg AS ( SELECT order_id, COUNT(*) AS item_count, SUM(item_revenue) AS item_revenue FROM order_items GROUP BY order_id ), refunds_agg AS ( SELECT order_id, SUM(refund_amount) AS refund_amount FROM refunds GROUP BY order_id ) SELECT o.order_id, o.revenue, i.item_count, r.refund_amount FROM orders o LEFT JOIN items_agg i ON o.order_id = i.order_id LEFT JOIN refunds_agg r ON o.order_id = r.order_id

Теперь каждый дочерний источник заранее приведён к одной строке на заказ.

JOIN снова становится фактически 1:1.

Именно такой подход у нас в итоге убрал ошибку.

Почему ошибка прошла тесты

Этот вопрос меня заинтересовал даже больше самой ошибки.

У нас были проверки.

Количество заказов сверялось с CRM.

Число клиентов тоже.

Дата обновления витрины контролировалась.

NULL по ключевым полям проверялись.

Но не было теста на grain.

Никто не проверял, что одна строка итоговой витрины действительно соответствует одной бизнес-сущности.

В итоге тест: COUNT(DISTINCT order_id)

проходил идеально.

А простой: COUNT(*) = COUNT(DISTINCT order_id)

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

После этого случая я добавил несколько контрольных правил:

1. grain каждой витрины должен быть явно описан;
2. ключ grain должен быть уникальным;
3. количество строк проверяется до и после JOIN;
4. для связей 1:N дочерние таблицы агрегируются до нужного уровня заранее;
5. денежные поля нельзя агрегировать после смены grain без отдельной проверки;
6. каждая новая связь проверяется на fan-out.

Самое полезное правило оказалось почти смешным.

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

одна строка этой таблицы означает...

Если ответ до JOIN и после JOIN разный, все агрегаты нужно пересматривать.

Отдельная ловушка — метрики разных уровней в одной таблице

После этого кейса я стал намного осторожнее относиться к широким аналитическим витринам.

В одной таблице легко одновременно хранить:

revenue уровня заказа;

discount уровня товарной позиции;

refund уровня возврата;

customer_ltv уровня клиента.

Технически SQL позволяет это сделать.

Но такие поля живут на разных уровнях детализации.

Если пользователь BI потом начинает свободно суммировать их, часть метрик почти гарантированно станет неправильной.

Например, customer_ltv — атрибут клиента.

Если клиент сделал десять заказов, а витрина хранится на уровне заказа, LTV повторится десять раз.

SUM(customer_ltv) даст бессмысленное число.

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

Поэтому я стараюсь разделять витрины по grain.

Отдельно order-level.

Отдельно item-level.

Отдельно customer-level.

А если всё-таки нужен единый слой, метрики должны быть либо заранее агрегированы, либо явно защищены от неправильного суммирования.

Как я теперь проверяю JOIN до того, как он попадёт в отчёт

После этой истории у меня появился небольшой ритуал.

Сначала проверяю уникальность ключа слева.

Потом уникальность ключа справа.

После JOIN сравниваю количество строк.

Затем число уникальных бизнес-сущностей.

После этого беру несколько конкретных ID и смотрю их вручную.

И только потом считаю денежные метрики.

На одном заказе с несколькими товарами ошибка видна намного быстрее, чем на миллионе строк.

Например, беру order_id = 103.

До JOIN — одна строка.

После — четыре.

Если revenue повторяется четыре раза, уже понятно, что обычный SUM использовать нельзя.

Мне кажется, именно такой маленький ручной пример часто полезнее сложной автоматической проверки.

Он показывает не просто то, что цифра неправильная, а почему она стала неправильной.

Вывод

В этом кейсе не было плохого SQL.

JOIN был корректным.

SUM был корректным.

COUNT DISTINCT тоже.

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

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

Теперь я проверяю не только формулу метрики.

Сначала проверяю grain таблицы.

Потом кардинальность связи.

Потом fan-out.

И только после этого агрегирование.

Если одна строка до JOIN означала заказ, а после JOIN уже означает товарную позицию, сама таблица фактически стала другой.

И относиться к ней как к прежней нельзя.

В моём случае одна такая невнимательность почти удвоила выручку в отчёте.

Самое неприятное, что отчёт выглядел очень убедительно.

А именно такие ошибки обычно живут дольше всего.