LEAD и LAG: доступ к следующей/предыдущей строке

Зачем нужны LEAD и LAG? 🔍

Окно в прошлое и будущее ваших данных! Эти функции позволяют заглянуть в соседние строки без сложных самообъединений таблиц. Представьте, что вы анализируете продажи и хотите:

• Сравнить текущий заказ с предыдущим
• Увидеть динамику изменения цен
• Найти "скачки" между последовательными значениями

Вот где LEAD и LAG становятся вашими лучшими друзьями!

-- Простейший пример
SELECT 
    order_id,
    order_date,
    LAG(order_date) OVER (ORDER BY order_date) AS prev_order,
    LEAD(order_date) OVER (ORDER BY order_date) AS next_order
FROM orders;

Как работает LAG? ⏮

Функция LAG заглядывает "назад" — берет значение из предыдущей строки в указанном порядке. Её синтаксис:

LAG(column_name, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

Разберём на реальном примере — анализ изменения зарплат сотрудников:

SELECT 
    employee_id,
    year,
    salary,
    LAG(salary) OVER (PARTITION BY employee_id ORDER BY year) AS prev_salary,
    salary - LAG(salary) OVER (PARTITION BY employee_id ORDER BY year) AS delta
FROM salaries
WHERE employee_id = 101;

💡 Ключевые моменты: - PARTITION BY — разделяет данные на группы (как GROUP BY) - ORDER BY — определяет порядок строк - Можно указать смещение (сколько строк "назад" смотреть) - Третий параметр заменяет NULL для несуществующих строк

Как работает LEAD? ⏭

LEAD — это зеркальная версия LAG, которая смотрит "вперёд". Идеально для:

• Сравнения текущего значения со следующим
• Предсказания трендов
• Анализа последовательностей событий

Пример из e-commerce — вычисление времени между заказами клиента:

SELECT 
    customer_id,
    order_date,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) - order_date AS days_between_orders
FROM orders;

Продвинутые техники 🧠

1. Окна с условиями

Можно комбинировать с другими оконными функциями:

SELECT 
    product_id,
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (PARTITION BY product_id ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1, 0) OVER (PARTITION BY product_id ORDER BY month) AS growth,
    ROUND(
        (revenue - LAG(revenue, 1, 0) OVER (PARTITION BY product_id ORDER BY month)) / 
        LAG(revenue, 1, 1) OVER (PARTITION BY product_id ORDER BY month) * 100, 
        2
    ) AS growth_percent
FROM sales;

2. Множественные смещения

Используйте разные offset для комплексного анализа:

SELECT
    date,
    temperature,
    LAG(temperature, 1) OVER (ORDER BY date) AS yesterday,
    LAG(temperature, 7) OVER (ORDER BY date) AS last_week
FROM weather;

Частые ошибки и как их избежать 🚧

1. Забыли ORDER BY — без него порядок строк не определён sql -- Так делать НЕ надо! LAG(salary) OVER (PARTITION BY department_id) -- нет ORDER BY!

2. Неправильные PARTITION — убедитесь, что делите данные на осмысленные группы

3. Игнорирование NULL — предусмотрите значения по умолчанию: sql LAG(salary, 1, 0) OVER (...) -- вместо NULL будет 0

4. Путаница между LEAD и LAG — помните: LAG = предыдущая, LEAD = следующая

Практикуем на реальных данных 🛠

Задача: найти сотрудников, чья зарплата уменьшилась по сравнению с предыдущим годом.

WITH salary_changes AS (
    SELECT 
        employee_id,
        year,
        salary,
        LAG(salary) OVER (PARTITION BY employee_id ORDER BY year) AS prev_salary
    FROM salaries
)
SELECT 
    employee_id,
    year,
    salary,
    prev_salary
FROM salary_changes
WHERE salary < prev_salary;

🔥 Профессиональный лайфхак: используйте WITH для создания промежуточных CTE — так запросы становятся чище и понятнее!

Когда использовать? Практические кейсы 🏆

1. Анализ временных рядов:

• Сравнение показателей с предыдущим периодом
• Вычисление дельты между измерениями

2. Финансовый анализ:

• Расчёт процентных изменений
• Выявление аномалий в последовательностях транзакций

3. Продуктовая аналитика:

• Время между действиями пользователя
• Анализ последовательностей событий (funnel)

Для глубокого погружения в оконные функции рекомендую курс Данилы Бежина на YouTube: SQL и оконные функции.