PrepBro
← Назад к вопросам

Как работает LAG и LEAD в SQL?

2.0 Middle🔥 171 комментариев
#SQL и базы данных

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI26 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

LAG и LEAD: Window Functions для Доступа к Соседним Строкам

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

Синтаксис

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

Параметры:

  • column: Колонка, из которой берётся значение
  • offset: Сколько строк назад/вперёд (по умолчанию 1)
  • default_value: Значение, если нет соседней строки (по умолчанию NULL)

LAG — Получить Значение из Предыдущей Строки

SELECT
    DATE(created_at) as date,
    revenue,
    LAG(revenue) OVER (ORDER BY created_at) as prev_day_revenue
FROM daily_sales
ORDER BY date;

Результат:

date       | revenue | prev_day_revenue
2025-01-01 | 1000    | NULL             <- Нет предыдущего дня
2025-01-02 | 1200    | 1000             <- Берёшь из 1 января
2025-01-03 | 1100    | 1200             <- Берёшь из 2 января
2025-01-04 | 1500    | 1100             <- Берёшь из 3 января

LEAD — Получить Значение из Следующей Строки

SELECT
    DATE(created_at) as date,
    revenue,
    LEAD(revenue) OVER (ORDER BY created_at) as next_day_revenue
FROM daily_sales
ORDER BY date;

Результат:

date       | revenue | next_day_revenue
2025-01-01 | 1000    | 1200             <- Будет 2 января
2025-01-02 | 1200    | 1100             <- Будет 3 января
2025-01-03 | 1100    | 1500             <- Будет 4 января
2025-01-04 | 1500    | NULL             <- Нет следующего дня

Практические Примеры

1. Вычисли День-к-Дню Рост (Day over Day Growth)

WITH sales_with_lag AS (
  SELECT
    DATE(created_at) as date,
    SUM(amount) as daily_revenue,
    LAG(SUM(amount)) OVER (ORDER BY DATE(created_at)) as prev_day_revenue
  FROM sales
  GROUP BY DATE(created_at)
)
SELECT
  date,
  daily_revenue,
  prev_day_revenue,
  ROUND(daily_revenue - prev_day_revenue, 2) as growth_amount,
  ROUND(
    100.0 * (daily_revenue - prev_day_revenue) / prev_day_revenue,
    2
  ) as growth_percent
FROM sales_with_lag
ORDER BY date;

2. Временная Разница Между Покупками (Customer Lifecycle)

WITH purchases AS (
  SELECT
    user_id,
    order_id,
    created_at,
    LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) as prev_purchase_date,
    LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) as next_purchase_date
  FROM orders
)
SELECT
  user_id,
  order_id,
  created_at,
  EXTRACT(DAY FROM created_at - prev_purchase_date) as days_since_last_purchase,
  EXTRACT(DAY FROM next_purchase_date - created_at) as days_to_next_purchase
FROM purchases
WHERE prev_purchase_date IS NOT NULL;

Это позволяет найти пользователей, которые долго не делали покупок (>30 дней).

3. Обнаружи Аномалии в Данных

WITH price_changes AS (
  SELECT
    product_id,
    DATE(created_at) as date,
    price,
    LAG(price) OVER (PARTITION BY product_id ORDER BY created_at) as prev_price,
    LAG(price) OVER (PARTITION BY product_id ORDER BY created_at DESC) as next_price
  FROM price_history
)
SELECT
  product_id,
  date,
  price,
  prev_price,
  ABS(price - prev_price) as price_change
FROM price_changes
WHERE prev_price IS NOT NULL
  AND ABS(price - prev_price) > price * 0.1  -- Больше 10% изменение
ORDER BY price_change DESC;

4. Найди Последовательности (Runs)

Кто зашёл 3 дня подряд?

WITH daily_logins AS (
  SELECT
    user_id,
    DATE(login_time) as login_date,
    LAG(DATE(login_time)) OVER (PARTITION BY user_id ORDER BY login_time) as prev_login_date,
    LEAD(DATE(login_time)) OVER (PARTITION BY user_id ORDER BY login_time) as next_login_date
  FROM login_logs
)
SELECT
  user_id,
  login_date,
  -- Проверь: сегодня, вчера, завтра последовательные дни
  CASE
    WHEN prev_login_date = login_date - INTERVAL 1 day 
     AND next_login_date = login_date + INTERVAL 1 day THEN 'YES'
    ELSE 'NO'
  END as is_part_of_streak
FROM daily_logins;

5. Статистика Временных Рядов

WITH metrics AS (
  SELECT
    DATE(created_at) as date,
    COUNT(*) as num_events,
    AVG(value) as avg_value,
    LAG(COUNT(*)) OVER (ORDER BY DATE(created_at)) as prev_events,
    LAG(AVG(value)) OVER (ORDER BY DATE(created_at)) as prev_avg_value
  FROM events
  GROUP BY DATE(created_at)
)
SELECT
  date,
  num_events,
  avg_value,
  num_events - prev_events as event_change,
  ROUND(
    100.0 * (num_events - prev_events) / prev_events,
    2
  ) as percent_change,
  ROUND(avg_value - prev_avg_value, 4) as metric_change
FROM metrics
WHERE prev_events IS NOT NULL
ORDER BY date;

PARTITION BY — Ленты внутри Групп

Каждый пользователь имеет свою собственную последовательность LAG/LEAD:

SELECT
    user_id,
    DATE(created_at) as date,
    amount,
    LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) as prev_amount,
    amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) as user_amount_change
FROM transactions
ORDER BY user_id, date;

Offset > 1

Получи значение не из соседней строки, а на несколько строк дальше:

SELECT
    DATE(created_at) as date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY created_at) as prev_1_day,
    LAG(revenue, 7) OVER (ORDER BY created_at) as prev_7_days,
    LAG(revenue, 30) OVER (ORDER BY created_at) as prev_30_days
FROM daily_sales
ORDER BY date;

Это позволяет сравнивать с неделю назад, месяц назад и т.д.

Default Values

Заполни NULL значения дефолтом:

SELECT
    DATE(created_at) as date,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY created_at) as prev_day_revenue,  -- 0 вместо NULL
    revenue - LAG(revenue, 1, revenue) OVER (ORDER BY created_at) as growth
FROM daily_sales;

Комбинация LAG/LEAD с Другими Functions

Вычисли 7-дневный Moving Average:

WITH lagged_values AS (
  SELECT
    DATE(created_at) as date,
    revenue,
    LAG(revenue, 0) OVER (ORDER BY created_at) as val_0,
    LAG(revenue, 1) OVER (ORDER BY created_at) as val_1,
    LAG(revenue, 2) OVER (ORDER BY created_at) as val_2,
    LAG(revenue, 3) OVER (ORDER BY created_at) as val_3,
    LAG(revenue, 4) OVER (ORDER BY created_at) as val_4,
    LAG(revenue, 5) OVER (ORDER BY created_at) as val_5,
    LAG(revenue, 6) OVER (ORDER BY created_at) as val_6
  FROM daily_sales
)
SELECT
  date,
  revenue,
  ROUND(
    (COALESCE(val_0, 0) + COALESCE(val_1, 0) + COALESCE(val_2, 0) +
     COALESCE(val_3, 0) + COALESCE(val_4, 0) + COALESCE(val_5, 0) +
     COALESCE(val_6, 0)) / 7.0,
    2
  ) as moving_avg_7
FROM lagged_values
ORDER BY date;

(Или проще: используй AVG() с ROWS BETWEEN)

Performance Tips

  1. PARTITION BY уменьшает объём данных:

    -- Медленнее: сравниваешь с всеми днями
LAG(revenue) OVER (ORDER BY created_at)

-- Быстрее: сравниваешь только в рамках категории
LAG(revenue) OVER (PARTITION BY category ORDER BY created_at)

  2. Ограничь данные в WHERE:

    WHERE created_at >= CURRENT_DATE - INTERVAL 365 day

  3. Используй индексы на ORDER BY колонках

Ключевые Выводы

  • LAG: Предыдущее значение (сравнение с прошлым)
  • LEAD: Следующее значение (заглядывание в будущее)
  • PARTITION BY: Разделяй последовательность по группам
  • offset: Можно брать не соседнюю строку, а на N строк дальше
  • Применение: Тренды, прирост, обнаружение аномалий, последовательности

LAG и LEAD — основные инструменты для анализа временных рядов и Cohort Analysis. Используй их часто.

Как работает LAG и LEAD в SQL? | PrepBro