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

Курс профессиональной Power BI аналитики	Подробнее
Курс SQL-анализа данных с нуля	Подробнее

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

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

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

Например, из датасета 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 = 'США')

SQL Базовый

Номер урока	Урок	Описание
1	SQL Базовый №1. Установка PostgreSQL, создание таблицы, импорт из CSV	Начинаем изучать SQL. Установим СУБД, создадим схему, таблицу и выполним импорт из CSV.
1.1	SQL Базовый №1.1. UI создание схемы, таблицы, импорт из CSV	Дополнение к уроку 1. Создадим схему, таблицу и выполним импорт данных из CSV с помощью пользовательского интерфейса.
2	SQL Базовый №2. Простые операции, SELECT	Научимся отбирать нужные нам столбцы и строки таблицы и делать сортировку.
3	SQL Базовый №3. Типы данных	Знакомство с основными типами данных в PostgreSQL: строковые, числовые, дата и время.
4	SQL Базовый №4. Импорт и экспорт данных	Научимся импортировать данные из CSV и экспортировать в CSV.
5	SQL Базовый №5. Группировка	Изучаем операцию группировки и основные агрегатные функции.
6	SQL Базовый №6. Математические операции	Сложение, вычитание, умножение, деление, остаток от деления, факториал, возведение в степень, корень.
7	SQL Базовый №7. CASE	CASE — это условный оператор, который позволяет осуществлять проверку и возвращать результат, который зависит от того, какое условие выполнено.
8	SQL Базовый №8. Дополнения к урокам 1-7	Значение NULL, запросы к таблицам из других схем, рекомендации по форматированию кода, порядок выполнения операций в SQL.
9	SQL Базовый №9. Повторение изученного	Повторим все, что изучили в уроках 1-8. Спонсоры также смогут получить домашнее задание.
10	SQL Базовый №10. Подзапросы	Подзапросы нужны, когда для решения задачи нужно сделать несколько действий. Подзапросы внутри WHERE, FROM, SELECT, HAVING.
11	SQL Базовый №11. CTE	CTE также используются для решения задач в несколько этапов.
12	SQL Базовый №12. Оконные функции. Ранг, ранжирование (ranking)	Как посчитать ранг для всей таблицы или для конкретной категории.
13	SQL Базовый №13. Оконные функции. Агрегаты (SUM, AVG, MIN, MAX, COUNT)	Получить агрегированную итоговую сумму для категории.
14	SQL Базовый №14. Смещение	Приемы из этого урока позволяют получить значение из другой строки таблицы.
15	SQL Базовый №15. Оконные функции. Скользящие агрегаты	Научимся вычислять скользящее среднее и нарастающий итог.
16	SQL Базовый №16. Операция JOIN	Какие бывают виды объединения по горизонтали. Чем они отличаются друг от друга. Практика и домашнее задание.
17	SQL Базовый №17. Практика	Повторим все изученное в предыдущих уроках. Создадим схему, несколько таблиц и выполним несколько запросов на закрепление изученного материала.
18	SQL Базовый №18. Объединение по вертикали (UNION, UNION ALL, INTERSECT, EXCEPT)	Научимся объединять таблицы по вертикали.
19	SQL Базовый №19. Устройство на работу 1	Тестовые задания при приеме на работу.
20	SQL Базовый №20. Сравнение с Excel, важные функции DBeaver	Важные функции DBeaver. Сравниваем SQL и Excel.
21	SQL Базовый №21. Устройство на работу 2	Решим еще один тест при устройстве на работу.
22	SQL Базовый №22. Краткий обзор основных DDL и DML команд	Познакомимся с командами, которые позволяют создавать таблицы, наполнять их данными, редактировать, удалять и т. д.
23	SQL Базовый №23. Устройство на работу 3	Еще одно тестовое задание по SQL при приеме на работу на позицию BI-аналитика.

предыдущая запись

Power Query. Обработка PDF выписки

следующая запись