Базы данных. Джоины

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

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

На прошлой неделе вы работали в «Убере». Всё круто, компания выросла в мирового гиганта — классный офис, стабильность, уважение. Но вам захотелось приключений. Вы переехали в Москву и присоединились к стартапу «Инвизибл», который продает вино в интернете с помощью смешных статей в блоге и рассылок.

В базе данных «Инвизибла» есть таблица «пользователи»(users) с именами, фамилиями и имейлами.

И есть таблица «заказы» (orders) с датой, суммой заказа и номерами покупателей.

Каждому пользователю присвоен уникальный номер (столбец id в таблице users). У каждого заказа есть номер покупателя (столбец user_id в таблице orders). Если объединить данные, можно узнать, что заказал конкретный человек.

Чтобы связать две таблицы, вы в разделе FROM пишете:
[название первой таблицы] JOIN [название второй таблицы] ON [поля, по которым связываете].

 

SELECT

FROM

пользователи JOIN заказы
ON пользователи.id = заказы.user_id

WHERE

….

 

Посмотрим на покупки Альберта Эйнштейна.

 

Запрос

SELECT

users.first_name,

users.last_name,

orders.order_date,

orders.order_cost

FROM

[analytics-one:Examples.users] users

JOIN [analytics-one:Examples.orders] orders

ON users.id = orders.user_id

WHERE

users.last_name = ‘Эйнштейн’

 

По-русски

ДОСТАНЬ

имя и фамилию пользователя, номера, дату и сумму заказа

ИЗ ТАБЛИЦ

пользователи и заказы, объединенных по полю «номер пользователя»

ГДЕ

фамилия пользователя = «Эйнштейн»

 

В результате получим таблицу с заказами Альберта Эйнштейна:

Такое объединение называется внутренний джоин (INNER JOIN). Расскажу, что это такое.

 

Внутренний джоин

 

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

Например, маркетологи «Инвизибла» задумали сделать рассылку самым активным покупателям и попросили выгрузить список заказов с контактными данными покупателей. Делаем запрос.

 

Запрос

SELECT

users.first_name,

users.last_name,

users.email,

orders.order_date,

orders.order_cost

FROM

[analytics-one:Examples.users] users

INNER JOIN [analytics-one:Examples.orders] orders

ON users.id = orders.user_id

 

По-русски

ДОСТАНЬ

имя, фамилию и имейл пользователя, номера, дату и сумму заказа

ИЗ ТАБЛИЦ

пользователи и заказы, объединенных через ВНУТРЕННИЙ ДЖОИН по полю «номер пользователя»

В результатах Нильс Бор присутствует дважды, потому что сделал два заказа. А Чарльза Дарвина и Ричарда Фейнмана нет, потому что они до сих пор ничего не купили.

Аналогично, вы видите заказы № 1, 2, 3 и 4. Заказов № 5 и 6 нет, потому что для них отсутствует соответствующая запись в таблице с пользователями (users).

 

Левый джоин

 

Гендиректор «Инвизибла» прознал, что некоторые клиенты ничего не покупают, расстроился и решил послать каждому из них купон на скидку. Ваша задача — получить список их имейлов. Для этого используем левый джоин (LEFT JOIN).

Левый джоин возвращает все записи из таблицы users (той, что слева от джоина) и дополняет их связанными записями из таблицы orders, там где они есть. Если пользователь не сделал ни одного заказа, в столбцах номер заказа и других столбцах стоит NULL, то есть «значение отсутствует»

 

Запрос

SELECT

users.first_name,

users.last_name,

users.email,

orders.order_date,

orders.order_cost

FROM

[analytics-one:Examples.users] users

LEFT JOIN [analytics-one:Examples.orders] orders

ON users.id = orders.user_id

 

По-русски

ДОСТАНЬ

имя, фамилию и имейл пользователя, номера, дату и сумму заказа

ИЗ ТАБЛИЦ

пользователи и заказы, объединенных через ЛЕВЫЙ ДЖОИН по полю «номер пользователя»

Эти пользователи нам и нужны. Чтобы оставить в результатах только их, добавим к запросу условие WHERE orders.id IS NULL. То есть, «заказы отсутствуют».

 

Запрос

SELECT

users.first_name,

users.last_name,

users.email,

orders.order_date,

orders.order_cost

FROM

[analytics-one:Examples.users] users

LEFT JOIN [analytics-one:Examples.orders] orders

ON users.id = orders.user_id

WHERE

orders.id IS NULL

 

По-русски

ДОСТАНЬ

имя, фамилию и имейл пользователя, номера, дату и сумму заказа

ИЗ ТАБЛИЦ

пользователи и заказы, объединенных через ЛЕВЫЙ ДЖОИН по полю «номер пользователя»

ГДЕ

Номер заказа отсутствуетdiv>

 

Результат:

Правый джоин

 

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

Чтобы починить баг, нужно достать список таких заказов и принести разработчикам, чтобы те разобрались. Для этого используем правый джоин (RIGHT JOIN).

Правый джоин работает так же, как левый, но возвращает все записи из таблицы, находящейся справа от джоина. В нашем случае, таблицы orders. Если для какого-то заказа в таблице users не хватает записи, в полях вида user_idбудет NULL.

 

Запрос

SELECT

users.first_name,

users.last_name,

users.email,

orders.order_date,

orders.order_cost

FROM

[analytics-one:Examples.users] users

RIGHT JOIN EACH [analytics-one:Examples.orders] orders

ON users.id = orders.user_id

 

По-русски

ДОСТАНЬ

имя, фамилию и имейл пользователя, номера, дату и сумму заказа

ИЗ ТАБЛИЦ

пользователи и заказы, объединенных через ПРАВЫЙ ДЖОИН по полю «номер пользователя»

 

Правый джоин в Бигквери пишется: RIGHT JOIN EACH.

 

На заметку: Левый джоин превращается в правый, если в разделе FROM поменять таблицы местами:

users LEFT JOIN orders = orders RIGHT JOIN users.

И наоборот.

Видно, что для заказов № 5 и 6 пользователи в базе отсутствуют. Отфильтруем их так же, как фильтровали клиентов в предыдущем примере. Добавим раздел WHERE users.id is NULL.

 

Запрос

SELECT

users.first_name,

users.last_name,

users.email,

orders.order_date,

orders.order_cost

FROM

[analytics-one:Examples.users] users

RIGHT JOIN EACH [analytics-one:Examples.orders] orders

ON users.id = orders.user_id

WHERE

users.id is null

 

По-русски

ДОСТАНЬ

имя, фамилию и имейл пользователя, номера, дату и сумму заказа

ИЗ ТАБЛИЦ

пользователи и заказы, объединенных через ПРАВЫЙ ДЖОИН по полю «номер пользователя»

ГДЕ

Номер пользователя отсутствует

Полный джоин

 

На практике полные джоины используются редко. Бигквери их вообще не поддерживает. Но я объясню, что это такое.

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

 

В следующий раз

 

В каждом уроке я рассказывал о структуре, записях и взаимосвязи таблиц между собой. Но в жизни всё иначе — аналитику часто говорят: «У нас магазин на Битриксе, вот база, там всё есть». Разобраться в структуре данных, понять, как они взаимосвязаны, всё ли на месте, и как с ними дальше жить — задача аналитика. Я называю это «разведкой данных». Этим и займёмся в следующий раз.

 

Теперь сами

 

Ваша задача на этой неделе: подключиться к Бигквери (если вы ещë этого не сделали), загрузить данные о пользователях и заказах, и ответить на вопросы:

1.      Выгрузите имена, фамилии и адреса электронной почты 5 пользователей, сделавших больше всего заказов.

2.     Узнайте, какая страна принесла больше денег, Россия или Бразилия?

3.     Сколько в базе пользователей, не сделавших ни одной покупки?

4.     Сколько в базе заказов, для которых отсутствуют записи о пользователях?

 

https://www.youtube.com/watch?v=VBMalqYtKLw

Инструкция: как загрузить данные в Бигквери