Оконные функцииСредний
LAG / LEAD
LAG возвращает значение из предыдущей строки окна, LEAD — из следующей.
Синтаксис
LAG(col [, offset [, default]]) OVER (ORDER BY col)
LEAD(col [, offset [, default]]) OVER (ORDER BY col)Объяснение
LAG и LEAD позволяют сравнивать текущую строку с соседними без self-join.
Параметры: LAG(col, offset, default) — col из строки на offset позиций назад; default — если строки нет.
Типичное применение: вычислить изменение/рост между периодами.
Пример
-- Изменение продаж по сравнению с предыдущим месяцем
SELECT
month,
sales,
LAG(sales) OVER (ORDER BY month) AS prev_sales,
sales - LAG(sales) OVER (ORDER BY month) AS diff
FROM monthly_sales;Связанные термины
OVER (оконные функции)Ключевое слово, превращающее агрегатную функцию в оконную — вычисляет значение без схлопывания строк.PARTITION BYДелит строки на разделы для оконной функции. Аналог GROUP BY, но без схлопывания строк.ROW_NUMBER / RANK / DENSE_RANKОконные функции нумерации строк. Различаются поведением при одинаковых значениях.
Анекдоты по теме
— Почему оконные функции не ходят на свидания вслепую? — Потому что им нужно знать OVER (PARTITION BY кто_пришёл ORDER BY красота DESC), иначе они не знают, как себя вести.
RANK() и DENSE_RANK() поспорили о справедливости. RANK(): — Если двое на первом месте, следующий — третий. DENSE_RANK(): — Если двое на первом месте, следующий — второй. ROW_NUMBER(): — Мне всё равно. Каждый получает уникальный номер.
— Зачем NTILE? — Делит строки на N равных групп. — Например? — NTILE(4) OVER (ORDER BY score) — разбивает пользователей на 4 квартиля. — Первый квартиль — лучшие 25%? — Зависит от ORDER BY. С DESC — да.