Заработок в сети для начинающих, арбитраж трафика, психология, профайлинг, юмористические рассказы
Базы данных. Джоины
На прошлой неделе мы научились подключаться к Бигквери, получать данные из одной таблицы и делать простые запросы. Обычно этого мало — нужные данные распределены по нескольким таблицам.
Например, в интернет-магазинах обычно есть три таблицы: с клиентами, товарами и заказами. Чтобы посмотреть, какие товары купил клиент в прошлом декабре, нужно использовать все три. Сегодня научимся объединять информацию из нескольких таблиц с помощью джоинов.
Письмо большое, почтовые программы могут спрятать часть текста.
Откройте его в браузере, так точно ничего не потеряется.
На прошлой неделе вы работали в «Убере». Всё круто, компания выросла в мирового гиганта — классный офис, стабильность, уважение. Но вам захотелось приключений. Вы переехали в Москву и присоединились к стартапу «Инвизибл», который продает вино в интернете с помощью смешных статей в блоге и рассылок.
В базе данных «Инвизибла» есть таблица «пользователи»(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. Сколько в базе заказов, для которых отсутствуют записи о пользователях?
