9. Полезные запросы для анализа товаров и складов в системе MARKETPLACE

9.1. Аналитические запросы по товарам

9.1.1. Товары с наибольшей скидкой

CREATE OR ALTER VIEW DISCOUNTED (PRODUCT_ID, "Название товара", "Цена", "Скидка %",
"Конечная цена", "Экономия %", "Продавец", "Наличие")
AS
SELECT
    p.product_id,
    g.name as "Название товара",
    p.price as "Цена",
    p.discount_percent as "Скидка %",
    p.end_price as "Конечная цена",
    ROUND((p.price - p.end_price) / p.price * 100, 2) as "Экономия %",
    s.name as "Продавец",
    ws.stock_quantity as "Наличие"
FROM products p
JOIN goods g ON p.goods_id = g.goods_id
JOIN sellers s ON p.seller_id = s.seller_id
LEFT JOIN warehouse_stock ws ON p.product_id = ws.product_id
WHERE p.discount_percent > 0
ORDER BY p.discount_percent DESC
;

Применение:

select * from DISCOUNTED where "Скидка %" > 75 FETCH FIRST 20 ROWS ONLY ;
--или (для скидок больше 75 процентов)
select * from SELLOUT;

9.1.2. Самые дорогие товары по категориям (атрибутам)

Такой вариант работает, даже если отсутствует атрибут с именем ‘категория’

WITH bycat AS (
    SELECT
        COALESCE((
            SELECT
                jt."value"
            FROM JSON_TABLE(g.ATTRIBUTES, 'lax $.attributes[*]'
                COLUMNS (
                    "name" VARCHAR(255) PATH '$.name',
                    "value" VARCHAR(255) PATH '$.value'
                )) AS jt
            WHERE jt."name" = 'категория'
            ROWS 1
        ), 'nan') AS "Категория",
        g.name AS "Название",
        p.end_price
    FROM goods g
    JOIN products p ON g.goods_id = p.goods_id)
SELECT
    bc."Категория",
    bc."Название",
    MAX(bc.end_price) AS "Максимальная цена",
    MIN(bc.end_price) AS "Минимальная цена",
    AVG(bc.end_price) AS "Средняя цена",
    COUNT(*) AS "Количество товаров"
FROM bycat bc
WHERE bc."Категория" IS NOT NULL
GROUP BY bc."Категория", bc."Название"
ORDER BY "Максимальная цена" DESC

9.1.3. Товары с низким запасом (нуждаются в пополнении)

CREATE OR ALTER VIEW PRODUCT_INVENTORY
AS
SELECT
    p.product_id,
    g.name as "Товар",
    SUM(ws.stock_quantity) as "Общий остаток",
    COUNT(DISTINCT ws.warehouse_id) as "Количество складов",
    s.name as "Продавец",
    CASE
        WHEN SUM(ws.stock_quantity) <= 10 THEN 'КРИТИЧЕСКИ НИЗКИЙ'
        WHEN SUM(ws.stock_quantity) <= 25 THEN 'НИЗКИЙ'
        WHEN SUM(ws.stock_quantity) <= 50 THEN 'СРЕДНИЙ'
        ELSE 'НОРМАЛЬНЫЙ'
    END as "Уровень запаса"
FROM products p
JOIN goods g ON p.goods_id = g.goods_id
JOIN sellers s ON p.seller_id = s.seller_id
LEFT JOIN warehouse_stock ws ON p.product_id = ws.product_id
GROUP BY p.product_id, g.name, s.name
HAVING SUM(ws.stock_quantity) <= 50
ORDER BY "Общий остаток" ASC;

9.2. Анализ продавцов

9.2.1. Топ продавцов по количеству товаров

CREATE OR ALTER VIEW SELLERS_TOP_BY_PRODUCTS
AS
SELECT
    s.seller_id,
    s.name as "Продавец",
    s.email,
    COUNT(p.product_id) as "Количество товаров",
    SUM(COALESCE(ws.stock_quantity, 0)) as "Общий запас",
    count(distinct COALESCE(ws.WAREHOUSE_ID,0)) as "Количество складов",
    ROUND(AVG(p.end_price),2) as "Средняя цена товара",
    ROUND(MIN(p.end_price),2) as "Минимальная цена",
    ROUND(MAX(p.end_price),2) as "Максимальная цена"
FROM sellers s
LEFT JOIN products p ON s.seller_id = p.seller_id
LEFT JOIN warehouse_stock ws ON p.product_id = ws.product_id
GROUP BY s.seller_id, s.name, s.email
ORDER BY "Количество товаров" DESC;

9.2.2. Продавцы с товарами на конкретном складе

CREATE OR ALTER VIEW SELLERS_AT_WAREHOUSE
AS
SELECT
    w.warehouse_id,
    a.address as "Адрес склада",
    s.name as "Продавец",
    COUNT(DISTINCT p.product_id) as "Товаров на складе",
    SUM(ws.stock_quantity) as "Общее количество",
    SUM(ws.stock_quantity * p.end_price) as "Общая стоимость"
FROM warehouses w
JOIN addresses a ON w.address_id = a.address_id
JOIN warehouse_stock ws ON w.warehouse_id = ws.warehouse_id
JOIN products p ON ws.product_id = p.product_id
JOIN sellers s ON p.seller_id = s.seller_id
GROUP BY w.warehouse_id, a.address, s.name
ORDER BY w.warehouse_id, "Общая стоимость" DESC;

Вызов:

select * from SELLERS_AT_WAREHOUSE where warehouse_id = 3

9.3. Анализ складов

9.3.1. Загрузка складов

CREATE OR ALTER VIEW WAREHOUSES_WORKLOAD
AS
SELECT
    w.warehouse_id,
    a.address as "Адрес",
    w.capacity as "Вместимость",
    w.current_stock as "Текущий запас",
    ROUND(w.current_stock * 100.0 / w.capacity, 2) as "Загрузка %",
    COUNT(DISTINCT ws.product_id) as "Уникальных товаров",
    SUM(ws.stock_quantity) as "Всего единиц",
    AVG(ws.stock_quantity) as "Средний запас на товар"
FROM warehouses w
JOIN addresses a ON w.address_id = a.address_id
LEFT JOIN warehouse_stock ws ON w.warehouse_id = ws.warehouse_id
GROUP BY w.warehouse_id, a.address, w.capacity, w.current_stock
ORDER BY "Загрузка %" DESC;

9.3.2. Распределение товаров по складам

CREATE OR ALTER VIEW GOODS_AT_WAREHOUSES AS
SELECT
    g.name as "Товар",
    SUM(ws.stock_quantity) as "Общее количество",
    COUNT(DISTINCT ws.warehouse_id) as "Количество складов",
    LIST(
        'Склад ' || w.warehouse_id || ': ' || ws.stock_quantity || ' ед.',
        ', '
    ) as "Распределение по складам"
FROM goods g
JOIN products p ON g.goods_id = p.goods_id
JOIN warehouse_stock ws ON p.product_id = ws.product_id
JOIN warehouses w ON ws.warehouse_id = w.warehouse_id
GROUP BY g.name, g.goods_id
HAVING COUNT(DISTINCT ws.warehouse_id) > 1
ORDER BY "Общее количество" DESC;

9.4. Оптимизационные запросы

9.4.1. Товары, которые есть только на одном складе (риск потери доступности)

CREATE OR ALTER VIEW SINGLE_WAREHOUSE_GOODS AS
SELECT
    p.product_id,
    g.name as "Товар",
    w.warehouse_id as "Единственный склад",
    ws.stock_quantity as "Количество",
    a.address as "Адрес склада",
    s.name as "Продавец"
FROM products p
JOIN goods g ON p.goods_id = g.goods_id
JOIN warehouse_stock ws ON p.product_id = ws.product_id
JOIN warehouses w ON ws.warehouse_id = w.warehouse_id
JOIN addresses a ON w.address_id = a.address_id
JOIN sellers s ON p.seller_id = s.seller_id
WHERE p.product_id IN (
    SELECT product_id
    FROM warehouse_stock
    GROUP BY product_id
    HAVING COUNT(DISTINCT warehouse_id) = 1
    )
ORDER BY ws.stock_quantity ASC;

9.4.2. Склады с потенциальной проблемой емкости

CREATE OR ALTER VIEW WAREHOUSE_CAPACITY_ISSUES
AS
SELECT
    w.warehouse_id,
    a.address,
    w.capacity,
    w.current_stock,
    ROUND(w.current_stock * 100.0 / w.capacity, 2) as "Текущая загрузка %",
    -- Прогноз загрузки при увеличении запасов на 20%
    ROUND((w.current_stock * 1.2) * 100.0 / w.capacity, 2) as "Прогноз +20%",
    -- Количество товаров с низким запасом на этом складе
    (
        SELECT COUNT(*)
        FROM warehouse_stock ws2
        WHERE ws2.warehouse_id = w.warehouse_id
        AND ws2.stock_quantity < 10
    ) as "Товаров с низким запасом"
FROM warehouses w
JOIN addresses a ON w.address_id = a.address_id
WHERE w.current_stock > w.capacity * 0.8  -- Более 80% загружено
ORDER BY "Текущая загрузка %" DESC;

9.5. Финансовые запросы

9.5.1. Стоимость товаров на каждом складе

CREATE OR ALTER VIEW WAREHOUSE_GOODS_COST AS
SELECT
    w.warehouse_id,
    a.address as "Адрес склада",
    COUNT(DISTINCT ws.product_id) as "Количество товаров",
    SUM(ws.stock_quantity) as "Всего единиц",
    SUM(ws.stock_quantity * p.end_price) as "Общая стоимость",
    ROUND(AVG(p.end_price), 2) as "Средняя цена за единицу",
    MAX(p.end_price) as "Самый дорогой товар",
    MIN(p.end_price) as "Самый дешевый товар"
FROM warehouses w
JOIN addresses a ON w.address_id = a.address_id
JOIN warehouse_stock ws ON w.warehouse_id = ws.warehouse_id
JOIN products p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, a.address
ORDER BY "Общая стоимость" DESC;

9.5.2. Анализ маржи по товарам

CREATE OR ALTER VIEW PRODUCT_MARGINS
AS
SELECT
    g.name as "Товар",
    p.price as "Цена продажи",
    -- Предположим, что закупочная цена = 70% от цены продажи
    (p.price * 0.7) as "Предполагаемая закупка",
    p.discount_percent as "Скидка %",
    p.end_price as "Конечная цена",
    (p.end_price - (p.price * 0.7)) as "Маржа",
    ROUND((p.end_price - (p.price * 0.7)) / p.end_price * 100, 2) as "Маржинальность %",
    SUM(ws.stock_quantity) as "Остаток",
    SUM(ws.stock_quantity * (p.end_price - (p.price * 0.7))) as "Потенциальная прибыль"
FROM goods g
JOIN products p ON g.goods_id = p.goods_id
LEFT JOIN warehouse_stock ws ON p.product_id = ws.product_id
GROUP BY g.name, p.price, p.discount_percent, p.end_price
ORDER BY "Потенциальная прибыль" DESC;

9.6. Практические запросы для оперативной работы

9.6.1. Поиск товара по названию и проверка наличия

CREATE OR ALTER VIEW GOODS_AVAILABILITY
AS
SELECT
    g.name as "Название",
    g.description as "Описание",
    p.product_id,
    p.end_price as "Цена",
    SUM(ws.stock_quantity) as "Общее наличие",
    COUNT(DISTINCT w.warehouse_id) as "Количество складов",
    LIST(
    'Склад ' || w.warehouse_id || ' (' || a.address || '): ' || ws.stock_quantity || ' шт.',
    '; '
    ) as "Наличие на складах",
    s.name as "Продавец",
    s.contact_info as "Контакты продавца"
FROM goods g
JOIN products p ON g.goods_id = p.goods_id
LEFT JOIN warehouse_stock ws ON p.product_id = ws.product_id
LEFT JOIN warehouses w ON ws.warehouse_id = w.warehouse_id
LEFT JOIN addresses a ON w.address_id = a.address_id
JOIN sellers s ON p.seller_id = s.seller_id
GROUP BY g.name, g.description, p.product_id, p.end_price, s.name, s.contact_info
ORDER BY "Общее наличие" DESC;

Применение

select * from GOODS_AVAILABILITY where UPPER("Название") LIKE '%ТЕСТ%';

select * from GOODS_AVAILABILITY where PRODUCT_ID = 23;

9.6.2. Анализ товарных остатков для планирования закупок

-- Шаг 1: дневные продажи по каждому товару
WITH daily_product_sales AS (
    SELECT
        oi.product_id,
        o.order_date,
        SUM(oi.quantity) AS daily_sales   --
    FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    GROUP BY oi.product_id, o.order_date
),
-- Шаг 2: средние продажи в день для каждого товара
avg_product_sales AS (
    SELECT
        product_id,
        AVG(1.0 * daily_sales) AS avg_daily_sales
    FROM daily_product_sales
    GROUP BY product_id
)
SELECT
    g.name AS "Товар",
    SUM(ws.stock_quantity) AS "Текущий остаток",
    COALESCE(av.avg_daily_sales, 0) AS "Средние продажи в день",
    CASE
        WHEN av.avg_daily_sales > 0
        THEN ROUND(SUM(ws.stock_quantity) / av.avg_daily_sales, 1)
        ELSE 999
    END AS "Дней остатка",
    s.name AS "Поставщик"
FROM goods g
JOIN products p ON g.goods_id = p.goods_id
JOIN sellers s ON p.seller_id = s.seller_id
LEFT JOIN warehouse_stock ws ON p.product_id = ws.product_id
LEFT JOIN avg_product_sales av ON p.product_id = av.product_id
GROUP BY
    g.name,
    p.product_id,
    s.name,
    av.avg_daily_sales
HAVING SUM(ws.stock_quantity) > 0
ORDER BY "Дней остатка" ASC
--OPTIMIZE FOR FIRST ROWS;

Эти запросы покрывают различные аспекты работы с товарами и складами: от оперативного контроля до стратегического анализа. Вы можете адаптировать их под конкретные бизнес-потребности вашего маркетплейса.

В демо-базе могут присутствовать другие представления, не описанные на данной странице, но предназначенные или пригодные для решения различных задач анализа, планирования и оптимизации работы электронного магазина.