Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 13
Часть 2. Продуктовая аналитика
Глава 11. DATE_TRUNC, SUM, кросс-таблицы. Выручка по месяцам
ОглавлениеОписание задачи
Новый отчёт: посчитаем общую выручку по месяцам на основе завершённых заказов. А затем представим эти данные в виде кросс-таблицы, где месяцы – строки, а категории товаров – колонки.
Таблицы: orders, order_items, products, categories.
Основные SQL-конструкции
DATE_TRUNC – обрезает дату до указанной точности (год, месяц, день, час).
text
DATE_TRUNC (’month’, order_date) /* 2024-01-15 → 2024-01-01 */
SUM – агрегатная функция, суммирует значения (см. главу 10).
crosstab – функция из расширения PostgreSQL tablefunc. Превращает строки в колонки. В функцию передаётся в кавычках запрос, который нам надо перевернуть.
Для использования crosstab нужно установить расширение:
text
CREATE EXTENSION IF NOT EXISTS tablefunc;
Совет
DATE_TRUNC удобен для группировки по периодам: неделям, месяцам, кварталам, годам.
SQL-запросы с объяснением
Задача 1. Выручка по месяцам.
text
SELECT
DATE_TRUNC (’month’, order_date) AS month,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM orders o
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) – превращает дату в первый день месяца.
– JOIN – соединяем заказы с их товарами.
– SUM – считаем общую выручку.
– GROUP BY – группируем по месяцам.
– ORDER BY month – сортируем по возрастанию.
Пример вывода (фрагмент):
text
month revenue
2024-01-01 00:00:00+03 1571090.00
2024-02-01 00:00:00+03 1458330.00
2024-03-01 00:00:00+03 1407930.00
2024-04-01 00:00:00+03 1193490.00
Задача 2. Выручка по месяцам и категориям (обычный GROUP BY).
Подготовим данные для кросс-таблицы: выручка по месяцам и категориям.
text
SELECT
DATE_TRUNC (’month’, o. order_date) AS month,
c.name AS category_name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
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 month, c.category_id, c.name
ORDER BY month, category_name;
Как это работает
– Группируем по месяцу и категории.
– Считаем выручку для каждой пары (месяц, категория).
Пример вывода (фрагмент):
text
month category_name revenue
2024-01-01 00:00:00+03 Женская 11970.00
2024-01-01 00:00:00+03 Мужская 11970.00
2024-01-01 00:00:00+03 Наушники 79700.00
2024-01-01 00:00:00+03 Ноутбуки 689600.00
2024-01-01 00:00:00+03 Планшеты 69900.00
Задача 3. Кросс-таблица: месяцы в строках, категории в колонках.
Превращаем результат задачи 2 в таблицу, где категории становятся колонками, используем crosstab из расширения tablefunc.
text
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab (
«SELECT
DATE_TRUNC (»’month’», o. order_date)::date AS month,
c.name AS category_name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
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 month, c.category_id, c.name
ORDER BY 1,2»,
«SELECT DISTINCT name FROM categories WHERE name IN (»«Ноутбуки»», ««Смартфоны»», ««Планшеты»») ORDER BY 1»)
AS ct (month date, «Ноутбуки» numeric, «Смартфоны» numeric, «Планшеты» numeric);
Как это работает
– Первый запрос – данные: месяц, категория, выручка.
– Второй запрос – список категорий, которые станут колонками.
– crosstab превращает строки в колонки AS ct (month date, «Ноутбуки» numeric, «Смартфоны» numeric, «Планшеты» numeric).
– ::date – преобразуем timestamp в дату.
– Кавычки в названиях колонок нужны из-за русских букв.
– Две одинарные кавычки подряд используются для экранирования кавычек внутри строки crosstab.
результат
«2024-01-01» 689600.00 69900.00 699000.00
«2024-02-01» 489500.00 209700.00 594200.00
«2024-03-01» 799400.00 209700.00 354500.00
«2024-04-01» 614400.00 69900.00 449400.00
«2024-05-01» 89900.00 null 154800.00
Вопросы и ответы
В: Зачем нужна кросс-таблица?
О: Она компактно показывает данные: строки – месяцы, колонки – категории. Удобно для отчётов и дашбордов.
В: Почему во втором запросе crosstab указаны не все категории?
О: Чтобы колонок было не слишком много. В реальных отчётах выбирают топ-5 или топ-10 категорий.
В: Что делать, если в какой-то месяц не было продаж по категории?
О: В примере выше будет 0.00 или NULL.
В: Можно ли использовать crosstab без второго запроса?
О: Да, но тогда категории нужно перечислить вручную в определении колонок, а запрос должен возвращать данные в строгом порядке.
Что в итоге
Мы научились:
– Группировать данные по месяцам с DATE_TRUNC
– Считать выручку по месяцам и категориям
– Строить кросс-таблицы с помощью crosstab
Кросс-таблицы удобны для отчётов, где нужно сравнить показатели по разным категориям в динамике.
* Задание со звёздочкой
Напишите запрос, который строит кросс-таблицу выручки по кварталам (используйте DATE_TRUNC (’quarter’, order_date)) и категориям «Ноутбуки», «Смартфоны», «Планшеты».
Подсказка: замените ’month’ на ’quarter’ в первом запросе crosstab.
