SQL Ready | Базы Данных on Telegram: “Генерация пропущенных дат в отчётах: почему LEFT…”

Генерация пропущенных дат в отчётах: почему LEFT JOIN не помогает!

Классическая задача — построить отчёт по дням. Например, количество заказов по датам. И внезапно оказывается, что в выдаче нет некоторых дней.

Таблица:

orders(id, created_at)

Плохой вариант:

SELECT
    DATE(created_at) AS day,
    COUNT(*) AS orders_count
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;

Проблема: если в какой-то день не было заказов — строки просто не будет. В аналитике это почти всегда ошибка, потому что отсутствие строки не равно ноль значений.

Попытка через LEFT JOIN сама по себе не помогает — без таблицы дат нечего достраивать. Правильный подход — сначала явно сгенерировать календарь, а потом присоединить данные.

В PostgreSQL для этого есть generate_series:

WITH calendar AS (
    SELECT generate_series(
        DATE '2026-01-01',
        DATE '2026-01-10',
        INTERVAL '1 day'
    )::date AS day
)
SELECT
    c.day,
    COUNT(o.id) AS orders_count
FROM calendar c
LEFT JOIN orders o
  ON DATE(o.created_at) = c.day
GROUP BY c.day
ORDER BY c.day;

Теперь: каждый день присутствует; если заказов нет — COUNT(o.id) вернёт 0.

Почему именно COUNT(o.id), а не COUNT(*): COUNT(*) посчитает строку календаря даже без совпадений — всегда ≥ 1; COUNT(o.id) считает только реальные заказы — корректный 0 при отсутствии данных.

Ещё один важный момент — условие в JOIN: ON DATE(o.created_at) = c.day. Так писать удобно, но это ломает использование индекса по created_at.

Лучше диапазон:

ON o.created_at >= c.day
AND o.created_at <  c.day + INTERVAL '1 day'

Это позволяет использовать индекс и ускоряет запрос на больших таблицах.

Расширение задачи — накопительный итог по дням (используется тот же CTE calendar):

SELECT
    c.day,
    COUNT(o.id) AS orders_count,
    SUM(COUNT(o.id)) OVER (ORDER BY c.day) AS running_total
FROM calendar c
LEFT JOIN orders o
  ON o.created_at >= c.day
 AND o.created_at <  c.day + INTERVAL '1 day'
GROUP BY c.day
ORDER BY c.day;

Если нужно ограничение по периоду по данным, а не вручную:

WITH bounds AS (
    SELECT
        MIN(created_at)::date AS min_day,
        MAX(created_at)::date AS max_day
    FROM orders
),
calendar AS (
    SELECT generate_series(min_day, max_day, INTERVAL '1 day')::date AS day
    FROM bounds
)
SELECT ...

Календарь строится автоматически по данным. Если created_at — timestamptz, границы дня зависят от timezone.

🔥 Если в отчёте есть время — сначала формируйте ось времени, потом присоединяйте данные. Иначе вы работаете не с нулями, а с отсутствием строк, что даёт искажённую картину.

➡️ SQL Ready | #практика