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-01 | 120000 |
| 2026-02 | 90000 |
| 2026-04 | 150000 |
| 2026-05 | 0 |
| 2026-06 | 60000 |
Исполнимый, но смыслово неверный для 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 | Дельта |
|---|---|---|---|---|
| 2026-01 | 120000 | NULL | 90000 | NULL |
| 2026-02 | 90000 | 120000 | 150000 | -30000 |
| 2026-04 | 150000 | 90000 | 0 | 60000 |
| 2026-05 | 0 | 150000 | 60000 | -150000 |
| 2026-06 | 60000 | 0 | NULL | 60000 |
Для апреля 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-01 | 120000 | NULL | NULL | NULL | 2026-02 |
| 2026-02 | 90000 | 120000 | -30000 | -25,00 | NULL |
| 2026-04 | 150000 | NULL | NULL | NULL | 2026-05 |
| 2026-05 | 0 | 150000 | -150000 | -100,00 | 2026-06 |
| 2026-06 | 60000 | 0 | 60000 | 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-01 | 120000 | NULL |
| 2026-02 | 90000 | 120000 |
| 2026-04 | 150000 | 90000 |
| 2026-05 | 0 | 150000 |
| 2026-06 | 60000 | 0 |
Две январские оплаты на 70 000 и 50 000 копеек дают 120 000, а не два отдельных «предыдущих месяца». Для помесячной дельты поверх этого запроса нужна та же проверка календарного соседства, что во втором примере. Если нужны сравнения по клиентам или направлениям, добавьте PARTITION BY и обеспечьте уникальный порядок периодов внутри каждой группы.
Контрольный вопрос
Дополните исходные месяцы наблюдением ('2026-03', 120000). Какую выручку, дельту и процент должен показать апрель? Проверьте второй запрос: для апреля получится предыдущая выручка 120 000, дельта +30 000 копеек и +25%. Объясните, почему прежние 66,67% были не арифметической, а смысловой ошибкой.
Все запросы самодостаточны: откройте бесплатную SQL-песочницу и выполните каждый целиком. В ней используется SQLite; отдельный курс ниже работает на MySQL 8, поэтому функции работы с датами потребуют адаптации синтаксиса.
Первоисточник: документация SQLite по LAG, LEAD и оконным функциям.
Если нужна последовательная практика на MySQL 8, а не SQLite, есть отдельный курс SQL для аналитика на Stepik. Примеры этой статьи рассчитаны именно на SQLite бесплатного тренажёра.