Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 17
Часть 3. Событийная аналитика. (7 задач)
Глава 15. LAG, EXTRACT. Среднее время между enter и buy
ОглавлениеОписание задачи
Мы знаем, какие пользователи зашли на сайт и какие купили. Но сколько времени проходит между этими событиями? Чем быстрее пользователь покупает, тем лучше для бизнеса.
Посчитаем среднее время между первым действием enter и первым действием buy для каждого пользователя, а затем среднее по всем пользователям.
Таблицы: users_log (логи действий пользователей).
Аналогия из жизни
Представьте, что вы пришли в торговый центр. Время входа – 12:00. Время покупки в магазине – 12:30. Разница – 30 минут. Если усреднить по всем посетителям, получим среднее время от входа до покупки. Чем оно меньше, тем быстрее люди принимают решение.
Основные SQL-конструкции
LAG () – оконная функция, которая возвращает значение из предыдущей строки в рамках окна.
text
LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm)
В нашей задаче: для каждого пользователя берём время предыдущего действия.
EXTRACT – извлекает часть из даты/времени (часы, минуты, секунды).
text
EXTRACT (epoch FROM interval) – возвращает количество секунд
Почему мы не можем просто вычесть даты и получить минуты?
Можно, но EXTRACT даёт больше гибкости. Мы можем получить секунды, минуты, часы. Для среднего времени удобнее работать в секундах, а потом перевести в минуты.
Почему LAG, а не LEAD?
LAG смотрит на предыдущее действие. LEAD – на следующее. Нам нужно предыдущее (первое enter), поэтому LAG.
SQL-запросы с объяснением
Шаг 1. Что нам нужно?
Для каждого пользователя:
– Найти время первого enter.
– Найти время первого buy после enter.
– Посчитать разницу в минутах.
– Затем усреднить по всем пользователям.
Почему первого buy? Пользователь мог купить несколько раз. Нас интересует первый раз, когда он купил после входа.
Шаг 2. Отфильтруем нужные действия.
Возьмём только действия enter и buy.
text
WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
)
SELECT * FROM filtered_log ORDER BY user_id, dt_tm;
Как это работает
– WHERE action IN (’enter’, ’buy’) – оставляем только нужные действия.
– Упорядочиваем по пользователю и времени, чтобы видеть последовательность.
Шаг 3. Добавим предыдущее действие с LAG.
Для каждого действия посмотрим, что было до него.
text
WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_id,
action,
dt_tm,
LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,
LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_action
FROM filtered_log
)
SELECT * FROM with_prev ORDER BY user_id, dt_tm;
Как это работает
– LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) – для каждой строки берём время предыдущего действия того же пользователя.
– LAG (action) … – берём предыдущее действие.
– Для самой первой строки пользователя предыдущего действия нет → будет NULL.
Шаг 4. Оставим только пары enter → buy.
Нас интересуют строки, где текущее действие buy, а предыдущее – enter.
text
WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_id,
action,
dt_tm,
LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,
LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_action
FROM filtered_log
)
SELECT
user_id,
prev_dt_tm AS enter_time,
dt_tm AS buy_time
FROM with_prev
WHERE action = ’buy’ AND prev_action = ’enter’;
Как это работает
– WHERE action = ’buy’ AND prev_action = ’enter’ – оставляем только те строки, где текущее действие buy, а предыдущее – enter.
– Теперь у нас есть пары (время входа, время покупки).
Пример вывода (фрагмент):
text
user_id enter_time buy_time
1 2024-02-01 10:15:00 2024-02-01 10:30:00
2 2024-02-15 12:00:00 2024-02-15 12:15:00
3 2024-01-25 14:10:00 2024-01-25 14:15:00
4 2024-03-05 10:00:00 2024-03-05 10:30:00
5 2024-02-20 13:00:00 2024-02-20 13:30:00
Шаг 5. Посчитаем разницу в минутах.
Используем EXTRACT (epoch FROM …) для перевода разницы в секунды, затем делим на 60.
text
WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_id,
action,
dt_tm,
LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,
LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_action
FROM filtered_log
),
enter_buy_pairs AS (
SELECT
user_id,
prev_dt_tm AS enter_time,
dt_tm AS buy_time
FROM with_prev
WHERE action = ’buy’ AND prev_action = ’enter’
)
SELECT
user_id,
enter_time,
buy_time,
EXTRACT (epoch FROM (buy_time – enter_time)) / 60 AS minutes_diff
FROM enter_buy_pairs
ORDER BY user_id;
Как это работает
– buy_time – enter_time – получаем интервал (тип interval).
– EXTRACT (epoch FROM …) – переводит интервал в секунды.
– / 60 – делим на 60, получаем минуты.
Что такое epoch? Epoch – это количество секунд с 1970-01-01. Для интервала EXTRACT (epoch) возвращает общее количество секунд в интервале.
Пример вывода (фрагмент):
text
user_id enter_time buy_time minutes_diff
1 2024-02-01 10:15:00 2024-02-01 10:30:00 15.00
2 2024-02-15 12:00:00 2024-02-15 12:15:00 15.00
3 2024-01-25 14:10:00 2024-01-25 14:15:00 5.00
4 2024-03-05 10:00:00 2024-03-05 10:30:00 30.00
5 2024-02-20 13:00:00 2024-02-20 13:30:00 30.00
Шаг 6. Посчитаем среднее время.
text
WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_id,
action,
dt_tm,
LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,
LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_action
FROM filtered_log
),
enter_buy_pairs AS (
SELECT
user_id,
prev_dt_tm AS enter_time,
dt_tm AS buy_time,
EXTRACT (epoch FROM (dt_tm – prev_dt_tm)) / 60 AS minutes_diff
FROM with_prev
WHERE action = ’buy’ AND prev_action = ’enter’
)
SELECT
COUNT (*) AS total_pairs,
ROUND (AVG (minutes_diff), 2) AS avg_minutes
FROM enter_buy_pairs;
Как это работает
– COUNT (*) – количество пар (пользователей, которые зашли и купили).
– AVG (minutes_diff) – среднее арифметическое разниц.
– ROUND (…, 2) – округляем до двух знаков.
Пример вывода:
text
total_pairs avg_minutes
11 23.18
Вопросы и ответы
В: Что будет, если пользователь сделал enter несколько раз, а потом buy?
О: LAG возьмёт предыдущее действие. Если перед buy был enter, то учтётся последний enter. Если нужен первый enter – запрос нужно усложнить (добавить MIN с оконной функцией).
В: Почему мы не используем LEAD?
О: LEAD смотрит вперёд. Можно было бы взять enter, а потом LEAD найти следующий buy. Результат будет тот же, но логика другая. LAG удобнее, когда мы идём от покупки назад к входу.
В: Что делать, если у пользователя нет buy?
О: Он не попадёт в результат. Это правильно – мы считаем время только для тех, кто купил.
В: Зачем мы используем EXTRACT (epoch FROM …)?
О: Потому что AVG не умеет работать напрямую с интервалами. Сначала переводим в секунды (число), считаем среднее, потом при желании переводим обратно в минуты.
