✅ Решения

📁 Блок: SQL / MySQL / Оконные функции ⏱️ Время изучения: ~60 мин 🎯 Сложность: Повторение

⚡ Кратко: решения

Задание 1 — MIN/MAX/AVG(unit_price) OVER (PARTITION BY order_id).
Задание 2 — добавить DISTINCT к запросу из задания 1.
Задание 3 — SUM(quantity * unit_price) OVER (PARTITION BY order_id) и GROUP BY order_id.
Задание 4 — COUNT(*) OVER (PARTITION BY order_id, posted_to_inventory).
Задание 5 — SUM(quantity) OVER (ORDER BY date_received).
Задание 6 — SUM(quantity * unit_cost) OVER (PARTITION BY product_id ORDER BY date_received).

Решение задания 1. MIN, MAX и AVG unit_price по order_id

Оконные функции считают агрегат внутри каждого заказа, не сворачивая строки.

SELECT
    order_id,
    unit_price,
    MIN(unit_price) OVER (PARTITION BY order_id) AS min_unit_price,
    MAX(unit_price) OVER (PARTITION BY order_id) AS max_unit_price,
    AVG(unit_price) OVER (PARTITION BY order_id) AS avg_unit_price
FROM purchase_order_details;

Решение задания 2. Только уникальные строки

Так как оконные функции дублируют результат в каждую строку заказа, достаточно добавить DISTINCT.

SELECT DISTINCT
    order_id,
    MIN(unit_price) OVER (PARTITION BY order_id) AS min_unit_price,
    MAX(unit_price) OVER (PARTITION BY order_id) AS max_unit_price,
    AVG(unit_price) OVER (PARTITION BY order_id) AS avg_unit_price
FROM purchase_order_details;

Решение задания 3. Суммарная стоимость заказа

Сначала считаем стоимость строки, затем сумму по заказу окном. Вариант с GROUP BY даёт свёрнутый результат.

-- Оконная функция: сохраняем строки
SELECT
    order_id,
    product_id,
    quantity,
    unit_price,
    quantity * unit_price AS line_total,
    SUM(quantity * unit_price) OVER (PARTITION BY order_id) AS order_total
FROM purchase_order_details;

-- GROUP BY: одна строка на заказ
SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total
FROM purchase_order_details
GROUP BY order_id;

Решение задания 4. Количество продуктов с учётом статуса

Считаем строки в окне, разбитом по заказу и статусу инвентаризации.

SELECT
    order_id,
    posted_to_inventory,
    product_id,
    COUNT(*) OVER (PARTITION BY order_id, posted_to_inventory) AS products_count
FROM purchase_order_details;

Решение задания 5. Кумулятивное количество товаров

ORDER BY date_received в окне задаёт порядок накопления.

SELECT
    purchase_order_id,
    date_received,
    quantity,
    SUM(quantity) OVER (ORDER BY date_received) AS cumulative_quantity
FROM purchase_order_details;

Решение задания 6. Кумулятивная выручка по продукту

Комбинация PARTITION BY product_id и ORDER BY date_received накапливает выручку внутри каждого продукта.

SELECT
    product_id,
    date_received,
    quantity * unit_cost AS revenue,
    SUM(quantity * unit_cost) OVER (
        PARTITION BY product_id
        ORDER BY date_received
    ) AS cumulative_revenue
FROM purchase_order_details;