SQL Базовый №9. Повторение изученного

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

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

• YouTube
• VK

Для этого урока понадобятся датасеты vehicle_sales и companies_500 доступные спонсорам.

В этом уроке мы пробежимся по изученному ранее. Начнем с создания таблицы и схемы.

-- Создать схему
create schema vehicles;


-- Создать таблицу
create table vehicles_sales (
  ordernumber integer,
  quantityordered smallint,
  priceeach numeric(6, 2),
  orderlinenumber smallint,
  sales numeric(7, 2),
  orderdate timestamp,
  status varchar(10),
  qtr_id smallint,
  month_id smallint,
  year_id smallint,
  productline varchar(20),
  msrp smallint,
  productcode varchar(12),
  customername varchar(50),
  phone text,
  addressline1 text,
  addressline2 text,
  city varchar(20),
  state varchar(20),
  postalcode varchar(10),
  country varchar(16),
  territory varchar(5),
  contactlastname varchar(16),
  contactfirstname varchar(16),
  dealsize varchar(6)
);

Добавим данные в созданную таблицу из CSV файла.

-- Импорт CSV
copy vehicles_sales
from 'C:\Users\user\Desktop\sql_training\vehicle_sales.csv'
csv
header
encoding 'WIN1251'

Определяем в каких странах компания ведет бизнес. Отсортируем страны по алфавиту.

-- В каких странах компания ведет бизнес
-- Сортировка по возрастанию
select distinct country
from vehicles.vehicles_sales
order by country

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

-- Выбрать столбцы orderdate, productline, country, city, sales
-- Отобрать только строки за 2004 год
-- Только доставленные заказы (status = Shipped)
-- Сортировка по алфавиту по стране, по городу, по дате, по productline, по убыванию sales
-- Соблюдайте рекомендации по форматированию кода
-- Начинайте с блока from
select
  orderdate
  ,productline
  ,country
  ,city
  ,sales
from vehicles.vehicles_sales
where 1 = 1
  and orderdate between '2004.01.01' and '2004.12.31'
  and status = 'Shipped'
order by
  country
  ,city
  ,orderdate
  ,productline
  ,sales desc

Допустим нам нужно отобрать строки, где в названии компании есть слова toy или gift.

-- Выбрать строки, где в customername содержатся подстки toy или gift
-- вне зависимости от регистра
-- а country США или Канада или Япония
select *
from vehicles.vehicles_sales
where 1 = 1
  and (customername ilike '%toy%'
  or customername ilike '%gift%')
  and country in ('USA', 'Canada', 'Japan')

Посчитаем сколько всего записей в таблице.

-- Сколько всего записей в таблице
select count(*)
from vehicles.vehicles_sales

Определим начало и конец периода, за который у нас есть данные в датасете.

-- Минимальное и минимальное значение orderdate
select
  min(orderdate)
  ,max(orderdate)
from vehicles.vehicles_sales

Найдем сумму продаж для каждого города каждой страны. Оставим только данные за 2004 году. Оставим только города с суммой продаж более 50000.

-- Сумма sales для каждой страны и города за 2004 год в orderdate
-- Сортировка по стране, а внутри страны по убыванию
-- Отфильтровать города, где sum_sales < 50'000
select
  country
  ,city
  ,sum(sales) as sum_sales
from vehicles.vehicles_sales
where 1 = 1
  and year_id = 2004
group by
  country
  ,city
having sum(sales) > 50000
order by
  country,
  sum_sales desc

Вывести уникальные пары значений страна-город.

-- Уникальные для каждой country и city
select distinct
  country
  ,city
from vehicles.vehicles_sales

Теперь перейдем к практике добавления столбцов в результате запроса. Добавим столбцы с округлением sales вниз до ближайшей тысячи и вверх. Добавим столбец типа «1000-2000» с использованием этих столбцов.

-- Добавить столбец с округлением sales вниз до ближайшей тысячи
-- Добавить столбец с округлением sales вверх до ближайшей тысячи
-- Добавить с толбец с суммой налога 10% от sales
-- Добавить столбец с текстом вида "2000-3000", где первое число - это округление sales 
-- до ближайшей тысячи вниз, а правое вверх
select
  orderdate
  ,productline
  ,country
  ,city
  ,sales
  ,sales - sales % 1000 as sales_k_down
  ,sales - sales % 1000 + 1000 as sales_k_up 
  ,sales * 0.1 as nalog
  ,cast(round(sales - sales % 1000, 0) as text) || ' - ' || cast(round(sales - sales % 1000 + 1000, 0) as text) as sales_category
from vehicles.vehicles_sales

Найти сумму продаж в каждой категории. Например, сумма продаж для категории «1000-2000», «2000-3000» и т. д.

-- Сумма продаж в каждой категории в 2004 году
-- т. е. продаж в категории 0-1000, 1000-2000 и т. д.
select
  sales - sales % 1000 as sales_k_down
  ,sales - sales % 1000 + 1000 as sales_k_up 
  ,cast(round(sales - sales % 1000, 0) as text) || ' - ' || cast(round(sales - sales % 1000 + 1000, 0) as text) as sales_category
  ,sum(sales) as sum_sales
from vehicles.vehicles_sales
where 1 = 1
  and year_id = 2004
group by
  sales - sales % 1000
  ,sales - sales % 1000 + 1000
  ,cast(round(sales - sales % 1000, 0) as text) || ' - ' || cast(round(sales - sales % 1000 + 1000, 0) as text)
order by sales_k_down

Добавим столбец с категорией типа транспорта: сухопутный, водный, воздушный.

-- Добавить столбец с типом транспорта ground, water, air
-- Сумма продаж по каждой категории
-- Сортировка по убыванию суммы
select
  case
  	when productline in ('Classic Cars','Trains','Trucks and Buses','Vintage Cars','Motorcycles') then 'ground'
  	when productline = 'Ships' then 'water'
  	else 'air'
  end as vehicle_type,
  sum(sales) as sum_sales
from vehicles.vehicles_sales
group by
  case
  	when productline in ('Classic Cars','Trains','Trucks and Buses','Vintage Cars','Motorcycles') then 'ground'
  	when productline = 'Ships' then 'water'
  	else 'air'
  end
order by sum_sales desc