Спринт 4/16: SQL для аналитиков → Тема 5/6: Подзапросы и общие табличные выражения → Урок 5/15

Подзапросы в WHERE

В этом уроке расскажем, как писать запросы в WHERE. Разберём два примера.

Изучаем пользователей с самыми крупными заказами

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

SQL
SELECT customer_id
FROM invoice
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 10; 

customer_id
6
26
57
46
45
28
24
37
25
7

Теперь эти данные можно использовать вместе с оператором WHERE. Такой подзапрос устроен точно так же, как и подзапрос с FROM. Нужно использовать полученные данные в качестве условия.

SQL
SELECT *
FROM client
WHERE customer_id IN (......) 

Можно написать окончательный запрос:

SQL
SELECT *
FROM client
WHERE customer_id IN (SELECT customer_id
                      FROM invoice
                      GROUP BY customer_id
                      ORDER BY SUM(total) DESC
                      LIMIT 10); 

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

customer_id	first_name	last_name	company	address
6	Helena	Holý	[null]	Rilská 3174/6
7	Astrid	Gruber	[null]	Rotenturmstraße 4, 1010 Innere Stadt
24	Frank	Ralston	[null]	162 E Superior Street
25	Victor	Stevens	[null]	319 N. Frances Street
26	Richard	Cunningham	[null]	2211 W Berry Street
28	Julia	Barnett	[null]	302 S 700 E
37	Fynn	Zimmermann	[null]	Berger Straße 10
45	Ladislav	Kovács	[null]	Erzsébet krt. 58.
46	Hugh	O'Reilly	[null]	3 Chatham Street
57	Luis	Rojas	[null]	Calle Lira, 198

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

Отбираем фильмы по двум условиям

Теперь более сложный пример. Нужно вычислить среднюю стоимость фильмов, отобранных по двум условиям:

их длительность не превышает среднюю длительность фильмов в базе,
в них снималось более 12 актёров и актрис.

Снова прибегнем к декомпозиции и разобьём задачу на подзадачи:

Найдём среднюю длительность всех фильмов в базе.
Найдём фильмы, в которых снималось более 12 актёров и актрис.
Отфильтруем таблицу по средней длительности фильмов.
Вычислим среднюю стоимость отобранных фильмов.

Начнём с первой подзадачи: найдём среднюю длительность всех фильмов. Это можно сделать простым запросом:

SQL
SELECT AVG(length) 
FROM movie AS m; 

avg
115.272

С этим числом будем сравнивать длительность фильмов.

Следующий этап: найдём фильмы, в которых снималось более 12 актёров и актрис.

SQL
SELECT m.film_id
FROM movie AS m 
LEFT JOIN film_actor AS fa ON m.film_id = fa.film_id
GROUP BY m.film_id
HAVING COUNT(actor_id) > 12; 

film_id
508
714
249
188
606
87
146

Сгруппировав фильмы по идентификатору, мы присоединили таблицу с фильмами, актёрами и актрисами. Среди них отобрали только те фильмы, в которых снималось более 12 актёров и актрис. Таблицу actor присоединять необязательно: в таблице film_actor и так есть идентификаторы актёров и актрис, которые можно сосчитать. В итоговую таблицу войдут только идентификаторы фильмов.

Итак, мы получили список фильмов, в которых снималось более 12 актёров и актрис. Осталось вычислить среднюю стоимость фильмов — сделаем это пока без добавления условий.

SQL
SELECT AVG(rental_rate)
FROM movie AS m; 

avg
2.98

Теперь можно указать WHERE и добавить два подзапроса:

SQL
SELECT AVG(rental_rate)
FROM movie AS m
WHERE film_id IN (SELECT m.film_id
                  FROM movie AS m LEFT JOIN film_actor AS fa ON m.film_id = fa.film_id
                  GROUP BY m.film_id
                  HAVING COUNT(actor_id) > 12)
 AND length <= (SELECT AVG(length) 
                FROM movie AS m); 

avg
2.32333

Задача решена. Благодаря декомпозиции писать подзапросы проще. И не забывайте про правила стиля, чтобы не запутаться в коде.