SQL: Определить время между событиями (LAG/LEAD)

Решение: Определить время между событиями (LAG/LEAD)

Объяснение задачи

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

Синтаксис: LAG() Window Function

LAG() — функция, которая возвращает значение из предыдущей строки в окне. Идеальна для этой задачи:

SELECT 
    user_id,
    event_time,
    event_type,
    LAG(event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ) AS prev_event_time,
    EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ))) / 60 AS time_since_prev_event_minutes
FROM user_events
ORDER BY user_id, event_time;

Разбор синтаксиса

LAG(event_time) OVER (...) — получает значение event_time из предыдущей строки в окне.

PARTITION BY user_id — каждый пользователь рассматривается отдельно. Первое событие каждого пользователя не будет иметь предыдущего события (будет NULL).

ORDER BY event_time — упорядочиваем события по времени, чтобы LAG работал корректно.

EXTRACT(EPOCH FROM (event_time - LAG(...))) — вычисляет разницу в секундах между двумя timestamp'ами:

• EPOCH возвращает секунды
• Делим на 60 для получения минут (опционально)

Практический пример

Исходные данные:

user_id | event_time           | event_type
--------|----------------------|----------
1       | 2024-01-01 10:00:00  | login
1       | 2024-01-01 10:05:30  | click
1       | 2024-01-01 10:15:00  | purchase
2       | 2024-01-01 09:30:00  | login
2       | 2024-01-01 09:45:00  | click

Результат:

user_id | event_time           | event_type | prev_event_time      | time_since_prev_event_minutes
--------|----------------------|------------|----------------------|------------------------------
1       | 2024-01-01 10:00:00  | login      | NULL                 | NULL
1       | 2024-01-01 10:05:30  | click      | 2024-01-01 10:00:00  | 5.5
1       | 2024-01-01 10:15:00  | purchase   | 2024-01-01 10:05:30  | 9.5
2       | 2024-01-01 09:30:00  | login      | NULL                 | NULL
2       | 2024-01-01 09:45:00  | click      | 2024-01-01 09:30:00  | 15

Видно:

• Первое событие каждого пользователя имеет NULL в prev_event_time
• Для 1-го пользователя: второе событие произошло через 5.5 минут после первого
• Для 1-го пользователя: третье событие произошло через 9.5 минут после второго
• События каждого пользователя обрабатываются отдельно

LEAD() вместо LAG()

Если нужно узнать время следующего события, используйте LEAD():

SELECT 
    user_id,
    event_time,
    event_type,
    LEAD(event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ) AS next_event_time,
    EXTRACT(EPOCH FROM (LEAD(event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ) - event_time)) / 60 AS time_until_next_event_minutes
FROM user_events
ORDER BY user_id, event_time;

Различие:

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

Единицы времени

Разные способы вычисления интервала:

-- Секунды (по умолчанию)
EXTRACT(EPOCH FROM (event_time - prev_event_time)) AS seconds

-- Минуты
EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 AS minutes

-- Часы
EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 3600 AS hours

-- Дни
EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 86400 AS days

-- PostgreSQL специфичный синтаксис
(event_time - prev_event_time) AS interval_value
-- Результат типа interval, можно использовать для фильтрации:
-- WHERE (event_time - prev_event_time) > INTERVAL '1 minute'

Фильтрация: события определённого пользователя или типа

SELECT ... 
FROM user_events
WHERE user_id = 123
ORDER BY user_id, event_time;

Или только события конкретного типа с интервалом:

WHERE event_type IN ('click', 'purchase')

Расширенный пример: с фильтрацией нулей

SELECT 
    user_id,
    event_time,
    event_type,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time,
    EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (
        PARTITION BY user_id ORDER BY event_time
    ))) / 60 AS time_since_prev_event_minutes
FROM user_events
WHERE LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NOT NULL
ORDER BY user_id, event_time;

WHERE ... IS NOT NULL исключает первые события пользователей (где нет предыдущего события).