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

Как найти среднее от среднего

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

SQL
SELECT m.rating,
       AVG(m.rental_rate) AS average_rental
FROM movie AS m
GROUP BY m.rating
ORDER BY average_rental DESC; 

rating	average_rental
PG	3.05186
PG-13	3.03484
NC-17	2.97095
R	2.93872
G	2.88888

Теперь вас просят дополнить запрос и посчитать среднее от найденных средних значений.

Промежуточная таблица со средней ценой аренды готова. Было бы удобно, если бы такая таблица уже была в базе данных. Если сохранить выдачу из прошлого запроса в таблицу best_rating, можно легко вывести среднее значение поля average_rental.

SQL
SELECT AVG(average_rental)
FROM (.......) AS best_rating 

Однако дополнительная таблица не понадобится. В SQL такие задачи решают с помощью подзапросов, или вложенных запросов.

Пишем подзапрос

Чтобы написать подзапрос, после оператора FROM вместо таблицы добавляют другой запрос. Подставим уже написанный код, который выводит пять самых «дорогих» рейтингов фильмов.

Сначала можно проверить, какую таблицу вернёт запрос с подзапросом. После SELECT укажем *, чтобы убедиться, что запрос вернёт всю таблицу без ошибок.

SQL
SELECT *
FROM (SELECT m.rating,
             AVG(m.rental_rate) AS average_rental
        FROM movie AS m
      GROUP BY m.rating
      ORDER BY average_rental DESC
      LIMIT 5) AS best_rating; 

rating	average_rental
PG	3.05186
PG-13	3.03484
NC-17	2.97095
R	2.93872
G	2.88888

❗ Подзапросу во FROM нужно всегда назначать псевдоним, иначе произойдёт ошибка.

Результат остался прежним. Теперь можно вычислить среднее значение.

SQL
SELECT AVG(best_rating.average_rental)
FROM (SELECT m.rating,
             AVG(m.rental_rate) AS average_rental
        FROM movie AS m
      GROUP BY m.rating
      ORDER BY average_rental DESC
      LIMIT 5) AS best_rating; 

avg
2.97705

Задача выполнена. И всё в одном запросе.

Декомпозиция с помощью подзапросов

Покажем ещё один пример. Посчитаем фильмы, в которых снималась тройка самых востребованных актёров и актрис — тех, кто чаще других снимались в кино.

Декомпозируем задачу:

Посчитаем, сколько фильмов приходится на каждого актёра или актрису, и выделим тройку рекордсменов по количеству фильмов.
Найдём общее количество фильмов, в которых снимались самые востребованные актёры и актрисы.

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

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

SQL
SELECT first_name, 
       last_name,
       COUNT(m.film_id) AS total_movie
FROM actor AS a LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id
LEFT JOIN movie AS m ON m.film_id = fa.film_id
GROUP BY first_name, last_name
ORDER BY total_movie DESC
LIMIT 3; 

first_name	last_name	total_movie
Susan	Davis	54
Gina	Degeneres	42
Walter	Torn	41

Первая часть задачи решена. Поместим решение в подзапрос после FROM и назначим псевдоним для получившейся таблицы.

💡 При использовании подзапросов соблюдайте правила стиля и обращайте внимание на отступы. Это позволит не запутаться в коде.

Выполним код с *, чтобы убедиться, что всё работает.

SQL
SELECT *
FROM (SELECT first_name, 
             last_name,
             COUNT(m.film_id) AS total_movie
      FROM actor AS a LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id
      LEFT JOIN movie AS m ON m.film_id = fa.film_id
      GROUP BY first_name, last_name
      ORDER BY total_movie DESC
      LIMIT 3) AS top_actor; 

first_name	last_name	total_movie
Susan	Davis	54
Gina	Degeneres	42
Walter	Torn	41

Результат не изменился. Теперь можно переходить к следующей задаче — найдём общее количество фильмов. Вместо * укажем функцию SUM и передадим ей поле total_movie.

SQL
SELECT SUM(total_movie)
FROM (SELECT first_name, 
             last_name,
             COUNT(m.film_id) AS total_movie
      FROM actor AS a LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id
      LEFT JOIN movie AS m ON m.film_id = fa.film_id
      GROUP BY first_name, last_name
      ORDER BY total_movie DESC
      LIMIT 3) AS top_actor; 

sum
137

Задача решена. Результат впечатляет: 137 фильмов на троих. Настоящие ударники кинематографического труда.