Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 14
Часть 2. Продуктовая аналитика
Глава 12. Аналитические функции. Топ-5 товаров
ОглавлениеОписание задачи
Представьте, что нам нужны 5 товаров, которые принесли больше всего выручки. Это классическая задача для отчётов и дашбордов.
Таблицы: order_items, products.
Основные SQL-конструкции
Аналитические (оконные) функции – выполняют вычисления по набору строк, связанных с текущей строкой. В отличие от GROUP BY, они не сворачивают строки в одну.
ROW_NUMBER () – присваивает уникальный номер каждой строке в рамках окна (партиции).
Синтаксис:
text
ROW_NUMBER () OVER (PARTITION BY колонка ORDER BY колонка)
– PARTITION BY – делит строки на группы (если не указано, всё окно)
– ORDER BY – задаёт порядок нумерации внутри группы
Совет
ROW_NUMBER () часто используют для нумерации строк в отчётах и для отбора топ-N записей в каждой категории.
SQL-запросы с объяснением
Задача 1. Топ-5 товаров по выручке (простой способ).
text
SELECT
p.product_id,
p.name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 5;
Как это работает
– Группируем по товару.
– Считаем выручку.
– Сортируем от большего к меньшему.
– LIMIT 5 оставляет только первые 5 строк.
Пример вывода:
text
product_id name revenue
4 MacBook Pro 14 2398800.00
5 Lenovo ThinkPad 1078800.00
1 iPhone 15 1038700.00
2 Samsung Galaxy S24 973700.00
11 iPad Air 699000.00
Задача 2. Топ-5 товаров с нумерацией (ROW_NUMBER).
Добавим нумерацию строк.
text
SELECT
ROW_NUMBER () OVER (ORDER BY SUM (oi. quantity * oi.price_per_unit) DESC) AS rank,
p.product_id,
p.name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 5;
Как это работает
– ROW_NUMBER () OVER (ORDER BY SUM (…) DESC) – нумерует строки от 1 до N в порядке убывания выручки.
– Остальное как в задаче 1.
Задача 3. Топ-3 товара в каждой категории (ROW_NUMBER с PARTITION BY).
Более сложный пример: для каждой категории найти 3 самых продаваемых товара.
text
WITH product_revenue AS (
SELECT
c.name AS category_name,
p.product_id,
p.name AS product_name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name, p.product_id, p.name
),
ranked AS (
SELECT
category_name,
product_name,
revenue,
ROW_NUMBER () OVER (PARTITION BY category_name ORDER BY revenue DESC) AS rank
FROM product_revenue
)
SELECT
category_name,
product_name,
revenue,
rank
FROM ranked
WHERE rank <= 3
ORDER BY category_name, rank;
Как это работает
– product_revenue – считаем выручку по товарам с категориями.
– ranked – нумеруем товары внутри каждой категории (PARTITION BY category_name) по убыванию выручки.
– WHERE rank <= 3 – оставляем только топ-3 в каждой категории.
Пример вывода (фрагмент):
text
category_name product_name revenue rank
Женская Сумка женская 29940.00 1
Женская Платье летнее 20940.00 2
Мужская Джинсы мужские 27930.00 1
Мужская Футболка хлопок 5970.00 2
Наушники Наушники Sony WH-1000XM5 179400.00 1
Наушники Наушники AirPods Pro 174300.00 2
Вопросы и ответы
В: Чем ROW_NUMBER () отличается от RANK () и DENSE_RANK ()?
О: При одинаковых значениях ROW_NUMBER () даёт разные номера (1,2,3,4…), RANK () пропускает номера при совпадении (1,1,3,4…), DENSE_RANK () не пропускает (1,1,2,3…).
В: Зачем использовать ROW_NUMBER (), если есть LIMIT?
О: LIMIT работает только на всём результате. ROW_NUMBER () с PARTITION BY позволяет отобрать топ-N в каждой группе (категории, стране и т.д.).
В: Можно ли использовать ROW_NUMBER () без PARTITION BY?
О: Да. Тогда нумерация будет по всему результату.
В: Что будет, если несколько товаров имеют одинаковую выручку?
О: ROW_NUMBER () назначит разные номера (порядок не определён). Если нужна одинаковая позиция, используйте RANK () или DENSE_RANK ().
Что в итоге
Мы научились:
– Находить топ-5 товаров с помощью GROUP BY и LIMIT
– Добавлять нумерацию с ROW_NUMBER ()
– Отбирать топ-N товаров в каждой категории с PARTITION BY
Оконные функции открывают возможности для более сложной аналитики.
* Задание со звёздочкой
Напишите запрос, который выводит топ-2 товара по выручке в каждой категории. Используйте ROW_NUMBER () OVER (PARTITION BY …).
Подсказка: возьмите за основу задачу 3 и измените условие фильтрации rank <= 2.
