0Pricing
SQL Academy · Урок

Распространённые отчёты по датам (MTD, WoW, YoY)

Создавайте отчёты с начала месяца, по сравнению с предыдущей неделей и по сравнению с прошлым годом с помощью date_trunc и самосоединений или оконных функций

«Распространённые отчёты по датам (MTD, WoW, YoY)» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Три классических отчёта по периодам

Почти каждой бизнес-панели нужны:

  • MTD — с начала месяца
  • НкН — неделя к неделе
  • ГкГ — год к году

С начала месяца

Сумма за текущий месяц на данный момент:

SELECT SUM(total) AS mtd_revenue
FROM orders
WHERE created_at >= date_trunc('month', NOW())
  AND created_at <  date_trunc('month', NOW()) + INTERVAL '1 month';

С начала года

Тот же шаблон, но на уровне года:

SELECT SUM(total) AS ytd_revenue
FROM orders
WHERE created_at >= date_trunc('year', NOW());

Неделя к неделе

Сравнение текущей недели с предыдущей:

SELECT
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW())
  ) AS this_week,
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week'
      AND created_at <  date_trunc('week', NOW())
  ) AS last_week
FROM orders
WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week';

Год к году

Тот же период по сравнению с тем же периодом прошлого года:

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
WHERE created_at >= NOW() - INTERVAL '2 years'
GROUP BY 1
ORDER BY 1;

-- Then plot with two lines: current year, previous year.

Ежедневный ряд без пропусков

Если в какой-то день нет заказов, GROUP BY пропускает этот день. Создайте полный календарный ряд и выполните LEFT JOIN:

SELECT day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE,
  INTERVAL '1 day'
) AS day
LEFT JOIN orders o ON date_trunc('day', o.created_at) = day
GROUP BY day
ORDER BY day;

Накопительные итоги

Оконная функция вычисляет накопительную сумму без самосоединений:

SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
ORDER BY day;

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

Рост год к году с использованием LAG:

SELECT month,
       revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
       (revenue - LAG(revenue, 12) OVER (ORDER BY month))
         / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) AS yoy_growth
FROM monthly_revenue;

Последние 7 / 30 дней (скользящий интервал)

Скользящий интервал и интервал, выровненный по календарю, — разные вещи. Выбирайте вариант, который действительно соответствует смыслу показателя в вашей компании:

-- Trailing 7 days, ending now:
WHERE created_at >= NOW() - INTERVAL '7 days';

-- This calendar week, from Monday:
WHERE created_at >= date_trunc('week', NOW());

Особенности часовых поясов

Понятие «сегодня» зависит от часового пояса пользователя. Выполните преобразование перед усечением:

SELECT date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS la_day,
       COUNT(*)
FROM events
GROUP BY 1;

Предварительное агрегирование с помощью материализованных представлений

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

Повторение

Отчёты по периодам строятся на нескольких шаблонах.

  • Полуоткрытые интервалы с date_trunc
  • FILTER для параллельных групп периодов
  • generate_series + LEFT JOIN для заполнения пропущенных дней
  • LAG для изменений от периода к периоду

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

Какое выражение обозначает «первый момент текущего месяца»?

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

Урок «Распространённые отчёты по датам (MTD, WoW, YoY)» бесплатный?

Да — полный текст урока «Распространённые отчёты по датам (MTD, WoW, YoY)» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Распространённые отчёты по датам (MTD, WoW, YoY)»?

Создавайте отчёты с начала месяца, по сравнению с предыдущей неделей и по сравнению с прошлым годом с помощью date_trunc и самосоединений или оконных функций Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

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

Сколько времени занимает урок «Распространённые отчёты по датам (MTD, WoW, YoY)»?

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

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

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

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

  1. Арифметика дат и INTERVAL
  2. EXTRACT, DATE_TRUNC, AGE
  3. Часовые пояса: TIMESTAMP и TIMESTAMPTZ
  4. Распространённые отчёты по датам (MTD, WoW, YoY)
← Назад к SQL Academy