Рост месяц к месяцу в SQL при пропущенных месяцах

В отчёте есть выручка за апрель и июнь, а май отсутствует. Аналитик добавляет LAG(revenue) и получает сравнение июня с апрелем, подписанное как «рост месяц к месяцу». Число вычислено верно по двум соседним строкам таблицы, но строка месяца и календарный месяц здесь не одно и то же. Чтобы сравнить июнь с маем, май должен присутствовать в последовательности.

Сначала задаём диапазон отчёта и строим по одной строке на первый день каждого месяца. Потом присоединяем продажи. В этом примере отсутствие строки означает подтверждённое отсутствие продаж, то есть ноль. Если данные за май просто не загрузились, подставлять ноль нельзя: отчёт должен показать проблему свежести данных, а не падение бизнеса.

После календаря LAG отвечает на нужный вопрос

Рекурсивный CTE перечисляет апрель, май и июнь. LEFT JOIN сохраняет май даже без записи в таблице продаж. Лишь затем LAG берёт предыдущую календарную строку. Процент роста считаем как (текущий − предыдущий) / предыдущий × 100.

CREATE TABLE monthly_sales (month_start TEXT PRIMARY KEY, revenue INTEGER);
INSERT INTO monthly_sales VALUES ('2025-04-01',100),('2025-06-01',120);
WITH RECURSIVE months(month_start) AS (
  VALUES('2025-04-01')
  UNION ALL
  SELECT date(month_start,'+1 month') FROM months
  WHERE month_start < '2025-06-01'
), filled AS (
  SELECT m.month_start, COALESCE(s.revenue,0) AS revenue
  FROM months AS m LEFT JOIN monthly_sales AS s USING(month_start)
), compared AS (
  SELECT month_start,revenue,
         LAG(revenue) OVER (ORDER BY month_start) AS previous_revenue
  FROM filled
)
SELECT month_start,revenue,previous_revenue,
       CASE WHEN previous_revenue IS NULL OR previous_revenue=0 THEN NULL
            ELSE ROUND((revenue-previous_revenue)*100.0/previous_revenue,1)
       END AS growth_pct
FROM compared ORDER BY month_start;

Результат: апрель — выручка 100, предыдущего месяца в диапазоне нет; май — 0 и изменение −100%; июнь — 120 после нулевого мая, процент роста остаётся NULL. Деление на ноль не превращается в бесконечный рост. Для июня можно отдельно показать абсолютное изменение +120 рублей, если такой показатель нужен.

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

Граница отчёта и частичный текущий месяц

Первый месяц диапазона не имеет предыдущего значения в этом наборе. Если нужен апрель к марту, начинайте внутренний календарь с марта, а фильтр отображения применяйте после вычисления LAG. Иначе предыдущая строка будет удалена до окна. Это частая ошибка, когда аналитик меняет период дашборда.

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

При загрузке нескольких магазинов сначала агрегируйте выручку до зерна «магазин — месяц», затем создайте календарные строки для каждого существующего магазина и посчитайте LAG с PARTITION BY shop_id. Иначе предыдущий месяц одного магазина может оказаться рядом со строкой другого.

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

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

На тестовом наборе проверьте три случая: два соседних месяца с выручкой, месяц с подтверждённым нулём между ними и отсутствующий месяц из-за сбоя загрузки. Только второй случай должен стать нулём в бизнес-показателе. Третий требует сигнала качества данных — иначе «падение на 100%» будет ошибкой пайплайна, а не продаж.

На курсе «Продвинутый SQL» календарные ряды и оконные функции соединяются с проверкой неполных данных и витриной интернет-магазина. Запросы выполняются на SQLite в браузере и проверяются на разных наборах. Откройте программу курса, чтобы уверенно строить временные сравнения за пределами маленького примера.

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