✅ Решения

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

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

Задание 1 — MAX(list_price) OVER ().
Задание 2 — разность делим на максимум и умножаем на 100.
Задание 3 — COUNT(id) OVER (PARTITION BY category).
Задание 4 — standard_cost - AVG(list_price) OVER ().
Задание 5 — CTE со средним и JOIN на 1=1.
Задание 6 — SUM(order_amount) OVER (ORDER BY order_date).

Решение задания 1. Максимальная цена

Пустое окно OVER () берёт все строки таблицы, поэтому MAX(list_price) повторяется в каждой строке.

SELECT
    product_name,
    list_price,
    MAX(list_price) OVER () AS max_list_price
FROM products;

Решение задания 2. Процент отклонения

Вычитаем текущую цену из максимума, делим на максимум и переводим в проценты. Умножение на 100.0 помогает избежать целочисленного деления в некоторых СУБД.

SELECT
    product_name,
    list_price,
    (MAX(list_price) OVER () - list_price)
        / MAX(list_price) OVER () * 100.0 AS pct_below_max
FROM products;

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

Оконная функция работает, но если результат нужен в виде «одна строка на категорию», удобнее GROUP BY.

-- Оконная функция: сохраняем все строки
SELECT
    category,
    product_name,
    COUNT(id) OVER (PARTITION BY category) AS products_in_category
FROM products;

-- GROUP BY: сворачиваем до категорий
SELECT
    category,
    COUNT(id) AS products_in_category
FROM products
GROUP BY category;

Решение задания 4. Отклонение от средней цены

Среднее по всей таблице считаем в пустом окне и вычитаем из себестоимости каждого продукта.

SELECT
    product_name,
    standard_cost,
    AVG(list_price) OVER () AS avg_list_price,
    standard_cost - AVG(list_price) OVER () AS cost_vs_avg
FROM products;

Решение задания 5. Без оконной функции

Посчитайте среднее в CTE, затем присоедините к каждой строке products. Условие ON 1=1 создаёт декартово произведение с одной строкой CTE.

WITH avg_price AS (
    SELECT AVG(list_price) AS avg_list_price
    FROM products
)
SELECT
    p.product_name,
    p.standard_cost,
    p.standard_cost - ap.avg_list_price AS cost_vs_avg
FROM products p
JOIN avg_price ap ON 1 = 1;

Решение задания 6. Кумулятивная сумма

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

SELECT
    order_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) AS cumulative_total
FROM orders;