Offset window functions

Кумулятивные значения рассчитываются с накоплением. Простой пример — счёт. Оценивая количество, вы называете числа: один, два, три и т. д. Каждое следующее число вы получаете, прибавляя единицу.

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;