📖 Теория

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

Что делают функции смещения

В источнике функции смещения и выбора описаны как оконные функции, которые зависят от значений в других строках в пределах заданного окна. Это удобно, когда нужно сравнить текущую строку с предыдущей, следующей или с крайним значением группы.

LAG(value) смотрит назад относительно текущей строки.
LEAD(value) смотрит вперёд относительно текущей строки.
FIRST_VALUE(value) берёт первое значение окна.
LAST_VALUE(value) берёт последнее значение окна.
NTH_VALUE(value, n) берёт n-е значение окна.

Общий синтаксис

Шаблон из источника:

SELECT column1,
       window_function(column2) OVER (
           PARTITION BY column3
           ORDER BY column4
       ) AS result_column
FROM table_name;

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

Без ORDER BY функции смещения теряют смысл: база данных не обязана хранить строки в естественном порядке.

LEAD и LAG

LAG() нужен для ответа на вопрос «что было до текущей строки?», а LEAD() — «что будет после текущей строки?».

SELECT
    supplier_id,
    creation_date,
    LAG(creation_date) OVER (
        PARTITION BY supplier_id
        ORDER BY creation_date
    ) AS previous_creation_date,
    LEAD(creation_date) OVER (
        PARTITION BY supplier_id
        ORDER BY creation_date
    ) AS next_creation_date
FROM purchase_orders;

Здесь окно строится отдельно для каждого supplier_id. Поэтому предыдущая дата не перескакивает к другому поставщику.

FIRST_VALUE, LAST_VALUE и NTH_VALUE

FIRST_VALUE() удобно использовать, когда нужно показать крайнее значение группы рядом с каждой строкой: например, первую цену месяца или минимальную цену заказа.

LAST_VALUE() и NTH_VALUE() чувствительны к рамке окна: если рамка заканчивается текущей строкой, «последним» может оказаться текущее значение.

Проверить по документации: в MySQL, PostgreSQL и SQLite поведение LAST_VALUE/NTH_VALUE зависит от window frame. Для «последнего значения всего раздела» обычно задают ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

SELECT
    order_id,
    unit_price,
    LAST_VALUE(unit_price) OVER (
        PARTITION BY order_id
        ORDER BY unit_price
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS max_price_in_order
FROM purchase_order_details;