Накопительные итоги, скользящие средние, LAG/LEAD

Накопительные итоги и скользящие средние

Накопительный итог — это SUM по фрейму, который растёт от начала партиции до текущей строки. Из урока о фреймах: всегда пиши ROWS для последовательного построчного итога:

SELECT region, sale_date, amount,
       SUM(amount) OVER (
         PARTITION BY region ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

Скользящее среднее — та же идея с ограниченным фреймом — среднее по текущей строке и нескольким перед ней:

-- скользящее среднее по 3 строкам назад от дневной суммы
SELECT sale_date, amount,
       AVG(amount) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM sales;

Единственное изменение от накопительного итога — начальная граница: 2 PRECEDING вместо UNBOUNDED PRECEDING. Ближе к началу партиции фрейм просто держит меньше строк (1, затем 2, затем полные 3) — корректное краевое поведение, не баг.

LAG и LEAD: построчные дельты

LAG(col, n) читает колонку из строки за n до текущей в порядке окна; LEAD(col, n) читает за n после. Это как считать изменение период-к-периоду без self-join:

-- изменение выручки месяц-к-месяцу
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly_revenue;

LAG(revenue) на первой строке — NULL (предыдущей строки нет) — задай дефолт через LAG(revenue, 1, 0), если хочешь 0; и защити деление через NULLIF, чтобы нулевое предыдущее значение не дало деление на ноль. LAG/LEAD полностью игнорируют фрейм; они навигируют по смещению строки внутри упорядоченной партиции.

FIRST_VALUE, LAST_VALUE и ловушка

FIRST_VALUE(col) возвращает колонку из первой строки фрейма, LAST_VALUE(col) — из последней. Ловушка: с ORDER BY в окне фрейм по умолчанию — RANGE UNBOUNDED PRECEDING AND CURRENT ROW, поэтому последняя строка фрейма и есть текущая строка. LAST_VALUE поэтому возвращает значение текущей строки, а не финальное в партиции — чего почти никто не хочет.

-- НЕВЕРНО: возвращает сумму текущей строки, потому что фрейм кончается на CURRENT ROW
LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date)

-- ВЕРНО: растяни фрейм до конца партиции
LAST_VALUE(amount) OVER (
  PARTITION BY region ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

FIRST_VALUE обычно выглядит корректно случайно: фрейм начинается с UNBOUNDED PRECEDING, что действительно первая строка, так что фрейм по умолчанию её уже покрывает. Кусается именно LAST_VALUE — его цель живёт на дальнем краю партиции, за потолком CURRENT ROW фрейма по умолчанию. Чини растяжкой фрейма до UNBOUNDED FOLLOWING или обойди через MAX/MIN по всей партиции (без ORDER BY, так что фрейм — вся партиция), когда хочешь экстремум, а не буквально последнюю-по-порядку строку.