🏠 Домашнее задание

Условия

Для выполнения домашнего задания используется база данных с доступом на чтение:

• hostname: ich-db.edu.itcareerhub.de
• MYSQL_USER: ich1
• MYSQL_PASSWORD: ich1_password_ilovedbs

Рабочая таблица: purchase_order_details.

1. Для каждого заказа order_id выведите минимальный, максимальный и средний unit_cost.
2. Оставьте только уникальные строки из предыдущего запроса.
3. Посчитайте стоимость продукта в заказе как quantity * unit_cost. Выведите суммарную стоимость продуктов с помощью оконной функции. Сделайте то же самое с помощью GROUP BY.
4. Посчитайте количество заказов по дате получения date_received и posted_to_inventory. Если оно превышает 1, то выведите '>1', в противном случае '=1'. Выведите purchase_order_id, date_received и вычисленный столбец.

Задача 1. MIN, MAX и AVG unit_cost по order_id

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

SELECT
    order_id,
    MIN(unit_cost) OVER (PARTITION BY order_id) AS min_unit_cost,
    MAX(unit_cost) OVER (PARTITION BY order_id) AS max_unit_cost,
    AVG(unit_cost) OVER (PARTITION BY order_id) AS avg_unit_cost
FROM purchase_order_details;

Задача 2. Только уникальные строки

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

SELECT DISTINCT
    order_id,
    MIN(unit_cost) OVER (PARTITION BY order_id) AS min_unit_cost,
    MAX(unit_cost) OVER (PARTITION BY order_id) AS max_unit_cost,
    AVG(unit_cost) OVER (PARTITION BY order_id) AS avg_unit_cost
FROM purchase_order_details;

Задача 3. Суммарная стоимость заказа

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

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

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

Задача 4. Флаг количества заказов по дате и статусу

Считаем количество строк в окне по паре date_received и posted_to_inventory и превращаем его в текстовый флаг.

SELECT
    purchase_order_id,
    date_received,
    CASE
        WHEN COUNT(*) OVER (PARTITION BY date_received, posted_to_inventory) > 1
            THEN '>1'
        ELSE '=1'
    END AS order_count_flag
FROM purchase_order_details;