LAG и LEAD в SQL: шпаргалка

Что делают LAG и LEAD

• LAG(col) — значение предыдущей строки
• LEAD(col) — значение следующей строки

Оконные функции, «смотрящие» вперёд или назад в окне.

Синтаксис

LAG(column [, OFFSET [, default]]) OVER (
    PARTITION BY group_col
    ORDER BY sort_col
)

LEAD(column [, OFFSET [, default]]) OVER (...)

Параметры:

• column — значение для «взгляда»
• offset — на сколько строк смотреть (по умолчанию 1)
• default — что вернуть, если строки нет (NULL по умолчанию)

1. Разница между соседними записями

Доход по дням и дельта к предыдущему дню:

SELECT
    day,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY day) AS delta_vs_prev
FROM daily_revenue;

2. % рост (growth rate)

SELECT
    day,
    revenue,
    (revenue - LAG(revenue) OVER (ORDER BY day)) * 100.0
        / LAG(revenue) OVER (ORDER BY day) AS growth_pct
FROM daily_revenue;

3. Предыдущий заказ пользователя

SELECT
    user_id,
    order_id,
    created_at,
    LAG(created_at) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS prev_order_at
FROM orders;

4. Время между заказами

SELECT
    user_id,
    order_id,
    created_at,
    EXTRACT(EPOCH FROM created_at
        - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
    ) / 86400 AS days_since_prev_order
FROM orders;

5. Следующий заказ (LEAD)

SELECT
    user_id,
    order_id,
    created_at,
    LEAD(created_at) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS next_order_at
FROM orders;

6. Сравнение с N дней назад

-- LAG со смещением 7 — неделя назад
SELECT
    day,
    revenue,
    LAG(revenue, 7) OVER (ORDER BY day) AS revenue_week_ago,
    revenue - LAG(revenue, 7) OVER (ORDER BY day) AS wow_diff
FROM daily_revenue;

7. Default-значение вместо NULL

Для первой строки LAG() возвращает NULL. Можно указать default:

LAG(revenue, 1, 0) OVER (ORDER BY day)

Первая строка получит 0, не NULL.

8. Определение начала сессии

Если между событиями > 30 минут — новая сессия.

WITH events_marked AS (
    SELECT
        user_id,
        event_at,
        CASE
            WHEN LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at)
                 < event_at - INTERVAL '30 minutes'
              OR LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) IS NULL
            THEN 1 ELSE 0
        END AS is_new_session
    FROM events
)
SELECT
    user_id,
    event_at,
    SUM(is_new_session) OVER (
        PARTITION BY user_id ORDER BY event_at
    ) AS session_id
FROM events_marked;

9. Первый и последний заказ через LAG/LEAD

SELECT
    user_id,
    order_id,
    CASE
        WHEN LAG(order_id) OVER (PARTITION BY user_id ORDER BY created_at) IS NULL
        THEN 'first'
        WHEN LEAD(order_id) OVER (PARTITION BY user_id ORDER BY created_at) IS NULL
        THEN 'last'
        ELSE 'middle'
    END AS order_position
FROM orders;

10. Fill forward (заполнить пропуски последним значением)

-- если нет значения в строке, взять предыдущее
SELECT
    day,
    COALESCE(
        value,
        LAG(value) IGNORE NULLS OVER (ORDER BY day)
    ) AS filled_value
FROM measurements;

(IGNORE NULLS доступно в BigQuery, Snowflake; в Postgres — обходной путь через first_value).

Частые ошибки

Ошибка 1. Забыли PARTITION BY

-- считает предыдущий заказ по ВСЕМ пользователям вперемешку
LAG(created_at) OVER (ORDER BY created_at)

-- правильно
LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)

Ошибка 2. LAG без ORDER BY

Без ORDER BY результат недетерминирован — порядок строк непредсказуем.

Ошибка 3. Деление на ноль в growth_pct

-- если revenue = 0 в предыдущем дне — деление на ноль
(revenue - LAG(revenue) ...) / LAG(revenue) ...

-- безопаснее через NULLIF
(revenue - LAG(revenue) ...) / NULLIF(LAG(revenue) ..., 0)

Ошибка 4. LAG для первой строки

Первая строка не имеет предыдущей — LAG возвращает NULL. Учитывайте:

COALESCE(LAG(revenue) OVER (...), 0)

LAG vs FIRST_VALUE / LAST_VALUE

Разные цели:

• LAG — предыдущая строка
• FIRST_VALUE — первая в окне
• LAST_VALUE — последняя в окне

-- первый и последний заказ пользователя в одной строке
SELECT DISTINCT
    user_id,
    FIRST_VALUE(created_at) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS first_order,
    LAST_VALUE(created_at) OVER (
        PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

Производительность

• LAG/LEAD — встроенные функции, быстрые
• PARTITION BY на большой группе — может быть тяжело
• Индекс на (partition_col, order_col) сильно ускоряет

Связанные темы

• Оконные функции SQL — шпаргалка
• PARTITION BY — шпаргалка
• ROW_NUMBER vs RANK — шпаргалка
• Задачи на оконные функции

FAQ

Чем LAG отличается от LEAD?

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

Можно ли LAG в агрегате?

LAG — оконная функция, работает без GROUP BY. В агрегате обычной функцией — нет.

Работает ли LAG в MySQL?

Да, с MySQL 8.0+. В более старых версиях оконных функций нет, используйте self-join или переменные.

LAG на первой строке возвращает NULL. Как задать default?

Третий аргумент: LAG(col, 1, 0) — для первой строки вернётся 0.

Можно ли LAG с NULL в ORDER BY?

Да, но будьте аккуратны с сортировкой NULL — используйте NULLS FIRST / LAST.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.