💻 Примеры

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

💻 Выполнимые примеры

Предыдущий заказ поставщика

Запрос из практической части: дата заказа и предыдущая дата заказа для того же поставщика.

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 days_between_orders
FROM orders_with_prev;

Почему так: CTE нужен, чтобы не повторять оконную функцию внутри расчёта разницы. В первой строке каждого поставщика предыдущей даты нет, поэтому результат будет NULL.

Средний интервал между заказами

После расчёта интервалов можно усреднить только непустые разницы.

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_days_between_orders
FROM intervals
WHERE previous_creation_date IS NOT NULL;

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

Самая ранняя submitted_date двумя способами

Источник просит сравнить MIN и FIRST_VALUE.

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;

Почему так: MIN не требует сортировки, а FIRST_VALUE требует. При сортировке по дате по возрастанию оба способа дают самую раннюю дату.