LAG и LEAD: анализ изменений во времени

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

LAG: доступ к предыдущим значениям

LAG(column, offset, default) возвращает значение из строки, находящейся на offset позиций выше текущей в рамках заданного порядка.

Параметры: - column — столбец, значение которого нужно получить - offset — количество строк назад (по умолчанию 1) - default — значение, возвращаемое если предыдущей строки нет (по умолчанию NULL)

Пример: Сравним продажи текущего месяца с предыдущим:

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_sales
ORDER BY month;

Для первой строки предыдущего месяца нет, поэтому LAG возвращает NULL. Начиная со второй строки можно рассчитать абсолютное и относительное изменение.

Расчет процентного прироста:

SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / 
        LAG(revenue) OVER (ORDER BY month), 
        2
    ) AS growth_percent
FROM monthly_sales;

Этот запрос показывает темп роста выручки в процентах месяц к месяцу — ключевую метрику для мониторинга динамики бизнеса.

LEAD: доступ к следующим значениям

LEAD(column, offset, default) работает аналогично LAG, но возвращает значение из строки, находящейся на offset позиций ниже текущей.

Пример: Определим интервал между последовательными событиями пользователя:

SELECT 
    user_id,
    event_time,
    event_type,
    LEAD(event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ) AS next_event_time,
    LEAD(event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ) - event_time AS time_to_next_event
FROM user_events
ORDER BY user_id, event_time;

Для последнего события каждого пользователя LEAD возвращает NULL. Такой анализ помогает понять поведенческие паттерны: как быстро пользователи переходят от просмотра к покупке, где происходят задержки.

Анализ изменений во времени

Комбинация LAG/LEAD с PARTITION BY позволяет анализировать изменения внутри групп — по пользователям, категориям, регионам.

Пример: Найдем пользователей, у которых активность снизилась (меньше сессий в текущем месяце по сравнению с предыдущим):

WITH monthly_activity AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', session_date) AS month,
        COUNT(*) AS session_count
    FROM sessions
    GROUP BY user_id, DATE_TRUNC('month', session_date)
)
SELECT 
    user_id,
    month,
    session_count,
    LAG(session_count) OVER (
        PARTITION BY user_id 
        ORDER BY month
    ) AS prev_month_sessions,
    session_count - LAG(session_count) OVER (
        PARTITION BY user_id 
        ORDER BY month
    ) AS session_change
FROM monthly_activity
WHERE session_count < LAG(session_count) OVER (
    PARTITION BY user_id 
    ORDER BY month
)
ORDER BY user_id, month;

Этот запрос выявляет пользователей с падающей активностью — потенциальных кандидатов на отток, требующих внимания retention-команды.

Расчет дельт для метрик продукта

Пример: Анализ изменения DAU (Daily Active Users):

SELECT 
    date,
    dau,
    LAG(dau, 1) OVER (ORDER BY date) AS dau_yesterday,
    dau - LAG(dau, 1) OVER (ORDER BY date) AS dau_change,
    LAG(dau, 7) OVER (ORDER BY date) AS dau_week_ago,
    dau - LAG(dau, 7) OVER (ORDER BY date) AS dau_change_wow
FROM daily_active_users
ORDER BY date DESC
LIMIT 30;

Этот запрос показывает изменение DAU день к дню и неделя к неделе (Week-over-Week), что помогает отличить случайные колебания от устойчивых трендов.

Выявление первого и последнего события

Используя LAG и LEAD с PARTITION BY, можно определить граничные события в последовательности:

SELECT 
    user_id,
    event_time,
    event_type,
    CASE 
        WHEN LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL 
        THEN 'first_event'
        WHEN LEAD(event_type) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL 
        THEN 'last_event'
        ELSE 'middle_event'
    END AS event_position
FROM user_events;

Это полезно для анализа воронок: какие действия пользователи совершают первыми, на каком этапе чаще всего заканчивают сессию.

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

• LAG() извлекает значения из предыдущих строк, LEAD() — из следующих, что устраняет необходимость в сложных self-join.
• Расчет дельт (абсолютных и относительных изменений) — основное применение для анализа динамики метрик во времени.
• PARTITION BY с LAG/LEAD позволяет анализировать изменения внутри групп (пользователей, категорий, когорт).
• Эти функции критически важны для работы с временными рядами: выявления трендов, аномалий, расчета темпов роста и анализа пользовательских путей.