Анализ последовательных строк данных с LEAD() и LAG()

Функции LEAD() и LAG() в SQL используются для анализа последовательных строк данных, предоставляя доступ к значениям предыдущих или последующих строк в пределах окна. Эти функции особенно полезны для сравнения данных между последовательными строками и для выполнения задач, таких как расчет изменений или трендов.

Синтаксис функций LEAD() и LAG()

Функция LEAD():

LEAD(column_name, [offset], [default_value]) OVER (
    PARTITION BY partition_column
    ORDER BY order_column
)

• column_name: Столбец, значение которого требуется получить из последующей строки.

• offset: (Опционально) Количество строк вперед от текущей строки. По умолчанию 1.

• default_value: (Опционально) Значение, возвращаемое, если нет следующей строки. По умолчанию NULL.

• PARTITION BY partition_column: (Опционально) Делит данные на группы. Если не указано, функция применяется ко всему набору данных.

• ORDER BY order_column: Определяет порядок строк в окне.

Функция LAG():

LAG(column_name, [offset], [default_value]) OVER (
    PARTITION BY partition_column
    ORDER BY order_column
)

• column_name: Столбец, значение которого требуется получить из предыдущей строки.

• offset: (Опционально) Количество строк назад от текущей строки. По умолчанию 1.

• default_value: (Опционально) Значение, возвращаемое, если нет предыдущей строки. По умолчанию NULL.

• PARTITION BY partition_column: (Опционально) Делит данные на группы. Если не указано, функция применяется ко всему набору данных.

• ORDER BY order_column: Определяет порядок строк в окне.

Примеры использования LEAD() и LAG()

Пример 1: Изменение зарплаты сотрудников

Предположим, у нас есть таблица employees с полями employee_id, salary, и hire_date. Мы хотим вычислить разницу в зарплате между текущей и следующей строкой.

Использование LEAD():

SELECT employee_id, salary, hire_date,
       LEAD(salary) OVER (ORDER BY hire_date) AS next_salary,
       LEAD(salary) OVER (ORDER BY hire_date) - salary AS salary_diff
FROM employees;

• ORDER BY hire_date: Упорядочивает строки по дате найма.

Результат: Для каждого сотрудника будет показана его текущая зарплата, зарплата следующего сотрудника (по дате найма) и разница между этими зарплатами.

Пример 2: Анализ продаж по неделям

Рассмотрим таблицу sales с полями sale_date и amount. Мы хотим рассчитать разницу в продажах между текущей неделей и предыдущей.

Использование LAG():

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS previous_week_amount,
       amount - LAG(amount) OVER (ORDER BY sale_date) AS week_to_week_change
FROM sales;

• ORDER BY sale_date: Упорядочивает строки по дате продажи.

Результат: Для каждой строки будут показаны продажи за текущую неделю, продажи за предыдущую неделю и разница между ними.

Пример 3: Ранжирование студентов по оценкам

Рассмотрим таблицу students с полями student_id, exam_date, и score. Мы хотим сравнить оценки студента с оценками его предыдущего экзамена.

Использование LAG():

SELECT student_id, exam_date, score,
       LAG(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_exam_score,
       score - LAG(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS score_change
FROM students;

• PARTITION BY student_id: Делит данные по студентам.

• ORDER BY exam_date: Упорядочивает строки по дате экзамена.

Результат: Для каждого студента будут показаны оценки на текущем экзамене, оценки на предыдущем экзамене и изменение оценок.

Пример 4: Анализ температуры по дням

Рассмотрим таблицу temperature с полями record_date и temperature. Мы хотим найти разницу температуры между текущим и следующим днем.

Использование LEAD():

SELECT record_date, temperature,
       LEAD(temperature) OVER (ORDER BY record_date) AS next_day_temperature,
       LEAD(temperature) OVER (ORDER BY record_date) - temperature AS temperature_change
FROM temperature;

• ORDER BY record_date: Упорядочивает строки по дате записи.

Результат: Для каждого дня будет показана температура текущего дня, температура следующего дня и изменение температуры.

Заключение

Функции LEAD() и LAG() позволяют анализировать данные, сравнивая их с предыдущими или последующими строками в пределах определенного окна. Они полезны для выполнения различных видов анализа, таких как расчёт изменений, выявление трендов и сравнение последовательных значений. Важно правильно указывать порядок и, при необходимости, делить данные на группы, чтобы получить точные результаты.