Оконные функцииСредний

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Оконные функции нумерации строк. Различаются поведением при одинаковых значениях.

Анекдоты по теме

Аналитик хочет топ-3 продукта в каждой категории. Пишет GROUP BY, ORDER BY, LIMIT 3. Получает топ-3 вообще, не по категориям. DBA: тебе нужна оконная функция. ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) Потом WHERE rn <= 3. Аналитик: почему это не просто LIMIT 3 внутри GROUP BY?! DBA: потому что SQL так работает.

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

— Что такое FRAME в оконных функциях? — Часть окна, над которой считается функция для текущей строки. — Например? — ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — скользящее среднее за 3 строки. — А UNBOUNDED PRECEDING? — От начала окна до текущей строки. Нарастающий итог.

Все анекдоты →

Читать статью →Потренироваться →← Все термины