Оконные функцииСредний
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Оконные функции нумерации строк. Различаются поведением при одинаковых значениях.
Анекдоты по теме
Разработчик: — У меня запрос на 8 таблиц через JOIN, подзапросы, оконные функции внутри WHERE и GROUP BY на хэш с миллиардом строк. Он идёт 3 дня. Как оптимизировать? DBA: — TRUNCATE TABLE карьера_разработчика. И иди в менеджеры.
Приходит запрос на свидание к оконной функции LAG(). — Пойдём со мной? — Нет, я люблю оглядываться на прошлое. Я буду смотреть на предыдущее значение, пока ты будешь говорить о настоящем. И вообще, без ORDER BY я не работаю.
— Зачем NTILE? — Делит строки на N равных групп. — Например? — NTILE(4) OVER (ORDER BY score) — разбивает пользователей на 4 квартиля. — Первый квартиль — лучшие 25%? — Зависит от ORDER BY. С DESC — да.