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

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

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

Для этого урока понадобятся датасеты 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

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. Повторить строку N раз

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

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

SQL Базовый

Power Query. Повторить строку N раз

Tableau Базовый №11. Сеты

Вас может заинтересовать