SQL Базовый №11. CTE (Common Table Expressions)

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

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

• YouTube
• VK

CTE — это временный результат, к которому можно обращаться в последующих запросах. CTE применяются, когда задачу нужно решать в несколько шагов. Можно ввести более одного CTE через запятую. Чем-то напоминает подзапрос. Многие задачи можно решить как с использованием CTE, так и с использованием подзапросов.

В этом уроке мы поработаем с датасетом sport_goods_sales. В предыдущих уроках он не встречался. В этом уроке пока будут разбираться простые примеры использования CTE.

Все датасеты и другие приложенные файлы доступны спонсорам канала Comrade Excel.

Создадим новую таблицу

-- Создать таблицу
create table sport_goods_sales (
  retailer text,
  retailer_id int,
  invoice_date date,
  region text,
  state text,
  city text,
  product text,
  price_per_unit int ,
  units_sold int,
  total_sales int,
  operating_profit numeric(8, 1),
  operating_margin numeric(4, 2),
  sales_method text
);

-- Импорт данных
copy sport_goods_sales
from 'C:\Users\user\Desktop\sql_training\sport_goods_sales.csv'
csv
delimiter ';'
header;

Среднемесячные продажи в 2020 году

С помощью CTE мы получили сумму каждого месяца 2020 года. Далее к этому промежуточному результату делается запрос среднего значения.

-- Среднемесячные продажи в 2020 году
with cte as (
	select
	  extract('month' from invoice_date) as mnth
	  ,sum(total_sales) as sales
	from sport_goods_sales
	where 1 = 1
	  and extract('year' from invoice_date) = 2020
	group by extract('month' from invoice_date)
	order by mnth)
select round(avg(sales)) as average_sales_per_month 
from cte

Среднедневные продажи для каждого месяца 2020

С помощью CTE находим сумму каждого дня каждого месяца. Далее к этому промежуточному результату делается запрос среднего для каждого месяца.

with cte as (
	select
	  extract('month' from invoice_date) as m
	  ,extract('day' from invoice_date) as d
	  ,sum(total_sales) as sales
	from sport_goods_sales
	where 1 = 1
	  and extract('year' from invoice_date) = 2020
	group by 1, 2
	)
select
  m
  ,round(avg(sales)) as avg_sales_per_day 
from cte
group by m

Доля каждого sales_method от общей суммы в 2021 году

С помощью CTE находим сумму каждого sales_method. В запросе к промежуточному результату CTE будет также использоваться простой подзапрос.

with cte as (
	select
	  sales_method
	  ,sum(total_sales) as sales
	from sport_goods_sales
	group by sales_method)
select
  *
  ,round( sales / (select sum(sales) from cte) * 100, 2) as pct_of_total
from cte

Штаты, в которых сумма продаж в 2021 году больше, чем в Texas в том же году

Решим этот пример небольшой цепочкой CTE. В первом CTE найдем сумму продаж в Texas. Во втором CTE найдем сумму каждого штата. Далее выполним запрос ко второму CTE и отфильтруем результат с помощью первого CTE.

with txs as (
	-- Сумма продаж в Texas
	select sum(total_sales) as txs_sales
	from sport_goods_sales
	where 1 = 1
	  and extract('year' from invoice_date) = 2020
	  and state = 'Texas')
,all_states as (
	select
	  state
	  ,sum(total_sales) as sales
	from sport_goods_sales
	where 1 = 1
	  and extract('year' from invoice_date) = 2020
	group by state)
select *
from all_states
where sales > (select txs_sales from txs)
order by sales desc

Оставить только строки для Топ-3 штатов по online продажам в 2021

В CTE находим сумму по штатам по выбранным условиям, сортируем по убыванию и оставляем только 3 строчки. В запросе к CTE воспользуемся коротким подзапросом.

with top as (
	-- Топ-3 по онлайн-продажам
	select
	  state
	  ,sum(total_sales) as sales
	from sport_goods_sales
	where 1 = 1
	  and extract('year' from invoice_date) = 2021
	  and sales_method = 'Online'
	group by state
	order by sales desc
	limit 3)
select *
from sport_goods_sales
where state in (select state from top)

Общая сумма total_sales для Топ-5 штатов по In-store продажам в 2021 году

С помощью CTE найдем Топ-5 штатов с суммами продаж. В запросе к CTE нужно просто сложить столбец с суммами.

with cte as (
	select
	  state
	  ,sum(total_sales) as sales
	from sport_goods_sales
	where 1 = 1
	  and extract('year' from invoice_date) = 2021
	  and sales_method = 'In-store'
	group by state
	order by sales desc
	limit 5)
select sum(sales) as top_5_sales
from cte