Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 11
Часть 2. Продуктовая аналитика
Глава 9. GROUP BY, COUNT. Повторные покупки
ОглавлениеОписание задачи
Составим отчёт о пользователях, которые совершили больше одной покупки. Иными словами – найти тех, кто возвращается в интернет магазин.
Узнать, сколько пользователей купили дважды, трижды и так далее.
Основные SQL-конструкции
COUNT – агрегатная функция, считает количество строк в группе.
text
COUNT (*) – считает все строки, включая NULL
COUNT (column) – считает только непустые значения в колонке
COUNT (DISTINCT column) – считает уникальные значения
HAVING – фильтрует группы после группировки (как WHERE, но для GROUP BY).
text
SELECT category, COUNT (*) AS cnt
FROM products
GROUP BY category
HAVING COUNT (*)> 5;
Типичная ошибка
Использовать WHERE вместо HAVING для фильтрации по агрегатным функциям. WHERE выполняется до группировки и не видит COUNT, SUM, AVG. Если есть группировка и нужно отфильтровать сгруппированные данные – всегда используем HAVING.
SQL-запросы с объяснением
Задача 1. Количество заказов по пользователям.
Посчитаем, сколько заказов сделал каждый пользователь.
text
SELECT
u.user_id,
u.user_name,
COUNT (o. order_id) AS orders_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name
ORDER BY orders_count DESC;
Как это работает
– LEFT JOIN – оставляем всех пользователей, даже без заказов.
– COUNT (o. order_id) – считает только непустые заказы (у пользователей без заказов будет 0).
– GROUP BY – группируем по пользователю.
– ORDER BY orders_count DESC – сортируем от частых покупателей к редким.
Пример вывода (фрагмент):
text
user_id user_name orders_count
1 alex_ivanov 9
14 lucas_silva 8
5 nurlan_kz 8
Задача 2. Пользователи с повторными покупками (больше одного заказа).
Используем HAVING, чтобы отфильтровать группы.
text
SELECT
u.user_id,
u.user_name,
COUNT (o. order_id) AS orders_count
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name
HAVING COUNT (o. order_id)> 1
ORDER BY orders_count DESC;
Как это работает
– INNER JOIN – берём только пользователей с заказами (те, у кого 0 заказов, не нужны).
– HAVING COUNT (o. order_id)> 1 – оставляем только тех, у кого заказов больше одного.
– Сортировка от самых активных.
Пример вывода (фрагмент):
text
user_id user_name orders_count
1 alex_ivanov 9
14 lucas_silva 8
5 nurlan_kz 8
10 armen_grigoryan 8
Совет
Если нужно посчитать пользователей с повторными покупками в процентах, можно использовать CTE:
text
WITH repeat_buyers AS (
SELECT user_id
FROM orders
GROUP BY user_id
HAVING COUNT (*)> 1
)
SELECT
COUNT (*) AS repeat_buyers,
(SELECT COUNT (*) FROM users) AS total_users,
ROUND (100.0 * COUNT (*) / (SELECT COUNT (*) FROM users), 2) AS percent
FROM repeat_buyers;
Задача 3. Распределение пользователей по количеству покупок.
Сколько пользователей купили 1 раз, 2 раза, 3 раза и так далее.
text
WITH order_counts AS
(
SELECT
u.user_id,
COUNT (o. order_id) AS orders_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id
)
SELECT
orders_count,
COUNT (*) AS user_count
FROM order_counts
GROUP BY orders_count
ORDER BY orders_count;
Как это работает
– CTE order_counts – считает количество заказов для каждого пользователя.
– Основной запрос группирует пользователей по числу заказов.
Пример вывода:
text
orders_count user_count
5 2
6 4
7 5
8 3
9 1
Вопросы и ответы
В: Чем HAVING отличается от WHERE?
О: WHERE фильтрует строки до группировки, HAVING – после. WHERE не может использовать агрегатные функции (COUNT, SUM, AVG).
В: Почему в первом запросе LEFT JOIN, а во втором INNER JOIN?
О: В первом запросе мы хотим видеть всех пользователей (даже с 0 заказов). LEFT JOIN это позволяет. COUNT (o. order_id) считает только непустые значения, так что 0 заказов = 0.
В: Как посчитать только уникальные заказы (без отменённых)?
О: Добавьте условие в JOIN или в WHERE: AND o.status = ’completed’.
Что в итоге
Мы научились:
– Считать количество заказов на пользователя с GROUP BY и COUNT
– Отбирать группы с помощью HAVING
– Строить распределение пользователей по частоте покупок
Эти навыки нужны для анализа лояльности и удержания пользователей.
* Задание со звёздочкой
Напишите запрос, который выводит пользователей, сделавших хотя бы 3 заказа со статусом ’completed’. Используйте HAVING.
