LAG и LEAD: почему прошлый месяц иногда не прошлый

Апрель выглядит на 66,67% лучше февраля. Но где март — и можно ли назвать это ростом за месяц?

SQL Studio / Разборы SQL / LAG и LEAD: почему прошлый месяц иногда не прошлый

В отчёте за апрель рост выручки выглядит как +66,67%. Но март в выгрузке отсутствует. LAG(revenue) OVER (ORDER BY ym) вернёт февраль, а не март: функция смотрит на предыдущую строку, не на предыдущий календарный месяц. Чтобы посчитать помесячное изменение, сначала получите соседнее значение через LAG, проверьте, что месяцы действительно идут подряд, а затем вычислите разницу и процент. LEAD работает зеркально — смотрит на следующую строку. Для первого месяца и периода после пропуска результат нужно отличать от настоящего нуля.

1. Предыдущая строка — не обязательно предыдущий месяц

Условимся, что в таблице одна строка на месяц и её значение — выручка в копейках. Ниже исходные наблюдения: январь — 120 000, февраль — 90 000, апрель — 150 000, май — 0, июнь — 60 000. Март отсутствует: мы не знаем, были ли продажи, и не вправе автоматически записывать ноль.

Исходные месячные наблюдения
МесяцВыручка, коп.
2026-01120000
2026-0290000
2026-04150000
2026-050
2026-0660000

Исполнимый, но смыслово неверный для month-over-month вариант не проверяет календарное соседство:

WITH monthly(ym, revenue_kopecks) AS (
 VALUES ('2026-01',120000), ('2026-02',90000),
        ('2026-04',150000), ('2026-05',0), ('2026-06',60000)
)
SELECT ym, revenue_kopecks,
       LAG(revenue_kopecks) OVER (ORDER BY ym) AS previous_row,
       LEAD(revenue_kopecks) OVER (ORDER BY ym) AS next_row,
       revenue_kopecks - LAG(revenue_kopecks) OVER (ORDER BY ym) AS delta
FROM monthly ORDER BY ym;
Что реально вернут LAG и LEAD по строкам
МесяцСейчасLAGLEADДельта
2026-01120000 NULL90000 NULL
2026-0290000120000150000-30000
2026-0415000090000060000
2026-05015000060000-150000
2026-06600000 NULL60000

Для апреля LAG берёт 90 000 копеек февраля и получает прибавку 60 000; деление этой прибавки на 90 000 дало бы те самые 66,67%. Но это февраль → апрель. Аналогично, LEAD у февраля указывает на апрель, хотя следующий календарный месяц — март.

2. Дельта и процент только при соседних месяцах

В SQLite месяц в формате YYYY-MM сортируется лексикографически в календарном порядке. Сначала извлекаем предыдущий и следующий наблюдаемые месяцы, затем сравниваем их с календарными соседями через date и strftime. Отдельный CTE не даёт случайно смешать NULL, ноль и отсутствие наблюдения.

WITH monthly(ym, revenue_kopecks) AS (
 VALUES ('2026-01',120000), ('2026-02',90000),
        ('2026-04',150000), ('2026-05',0), ('2026-06',60000)
),
neighbors AS (
 SELECT ym, revenue_kopecks,
        LAG(ym) OVER (ORDER BY ym) AS prev_ym,
        LAG(revenue_kopecks) OVER (ORDER BY ym) AS prev_amount,
        LEAD(ym) OVER (ORDER BY ym) AS next_ym
 FROM monthly
), checked AS (
 SELECT ym, revenue_kopecks,
        CASE WHEN prev_ym = strftime('%Y-%m',date(ym || '-01','-1 month'))
             THEN prev_amount END AS previous_month,
        CASE WHEN next_ym = strftime('%Y-%m',date(ym || '-01','+1 month'))
             THEN next_ym END AS next_calendar_month
 FROM neighbors
)
SELECT ym, revenue_kopecks, previous_month,
       revenue_kopecks - previous_month AS delta,
       CASE WHEN previous_month IS NOT NULL AND previous_month <> 0
            THEN ROUND(100.0*(revenue_kopecks-previous_month)/previous_month,2)
       END AS change_pct, next_calendar_month
FROM checked ORDER BY ym;
Изменение относительно предыдущего календарного месяца
МесяцВыручкаПрошлый месяцΔ, коп.Изменение, %Следующий месяц
2026-01120000 NULL NULL NULL2026-02
2026-0290000120000-30000-25,00 NULL
2026-04150000 NULL NULL NULL2026-05
2026-050150000-150000-100,002026-06
2026-0660000060000 NULL NULL

Теперь апрель не выдаёт рост за месяц: нет мартовской строки. Май показывает падение −100% относительно апреля. В июне дельта составляет +60 000 копеек, но процент равен NULL: база предыдущего месяца равна нулю, относительное изменение не определено. NULLIF(previous_month, 0) тоже защитит деление; здесь случай выражен явно через CASE. Значение «0%» для июня было бы ложным.

3. Что делать, если за месяц есть несколько оплат

Сортировка OVER (ORDER BY ym) определена однозначно только когда ym уникален в наборе для окна. При нескольких оплатах в январе сначала сворачиваем их до одного месяца, и уже потом вызываем LAG:

WITH payments(payment_id, ym, revenue_kopecks) AS (
 VALUES (1,'2026-01',70000),(2,'2026-01',50000),
        (3,'2026-02',90000),(4,'2026-04',150000),
        (5,'2026-05',0),(6,'2026-06',60000)
), monthly AS (
 SELECT ym, SUM(revenue_kopecks) AS revenue_kopecks
 FROM payments GROUP BY ym
)
SELECT ym, revenue_kopecks,
       LAG(revenue_kopecks) OVER (ORDER BY ym) AS previous_observed_month
FROM monthly ORDER BY ym;
После предварительной группировки: одна строка на месяц
МесяцВыручка, коп.Предыдущий наблюдаемый
2026-01120000 NULL
2026-0290000120000
2026-0415000090000
2026-050150000
2026-06600000

Две январские оплаты на 70 000 и 50 000 копеек дают 120 000, а не два отдельных «предыдущих месяца». Для помесячной дельты поверх этого запроса нужна та же проверка календарного соседства, что во втором примере. Если нужны сравнения по клиентам или направлениям, добавьте PARTITION BY и обеспечьте уникальный порядок периодов внутри каждой группы.

Контрольный вопрос

Дополните исходные месяцы наблюдением ('2026-03', 120000). Какую выручку, дельту и процент должен показать апрель? Проверьте второй запрос: для апреля получится предыдущая выручка 120 000, дельта +30 000 копеек и +25%. Объясните, почему прежние 66,67% были не арифметической, а смысловой ошибкой.

Все запросы самодостаточны: откройте бесплатную SQL-песочницу и выполните каждый целиком. В ней используется SQLite; отдельный курс ниже работает на MySQL 8, поэтому функции работы с датами потребуют адаптации синтаксиса.

Для продолжения: основы OVER и PARTITION BY · границы дат · накопительная сумма по времени.

Первоисточник: документация SQLite по LAG, LEAD и оконным функциям.

Следующий практический шаг — задача P105 «Прошлый месяц»: получите число заказов предыдущего месяца через LAG, а затем самостоятельно проверьте, есть ли в данных пропущенные месяцы.


Если нужна последовательная практика на MySQL 8, а не SQLite, есть отдельный курс SQL для аналитика на Stepik. Примеры этой статьи рассчитаны именно на SQLite бесплатного тренажёра.