Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 10
Часть 2. Продуктовая аналитика
Глава 8. GROUP BY, AVG. Средний чек по категориям
ОглавлениеОписание задачи
В отчёте нужны данные, какой у нас средний чек по каждой категории товаров. То есть какие категории приносят больше денег за одну покупку.
Таблицы: orders, order_items, products, categories.
Нужно вывести:
– название категории
– среднюю сумму заказа в рублях
Только по завершённым заказам (status = ’completed’).
Основные SQL-конструкции
GROUP BY – группирует строки с одинаковыми значениями (проходили, глава 4).
AVG – агрегатная функция, она считает среднее арифметическое в заданной колонке по группе.
text
AVG (числовое_поле)
Важно: AVG игнорирует NULL. Если в группе все значения NULL, результат будет NULL.
Совет
Если нужно среднее с учётом NULL как нулей, используйте AVG (COALESCE (price, 0)). COALESCE проверяет: если price равен NULL, возвращает 0.
SQL-запросы с объяснением
Задача 1. Средний чек по категориям.
text
SELECT
c.name AS category_name,
AVG(oi.price_per_unit * oi. quantity)::decimal (10,2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.status = ’completed’/*завершенные*/
GROUP BY c.category_id, c.name
ORDER BY avg_order_value DESC;
Как это работает
– Присоединяем заказы → товары в заказе → продукты → категории
– Отбираем только завершённые заказы
– Группируем по категории
– Считаем среднюю сумму заказа в каждой группе
– Сортируем от самой дорогой категории к дешёвой
Мы используем INNER JOIN (просто JOIN), потому что категории без заказов нас не интересуют. Если нужны и они, замените JOIN на LEFT JOIN.
Что выводит запрос: две колонки – category_name (название категории) и avg_order_value (средняя сумма заказа в рублях).
Пример вывода (фрагмент):
text
«Ноутбуки» 121945.45
«Смартфоны» 77651.72
«Планшеты» 69900.00
Задача 2. Средний чек с количеством заказов.
Добавим информацию о том, сколько заказов попало в расчёт.
text
SELECT
c.name AS category_name,
COUNT (DISTINCT o. order_id) AS orders_count,
AVG(oi.price_per_unit * oi. quantity)::decimal (10,2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.status = ’completed’
GROUP BY c.category_id, c.name
ORDER BY avg_order_value DESC;
Как это работает
Добавили COUNT (DISTINCT o. order_id), чтобы не считать один заказ несколько раз, если в нём несколько товаров из одной категории.
Типичная ошибка
Добавить в GROUP BY не все колонки из SELECT. PostgreSQL требует, чтобы все колонки, не обёрнутые в агрегатную функцию, были перечислены в GROUP BY. Поэтому мы добавили c.category_id и c.name.
Вопросы и ответы
В: А если в категории нет заказов?
О: Такая категория не попадёт в результат, потому что INNER JOIN её отсекает. Чтобы показать категории с нулевыми продажами, замените JOIN на LEFT JOIN. Тогда в avg_order_value будет NULL.
В: Что будет, если в заказе несколько товаров из одной категории?
О: Сумма по каждому товару считается отдельно, потом все складываются в группе. Получается средняя сумма заказа по категории. Всё верно.
В: Почему мы используем DISTINCT в COUNT?
О: Если в заказе несколько товаров из одной категории, без DISTINCT один заказ посчитается несколько раз. Нам важно количество уникальных заказов.
В: Как понять, что средний чек получился достоверным?
О: Посмотрите на количество заказов в категории. Если заказов мало, средний чек может быть случайным.
Что в итоге
Мы научились считать средний чек:
• Группировать данные по категориям с GROUP BY
• Считать среднее арифметическое с AVG
• Добавлять в отчёт количество заказов для оценки достоверности
Эти навыки нужны для любой аналитики продаж.
* Задание со звёздочкой
Напишите запрос, который выводит средний чек по категориям для заказов, сделанных в феврале 2024 года. Используйте фильтр по дате и группировку.
Подсказка: добавьте в WHERE условие на order_date BETWEEN «2024-02-01» AND «2024-02-29» – .
