✅ Решения

📁 Блок: 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 без явной рамки окна.

✅ Решения и разбор

Задание 1

WITH orders_with_prev AS (
    SELECT
        supplier_id,
        creation_date,
        LAG(creation_date) OVER (
            PARTITION BY supplier_id
            ORDER BY creation_date
        ) AS previous_creation_date
    FROM purchase_orders
)
SELECT
    supplier_id,
    creation_date,
    previous_creation_date,
    DATEDIFF(creation_date, previous_creation_date) AS date_diff
FROM orders_with_prev;

Сначала получаем предыдущую дату внутри поставщика, затем считаем разницу. Так запрос читается проще, чем при повторении LAG дважды.

Задание 2

WITH intervals AS (
    SELECT
        supplier_id,
        creation_date,
        LAG(creation_date) OVER (
            PARTITION BY supplier_id
            ORDER BY creation_date
        ) AS previous_creation_date
    FROM purchase_orders
)
SELECT AVG(DATEDIFF(creation_date, previous_creation_date)) AS avg_date_diff
FROM intervals
WHERE previous_creation_date IS NOT NULL;

Среднее считается только по строкам, где предыдущий заказ существует.

Задание 3

WITH intervals AS (
    SELECT
        supplier_id,
        creation_date,
        LEAD(creation_date) OVER (
            PARTITION BY supplier_id
            ORDER BY creation_date
        ) AS next_creation_date
    FROM purchase_orders
)
SELECT AVG(DATEDIFF(next_creation_date, creation_date)) AS avg_date_diff
FROM intervals
WHERE next_creation_date IS NOT NULL;

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

Задание 4

SELECT
    created_by,
    submitted_date,
    MIN(submitted_date) OVER (
        PARTITION BY created_by
    ) AS min_submitted_date,
    FIRST_VALUE(submitted_date) OVER (
        PARTITION BY created_by
        ORDER BY submitted_date
    ) AS first_submitted_date
FROM purchase_orders;

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