Оконные функции для временных данных: LEAD(), LAG()

Теперь наша задача — шагнуть еще дальше и научиться использовать оконные функции для анализа временных данных. Готовы? Надеюсь, вы прихватили с собой чашку кофе, потому что это будет интересно.

Итак, как всегда, сначала отвечаем на главный вопрос: зачем нам оконные функции (LEAD(), LAG())? Представьте, что вы работаете с временными данными, будь то логи событий, часы работы, временные ряды или что угодно, где важна последовательность событий.

Например, вы хотите:

• Узнать, когда произошло следующее событие после текущего.
• Вычислить разницу во времени между текущим событием и предыдущим.
• Отсортировать данные и вычислить разницу между записями.

Тут на арену выходят две шикарные функции: LEAD() и LAG(). Они позволяют доставать данные из предыдущей либо следующей строки в рамках определенного окна. Это как если бы у вас была магическая книга, где вы могли посмотреть, что будет на следующей странице, не переворачивая текущую.

LEAD() и LAG(): синтаксис и основные принципы

Обе функции используют схожий синтаксис:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — столбец, из которого мы хотим взять данные.
• offset (опционально) — смещение относительно текущей строки. По умолчанию это 1.
• default_value (опционально) — значение, которое возвращается, если строки с требуемым смещением нет (например, когда вы находитесь в последней строке).
• OVER() — тут задается "окно", по которому будет проводиться расчет. Чаще всего это ORDER BY, иногда используется PARTITION BY для разделения данных на группы.

Пример: Просто LEAD() и LAG()

Давайте создадим простую таблицу events для наших экспериментов:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Event A', '2023-10-01 10:00:00'),
    ('Event B', '2023-10-01 11:00:00'),
    ('Event C', '2023-10-01 12:00:00'),
    ('Event D', '2023-10-01 13:00:00');

Теперь мы хотим посмотреть, когда произошли предыдущие и следующие события относительно каждого события:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS previous_event,
    LEAD(event_date) OVER (ORDER BY event_date) AS next_event
FROM events;

Результат будет следующим:

Здесь LAG() берет данные из предыдущей строки, а LEAD() — из следующей. Первому событию не на что оглядываться, а последнему некого опережать, поэтому они получают NULL.

Пример: разница между событиями

Иногда нам нужно узнать, сколько времени прошло между событиями. Для этого мы можем просто вычесть одно время из другого:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS time_since_last_event
FROM events;

Результат:

Пример: использование PARTITION BY

Допустим, у нас есть несколько пользователей, у каждого из которых свои события. Мы хотим найти разницу между событиями для каждого пользователя.

Обновим таблицу и добавим столбец user_id:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Теперь у нас два пользователя. Используем PARTITION BY для расчета внутри каждой группы:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS time_since_last_event
FROM events;

Результат:

Примеры использования в реальных задачах

1. Логи событий: анализ времени между событиями, таких как логин пользователя и выход.
2. Тайм-трекинг: расчет времени, проведенного на определенных задачах.
3. Поведенческая аналитика: анализ последовательности действий клиентов в интернет-магазине.
4. Расчет накопительных метрик: использование оконных функций для работы с временными рядами.

Типичные ошибки

При работе с LEAD() и LAG() ключевыми проблемами могут быть:

• Забыт ORDER BY в OVER(). Без него функция не сможет определить последовательность строк.
• Проблемы с временными интервалами или типами данных (TIMESTAMP vs DATE).
• Игнорирование NULL значений, которые могут появиться в начале и конце оконного диапазона.

Чтобы избежать этих ошибок, всегда проверяйте свои данные и убедитесь, что вы определили правильное окно для операций.

2

Задача

SQL SELF, 32 уровень, 3 лекция

Недоступна

Извлечение следующего и предыдущего события

Извлечение следующего и предыдущего события

Открыть