SQL Базовый №5. Группировка

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

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

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

• YouTube
• VK

В этом уроке мы будем работать с таблицами bike_sales и religion, созданными в предыдущих уроках.

Счет количества строк

select count(*)
from religion

Посчитать количество значений в столбце

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

select count(metro_line)
from religion

Уникальные значения в столбце

Посчитать количество уникальных значений столбца.

-- Количество уникальных станий метро
select count(distinct metro_station)
from religion

-- Количество уникальных линий метро
select count(distinct metro_line)
from religion

Максимальное и минимальное значение в столбце

Вычислим максимальный и минимальный profit в таблице bikes_sales.

select
  max(profit) as max_profit,
  min(profit) as min_profit
from bike_sales

Группировка и агрегирование с помощью GROUP BY

С помощью GROUP BY можно сгруппировать результаты по одному или нескольким столбцам. Это позволяет нам выполнять операции SUM(), COUNT() и другие. Например, можно найти сумму продаж по странам. GROUP BY оставляет только уникальные значения как DISTINCT. Выполним GROUP BY без агрегирования.

-- Вернутся уникальные значения customer_country
select
  customer_country
from bikes.bike_sales
group by customer_country
order by customer_country

Операция группировки не ограничена одним столбцом. Группировать можно по нескольким полям.

-- Уникальные пары customer_country-customer_state
select
  customer_country,
  customer_state
from bikes.bike_sales
group by
  customer_country,
  customer_state
order by
  customer_country,
  customer_state

Совмещаем GROUP BY и COUNT

-- Посчитать количество храмов на каждой ветке метро
-- Выполняем сортировку по убыванию количества храмов
select
  metro_line,
  count(*)
from religion
group by metro_line
order by count(*) desc

Группировку можно выполнять по нескольким полям. Например, мы можем посчитать количество храмов на каждой станции каждой ветки метро.

-- Посчитать количество храмов на каждой станции каждой ветки
-- Сортировка по веткам, а внутри по станциям по алфавиту
select
  metro_line,
  metro_station,
  count(*)
from religion
group by
  metro_line,
  metro_station
order by
  metro_line,
  metro_station

Совмещаем GROUP BY и COUNT

Посчитаем сумму выручки и прибыли по каждой стране.

-- Сумма выручки и прибыли по каждой стране
select
  customer_country,
  sum(revenue) as revenue,
  sum(profit) as profit
from bikes.bike_sales
group by
  customer_country
order by
  profit desc

-- Сумма выручки и прибыли по каждой стране и штату
select
  customer_country,
  customer_state,
  sum(revenue) as revenue,
  sum(profit) as profit
from bikes.bike_sales
group by
  customer_country,
  customer_state
order by
  profit desc

Фильтрация запроса с помощью HAVING

HAVING позволяет отфильтровать результат группировки.

-- Сумма выручки, расходов, прибыли по категории и подкатегории
-- Год = 2015
-- Оставить только строки с прибылью >= 100'000
select
  product_category,
  product_subcategory,
  sum(revenue) as revenue,
  sum(cost) as cost,
  sum(profit) as profit
from bikes.bike_sales
where order_year = 2015
group by
  product_category,
  product_subcategory
having sum(profit) >= 100000
order by profit 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-аналитика.