0Pricing
SQL Interview Prep · Урок

LAG и LEAD для соседних строк

Получайте значения предыдущей и следующей строки без самообъединения.

«LAG и LEAD для соседних строк» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Вопрос, который задают на собеседованиях

Один из самых распространённых вопросов на собеседовании аналитика: «Сравните каждую строку с предыдущей без самообъединения». Например, это может быть сравнение выручки месяц к месяцу, предыдущего входа пользователя или следующего события в последовательности.

Правильный и лаконичный ответ — оконные функции LAG и LEAD. Они позволяют строке получить значение соседней строки, сохраняя все строки с деталями. В этом уроке Вы сформируете точное представление о том, как они перемещаются между соседними строками.

Что делают LAG и LEAD

LAG(col) возвращает значение col из предыдущей строки. LEAD(col) возвращает значение из следующей строки. Понятия «предыдущая» и «следующая» полностью определяются выражением ORDER BY внутри предложения OVER.

  • LAG смотрит назад.
  • LEAD смотрит вперёд.

Обе функции относятся к оконным функциям со смещением: они не сворачивают строки, а лишь добавляют к текущей строке значение соседней.

Базовый синтаксис LAG

Вот каноническая форма записи. У нас есть таблица sales со столбцами month и revenue. Мы хотим, чтобы в каждой строке также отображалась выручка за предыдущий месяц.

Конструкция OVER (ORDER BY month) сообщает движку, как определить «предыдущую» строку. У первой строки нет предшественницы, поэтому в ней prev_revenue имеет значение NULL.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

Чтение результата

Для данных 2024-01 = 100, 2024-02 = 130, 2024-03 = 120 запрос вернёт:

  • Янв.: выручка 100, предыдущая выручка NULL
  • Февр.: выручка 130, предыдущая выручка 100
  • Март: выручка 120, предыдущая выручка 130

Каждая строка получила значение непосредственно из расположенной выше строки упорядоченного набора. Никакого самообъединения, подзапроса или потери строк.

LEAD смотрит вперёд

LEAD работает зеркально. Используйте её, когда строке нужно узнать, что произойдёт дальше, например чтобы получить дату следующей покупки и вычислить интервал между заказами.

У последней строки упорядоченного набора нет следующей строки, поэтому результат LEAD для неё равен NULL.

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

Аргумент смещения

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

На собеседовании это используют, чтобы попросить, например, «выручку два месяца назад» или «значение через три строки». Смещение по умолчанию равно 1.

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

Аргумент значения по умолчанию

Третий аргумент задаёт замену, если соседней строки нет, вместо результата NULL. Сигнатура выглядит так: LAG(col, offset, default).

Это удобно, если последующий расчёт не может работать с NULL; например, можно считать отсутствующее предыдущее значение равным 0, чтобы разность всё равно вычислялась.

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY перезапускает окно

В реальных данных редко бывает один общий ряд. Обычно сравнение выполняется отдельно для каждого клиента, продукта или региона. PARTITION BY начинает вычисление LAG/LEAD заново в начале каждого раздела.

Это означает, что первая строка каждого раздела получает от LAG значение NULL, и значение никогда не переносится через границу в данные другого клиента.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

Разбор примера: количество дней между заказами

Распространённая задача — измерить промежуток между последовательными заказами клиента. Получите дату предыдущего заказа с помощью LAG, а затем вычтите одну дату из другой.

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

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

Почему не соединение таблицы с самой собой?

До появления оконных функций ответом было коррелированное соединение таблицы с самой собой: соединить таблицу со строкой, «дата которой является наибольшей датой, меньшей текущей». Это работает, но получается многословным, плохо обрабатывает совпадения и часто выполняется медленнее.

  • LAG/LEAD выражают замысел в одной строке.
  • Они вычисляются за один упорядоченный проход.
  • Порядок строк при совпадениях однозначно задаётся вашим ORDER BY.

Фраза «Я бы использовал LAG вместо соединения таблицы с самой собой» показывает уверенное владение темой.

Распространённая ошибка: отсутствие ORDER BY

Без ORDER BY в предложении OVER понятие «предыдущая строка» не определено. Некоторые системы отклоняют такой запрос, а другие возвращают непредсказуемые результаты. Всегда задавайте порядок в окне.

Также помните, что порядок внутри OVER не зависит от внешнего ORDER BY запроса. Окно определяет, какая строка является соседней, а внешнее предложение определяет только порядок отображения.

Быстрая проверка

Проверьте, насколько Вы понимаете оконные функции со смещением.

Итоги

Теперь Вы знаете оконные функции со смещением:

  • LAG(col) считывает предыдущую строку, а LEAD(col) — следующую, согласно ORDER BY окна.
  • Необязательные аргументы: LAG(col, offset, default).
  • PARTITION BY начинает навигацию заново для каждой группы, поэтому граничные строки содержат NULL.
  • Они заменяют громоздкие соединения таблицы с самой собой при сравнении соседних строк.

Далее мы применим это к обязательному вопросу для аналитика: изменению от периода к периоду.

Часто задаваемые вопросы

Урок «LAG и LEAD для соседних строк» бесплатный?

Да — полный текст урока «LAG и LEAD для соседних строк» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «LAG и LEAD для соседних строк»?

Получайте значения предыдущей и следующей строки без самообъединения. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «LAG и LEAD для соседних строк»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. LAG и LEAD для соседних строк
  2. Изменение от периода к периоду
  3. NTILE для разбиения на группы
  4. FIRST_VALUE, LAST_VALUE и границы рамки
← Назад к SQL Interview Prep