Кумулятивные значения рассчитываются с накоплением. Простой пример — счёт. Оценивая количество, вы называете числа: один, два, три и т. д. Каждое следующее число вы получаете, прибавляя единицу.
SELECT *,
SUM(costs) OVER (ORDER BY dt) AS costs_cum
FROM online_store.costs
WHERE channel = 'Yandex';
Запрос отсортировал данные в поле dt по возрастанию — это поле указано после оператора ORDER BY в выражении OVER. Сумма с накоплением считается так: к значению предыдущей записи прибавляется значение текущей — и так с каждой записью до конца таблицы.
Сумму с накоплением можно рассчитать и для каждого окна отдельно. Для этого нужно добавить оператор PARTITION BY и указать нужное поле.
SELECT user_id,
session_duration,
session_start,
SUM(session_duration) OVER (PARTITION BY user_id ORDER BY session_start)
FROM online_store.sessions;
Кумулятивно посчитаем количество пользователей
SELECT user_id,
COUNT(*) OVER (PARTITION BY dt ORDER BY user_id)
FROM online_store.profiles;
Это то же что и
-- использовать ROW_NUMBER в данном случае считается лучшим стилем
SELECT user_id,
ROW_NUMBER() OVER (PARTITION BY dt ORDER BY user_id)
FROM online_store.profiles
Функции MIN, MAX, AVG тоже используют для расчёта кумулятивных значений.
SELECT *,
MIN(costs) OVER (ORDER BY dt) AS costs_cum
FROM online_store.costs
WHERE channel = 'Yandex';
LEAD(), LAG()
Относятся к функциям смещения.
С помощью функций смещения можно для каждой записи с заказом пользователя вернуть дату прошлого или следующего заказа — previous_order_dt и next_order_dt соответственно. Функция LAG() позволяет возвращать предыдущие записи, а LEAD() — следующие.
SELECT user_id,
event_dt,
LAG(event_dt) OVER (PARTITION BY user_id ORDER BY event_dt) AS previous_order_dt,
LEAD(event_dt) OVER (PARTITION BY user_id ORDER BY event_dt) AS next_order_dt
FROM online_store.orders
WHERE user_id IN (300768196,
840452722,
59432616)
ORDER BY user_id;
У функций есть несколько аргументов:
- LEAD(поле, смещение, значение по умолчанию) OVER (определение окна)
- LAG(поле, смещение, значение по умолчанию) OVER (определение окна)
-- 1 и NULL - можно не указывать это значения по умолчанию
SELECT user_id,
event_dt,
LEAD(event_dt, 1, NULL) OVER (PARTITION BY user_id ORDER BY event_dt) AS next_order_dt
FROM online_store.orders
WHERE user_id IN (300768196,
840452722,
59432616)
ORDER BY user_id;
- LEAD() и LAG() используют аргумент для смещения в типе integer
- использование отрицательных значений в качестве аргумента инвертирует действие функций
- аргумент для значения по умолчанию должен быть в том же типе что и поле к которому применяется функция
SELECT user_id,
event_dt,
LAG(event_dt, 2, '2020-01-01') OVER (PARTITION BY user_id ORDER BY event_dt) AS previous_order_dt
FROM online_store.orders
WHERE user_id IN (300768196,
840452722,
59432616)
ORDER BY user_id;
С помощью функций LEAD() и LAG() удобно рассчитывать время между событиями. Например, можно рассчитать, сколько дней прошло со дня предыдущего заказа пользователя.
SELECT user_id,
event_dt,
LAG(event_dt) OVER (PARTITION BY user_id ORDER BY event_dt) AS previous_order_date,
event_dt - LAG(event_dt) OVER (PARTITION BY user_id ORDER BY event_dt) AS days_from_previous_order
FROM online_store.orders
WHERE user_id IN (300768196,
840452722,
59432616)
ORDER BY user_id;
-- найдем количество пользователей за каждый день
WITH users_cnt AS (
SELECT session_start::date AS session_date,
COUNT(DISTINCT user_id) AS users_cnt
FROM online_store.sessions
GROUP BY session_date
)
-- получим пользователей за 7 дней назад
-- и посчитаем во сколько раз увеличилось количество пользователей
SELECT *,
LAG(users_cnt, 7) OVER (ORDER BY session_date) AS previous_weekday_users_cnt,
users_cnt::numeric / LAG(users_cnt, 7, users_cnt) OVER (ORDER BY session_date) AS user_growth
FROM users_cnt;
Еще примеры:
SELECT o.order_id,
o.user_id,
o.paid_at,
LAG(o.paid_at, 1, '1980-01-01') OVER(PARTITION BY o.user_id ORDER BY o.paid_at) AS prev_order_dt
FROM tools_shop.orders AS o
SELECT e.event_id,
e.event_time,
e.user_id,
LEAD(e.event_time) OVER(PARTITION BY e.user_id ORDER BY e.event_time)
FROM tools_shop.events AS e;
SELECT e.event_id,
e.event_time,
e.user_id,
LEAD(e.event_time) OVER(PARTITION BY e.user_id ORDER BY e.event_time) - e.event_time
FROM tools_shop.events AS e;
