LEAD и LAG в SQL: работа с соседними строками

LAG и LEAD — оконные функции, которые позволяют обращаться к значениям соседних строк без самосоединения (self-join). Незаменимы в анализе временных рядов, расчёте роста и поиска изменений.

Синтаксис

LAG(column, offset, default)  OVER (PARTITION BY ... ORDER BY ...)
LEAD(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

• column — значение из какого столбца брать
• offset — на сколько строк назад (LAG) или вперёд (LEAD). По умолчанию: 1
• default — что вернуть если строки нет (NULL по умолчанию)

Базовый пример

-- Продажи по дням
SELECT
    sale_date,
    revenue,
    LAG(revenue)  OVER (ORDER BY sale_date) AS prev_day_revenue,
    LEAD(revenue) OVER (ORDER BY sale_date) AS next_day_revenue
FROM daily_sales;

Расчёт роста (Growth)

SELECT
    sale_date,
    revenue,
    LAG(revenue) OVER (ORDER BY sale_date) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY sale_date))
        / NULLIF(LAG(revenue) OVER (ORDER BY sale_date), 0) * 100,
        1
    ) AS growth_pct
FROM daily_sales
ORDER BY sale_date;

NULLIF(prev, 0) защищает от деления на ноль, когда предыдущая выручка была нулевой.

PARTITION BY — рост по группам

-- Рост продаж по каждому менеджеру отдельно
SELECT
    manager_id,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY manager_id ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (PARTITION BY manager_id ORDER BY month) AS delta
FROM manager_sales;

Каждый менеджер нумеруется независимо — в первой строке каждого менеджера LAG вернёт NULL.

Offset > 1: данные через N строк

-- Сравнить с позапрошлым месяцем (MoM-2)
SELECT
    month,
    revenue,
    LAG(revenue, 2) OVER (ORDER BY month) AS two_months_ago
FROM monthly_revenue;

-- Год к году (если данные по месяцам — offset = 12)
SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS year_ago,
    ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 1) AS yoy_growth
FROM monthly_revenue;

Default значение для краевых строк

-- Вместо NULL для первой строки — 0
SELECT
    sale_date,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY sale_date) AS prev_revenue
FROM daily_sales;

Поиск изменений — находим переходы

-- Найти моменты смены статуса подписки
SELECT
    user_id,
    event_date,
    status,
    LAG(status) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_status
FROM subscription_events;

Затем фильтруем переходы:

SELECT user_id, event_date, prev_status, status AS new_status
FROM (
    SELECT
        user_id,
        event_date,
        status,
        LAG(status) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_status
    FROM subscription_events
) t
WHERE prev_status IS DISTINCT FROM status;
-- IS DISTINCT FROM корректно обрабатывает NULL

Время между событиями

-- Сколько времени прошло между посещениями сайта
SELECT
    user_id,
    visit_at,
    LAG(visit_at) OVER (PARTITION BY user_id ORDER BY visit_at) AS prev_visit,
    visit_at - LAG(visit_at) OVER (PARTITION BY user_id ORDER BY visit_at) AS gap
FROM page_visits
ORDER BY user_id, visit_at;

Поиск пропусков в последовательности

-- Найти пропуски в нумерованных заказах
SELECT
    order_id,
    LAG(order_id) OVER (ORDER BY order_id) AS prev_id,
    order_id - LAG(order_id) OVER (ORDER BY order_id) AS gap
FROM orders
WHERE order_id - LAG(order_id) OVER (ORDER BY order_id) > 1;
-- Нельзя WHERE с оконной функцией напрямую → нужен подзапрос:

SELECT order_id, prev_id, gap
FROM (
    SELECT
        order_id,
        LAG(order_id) OVER (ORDER BY order_id) AS prev_id,
        order_id - LAG(order_id) OVER (ORDER BY order_id) AS gap
    FROM orders
) t
WHERE gap > 1;

Первый и последний визит в периоде

-- Первое и последнее событие в сессии пользователя
SELECT
    session_id,
    event_type,
    event_at,
    FIRST_VALUE(event_type) OVER (PARTITION BY session_id ORDER BY event_at) AS first_event,
    LAST_VALUE(event_type) OVER (
        PARTITION BY session_id ORDER BY event_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_event
FROM session_events;

LAG vs self-JOIN

До появления оконных функций приходилось делать self-JOIN:

-- Старый способ (медленнее, сложнее)
SELECT a.sale_date, a.revenue, b.revenue AS prev_revenue
FROM daily_sales a
LEFT JOIN daily_sales b ON b.sale_date = a.sale_date - INTERVAL '1 day';

-- Современный способ (быстрее, читаемее)
SELECT sale_date, revenue,
       LAG(revenue) OVER (ORDER BY sale_date) AS prev_revenue
FROM daily_sales;

LAG/LEAD работают за один проход по таблице — это всегда быстрее self-JOIN.

Практический чеклист

Итог

LAG и LEAD — ключевые инструменты временного анализа:

• LAG смотрит назад, LEAD — вперёд
• PARTITION BY разделяет на независимые группы
• offset задаёт расстояние (по умолчанию 1)
• default заменяет NULL на краях (по умолчанию NULL)
• Всегда быстрее self-JOIN