Как с помощью рекурсивного CTE построить полный список дат от начала до конца периода, если отдельной календарной таблицы нет?

Короткий ответ

Нужно написать рекурсивный WITH RECURSIVE, где «якорная» часть задаёт стартовую дату, а рекурсивная часть добавляет по одному дню, пока не достигнута конечная дата; в BigQuery для той же задачи проще использовать встроенную функцию GENERATE_DATE_ARRAY.

Как это работает подробнее

рекурсивный CTE состоит из двух частей, объединённых UNION ALL: «базовый случай» (одна начальная строка) и «рекурсивный шаг», который ссылается на сам CTE и добавляет следующий день; СУБД выполняет рекурсивный шаг многократно, пока условие в WHERE не остановит рекурсию.

Пример кода

question-42.sqlКопировать

WITH RECURSIVE date_series(day) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT day + 1
  FROM date_series
  WHERE day < DATE '2024-01-31'
)
SELECT day
FROM date_series
ORDER BY day;

Что использовать на практике

• сгенерированный список дат обычно используют как «скелет» для LEFT JOIN к фактическим данным, чтобы явно увидеть дни без событий как 0, а не как отсутствующие строки — это важно для корректных графиков временных рядов и расчёта средних по дням. Результат — ровно 31 строка. Проверка: SELECT COUNT(*) FROM date_series должен совпасть с числом дней между начальной и конечной датой включительно.

Подводные камни

• Не задают условие остановки и получают бесконечную рекурсию либо превышение лимита СУБД. В SQL Server по умолчанию действует MAXRECURSION 100, тогда как в PostgreSQL такого стандартного лимита 100 нет; для больших диапазонов обычно удобнее generate_series или календарная таблица.