sql_queries.md

SQL Queries (ТЗ п.2)

Ниже приведены SQL-запросы для схемы: clients -> orders -> order_items -> nomenclatures -> categories.

Предположения:

• PostgreSQL
• В orders.created_at хранится время создания заказа
• “последний месяц” = последние 30 дней (можно заменить на date_trunc('month', now()) если нужен календарный месяц)

---

2.1 Сумма товаров, заказанных под каждого клиента (Наименование клиента, сумма)

SELECT
    c.name AS client_name,
    COALESCE(SUM(oi.quantity * oi.price_at_order), 0) AS total_amount
FROM clients c
LEFT JOIN orders o
    ON o.client_id = c.id
LEFT JOIN order_items oi
    ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY total_amount DESC;

2.2 Количество дочерних элементов первого уровня вложенности для категорий

SELECT
    p.id,
    p.name,
    COUNT(c.id) AS children_lvl1_count
FROM categories p
LEFT JOIN categories c
    ON c.parent_id = p.id
GROUP BY p.id, p.name
ORDER BY p.id;

2.3.1 View: «Топ-5 самых покупаемых товаров за последний месяц»

CREATE OR REPLACE VIEW v_top5_nomenclatures_last_month AS
WITH RECURSIVE cat_path AS (
    SELECT
        c.id AS category_id,
        c.parent_id,
        c.name,
        c.id AS origin_category_id
    FROM categories c

    UNION ALL

    SELECT
        p.id AS category_id,
        p.parent_id,
        p.name,
        cp.origin_category_id
    FROM categories p
    JOIN cat_path cp
        ON cp.parent_id = p.id
),
root_cat AS (
    SELECT
        origin_category_id,
        category_id AS root_category_id,
        name AS root_category_name
    FROM cat_path
    WHERE parent_id IS NULL
)
SELECT
    nm.name AS nomenclature_name,
    rc.root_category_name AS category_lvl1,
    SUM(oi.quantity) AS total_qty
FROM order_items oi
JOIN orders o
    ON o.id = oi.order_id
JOIN nomenclatures nm
    ON nm.id = oi.nmoduct_id
JOIN root_cat rc
    ON rc.origin_category_id = nm.category_id
WHERE o.created_at >= (NOW() - INTERVAL '30 days')
GROUP BY nm.id, nm.name, rc.root_category_name
ORDER BY total_qty DESC
LIMIT 5;

2.3.2 Оптимизация запроса и схемы под рост данных (тысячи заказов в день)

1. Добавление индексов для ускорения выполнения запросов
2. Партиционирование. Например order по created_at