Перед вами новая статья из цикла «SQL для начинающих». В предыдущих статьях мы уже рассказали о том, с чего начать изучение SQL, как с его помощью узнать количество активных пользователей продукта, рассчитать основные монетизационные метрики нашего демо проекта, и определить структуру покупок пользователей. В этот раз поговорим об объединении таблиц при работе с базами данных.
Объединение таблиц join – это базовый инструмент в работе с базами данных, который используется для связывания таблиц по определенному набору полей. В этой операции всегда используются две таблицы, обычно называемые левая (находящаяся в from) и правая (находящаяся в join), а также условие объединения.
select column_names (1..N)
from left_table_name
join right_table_name
on join_condition
Существует несколько основных видов объединения, каждый из которых может быть использован для совершенно разных задач.
Или посложнее:
Обогащение данных
Одна из самых часто используемых задач, выполняемых с помощью join – обогащение данных. Допустим, мы хотим узнать, после каких кампаний к нам приходят платящие пользователи. Мы имеем таблицу payments, но в ней нет данных о кампании, которая привела пользователя. Эти данные содержатся в таблице users.
В данном случае лучшим решением будет использовать left join, который в любом случае вернёт все строки из таблицы с платежами и добавит к ней данные из таблицы users.
Возможно, в вашей базе данных отсутствуют данные о некоторых пользователях в таблице users, и тогда, в результате работы left join, платежи от пользователей, которых не существуют в users не потеряются, а просто останутся с незаполненными данными. Условием нашего соединения будет уникальный идентификатор пользователя, который есть в обеих таблицах.
select p.devtodevid::text as devtodevid, p.eventtime, p.product, u.campaign, u.publisher
from p102968.payments
left join p102968.users on p.devtodevid = u.devtodevid
where eventtime>current_date - interval '7 day'
order by devtodevid, eventtime
В результате мы получаем таблицу, в которой к каждому платежу добавлена информация о кампании и паблишере, которые привели к нам этого пользователя.
Помните, что результатом операции join будут все столбцы обеих таблиц. Допустим, в таблице payments 20 столбцов, а в users – 30. После их объединения мы получим все 50 столбцов в результирующей таблице, поэтому будьте осторожны с этими операциями.
Два ивента (X и Y)
Нам интересно узнать, сколько пользователей за последнюю неделю отправили помощь своим соратникам после вступления в клан.
Для этого берём таблицу from p102968."_joined the clan" as jc и объединяем с таблицей inner join p102968."_help sent" as hs using(devtodevid).
select count(distinct jc.devtodevid) as "Joined the clan and Help sent users"
from p102968."_joined the clan" as jc
inner join p102968."_help sent" as hs
using(devtodevid)
where jc.eventtime>current_date - interval '7 day'
and hs.eventtime>current_date - interval '7 day'
Объединение с помощью inner join позволяет нам получить в результате только тех пользователей, которые совершили и первое и второе событие. Мы объединили таблицы, используя using(devtodevid), – это более компактный способ записи условия on jc.devtodevid = hs.devtodevid. Также в условиях where мы указали, что оба события должны были произойти в течении последней недели.
Воронка из ивента X в ивент Y
Но если нам необходимо узнать не просто количество совершивших оба ивента, а число тех, кто вступил в клан, и тех, кто после вступления отправил помощь, мы можем использовать для этого объединение left join. При этом нам нужно не только соотнести пользователя из левой таблицы с пользователем из правой on jc.devtodevid = hs.devtodevid, но также учесть, что второй ивент должен совершиться после первого (если нам важен порядок действий). Тогда в условие объединения мы должны добавить and jc.eventtime < hs.eventtime.
select count(distinct jc.devtodevid) as "Joined the clan and Help sent users"
, count(distinct hs.devtodevid) as "Help sent users"
from p102968."_joined the clan" as jc
left join p102968."_help sent" as hs
on jc.devtodevid = hs.devtodevid and jc.eventtime < hs.eventtime
where jc.eventtime>current_date - interval '7 day'
and (hs.eventtime>current_date - interval '7 day' or hs.eventtime is null)
Есть один нюанс, который вы, возможно, не заметили – это условие фильтрации or hs.eventtime is null. Оно здесь необходимо, потому что в результате работы left join, для пользователей, у которых после входа в клан нет события оказания помощи, часть данных (из второй таблицы) будет отсутствовать, а следовательно заполнена null. Без него наше изначальное условие hs.eventtime > current_date-interval '7 day' отсеяло бы все строки с такими пользователями (с null данными по второму ивенту), так как eventtime=null не сравним с датой.
Пользователи, не совершившие Y после X
Теперь попробуем найти тех пользователей, которые отвалились в нашей воронке. Это очень просто сделать, используя запрос, описанный выше.
Мы уже упоминали, что результатом такого объединения таблиц будет список вступлений в клан пользователей и последующих отправок помощи. Если пользователь не отправлял помощь после вступления в клан, то правая таблица hs будет для него пустая.
Это значит, что для получения отвалившихся в воронке пользователей, нам нужно всего лишь найти тех, кто имеет пустое второе событие. Для этого в условие where мы просто добавим and hs.devtodevid is null. Такой запрос будет называться Left exclusive join.
Итоговый запрос:
select count(distinct jc.devtodevid) as "Joined the clan and Help sent users"
from p102968."_joined the clan" as jc
left join p102968."_help sent" as hs
on jc.devtodevid = hs.devtodevid and jc.eventtime < hs.eventtime
where jc.eventtime>current_date - interval '7 day'
and (hs.venttime>current_date - interval '7 day' or hs.eventtime is null)
and hs.devtodevid is null
P. S.
Объединять таблицы можно не только по id пользователей, но и по любому другому полю – датам, странам, наименованиям товаров. С помощью одного запроса вы можете посчитать, сколько активных пользователей было в течение каждого из дней месяца (по таблице sessions) и объединить с таблицей, в которой рассчитан суммарный доход (по таблице payments) для тех же дней. Тогда в итоговой таблице вы сможете вывести для каждого из дней Active users, Gross и посчитаете ARPDAU (Gross / Active users). Эту тему мы оставим для следующих статей.
