SQL JOIN для аналитика: как соединять users, events и payments

Коротко

JOIN соединяет строки из двух источников по ключу. Для продуктовой аналитики важно помнить не только условие ON, но и кардинальность: одна строка users может соединиться с несколькими events или payments.

• INNER JOIN оставляет только совпавшие строки с обеих сторон.
• LEFT JOIN сохраняет все строки из таблицы слева и добавляет совпадения справа.
• До JOIN зафиксируй grain каждой таблицы и ожидаемый grain результата.
• После соединения проверь COUNT(*) и COUNT(DISTINCT user_id).
• Две таблицы one-to-many лучше сначала агрегировать до нужного grain, а потом соединять.

Сначала нарисуй связи

В простом учебном наборе users.user_id — ключ пользователя. В events один пользователь может иметь много строк, потому что одна строка означает одно событие. В payments тоже может быть несколько платежей на пользователя. Поэтому связь users → events и users → payments имеет тип one-to-many.

Если соединить users сразу и с events, и с payments, пользователь с тремя событиями и двумя платежами даст шесть строк промежуточного результата. Это не “ошибка JOIN”: база честно показала все комбинации. Ошибка появляется, когда эти строки затем принимают за уникальных пользователей или складывают платежи повторно.

INNER JOIN: оставить только совпадения

Начнём с вопроса: какие пользователи сделали core action? Для этого достаточно соединить users с отфильтрованными events. Пользователи без подходящего события в результат не попадут — это и есть поведение INNER JOIN.

select distinct
  u.user_id,
  u.channel,
  e.event_date
from users as u
join events as e
  on e.user_id = u.user_id
where e.event_name = 'core_action'
order by e.event_date, u.user_id;

LEFT JOIN: не потерять пользователей без событий

Теперь другой вопрос: сколько пользователей из каждого канала вообще не сделали core action? Здесь users должна остаться главной таблицей, поэтому используем LEFT JOIN. Строки без совпадения справа сохранятся, а поля events будут равны NULL.

Обрати внимание, где стоит условие e.event_name = .... Если поставить его в WHERE, строки с NULL справа будут отброшены, и LEFT JOIN фактически начнёт вести себя как INNER JOIN. Условия по правой таблице, которые должны сохранить “пустые” совпадения, обычно ставят в ON.

select
  u.user_id,
  u.channel,
  e.event_date
from users as u
left join events as e
  on e.user_id = u.user_id
 and e.event_name = 'core_action'
order by u.user_id, e.event_date;

Агрегируй до JOIN, если считаешь деньги

Самая надёжная защита от размножения — сначала привести каждую таблицу к зерну, которое нужно в результате. Например, сначала посчитать платежи по пользователю, а события — отдельно по пользователю, и только потом присоединить обе сводки к users.

Перед финальным SELECT обе промежуточные таблицы имеют grain “один пользователь”. Поэтому JOIN больше не создаёт комбинации “каждый платёж × каждое событие”. COALESCE превращает отсутствие платежей или событий в ноль, чтобы результат было удобно читать в BI.

with payments_by_user as (
  select
    user_id,
    sum(amount) as revenue
  from payments
  group by user_id
),
events_by_user as (
  select
    user_id,
    count(*) filter (where event_name = 'core_action') as core_actions
  from events
  group by user_id
)
select
  u.user_id,
  u.channel,
  coalesce(p.revenue, 0) as revenue,
  coalesce(e.core_actions, 0) as core_actions
from users as u
left join payments_by_user as p using (user_id)
left join events_by_user as e using (user_id)
order by revenue desc, u.user_id;

Как найти размножение строк

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

select
  u.user_id,
  count(*) as joined_rows,
  count(distinct e.event_date) as active_days,
  count(distinct p.paid_at) as payment_days
from users as u
left join events as e using (user_id)
left join payments as p using (user_id)
group by u.user_id
having count(*) > 1
order by joined_rows desc;

Частые ошибки в JOIN

JOIN ломается не только из-за неправильного синтаксиса. Чаще запрос запускается успешно, но меняет смысл метрики из-за неверной стороны LEFT JOIN, дублирующего ключа или фильтра в неправильном месте.

• Соединить две one-to-many таблицы напрямую и посчитать сумму после размножения строк.
• Использовать INNER JOIN, когда нужны и пользователи без событий или платежей.
• Поставить фильтр правой таблицы в WHERE и потерять строки без совпадений.
• Соединить по неуникальному полю вроде channel вместо user_id.
• Не учитывать временную логику: событие должно произойти до платежа, а не когда-нибудь в истории.
• Считать COUNT(*) как количество пользователей после соединения с events.

JOIN по времени: факт связи ещё не означает причинность

Соединить пользователя с событием по user_id недостаточно, если вопрос связан с периодом. Для кампании или эксперимента событие должно попасть в окно после exposure, а платеж — после целевого действия. Иначе в расчёт попадут старые факты из истории пользователя.

Временное условие лучше писать явно в ON, если нужно сохранить пользователей без подходящего события. Условие в WHERE после LEFT JOIN удалит NULL-строки и изменит смысл запроса. После соединения покажи долю пользователей с совпадением и диапазон дат найденных фактов.

Для нескольких событий заранее реши, нужен ли первый факт, любой факт или факт в каждой сессии. MIN(event_date) отвечает только за первую дату. Если нужны свойства именно первой строки, используй оконную функцию или отдельный CTE до JOIN.

select
  u.user_id,
  u.signup_date,
  min(e.event_date) as first_core_action
from users as u
left join events as e
  on e.user_id = u.user_id
 and e.event_name = 'core_action'
 and e.event_date >= u.signup_date
group by u.user_id, u.signup_date;

Кардинальность нужно проверять до финансового вывода

Перед JOIN посчитай уникальность ключа на правой стороне. Если users должен быть one-to-one по user_id, проверь это отдельно. Если правая таблица many, агрегируй её до уровня результата. validate есть в pandas, а в SQL ту же идею нужно выразить контрольным запросом на дубли и сравнением строк до и после соединения.

Особенно опасно соединять events и payments одновременно. Пользователь с пятью событиями и двумя платежами создаёт десять комбинаций. count(distinct user_id) может остаться правильным, а revenue будет завышен. Уникальный count не спасает денежные агрегаты.

После исправления напиши короткую проверку: сумма payments_by_user до JOIN равна сумме revenue после JOIN, а количество пользователей в users не изменилось при LEFT JOIN. Такие инварианты проще поддерживать, чем объяснять расхождение в конце месяца.

Мини-практика и следующий шаг

Возьми users и events в SQL-тренажёре. Сначала посчитай пользователей с core action через JOIN, затем перепиши запрос на LEFT JOIN и найди тех, у кого события нет. После этого добавь payments и сравни результат с вариантом, где платежи агрегированы заранее.

ON и WHERE: где оставить условие правой таблицы

Разница между ON и WHERE особенно важна для LEFT JOIN. Условие в ON ограничивает совпадения справа, но сохраняет строку слева. Условие в WHERE применяется уже после соединения и может удалить строки, где справа оказался NULL. Поэтому запросы с одинаковым текстом фильтра могут отвечать на разные вопросы.

Например, “все пользователи и их core action в июне” требует условия даты в ON. Если поставить дату в WHERE, пользователи без июньского события исчезнут, и результат уже будет списком активных пользователей, а не полной базой с признаком активности.

USING, ON и явные ключи

USING (user_id) удобен, когда колонка с ключом называется одинаково в обеих таблицах. ON e.user_id = u.user_id длиннее, но лучше показывает связь, если ключи имеют разные названия, составной смысл или в запросе много таблиц. Для учебного примера USING сокращает шум, для критического отчёта явное условие часто легче проверять.

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

• Ключ уникален на стороне, которую считаешь one-to-one?
• Типы ключей одинаковы: integer, text или UUID?
• Нужна ли связь по дате, версии или статусу дополнительно к user_id?
• Есть ли NULL в ключе и как он должен вести себя?

JOIN для признака, а не для списка событий

Если задача — добавить пользователю признак “делал core action”, не присоединяй все события как строки. Сначала собери одну строку на user_id: max по флагу, min по первой дате или count(*) по периоду. Тогда итоговый JOIN сохраняет зерно users и становится безопаснее для последующих метрик.

Флаг особенно удобен для сегментации: пользователи с действием и без него, payer и non-payer, новый и возвращающийся. Если нужен список всех событий, оставляй event grain и не смешивай его с пользовательской агрегацией в одном SELECT.

with active_users as (
  select
    user_id,
    min(event_date) as first_core_action
  from events
  where event_name = 'core_action'
  group by user_id
)
select
  u.user_id,
  u.channel,
  a.first_core_action,
  (a.user_id is not null) as has_core_action
from users as u
left join active_users as a using (user_id);

Проверь JOIN контрольными инвариантами

У хорошего запроса есть ожидаемые свойства. После LEFT JOIN users не должно стать меньше. После присоединения агрегированной таблицы users не должно стать больше, если справа один ряд на user_id. Сумма платежей до и после соединения должна совпадать. Эти проверки превращают JOIN из догадки в контролируемый шаг.

Сохрани такие сверки рядом с запросом или в data quality наборе. Когда схема изменится, проверка покажет, что нарушилась уникальность ключа или появилась новая связь one-to-many. Чем раньше обнаружено размножение строк, тем дешевле исправление.

Мини-кейс: выручка выросла из-за двух one-to-many таблиц

Аналитик соединяет users, events и payments по user_id, а затем получает выручку по каналам. В результате paid revenue на 40% выше платёжного реестра. Проверка показывает, что активный пользователь в среднем имеет четыре события и два платежа: каждая операция размножилась на количество событий. COUNT(DISTINCT user_id) выглядел правдоподобно, поэтому проблема обнаружилась только на сумме.

Исправление — агрегировать payments и events отдельно до user_id, затем соединить две сводки с users. После этого сверить сумму с исходной payments. Такой пример полезен как правило для всей серии: правильный JOIN начинается не с ON, а с определения зерна результата.

Сквозной разбор: сколько денег принёс каждый канал

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

Вопрос звучит буднично: сколько выручки принёс каждый канал привлечения. Данные лежат в трёх таблицах — канал в users, деньги в payments, активность в events. Первый порыв — соединить всё сразу, чтобы «данные были под рукой».

SELECT
  u.channel,
  sum(p.amount) AS revenue
FROM users u
JOIN events   e ON e.user_id = u.user_id
JOIN payments p ON p.user_id = u.user_id
GROUP BY u.channel;

Почему получилось 606 вместо 125

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

Масштаб искажения видно только при сверке с первоисточником. По каналу organic реальная выручка — 125, а соединение через события даёт 606: почти пятикратное завышение. И оно неравномерное — сильнее всего раздуваются активные каналы, то есть именно те, которые вы собираетесь хвалить в отчёте.

Обратите внимание, что count(DISTINCT user_id) в том же запросе остался бы правильным. Поэтому ошибка живёт долго: число пользователей сходится, а деньги — нет, и подозрение падает на что угодно, кроме соединения.

Правильная форма: сначала свернуть, потом соединить

Лечение всегда одно: привести каждый источник к зерну «одна строка на пользователя» до соединения. Тогда JOIN связывает наборы один-к-одному и не может ничего размножить.

Заодно обратите внимание на LEFT JOIN и coalesce. Канал partner в учебной базе не дал ни одного платежа. При обычном JOIN он просто исчез бы из отчёта, и в таблице каналов оказалось бы три строки вместо четырёх — а «нет выручки» это как раз тот факт, ради которого отчёт и делается.

WITH revenue_by_user AS (        -- зерно: один пользователь
  SELECT user_id, sum(amount) AS revenue
  FROM payments
  GROUP BY user_id
)
SELECT
  u.channel,
  count(*)                        AS signups,
  count(r.user_id)                AS payers,
  coalesce(sum(r.revenue), 0)     AS revenue
FROM users u
LEFT JOIN revenue_by_user r ON r.user_id = u.user_id
GROUP BY u.channel
ORDER BY revenue DESC;

Проверка, которая ловит это до отчёта

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

Вторая проверка — количество строк. Если левая таблица содержит 18 пользователей, то и результат соединения до группировки должен содержать 18 строк. Любое превышение означает, что справа нашлось несколько совпадений на одного человека.

Эти две проверки занимают меньше минуты и закрывают самый дорогой класс ошибок в аналитике: не «запрос не работает», а «запрос работает и уверенно врёт».

-- 1. итог по каналам против первоисточника
SELECT sum(amount) AS revenue_fact FROM payments;

-- 2. соединение не должно менять число строк слева
SELECT
  (SELECT count(*) FROM users)                       AS users_rows,
  (SELECT count(*)
     FROM users u
     LEFT JOIN (SELECT user_id, sum(amount) AS revenue
                FROM payments GROUP BY user_id) r
       ON r.user_id = u.user_id)                     AS joined_rows;

Когда ключи «одинаковые», а соединение пустое

Отдельный сорт боли — JOIN, который отработал и вернул ноль строк или подозрительно мало. Ключи выглядят одинаково на экране, но базе они не равны.

Самые частые причины три. Разные типы: user_id пришёл из выгрузки текстом, а в основной таблице он число — сравнение либо падает, либо молча не находит пар. Невидимые символы: пробел в конце или неразрывный пробел из Excel делают '42 ' и '42' разными значениями. И регистр: идентификаторы вроде почты или кода кампании в одной системе приведены к нижнему регистру, а в другой сохранены как ввёл пользователь.

Диагностика простая: посчитайте, сколько ключей из левой таблицы вообще встречаются в правой, и посмотрите на несколько «непопавших» значений глазами. Чинить лучше на входе — привести ключ к одному типу и виду в слое очистки, а не подставлять trim(lower(...)) в каждое соединение по всему проекту.

SELECT
  count(*)                          AS left_rows,
  count(p.user_id)                  AS matched_rows,
  count(*) - count(p.user_id)       AS unmatched_rows
FROM users u
LEFT JOIN (SELECT DISTINCT user_id FROM payments) p
  ON p.user_id = u.user_id;

Остальные виды соединений: когда они действительно нужны

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

RIGHT JOIN — это LEFT JOIN с переставленными таблицами; в запросах его почти не пишут, потому что читать смешанные направления тяжело. FULL OUTER JOIN оставляет строки без пары с обеих сторон и уместен при сверке двух источников, которые должны совпадать. CROSS JOIN соединяет всё со всем и нужен осознанно — например, чтобы построить календарь дат и получить нули в днях без активности.

Отдельно стоит анти-соединение: «пользователи, у которых нет ни одного события». Его выражают через NOT EXISTS или через LEFT JOIN с проверкой IS NULL по ключу правой таблицы. Формально это тоже JOIN, но задача обратная — найти отсутствие связи.

Про порядок таблиц в запросе беспокоиться почти не нужно: планировщик сам решает, что читать первым, и написанная последовательность на это влияет слабо. А вот на что стоит смотреть — это объём того, что вы соединяете. Отфильтровать период и свернуть события до пользователя дешевле, чем соединить всю историю и обрезать результат в конце: во втором случае база сначала честно построит огромный промежуточный набор.