LAG, LEAD і поточні підсумки

LAG та LEAD — дві спеціальні віконні функції, які надають вам доступ до інші рядки того самого розділу: попередній рядок (LAG) або наступний (LEAD) відповідно до ORDER BY вікна. Вони є інструмент переходу до порядкового порівняння (дельти, варіації, інтервали) і — у поєднанні з SUM/AVG/COUNT OVER — для загальних підсумків.

ЛАГ: попередній ряд

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;

Загальні підсумки: об’єднуються з НАД

Агрегатні функції (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 вікна. Результатом є класичний «біговий баланс».

Спробуйте

Повторна вправа