✅ Решения

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

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

Задание 1 — MIN/MAX/AVG(unit_price) OVER (PARTITION BY order_id).
Задание 2 — добавить DISTINCT к запросу из задания 1.
Задание 3 — SUM(quantity*unit_price) OVER (PARTITION BY product_id) и GROUP BY product_id.
Задание 4 — COUNT(product_id) OVER (PARTITION BY order_id, status_id) в purchase_order_details.
Задание 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 по заказу

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

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 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 order_details;

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

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

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

-- GROUP BY: одна строка на продукт
SELECT
    product_id,
    SUM(quantity * unit_price) AS product_total
FROM order_details
GROUP BY product_id;

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

Работаем с таблицей purchase_order_details. Окно разбивается по паре order_id и status_id.

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

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

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

SELECT
    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,
    quantity * unit_cost AS line_cost,
    SUM(quantity * unit_cost) OVER (
        PARTITION BY product_id
        ORDER BY date_received
    ) AS cumulative_revenue
FROM purchase_order_details;