PrepBro
← Назад к вопросам

Как определить топ-3 товара по продажам в каждой категории?

1.3 Junior🔥 201 комментариев
#SQL и базы данных#Аналитика и метрики

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI26 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Как определить топ-3 товара по продажам в каждой категории?

Это классическая задача аналитики, которая требует правильного использования оконных функций в SQL и правильного понимания требований. Покажу несколько подходов.

Подход 1: Использование ROW_NUMBER (рекомендуемый)

SELECT 
    category_id,
    category_name,
    product_id,
    product_name,
    total_sales,
    rank
FROM (
    SELECT 
        c.category_id,
        c.category_name,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.price) AS total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY c.category_id 
            ORDER BY SUM(oi.quantity * oi.price) DESC
        ) AS rank
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
) ranked
WHERE rank <= 3
ORDER BY category_id, rank;

Объяснение:

  • PARTITION BY category_id — разделяет данные по категориям
  • ORDER BY total_sales DESC — сортирует внутри каждой категории
  • ROW_NUMBER() — нумерует строки (1, 2, 3, 4...)
  • WHERE rank <= 3 — берём только топ-3

Подход 2: Использование RANK (если хочешь ties)

SELECT 
    category_name,
    product_name,
    total_sales,
    rank
FROM (
    SELECT 
        c.category_name,
        p.product_name,
        SUM(oi.quantity * oi.price) AS total_sales,
        RANK() OVER (
            PARTITION BY c.category_id 
            ORDER BY SUM(oi.quantity * oi.price) DESC
        ) AS rank
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
) ranked
WHERE rank <= 3;

Отличие от ROW_NUMBER:

  • ROW_NUMBER() — всегда 1, 2, 3, 4...
  • RANK() — если есть tie, то 1, 1, 3, 4... (пропускает номера)

Пример:

Товар A: 1000 продаж → rank 1
Товар B: 1000 продаж → rank 1  (tie!)
Товар C: 999 продаж  → rank 3  (пропустили 2)
Товар D: 900 продаж  → rank 4

Для топ-3 с ties в результате может быть > 3 строк!

Подход 3: Использование DENSE_RANK

WITH ranked_products AS (
    SELECT 
        c.category_id,
        c.category_name,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.price) AS total_sales,
        DENSE_RANK() OVER (
            PARTITION BY c.category_id 
            ORDER BY SUM(oi.quantity * oi.price) DESC
        ) AS rank
    FROM products p
    INNER JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
)
SELECT 
    category_name,
    product_name,
    total_sales,
    rank
FROM ranked_products
WHERE rank <= 3
ORDER BY category_id, rank;

Отличие DENSE_RANK:

  • DENSE_RANK() — 1, 1, 2, 3... (не пропускает)

Сравнение окончательных функций

ФункцияTiesРезультатИспользование
ROW_NUMBER()Разные номера1, 2, 3Хочешь ровно 3 товара
RANK()Одинаковые номера, пропускает1, 1, 3Медали в спорте (золото, серебро...)
DENSE_RANK()Одинаковые, не пропускает1, 1, 2Ранжирование без пропусков

Практический пример с реальными данными

-- Таблицы
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- INSERT test data
INSERT INTO categories VALUES 
    (1, 'Electronics'),
    (2, 'Books'),
    (3, 'Clothing');

INSERT INTO products VALUES 
    (101, 'Laptop', 1),
    (102, 'Phone', 1),
    (103, 'Tablet', 1),
    (104, 'Monitor', 1),
    (201, 'Python Guide', 2),
    (202, 'SQL Bible', 2),
    (301, 'T-Shirt', 3),
    (302, 'Jeans', 3);

-- Queries
WITH sales_data AS (
    SELECT 
        c.category_id,
        c.category_name,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.price) AS revenue,
        COUNT(oi.order_item_id) AS items_sold
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
),
ranked AS (
    SELECT 
        category_id,
        category_name,
        product_id,
        product_name,
        revenue,
        items_sold,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC NULLS LAST) AS rank
    FROM sales_data
)
SELECT 
    category_name,
    product_name,
    revenue,
    items_sold,
    'Позиция ' || rank AS position
FROM ranked
WHERE rank <= 3
ORDER BY category_id, rank;

Альтернатив: использование LIMIT в подзапросе (не рекомендуется)

-- Неправильный подход (может привести к ошибкам)
SELECT category_id, product_id, total_sales
FROM (
    SELECT 
        category_id,
        product_id,
        SUM(quantity * price) AS total_sales
    FROM order_items
    GROUP BY category_id, product_id
    ORDER BY category_id, total_sales DESC
    LIMIT 3  -- ❌ Это даст топ-3 ПО ВСЕМ категориям, не по каждой!
) t;

Оптимизация производительности

-- 1. Индексы
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_products_category ON products(category_id);

-- 2. Материализованное представление (если запрашиваешь часто)
CREATE MATERIALIZED VIEW top_products_by_category AS
WITH ranked AS (
    SELECT 
        c.category_id,
        c.category_name,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.price) AS total_sales,
        ROW_NUMBER() OVER (PARTITION BY c.category_id ORDER BY SUM(oi.quantity * oi.price) DESC) AS rank
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY c.category_id, c.category_name, p.product_id, p.product_name
)
SELECT * FROM ranked WHERE rank <= 3;

На Python с Pandas

import pandas as pd

# Получаем данные из БД
df = pd.read_sql("""
    SELECT c.category_name, p.product_name, 
           SUM(oi.quantity * oi.price) as total_sales
    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_name, p.product_name
""", conn)

# Группируем по категориям и берём топ-3
top_3 = df.groupby('category_name').apply(
    lambda x: x.nlargest(3, 'total_sales')
).reset_index(drop=True)

print(top_3)

С Spark SQL

from pyspark.sql import SparkSession
from pyspark.sql.window import Window
import pyspark.sql.functions as F

spark = SparkSession.builder.appName("top_products").getOrCreate()

window = Window.partitionBy("category_name").orderBy(F.desc("total_sales"))

top_3 = df.withColumn(
    "rank", F.row_number().over(window)
).filter(F.col("rank") <= 3)

top_3.show()

Рекомендации

✅ Используй ROW_NUMBER() если нужно ровно 3 товара на категорию ✅ Используй CTE (WITH clause) для читаемости ✅ Добавляй индексы на category_id и product_id ✅ Тестируй на категориях с < 3 товаров (например, новые категории) ✅ Обработай NULL значения с NULLS LAST в ORDER BY ❌ Не используй LIMIT без PARTITION — даст неправильный результат

Эта задача демонстрирует необходимость оконных функций в SQL — инструмента, который должен быть в арсенале каждого Data Engineer.