SQL Базовый №10. Подзапросы

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Зачем нужны подзапросы

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

Например, из датасета kinopoisk нужно получить только фильмы из стран, которые входят в Топ-3 по среднему баллу. Чтобы получить такие фильмы предварительно придется найти Топ-3 стран по среднему баллу.

В этом уроке:

• Подзапросы внутри WHERE
• Подзапросы внутри FROM
• Подзапросы внутри SELECT
• Подзапросы внутри HAVING

Подзапросы внутри WHERE

В этом и последующий разделах будут использоваться таблицы companies_500 и kinopoisk, созданные в предыдущих уроках.

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

-- Компании, где количество сотрудников больше среднего
select *
from companies.companies_500
where employees > (
	select avg(employees)
	from companies.companies_500)

Получим компании, в которых profits_change больше, чем у UnitedHealth Group. В подзапросе получим значение profits_change для этой компании.

-- Названия компаний, у которых profits_change больше, чем у UnitedHealth Group
select *
from companies.companies_500 
where profits_change > (
	select profits_change 
	from companies.companies_500 c 
	where title = 'UnitedHealth Group')

Получим список компаний, которые не входят в Топ-3 секторов по revenues. В подзапросе нужно вычислить Топ-3 секторов.

-- Компании, которые не входят в Топ-3 секторов по revenues
select *
from companies.companies_500
where sector not in 
	(select
	  sector
	from companies.companies_500
	group by sector
	order by sum(revenues) desc
	limit 3)

Получим фильмы, которые входят в Топ-3 стран по среднему рейтингу. В подзапросе нужно вычислить Топ-3 стран по среднему рейтингу.

-- Выбрать только фильмы из стран из Топ-3 по среднему рейтингу
select *
from public.kinopoisk
where 1 = 1
	and country in
		(select	country
		from public.kinopoisk
		group by country
		order by avg(rating_balls) desc
		limit 3)
order by
	country
	,rating_balls desc

Подзапросы внутри FROM

Найдем сумму revenues компаний, которые входят в Топ-10 по revenues. В подзапросе получим 10 компаний, входящих в Топ-10 по revenues.

-- Сумма revenues Топ-10 компаний по revenues
select sum(revenues)
from
	(select
	 	title
	 	,revenues
	from companies.companies_500
	where 1 = 1
		and revenues is not null
	order by revenues desc
	limit 10) sq

Из 20 фильмов с самым низким рейтингом выберем фильмы не из США. В подзапросе получим 20 фильмов с самым низким рейтингом.

-- Из 20 фильмов с самым низким рейтингом выбрать фильмы не из США
select *
from
	(select *
	from kinopoisk
	order by rating_balls
	limit 20) as sq
where country != 'США'

Подзапросы внутри SELECT

Найдем долю каждого сектора от общей суммы revenues. В подзапросе внутри SELECT нужно найти общую сумму.

-- Процент от общей суммы
select
	*
	,(select sum(revenues)
	 from companies.companies_500) as total
	,round(revenues::numeric / (select sum(revenues)
	 			 		  		from companies.companies_500), 2) as pct_of_total 
from
	(select
	sector
	,sum(revenues) as revenues
	from companies.companies_500
	group by sector) as sq
order by pct_of_total desc

Решим еще одну похожую задачу. Найдем долю каждой страны от общего количества фильмов.

-- Доля фильмов каждой страны от общего количества фильмов
select
	*
	,round(cnt::numeric / (select count(*) from public.kinopoisk), 2) as pct
from
	(select
		country
		,count(*) as cnt
	from public.kinopoisk
	group by country) as sq
order by pct desc

Подзапросы внутри HAVING

Получим сектора, которые имеют больший revenues, чем «Food, Beverages & Tobacco». В подзапросе внутри HAVING найдем сумму revenues для «Food, Beverages & Tobacco».

-- Какие сектора имеют больше revenues, чем Food, Beverages & Tobacco
select
	sector
	,sum(revenues) as revenues
from companies.companies_500
group by sector
having sum(revenues) >
	(select sum(revenues)
	from companies.companies_500
	where 1 = 1
	  and sector = 'Food, Beverages & Tobacco')
order by revenues desc

Получим страны, у которых средний балл больше, чем в США.

-- Страны, у которых средний балл больше, чем у США
select
	country
	,avg(rating_balls) as rating
from public.kinopoisk
group by country
having avg(rating_balls) > 
	(select
	 	avg(rating_balls)
	from public.kinopoisk
	where country = 'США')