generate_series в SQL: ряд дат без пропусков и нули

Короткий ответ: ось отдельно, факты отдельно

В PostgreSQL календарь создаёт generate_series(TIMESTAMP '2026-07-15', TIMESTAMP '2026-07-21', INTERVAL '1 day'); результат приводим к DATE. Сначала агрегируем рейсы по дню, затем присоединяем их к каждой календарной дате через LEFT JOIN и ставим COALESCE(flights,0). Важно ограничить факты тем же полуоткрытым периодом [15 июля, 22 июля).

Здесь все статусы рейса входят в счёт. Если нужен только вылетевший борт, добавьте условие статуса в CTE фактов, а не в WHERE после LEFT JOIN. Тогда календарные нули останутся. Опорная карта курса связывает этот приём с окнами и проверкой временных рядов.

Перед запросом выпишите три договора: начало включается, конец исключается, дата берётся из запланированного вылета. Календарь перечисляет дни, а не подбирает их из фактов. Это принципиально: если строить границы как min/max только по IWA, период без единого рейса вообще не породит календарь. В отчёте обычно нужен внешний диапазон от продукта или планирования, а не диапазон, случайно обнаруженный в разреженных фактах.

Без календаря пять дней исчезают

Прямой GROUP BY CAST(scheduled_departure AS DATE) по flights даст 18 июля — 2 и 21 июля — 2. В ответе нет 15, 16, 17, 19 и 20 июля. По отсутствию строки нельзя понять, было ли ноль рейсов, не пришли ли данные или дата просто не предусмотрена отчётом.

Иваново выбрано не как задача урока о редких аэропортах за августовскую неделю, а как другой июльский период. Одна строка результата — дата вылета по расписанию, не дата продажи и не дата фактического вылета. Сменить поле времени — значит задать другую метрику.

Для контроля исходника полезны два запроса: число вылетов во всём заданном окне и список уникальных дат с фактом. Здесь первая проверка даёт четыре рейса, вторая — две даты. Если после календаря сумма стала меньше четырёх, потерян конец периода или неверно задан ключ JOIN. Если стала больше четырёх, вероятно, календарь содержит повторные даты или присоединён неагрегированный набор с другой размерностью.

PostgreSQL: семь дат и семь ответов

В календарном CTE выбираем семь дат. В фактах фильтр по аэропорту и времени ставим до соединения; каждая строка суточной сводки уникальна по дате. Поэтому LEFT JOIN не может размножить дату. В результате пять нулей и две двойки, всего четыре вылета.

Обратите внимание на x::date: generate_series с явно заданными аргументами TIMESTAMP возвращает timestamp, а не DATE. Если передать аргументы типа DATE в PostgreSQL 14, выбирается перегрузка timestamp with time zone; на локальную календарную дату тогда может повлиять часовой пояс сеанса. Явный тип аргументов и приведение результата удерживают нужный контракт.

В отдельном CTE daily уже одна строка на день. Можно соединять календарь и непосредственно с flights, а затем группировать, но при добавлении второй категории легко ошибиться и умножить строки. Предварительная агрегация показывает зерно каждой стороны: calendar — день, daily — день IWA. Если расширять до нескольких аэропортов, ключ сводки и JOIN должен стать составным: день плюс код аэропорта.

WITH calendar AS (
  SELECT x::date AS day
  FROM generate_series(TIMESTAMP '2026-07-15', TIMESTAMP '2026-07-21', INTERVAL '1 day') AS g(x)
), daily AS (
  SELECT scheduled_departure::date AS day, count(*) AS flights
  FROM flights
  WHERE departure_airport = 'IWA'
    AND scheduled_departure >= TIMESTAMP '2026-07-15'
    AND scheduled_departure < TIMESTAMP '2026-07-22'
  GROUP BY 1
)
SELECT c.day, coalesce(d.flights,0) AS flights
FROM calendar c LEFT JOIN daily d ON d.day = c.day
ORDER BY c.day;

DuckDB: generate_series включает конец, range исключает

DuckDB умеет табличные generate_series и range. Для времени с шагом в день первая функция включает правый конец; вторая — нет. Поэтому range(DATE '2026-07-15', DATE '2026-07-22', INTERVAL '1 day') тоже создаёт ровно семь дат, от 15-го до 21-го. В DuckDB доступна и списочная форма этих функций; ниже используется табличная в FROM.

Нельзя автоматически заменить generate_series на range с теми же двумя датами: если передать 21 июля как stop, последний день пропадёт. Проверка count(*)=7, min=15, max=21 ловит эту ошибку до присоединения фактов.

В PostgreSQL допустим числовой generate_series для часов или порядковых номеров; для календаря нужен шаг INTERVAL. Не собирайте дату строковым сложением номера дня: месяцы имеют разную длину, а тип времени важен для JOIN. DuckDB возвращает ряд как таблицу в FROM или как список в выражении; в запросе показана табличная форма, потому что она сразу даёт одну строку на день.

SELECT CAST(x AS DATE) AS day
FROM range(DATE '2026-07-15', DATE '2026-07-22', INTERVAL '1 day') AS r(x)
ORDER BY day;

То же в pandas: date_range и reindex

В pandas сначала отбираем тот же полуоткрытый период и агрегируем даты из Parquet. pd.date_range(start, end, inclusive="left") при end=22 июля создаёт даты 15–21 июля; reindex(..., fill_value=0) достраивает отсутствующие. Код печатает семь дневных значений и сумму 4, как SQL.

Если написать pd.date_range(15 июля, 22 июля) без inclusive, pandas включит обе границы и вернёт восемь дат. Колонку scheduled_departure явно приводим к datetime. В этих Parquet время московское без timezone; перенос на UTC-поток требует явного преобразования до выделения дня.

Обратите внимание на индекс: groupby("day").size() содержит только дни с рейсами, а reindex(calendar, fill_value=0) расширяет его до семи. Это pandas-аналог LEFT JOIN к справочнику дат. resample("D") может дать похожую форму, но его начальная и конечная границы зависят от имеющихся фактов; если весь период пуст, ось не появится. Внешний date_range сохраняет контракт отчёта независимо от наличия строк.

import pandas as pd

f = pd.read_parquet('flights.parquet', columns=['scheduled_departure', 'departure_airport'])
f['scheduled_departure'] = pd.to_datetime(f['scheduled_departure'])
start, end = pd.Timestamp('2026-07-15'), pd.Timestamp('2026-07-22')
f = f.loc[f.departure_airport.eq('IWA') & f.scheduled_departure.ge(start) & f.scheduled_departure.lt(end)].copy()
f['day'] = f.scheduled_departure.dt.normalize()
calendar = pd.date_range(start, end, freq='D', inclusive='left')
daily = f.groupby('day').size().reindex(calendar, fill_value=0)
print(len(daily), daily.tolist(), int(daily.sum()))
print(len(pd.date_range(start, end, freq='D')))

Сетка день × категория

Одна дата — одна ось. Для отчёта по нескольким аэропортам понадобится сетка: calendar CROSS JOIN со списком аэропортов, затем LEFT JOIN дневного агрегата по обоим ключам. Для трёх категорий и семи дней это 21 ожидаемая клетка. Оцените размер произведения до запуска; сырой flights здесь не должен стоять справа от CROSS JOIN как вторая ось.

Если категория появляется только в фактах, она может отсутствовать весь период и не попасть в список. Лучше брать список из справочника или заданного контрактом отчёта. FULL/CROSS JOIN на сверке показывает тот же приём на часах и платформах.

Ловушка 1: фильтр в WHERE после LEFT JOIN

Неправильно: сначала соединить календарь с flights, затем написать WHERE f.departure_airport='IWA'. У пяти дат без пары справа NULL, условие не истинно, и останутся только 18 и 21 июля. Исправление — фильтровать факты внутри CTE или в ON соединения.

Если нужен диапазон времени, он тоже относится к фактам. Условие WHERE f.scheduled_departure < ... после LEFT JOIN так же удалит пустые даты, хотя результат будет выглядеть технически корректным.

Ловушка 2: конец ряда считают одинаковым в разных функциях

generate_series(15,21,1 день) включает 21 июля, а DuckDB range(15,21,1 день) его исключает. Получится шесть дат вместо семи и исчезнет один из двух дней с вылетами: общая сумма упадёт с 4 до 2.

В pandas date_range(15,22) без inclusive="left" наоборот добавит 22 июля и даст восемь дат. Исправление — задать единое соглашение [start,end) и проверять минимум, максимум и число элементов оси.

Ловушка 3: график подменяет отсутствие данных нулём

Пять нулей за июльскую неделю здесь означают «нет строк рейсов по расписанию из IWA в пределах полного периода». Это не доказывает, что каждый источник поставил данные вовремя. Если загрузка за 19 июля отсутствовала целиком, SQL всё равно покажет 0.

Исправление — параллельно контролировать объём источника и статус загрузки. Не прячьте отсутствие данных в COALESCE до того, как определили ожидаемую ось и полноту периода.

Ловушка 4: неполный край наблюдения

Срез учебной базы — 11 августа 2026 года в 18:00 МСК. Достроить календарь до полуночи 12 августа и считать оставшиеся часы нулём означало бы сравнивать неполный день с полными. Недельная линия может «упасть» только из-за границы выгрузки.

Наш пример 15–21 июля лежит далеко от края. Для текущих дашбордов исключайте незавершённый день либо показывайте его отдельной отметкой. Интервалы в SQL разбирают границы подробнее.

Ловушка 5: дата в неверной временной зоне

В Parquet scheduled_departure уже записан московским локальным временем без смещения. Если сначала ошибочно объявить его UTC и перевести в Москву, рейсы позднего вечера перейдут на соседний день; сумма недели может сохраниться, а дневной график изменится.

На реальном UTC-потоке конвертация нужна, но она должна происходить до ::date и до группировки. Тип timestamp без зоны не сообщает бизнес-смысл автоматически; его задаёт контракт источника.

Ловушка 6: окно по разреженным строкам

Если после исходного GROUP BY оставить лишь 18 и 21 июля, ROWS BETWEEN 6 PRECEDING на этих двух строках будет означать «до семи дат с рейсами», а не календарную неделю. На полном ряду из семи дат та же рамка действительно охватит неделю 15–21 июля с пятью нулями.

Сначала решите, входят ли пустые дни в знаменатель среднего, потом выбирайте рамку. ROWS и RANGE на нерегулярном ряду показывает, насколько различается результат.

Даже после построения ряда окно надо вычислять до финального фильтра отображения. Если график показывает только 18–21 июля, а среднее за семь дней рассчитывается на том же сокращённом наборе, 15–17 июля снова выпадут из истории. Календарь должен покрывать и видимый период, и необходимый запас слева для рамки. Сколько дней запаса нужно, следует из определения метрики, а не из удобства SELECT.

Частые вопросы

Включает ли generate_series последнюю дату? Да, если шаг точно достигает stop; здесь 21 июля входит.

Почему мой LEFT JOIN потерял нули? Часто условие по правой таблице стоит в WHERE и удаляет строки без пары.

Чем range в DuckDB отличается? Табличный range исключает правый конец; для 15–21 июля задайте stop=22 июля.

Можно ли заменить NULL на 0 сразу? После построения ожидаемой оси и проверки полноты источника; иначе отсутствие загрузки станет похожим на измеренный ноль.