Оконные функцииСредний
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(). Потому что она всегда оглядывается на прошлое и никогда не смотрит вперёд.
Задача: найти разницу продаж между текущим и предыдущим месяцем. Решение: SELECT month, sales, sales - LAG(sales) OVER (ORDER BY month) AS diff FROM monthly_sales; Программист: одна строка вместо самостоятельного JOIN! DBA: вот за это любят оконные функции.