LAG, LEAD и текущие итоги

LAG и LEAD — две специальные оконные функции, которые дают вам доступ к другие строки того же раздела: предыдущая строка (LAG) или следующий (LEAD) в соответствии с ORDER BY окна. Они являются инструмент перехода для сравнений строк (дельты, вариации, интервалы) и — в сочетании с SUM/AVG/COUNT OVER — для промежуточных итогов.

LAG: предыдущий ряд

LAG(colonna)            OVER (PARTITION BY p ORDER BY o)
LAG(colonna, n)         OVER (PARTITION BY p ORDER BY o)   -- n righe indietro
LAG(colonna, n, default) OVER (PARTITION BY p ORDER BY o)  -- default se non c'è

Пример: для каждого заказа «дней с момента предыдущего заказа на тот же самый клиент":

SELECT id,
       customer_id,
       ordered_at,
       ordered_at - LAG(ordered_at) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous_order
FROM   orders
ORDER BY customer_id, ordered_at;

Первый заказ каждого клиента имеет since_previous_order = NULL, потому что в разделе нет «предыдущего».

ВЕДУЩИЙ: следующий ряд

Зеркало LAG. Типично: «время до следующего события», «разница со следующей версией».

SELECT id, ordered_at,
       LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)
         - ordered_at AS to_next
FROM   orders;

Промежуточные итоги: суммируются с OVER

Агрегатные функции (SUM, AVG, COUNT, MAX, MIN) становятся кумулятивно в сочетании с OVER (… ORDER BY …):

SELECT order_id,
       total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY     ordered_at
       ) AS running_total
FROM   …;

Для каждой строки SUM вычисляет сумму всех строк в разделе до эта точка, согласно ORDER BY окна. Результатом является классический «бегущий баланс».

Попробуйте это

Обзор упражнения