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

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

⚡ Кратко: домашка

Функции смещения и выбора позволяют смотреть не только на текущую строку, но и на соседние или крайние строки внутри окна.

LAG() возвращает значение из предыдущей строки, а LEAD() — из следующей строки в порядке, заданном внутри OVER.

FIRST_VALUE(), LAST_VALUE() и NTH_VALUE() выбирают первое, последнее или n-е значение в окне.

Ключевая конструкция урока: function(column) OVER (PARTITION BY group_column ORDER BY sort_column).

Минимальный пример: LAG(creation_date) OVER (PARTITION BY supplier_id ORDER BY creation_date) показывает предыдущую дату заказа поставщика.

Топ-3 ошибки: забыть ORDER BY, перепутать направление LEAD/LAG, использовать LAST_VALUE без явной рамки окна.

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

Домашнее задание из источника выполняется на таблице purchase_order_details.

Для каждого product_id вывести inventory_id, предыдущий и последующий inventory_id по убыванию quantity.
Вывести максимальный и минимальный unit_price для каждого order_id с помощью FIRST_VALUE.
Вывести order_id и разницу между unit_price заказа и минимальным unit_price в рамках одного заказа двумя способами: через FIRST_VALUE и MIN.
Присвоить ранг каждой строке с помощью RANK по убыванию quantity.
Из предыдущего запроса выбрать только строки с рангом до 10 включительно.

Пошаговое решение и проверка

ДЗ 1

SELECT
    product_id,
    inventory_id,
    quantity,
    LAG(inventory_id) OVER (
        PARTITION BY product_id
        ORDER BY quantity DESC
    ) AS previous_inventory_id,
    LEAD(inventory_id) OVER (
        PARTITION BY product_id
        ORDER BY quantity DESC
    ) AS next_inventory_id
FROM purchase_order_details;

Окно делится по продукту, а порядок задаётся количеством по убыванию.

ДЗ 2

SELECT
    order_id,
    unit_price,
    FIRST_VALUE(unit_price) OVER (
        PARTITION BY order_id
        ORDER BY unit_price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_unit_price,
    FIRST_VALUE(unit_price) OVER (
        PARTITION BY order_id
        ORDER BY unit_price ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS min_unit_price
FROM purchase_order_details;

Один и тот же FIRST_VALUE даёт максимум или минимум в зависимости от сортировки.

ДЗ 3

SELECT
    order_id,
    unit_price,
    unit_price - FIRST_VALUE(unit_price) OVER (
        PARTITION BY order_id
        ORDER BY unit_price ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS diff_by_first_value,
    unit_price - MIN(unit_price) OVER (
        PARTITION BY order_id
    ) AS diff_by_min
FROM purchase_order_details;

Оба способа должны дать одинаковую разницу, потому что минимальная цена заказа одна и та же для всех строк заказа.

ДЗ 4-5

WITH ranked_details AS (
    SELECT
        *,
        RANK() OVER (
            ORDER BY quantity DESC
        ) AS quantity_rank
    FROM purchase_order_details
)
SELECT *
FROM ranked_details
WHERE quantity_rank <= 10;

CTE нужен, чтобы отфильтровать результат ранжирования во внешнем запросе.