Месячный рост SQL: LAG, формула роста и ловушки

1. Формула роста и как ее выразить в предложении

До пояснения: выручка была 250 в январе и 300 в феврале. Прежде чем прочитать формулу, произнесите вслух темпы роста за февраль.

Месячный рост равен этому месяцу минус прошлый месяц, разделенному на последний месяц. В цифрах: 300 минус 250 — 50, а 50 разделить на 250 — 0,2, что составляет 20%. Деление — это часть, которую люди пропускают при разговоре, и именно она делает число сопоставимым. Скачок на 50 — это огромная ошибка для тележки с кофе и ошибка округления для авиакомпании. Деление на последний месяц превращает необработанные изменения в долю от того, с чего вы начали.

Версия предложения имеет значение, потому что вас будут спрашивать об этом на собеседованиях и в коридорах. Практикуйте именно такую ​​формулу: «Доход вырос на 20% по сравнению с предыдущим месяцем, с 250 в январе до 300 в феврале». Сначала оцените, затем два необработанных числа. Использование обоих защитит вас, потому что процент без базы скрывает небольшие числа, а необработанное изменение без базы скрывает масштаб.

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

2. Сначала одна строка в месяц, затем сравнение

Перед пояснением: таблица заказов состоит из двенадцати строк, охватывающих пять месяцев. Сколько строк должна иметь таблица, в которой вы сравниваете соседей?

Пять. Рост — это сравнение месяцев, поэтому, прежде чем какое-либо сравнение может произойти, данные должны стать одной строкой в ​​месяц. Это зерновой ход. Уровень детализации — это то, что означает одна строка: сейчас одна строка — это один заказ, а вопрос требует, чтобы одна строка соответствовала одному месяцу. GROUP BY делает этот шаг, и GROUP BY и HAVING покрывает его полностью, если коллапс кажется волшебным.

SELECT strftime('%Y-%m', order_date) AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

тот strftime('%Y-%m', ...) часть сокращает полную дату, например 2025-01-04, до месяца, 2025-01. В других базах данных это записано по-другому: DATE_TRUNC('month', order_date) в PostgreSQL, FORMAT(order_date, 'yyyy-MM') в SQL Serverе. Везде одна и та же идея. Вот реальный результат.

Проверьте это вручную один раз, потому что доверие к остальной части страницы вытекает из этой таблицы. Январь — это 120 плюс 80 плюс 50, что составляет 250. Март — это 200 плюс 60 плюс 100, что составляет 360. И обратите внимание, чего в таблице нет: апрельской строки. Нулевые заказы означают отсутствие строк для группировки, поэтому апрель здесь не равен нулю. Оно отсутствует. Имейте это в виду.

3. ЛАГ в повседневных словах

Перед объяснением: у вас есть пять ежемесячных строк, и вам нужно, чтобы каждая строка также знала доход предыдущей строки. Куда физически пойдет это число?

В ту же строку, в новом столбце. В этом вся работа LAG(). Говоря повседневными словами: отсортируйте строки, затем для каждой строки доберитесь до строки выше и скопируйте значение вниз. LAG(revenue) OVER (ORDER BY month) говорит: «Отсортируйте по месяцам, а затем передайте в каждую строку доход из предыдущей строки». Это оконная функция, что означает, что она может видеть соседние строки, не сворачивая их. Оконные функции охватывает эту семью должным образом.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly
ORDER BY month;

Если прошлый месяц находится рядом с этим месяцем, рост представляет собой простую арифметическую операцию в одной строке: доход минус prev_revenue, деленный на prev_revenue. Но сначала посмотрите внимательно на этот результат. Две из пяти строк уже содержат оставшиеся ловушки на этой странице. Значение prev_revenue за январь равно NULL. А prev_revenue Мэй — 360, что является числом марта. У обоих есть свой раздел.

тот ORDER BY month внутри круглых скобок это не украшение. LAG понятия не имеет, что такое месяц. Он знает только «строку перед этой в том порядке, который мне дали». Выполните сортировку по неправильному столбцу, и LAG с радостью выдаст вам доход по какой-то несвязанной строке.

4. Две тихие ловушки: целочисленное деление и нулевой первый месяц.

Перед пояснением: 50 разделить на 250 равно 0,2. Что ответит база данных, если оба числа храниться как целые числа?

Ноль. В большинстве баз данных при делении одного целого числа на другое остаток выбрасывается, поскольку столбец целых чисел обещает целые числа. Так (revenue - prev_revenue) / prev_revenue равно 50/250, то есть целочисленное деление округляет до 0. Любой темп роста между минус 99% и плюс 99% становится нулевым, запрос выполняется без нареканий, и в отчете говорится, что рост остается неизменным. Я сравнивал именно это с этой таблицей и каждый месяц получал 0.

Исправление состоит в том, чтобы принудительно выполнить десятичную математику до того, как произойдет деление. Умножение на 100.0 сначала делает это, потому что одно десятичное число в выражении делает все выражение десятичным:

ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS growth_pct

Это возвращает 20,0 для февраля вместо 0. CAST(revenue AS REAL) выполняет ту же работу, если вы предпочитаете, чтобы это было прописано.

Вторая тихая ловушка – январь. Его prev_revenue равно NULL, потому что LAG добрался до первой строки и ничего там не нашел. NULL — это SQL-маркер неизвестного, дыра, куда может попасть значение, и NULL в SQL это целое руководство о том, как ведут себя эти дыры. Здесь поведение на самом деле милосердное: арифметика с NULL дает NULL, поэтому январский рост печатается как NULL, а не как поддельное число. Оставьте это так. НУЛЕВЫЙ первый месяц — это честный ответ, потому что рост до начала ваших данных действительно неизвестен. Единственная ошибка — «фиксировать» его на ноль, что означает фиксированный рост, для которого у вас нет доказательств.

5. Ловушка пропавшего месяца, происходящая на самом деле

Вот полный запрос наивного роста. Выглядит законченным. Скажите, каким будет майский рост_процента и по отношению к какому месяцу, прежде чем смотреть на результат.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

Майский ряд сообщает о падении на 30,6%. Это число указано из месяца в месяц, а не из месяца в месяц. В апреле не было заказов, поэтому в апреле нет строки, поэтому строка над маем — это март. LAG отлично справился со своей задачей: достал предыдущий ряд. Предыдущий ряд был два календарных месяца назад. Ничто не ошибается, ничего не предупреждает, и теперь в отчете говорится о снижении на один месяц, хотя на самом деле это двухмесячный спад с мертвым месяцем посередине.

Прежде чем читать исправление, скажите вслух, почему Мэй сравнила себя с Мартом. Если вы сможете это сформулировать, вы сможете найти это в любом запросе до конца вашей карьеры.

Решение состоит в том, чтобы самостоятельно создать календарь и прикрепить к нему данные, чтобы в каждом месяце была строка, независимо от того, были ли в нем заказы. В SQLite рекурсивный CTE генерирует месяцы. PostgreSQL имеет generate_series для одного и того же задания, а на складе вы обычно присоединяетесь к уже существующей таблице измерений даты.

WITH RECURSIVE calendar(month) AS (
  SELECT '2025-01'
  UNION ALL
  SELECT strftime('%Y-%m', date(month || '-01', '+1 month'))
  FROM calendar
  WHERE month < '2025-06'
),
monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT c.month,
       COALESCE(m.revenue, 0) AS revenue,
       LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month) AS prev_revenue,
       ROUND(
         100.0 * (COALESCE(m.revenue, 0)
                  - LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month))
         / NULLIF(LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month), 0),
       1) AS growth_pct
FROM calendar c
LEFT JOIN monthly m ON m.month = c.month
ORDER BY c.month;

Три небольшие детали несут вес. LEFT JOIN сохраняется каждый календарный месяц, даже если нет соответствующих заказов. COALESCE(m.revenue, 0) превращает пустой апрель в настоящий ноль, и это правильно, потому что нулевые заказы действительно означали нулевой доход. И NULLIF(..., 0) защищает деление: рост с нулевого месяца будет делиться на ноль, поэтому вместо этого возвращается NULL. Вот реальный результат.

Теперь история правдива. Апрель упал на 100% и ни к чему. Мэйский рост равен NULL, поскольку процентное увеличение от нуля не определено, а NULL — это честный способ вывести неопределенное значение. Фальшивое месячное падение на 30,6% исчезло, его заменило то, что произошло на самом деле: крах и перезапуск.

6. Полное до и после

Та же таблица, тот же вопрос: как растет выручка из месяца в месяц?

До

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly;

Три дефекта, ноль сообщений об ошибках. При целочисленном делении каждый темп роста выводится как 0. Отсутствие апреля сравнивает май с мартом. И ничто на экране не сообщает читателю, что что-то произошло.

после

-- ============================================================
-- Month-over-month revenue growth.
-- WHY the calendar CTE: LAG compares adjacent ROWS. April has
--      no orders, so without a calendar row May would compare
--      against March and report a fake one-month drop.
-- WHY 100.0: integer division would round every rate to 0.
-- WHY NULLIF: growth from a zero month is undefined, and NULL
--      is the honest way to report undefined.
-- ============================================================
WITH RECURSIVE calendar(month) AS (
  SELECT '2025-01'
  UNION ALL
  SELECT strftime('%Y-%m', date(month || '-01', '+1 month'))
  FROM calendar
  WHERE month < '2025-06'
),
monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT c.month,
       COALESCE(m.revenue, 0) AS revenue,
       ROUND(
         100.0 * (COALESCE(m.revenue, 0)
                  - LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month))
         / NULLIF(LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month), 0),
       1) AS growth_pct
FROM calendar c
LEFT JOIN monthly m ON m.month = c.month
ORDER BY c.month;

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

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

7. Крайние случаи: чтение негативов, форматирование, соотношение месячного и годового показателей.

Перед пояснением: в один месяц выручка упала на 30%, а в следующем выросла на 30%. Вернулся ли он туда, где все началось?

Нет, и это ловушка отрицательного роста показателей. Уменьшение на 30% от 100 — это 70. Увеличение на 30% от 70 — это 91. Процентные изменения складываются независимо от того, где они происходят, поэтому для того, чтобы отменить падение, требуется больший подъем. Когда вы представляете падение, назовите и уровень: «на 30,6%, с 360 до 250». Пара уровней удерживает читателей от мысленного изменения процентного значения.

Форматируем в последний момент. Сохраняйте рост в виде простого числа, например -30,6, на протяжении всего SQL и позволяйте уровню отчета добавлять знак процента. Строки типа «-30,6%» невозможно отсортировать, усреднить или составить диаграмму, и кто-то всегда пытается это сделать. ROUND(x, 1) достаточна точность для деловой аудитории; 20,03847% не сообщают ничего такого, чего не сообщает 20,0%.

Крошечная база дает глупый процент. Рост от 2 до 10 составляет 400%. Правда и бесполезно. Когда последний месяц близок к нулю, сообщите о необработанном изменении вместе со ставкой или подавите ставку ниже записанного вами минимального уровня.

Месяц месяца шумен там, где календарь сезонный. Продажи мороженого падают каждый сентябрь, и никто не должен получать тревожные электронные письма по этому поводу. Месяц за месяцем задается вопрос «что только что изменилось», что усиливает сезонность и разовые всплески. Из года в год июнь сравнивается с июнем прошлого года, поэтому сезон сводится на нет, и тенденция проявляется. Используйте Мама, чтобы быстро обнаружить повороты, г/г, чтобы спокойно констатировать тенденцию, и обе линии на одном графике, когда аудитория может это принять. Комплект прогнозирования корректно работает с сезонностью, в том числе когда «падение» — это просто дыхание календаря.