SQL для начинающих: объединение таблиц и воронки

RU
Встречайте новую статью из серии про SQL. На этот раз поговорим об объединении таблиц
SQL для начинающих: объединение таблиц и воронки
Published
28.10.2021
|
devtodev

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

Объединение таблиц join – это базовый инструмент в работе с базами данных, который используется для связывания таблиц по определенному набору полей. В этой операции всегда используются две таблицы, обычно называемые левая (находящаяся в from) и правая (находящаяся в join), а также условие объединения.

select column_names (1..N)
from left_table_name
join right_table_name
on join_condition

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

Типы объединений в SQL

Или посложнее:

Сложные типы объединений в SQL

Обогащение данных

Одна из самых часто используемых задач, выполняемых с помощью join – обогащение данных. Допустим, мы хотим узнать, после каких кампаний к нам приходят платящие пользователи. Мы имеем таблицу payments, но в ней нет данных о кампании, которая привела пользователя. Эти данные содержатся в таблице users.

Payments table, Users table
Результат запроса. Скриншот из демо devtodev

В данном случае лучшим решением будет использовать 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.devtodevi= u.devtodevid
where eventtime>current_date - interval '7 day'
order by devtodevid, eventtime

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

Помните, что результатом операции join будут все столбцы обеих таблиц. Допустим, в таблице payments 20 столбцов, а в users – 30. После их объединения мы получим все 50 столбцов в результирующей таблице, поэтому будьте осторожны с этими операциями.

Объединение таблиц
Результат запроса. Скриншот из демо devtodev
Таким образом мы можем обогатить какое-либо пользовательское событие любыми данными о пользователе. Например, выгрузить email для проведения опроса или понять, какие типы пользователей платят (если вы их передаёте как custom user properties).

Два ивента (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'

Событие help sent users
Результат запроса. Скриншот из демо devtodev

Объединение с помощью 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)

Joined the clan and help sent users events
Результат запроса. Скриншот из демо devtodev

Есть один нюанс, который вы, возможно, не заметили – это условие фильтрации or hs.eventtime is null. Оно здесь необходимо, потому что в результате работы left join, для пользователей, у которых после входа в клан нет события оказания помощи, часть данных (из второй таблицы) будет отсутствовать, а следовательно заполнена null. Без него наше изначальное условие hs.eventtime > current_date-interval '7 day' отсеяло бы все строки с такими пользователями (с null данными по второму ивенту), так как eventtime=null не сравним с датой.

Пользователи, не совершившие Y после X

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

Мы уже упоминали, что результатом такого объединения таблиц будет список вступлений в клан пользователей и последующих отправок помощи. Если пользователь не отправлял помощь после вступления в клан, то правая таблица hs будет для него пустая.

Результат объединения таблиц SQL
Результат запроса. Скриншот из демо devtodev

Это значит, что для получения отвалившихся в воронке пользователей, нам нужно всего лишь найти тех, кто имеет пустое второе событие. Для этого в условие 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
Итоговый результат объединения таблиц SQL
Результат запроса. Скриншот из демо devtodev

P. S. 

Объединять таблицы можно не только по id пользователей, но и по любому другому полю – датам, странам, наименованиям товаров. С помощью одного запроса вы можете посчитать, сколько активных пользователей было в течение каждого из дней месяца (по таблице sessions) и объединить с таблицей, в которой рассчитан суммарный доход (по таблице payments) для тех же дней. Тогда в итоговой таблице вы сможете вывести для каждого из дней Active users, Gross и посчитаете ARPDAU (Gross / Active users). Эту тему мы оставим для следующих статей.

Read more