Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 6
Часть 1. Установка. Повторение SQL
Глава 4. Повторяем продвинутый SQL (GROUP BY, JOIN, IN, BETWEEN)
ОглавлениеОписание задачи
Продолжим повторение SQL и вспомним запросы с группировкой данных, объединением таблиц, а также IN и BETWEEN. Используется почти в каждой аналитической задаче.
Разберём, как GROUP BY собирает строки в группы, JOIN соединяет данные из разных таблиц, а IN с подзапросом проверяет вхождение в список значений. Узнаем про команду BETWEEN для проверки на диапазон значений.
Основные SQL-конструкции
GROUP BY – создаёт группы из строк с одинаковыми значениями.
Обычно используется вместе с агрегатными функциями:
– COUNT (*) – количество элементов в группе
– MAX (column) – максимальное значение в группе
– MIN (column) – минимальное значение в группе
– AVG (column) – среднее значение в группе
Пример: посчитать количество пользователей в каждой стране.
text
SELECT country, COUNT (*) AS user_count
FROM users
GROUP BY country;
Как это работает
– GROUP BY country – все строки с одинаковым country попадают в одну группу.
– COUNT (*) считает количество строк в каждой группе.
JOIN – объединяет две таблицы по ключу.
Основные виды JOIN:
– INNER JOIN – только строки, где есть совпадение в обеих таблицах
– LEFT JOIN – все строки из левой таблицы, даже если нет совпадения в правой
– RIGHT JOIN – все строки из правой таблицы
– FULL JOIN – все строки из обеих таблиц
Пример: вывести заказы вместе с именами пользователей.
text
SELECT o. order_id, u.user_name, o. order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;
Как это работает
– o – псевдоним для таблицы orders
– u – псевдоним для таблицы users
– ON o.user_id = u.user_id – условие соединения
Типичная ошибка
Забыть условие JOIN. Если написать FROM orders o, users u без ON, получите декартово произведение (каждая строка из orders соединится с каждой строкой из users). Результат будет огромным и бессмысленным.
IN с подзапросом – проверяет, входит ли значение в результат подзапроса.
Пример: найти пользователей, которые делали заказы.
text
SELECT user_name
FROM users
WHERE user_id IN (SELECT DISTINCT user_id FROM orders);
Как это работает
– Подзапрос (SELECT DISTINCT user_id FROM orders) возвращает список user_id, которые есть в заказах. DISTINCT убирает дубли и возвращает только уникальные значения.
– WHERE user_id IN… оставляет только тех пользователей, чей user_id есть в этом списке.
IN со списком значений – проверяет, входит ли значение в заданный список.
Пример: найти пользователей из нескольких стран.
text
SELECT user_name, country
FROM users
WHERE country IN («Россия», «Беларусь», «Казахстан»);
Как это работает
– IN («Россия», «Беларусь», «Казахстан») – проверяет, совпадает ли country с одним из указанных значений.
– Это то же самое, что WHERE country = «Россия» OR country = «Беларусь» OR country = «Казахстан», но короче и понятнее.
Совет
Для коротких списков (до 5—7 значений) IN удобен и читаем. Если список большой, лучше использовать JOIN с временной таблицей.
BETWEEN – фильтр по интервалу значений, проверяет, входит ли значение в интервал чисел или дат.
Найти товары с ценой от 10000 до 50000 рублей.
text
SELECT product_id, name, price
FROM products
WHERE price BETWEEN 10000 AND 50000
ORDER BY price;
BETWEEN 10000 AND 50000 включает границы. То же самое что price> = 10000 AND price <= 50000.
Найти заказы, сделанные в феврале 2024 года.
text
SELECT order_id, user_id, order_date
FROM orders
WHERE order_date BETWEEN «2024-02-01» AND «2024-02-29» ORDER BY order_date;
Учтите, что в феврале 2024 было 29 дней (високосный год).
SQL-запросы с объяснением
Задача 1. Количество заказов по статусам.
Посчитаем, сколько заказов в каждом статусе.
text
SELECT status, COUNT (*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;
Как это работает
– GROUP BY status – группируем заказы по статусу.
– COUNT (*) – считаем количество в каждой группе.
– ORDER BY order_count DESC – сортируем от большего к меньшему.
Пример вывода (фрагмент):
text
’completed’ 79
’pending’ 11
’shipped’ 10
’cancelled’ 2
Задача 2. Сумма выручки по месяцам.
Посчитаем общую выручку по месяцам на основе завершённых заказов.
text
SELECT
DATE_TRUNC (’month’, order_date) AS month,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM orders o
INNER JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY DATE_TRUNC (’month’, order_date)
ORDER BY month;
Как это работает
– DATE_TRUNC (’month’, order_date) – обрезаем дату до первого дня месяца.
– INNER JOIN – соединяем заказы с их товарами.
– SUM (oi. quantity * oi.price_per_unit) – считаем общую сумму.
– GROUP BY month – группируем по месяцам.
Задача 3. Топ-5 пользователей по сумме покупок.
Найдём пользователей, которые потратили больше всего.
text
SELECT
u.user_id,
u.user_name,
SUM (oi. quantity * oi.price_per_unit) AS total_spent
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY u.user_id, u.user_name
ORDER BY total_spent DESC
LIMIT 5;
Как это работает
– Два INNER JOIN – соединяем users → orders → order_items.
– GROUP BY u.user_id, u.user_name – группируем по пользователю.
– SUM – считаем общую сумму покупок.
– ORDER BY total_spent DESC – сортируем от большего к меньшему.
– LIMIT 5 – оставляем только пять строк.
Задача 4. Пользователи, которые ничего не покупали (через NOT IN).
Найдём пользователей, у которых нет ни одного заказа.
text
SELECT user_id, user_name, country
FROM users
WHERE user_id NOT IN (
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);
Как это работает
– Подзапрос собирает список всех user_id, которые есть в заказах.
– NOT IN оставляет пользователей, которых нет в этом списке.
Типичная ошибка
Если в подзапросе NOT IN встретится NULL, результат будет пустым. Поэтому внутри подзапроса всегда добавляйте WHERE user_id IS NOT NULL.
Задача 5. Пользователи, которые покупали хотя бы раз (через IN).
Найдём пользователей, у которых есть хотя бы один заказ.
text
SELECT user_id, user_name, country
FROM users
WHERE user_id IN (
SELECT DISTINCT user_id FROM orders
);
Как это работает
– Подзапрос собирает список всех user_id, которые есть в заказах.
– IN оставляет пользователей, которые есть в этом списке.
Задача 6. Пользователи из выбранных стран (IN со списком).
Найдём пользователей из России, Беларуси и Казахстана.
text
SELECT user_id, user_name, country
FROM users
WHERE country IN («Россия», «Беларусь», «Казахстан»)
ORDER BY country, user_name;
Как это работает
– IN («Россия», «Беларусь», «Казахстан») – оставляет только пользователей из этих трёх стран.
– ORDER BY country, user_name – сортируем сначала по стране, потом по имени.
Пример вывода (фрагмент):
text
user_id user_name country
4 ’dmitry_kozlov’ «Беларусь».
11 ’sergey_volkov’ «Беларусь».
12 ’aidar_kz’ «Казахстан».
Вопросы и ответы
В: Что будет, если в GROUP BY указать не все колонки из SELECT?
О: PostgreSQL выдаст ошибку. Все колонки, которые не обёрнуты в агрегатную функцию (SUM, COUNT, AVG), должны быть перечислены в GROUP BY.
В: Чем INNER JOIN отличается от LEFT JOIN?
О: INNER JOIN оставляет только строки с совпадением в обеих таблицах. LEFT JOIN оставляет все строки из левой таблицы, даже если в правой нет совпадения (тогда поля из правой таблицы будут NULL).
В: Чем IN с подзапросом отличается от JOIN?
О: Оба могут дать одинаковый результат, но работают по-разному. IN удобен, когда нужно просто проверить вхождение. JOIN даёт больше гибкости (можно вывести поля из обеих таблиц).
В: Почему в подзапросе для NOT IN нужно убирать NULL?
О: Потому что NOT IN с NULL в списке всегда возвращает пустой результат. Это особенность SQL.
Что в итоге
Мы научились:
– Группировать строки с GROUP BY и считать агрегаты (COUNT, SUM)
– Соединять таблицы с помощью INNER JOIN и LEFT JOIN
– Использовать IN и NOT IN с подзапросами и со списками значений
– Использовать BETWEEN для фильтрации по интервалу
– Комбинировать WHERE, GROUP BY и ORDER BY в одном запросе
Эти навыки – основа для следующих глав, где мы будем решать реальные аналитические задачи.
* Задание со звёздочкой
Напишите запрос, который выводит общую сумму выручки по каждому пользователю (user_id, user_name) и название страны, но только для пользователей из России и Казахстана. Используйте JOIN и WHERE с IN.
Результат отсортируйте по сумме от большей к меньшей.
