Віконні функції для часових даних: 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
    ('Podija A', '2023-10-01 10:00:00'),
    ('Podija B', '2023-10-01 11:00:00'),
    ('Podija C', '2023-10-01 12:00:00'),
    ('Podija 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 значень, які можуть з'явитися на початку й в кінці віконного діапазону.

Щоб уникнути цих граблів, завжди перевіряй свої дані й переконайся, що ти визначив правильне вікно для операцій.