PricingLoginStart free
Coding Interview Prep · Урок

Изменение от периода к периоду

Вычисляйте рост месяц к месяцу и изменения день ко дню с помощью LAG.

Урок 2 из 413 шагов

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

Обязательный вопрос для аналитика

Если Вы проходите собеседование на должность аналитика данных, ожидайте вопрос: «Рассчитайте рост месяц к месяцу» или «Как изменилось значение день ко дню?» Почти невозможно избежать этой темы.

Основа решения — LAG: получите значение предыдущего периода, а затем вычислите абсолютное или процентное изменение. В этом уроке показаны шаблоны с LAG для изменения от периода к периоду, которые интервьюеры ожидают от кандидатов.

Абсолютное изменение с помощью LAG

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

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

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales
ORDER BY month;

Процентное изменение

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

Следите за порядком операций и скобками: лишняя или пропущенная скобка — классическая ошибка в стрессовой ситуации.

SELECT
  month,
  revenue,
  100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) AS pct_growth
FROM monthly_sales
ORDER BY month;

Избегайте целочисленного деления

Многие кандидаты попадаются на следующую ловушку: если revenue — целое число, выражение (110 - 100) / 100 вычисляется как 0 в базах данных, использующих целочисленное деление.

Умножьте на 100.0 (литерал с плавающей точкой) или сначала выполните CAST в десятичный тип, чтобы принудительно использовать вычисления с плавающей точкой. Если Вы упомянете это заранее, это покажет внимание к деталям.

SELECT
  month,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / LAG(revenue) OVER (ORDER BY month), 2
  ) AS pct_growth
FROM monthly_sales;

Чище: вычислите один раз в CTE

Дважды вызывать LAG (для числителя и знаменателя) повторно и легко допустить опечатку. Распространённый способ улучшить запрос — один раз вычислить предыдущее значение в CTE, а затем выполнить арифметику во внешнем запросе.

Такой вариант лучше читается на собеседовании с решением задачи на доске и предотвращает расхождения между двумя вызовами LAG.

WITH t AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly_sales
)
SELECT
  month,
  revenue,
  ROUND(100.0 * (revenue - prev) / prev, 2) AS pct_growth
FROM t;

Защититесь от деления на ноль

Если значение предыдущего периода может быть равно 0, процентная формула выполняет деление на ноль и завершается ошибкой. Используйте NULLIF(prev, 0), чтобы знаменатель стал равен NULL, а результатом было NULL, а не исключение.

Безопасное деление — небольшая деталь, которую интервьюеры замечают у кандидатов, думающих о работе в реальных системах.

SELECT
  month,
  revenue,
  100.0 * (revenue - prev) / NULLIF(prev, 0) AS pct_growth
FROM (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly_sales
) s;

Изменение от периода к периоду для каждой группы

Большинство реальных вопросов относятся к отдельным группам: рост месяц к месяцу для каждого продукта или для каждого региона. Добавьте PARTITION BY, чтобы сравнивать временной ряд каждой группы только с самим собой.

Самый ранний месяц каждой группы снова получает NULL; сравнения между разными продуктами не происходит.

SELECT
  product_id,
  month,
  revenue,
  revenue - LAG(revenue) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS mom_change
FROM product_sales;

Изменение год к году со смещением

Для расчёта изменения год к году на помесячных данных, где строки идут с интервалом в один месяц, нужно сравнить значение со значением, находящимся на 12 строк выше. Аргумент смещения делает это напрямую: LAG(revenue, 12).

Это предполагает наличие одной строки на каждый месяц без пропусков. Если месяцы могут отсутствовать, вместо этого следует соединять данные по явной дате — это важная тонкость, которую стоит озвучить.

SELECT
  month,
  revenue,
  revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_sales
ORDER BY month;

Сначала агрегируйте данные перед сравнением

Исходные таблицы событий не содержат предварительных итогов по месяцам. Реалистичный ответ сначала агрегирует данные до одной строки на период, а затем применяет LAG к результату агрегации, обычно через CTE.

Попытка применить LAG к неагрегированным строкам сравнивает отдельные транзакции, а не месячные итоги. Это распространённая логическая ошибка.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;

Как интерпретировать первый NULL

Иногда интервьюеры спрашивают: «Почему рост в первой строке равен NULL и допустимо ли это?» Правильный ответ: предыдущего периода нет, поэтому изменение не определено, а NULL корректно это представляет.

Если бизнесу вместо этого требуется 0, можно использовать LAG(revenue, 1, 0) или COALESCE, но только если это соответствует требуемому смыслу.

Обозначение направления роста

Иногда интервьюеры расширяют вопрос: «Также укажите, росло ли значение в каждом месяце, снижалось или оставалось неизменным». Оберните сравнение с LAG в выражение CASE.

Сравнение с предыдущим значением даёт понятный для человека столбец с тенденцией в дополнение к числовому изменению и корректно показывает NULL либо метку по умолчанию для первого периода, для которого нет сравнения.

SELECT
  month,
  revenue,
  CASE
    WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'up'
    WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'down'
    ELSE 'flat'
  END AS trend
FROM monthly_sales
ORDER BY month;

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

Найдите распространённую ошибку в запросах с процентным изменением.

Ответь в интерактивном уроке

Итоги

Изменение от периода к периоду — это LAG и арифметика:

  • Абсолютное изменение: value - LAG(value) OVER (ORDER BY period).
  • Процентное изменение: 100.0 * (value - prev) / NULLIF(prev, 0).
  • Используйте вычисления с плавающей точкой, защищайтесь от деления на ноль и предварительно агрегируйте данные до одной строки на период.
  • Ограничивайте область с помощью PARTITION BY; для изменения год к году используйте смещение.

Далее: разделение строк на группы и уровни с помощью NTILE.

Можно начать бесплатно

Изучай Coding Interview Prep с ИИ-репетитором — бесплатно

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

Курсы
90
Уроки
360
Начать учиться бесплатноПосмотреть полный курс

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

Урок «Изменение от периода к периоду» бесплатный?

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

Чему я научусь в уроке «Изменение от периода к периоду»?

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

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

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

Сколько времени занимает урок «Изменение от периода к периоду»?

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

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

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

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

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