Оператор INNER JOIN

Перед тем как перейти к практике, напомним то, что вы узнали в прошлых уроках:

Таблицы в базе данных связаны, и все взаимосвязи между ними отражает ER-диаграмма.
Связь между таблицами обеспечивается внешними ключами, и с их помощью таблицы можно объединить.
Псевдонимы позволяют привязывать поле к конкретной таблице — это пригодится при объединении.
Соединить таблицы можно по-разному, и от выбранного способа зависит результат слияния.
Для объединения таблиц используют операторы INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.

Этот урок посвятим оператору INNER JOIN. Задача, которую мы будем решать, звучит так:

Составить список из десяти покупателей, которые оформили самые крупные заказы в США. В таблицу должны войти фамилии пользователей, но не их идентификаторы.

Если пользователь при оформлении заказа не указал фамилию, такой пользователь в таблицу не войдёт. И наоборот: если пользователь зарегистрировался, но ещё не сделал ни одного заказа, такого пользователя следует исключить из финальной таблицы.

Напомним, что таблица invoice содержит информацию о заказах, а таблица client хранит данные о пользователях: их фамилии, имена и адреса. Таблица client связана с таблицей invoice полем customer_id, в котором указан идентификатор покупателя. В таблице client это поле — первичный ключ, а в таблице invoice — внешний.

Выбираем тип объединения

Теперь можно приступить к задаче. Для того чтобы получить список из фамилий покупателей с самыми крупными заказами, понадобится информация из обеих таблиц: invoice и client.

Перед тем как объединить таблицы, нужно решить, какой тип объединения использовать. Неизвестно, для каждой ли записи в таблице client найдётся соответствие в таблице с заказами invoice. В прошлом уроке вы узнали, что тип объединения INNER JOIN включит в итоговую таблицу только те данные, которые являются общими для двух таблиц. Если нужно исключить фамилии пользователей без заказов и идентификаторы пользователей без фамилий — оператор INNER JOIN подойдёт.

Соединяем таблицы

В PostgreSQL используют два вида записи оператора. INNER JOIN — полная запись. Можно использовать короткую форму — JOIN. И в том и в другом случае результат будет одинаковым.

В итоговую таблицу должны войти поля first_name и last_name из таблицы client, а также поле total из таблицы invoice. Без поля с суммой заказа не узнать, какие пользователи оформили самые крупные заказы. Объединить таблицы invoice и client можно с помощью общего поля customer_id.

SQL
SELECT c.first_name,
       c.last_name,
       i.total
FROM invoice AS i
INNER JOIN client AS c ON i.customer_id = c.customer_id
LIMIT 10; 

Поля, которые указали после оператора SELECT, можно не переименовывать — у них понятные названия. Перед каждым полем нужно добавить псевдоним для таблицы — иначе не разобраться, в какой из двух таблиц находится нужное поле.

Одну из таблиц указали после оператора FROM, добавив псевдоним i. Если тип объединения — INNER JOIN, порядок, в котором указывают таблицы, не важен. Результат слияния не поменяется.

После INNER JOIN можно указать вторую таблицу, добавив после ключевого слова ON условие для соединения. Это условие определяет, как будут сравниваться две таблицы. В запросе внешний ключ таблицы invoice сопоставляется с первичным ключом таблицы client.

Если понадобится, можно соединить несколько таблиц сразу. Для этого указывают оператор, таблицу и нужные поля.

SQL
...
FROM таблица_1
INNER JOIN таблица_2 ON таблица_1.поле=таблица_2.поле -- первое присоединение
INNER JOIN таблица_3 ON таблица_1.поле=таблица_3.поле -- второе присоединение
... 

Посмотрите, какая таблица получилась в результате присоединения invoice и client.

first_name	last_name	total
Luís	Gonçalves	3.98
Luís	Gonçalves	3.96
Luís	Gonçalves	5.94
Luís	Gonçalves	0.99
Luís	Gonçalves	1.98
Luís	Gonçalves	13.86
Luís	Gonçalves	8.91
Leonie	Köhler	1.98
Leonie	Köhler	13.86
Leonie	Köhler	8.91

То что нужно: фамилия, имя покупателя и сумма заказа. Самое сложное позади, осталось добавить группировку и сортировку.

Фильтруем данные и добавляем детали

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

Для информативности в итоговую таблицу можно добавить минимальную и максимальную суммы заказа для каждого покупателя, а также количество заказов.

SQL
SELECT c.first_name,
       c.last_name,
       MIN(i.total) AS min_cost,
             MAX(i.total) AS max_cost,
             ROUND(AVG(i.total), 2) AS average_cost,
             COUNT(i.total) AS total_purchases
FROM invoice AS i
INNER JOIN client AS c ON i.customer_id = c.customer_id
WHERE i.billing_country = 'USA'
GROUP BY first_name, last_name
ORDER BY average_cost DESC
LIMIT 10; 

first_name	last_name	min_cost	max_cost	average_cost	total_purchases
Richard	Cunningham	0.99	23.86	6.80	7
Frank	Ralston	0.99	13.86	6.23	7
Julia	Barnett	0.99	15.86	6.23	7
Victor	Stevens	0.99	13.86	6.09	7
Jack	Smith	0.99	13.86	5.66	7
Dan	Miller	0.99	13.86	5.66	7
Heather	Leacock	0.99	13.86	5.66	7
Tim	Goyer	1.98	13.86	5.52	7
Michelle	Brooks	0.99	13.86	5.37	7
John	Gordon	0.99	13.86	5.37	7

Обратите внимание, что данные сгруппировали по двум полям сразу: first_name и last_name. Ведь фамилии пользователей могут повторяться. С помощью условия WHERE i.billing_country = 'USA' отобрали пользователей из США. Отсортировав таблицу по средней сумме заказа от большего к меньшему, можно получить нужный список.

Схема базы данных

Задача 4 / 6

Нужно объединить данные двух таблиц: track со всеми треками в магазине и invoice_line с купленными треками.

Выгрузите таблицу с названием трека и числом, которое соответствует тому, сколько раз трек покупали. Учитывайте, что в одном заказе один и тот же трек может встречаться несколько раз. Если какой-либо из треков не покупали или о купленном треке нет информации — такие записи не должны войти в таблицу. Оставьте только уникальные значения и отберите первые 20 записей.

Подсказка

Для соединения таблиц используйте INNER JOIN. После ключевого слова ON укажите поля, по которым нужно объединить таблицы: t.track_id=i.track_id.

Нужно дополнить запрос: добавьте поле с идентификатором плейлиста playlist_id. Такое поле можно получить из таблицы playlist_track. В этой таблице собраны идентификаторы плейлистов и треков (поле track_id). Условие остаётся прежним: если идентификаторы треков не совпадают во всех трёх таблицах, такие треки не должны войти в итоговую таблицу. Выведите первые 20 записей.

Подсказка

Снова используйте INNER JOIN. Объедините таблицы по полю track_id.

Идентификатор плейлиста теперь указан в итоговой таблице. Но что это за плейлисты — непонятно. Эту информацию можно взять в четвёртой таблице — playlist. Таблица содержит поле playlist_id с идентификатором плейлиста и поле name — с его названием. Добавьте в итоговую таблицу поле name. Условия те же: данные без совпадения не должны попасть в таблицу. Ограничьте вывод первыми 20 записями.

Подсказка

Используйте INNER JOIN и объедините таблицы по полю playlist_id.

Теперь проанализируйте получившуюся таблицу. Нужно посчитать суммарную стоимость треков для каждого плейлиста. Отобразите в таблице два поля: playlist_name с названием плейлиста и total_revenue с суммарной стоимостью. Отсортируйте данные по значению в поле total_revenue от большего к меньшему.

Подсказка

Сгруппируйте данные по названию плейлиста и посчитайте сумму. Не забудьте переименовать поля.

Массовую любовь к музыке 90-х можно понять. А как обстоит дело с жанрами — какие популярнее? Сгруппируйте данные по жанрам и посчитайте количество заказов. Выведите на экран два поля: одно с названием жанра, второе — с количеством купленных треков этого жанра. Отсортируйте таблицу по убыванию количества заказов.

Подсказка

Отберите названия уникальных категорий фильмов, в которых снималась Ума Вуд (англ. Uma Wood).

Подсказка

xxxxxxxxxx
 
SELECT p.name AS playlist_name,
        SUM(i.unit_price) AS total_revenue
-- укажите нужные поля
FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id=i.track_id
INNER JOIN playlist_track AS pl ON t.track_id=pl.track_id
INNER JOIN playlist AS p ON pl.playlist_id = p.playlist_id
GROUP BY playlist_name
ORDER BY total_revenue DESC;

playlist_name	total_revenue
Music	4215.42
90’s Music	944.46
TV Shows	441.78
Classical	40.59
Brazilian Music	26.73
Heavy Metal Classic	21.78
Classical 101 - Deep Cuts	18.81
Classical 101 - Next Steps	14.85
Classical 101 - The Basics	6.93
Grunge	6.93