Как найти пропуски в датах SQL

Зачем это знать

Пропуск в данных превращается в пропуск в отчёте: день без записей «съедает» сезонность, а дашборд рисует ложный провал динамики. Причём отличить «в этот день реально ничего не произошло» от «данные за этот день не доехали» по одной таблице событий нельзя — надо явно сопоставить факт с полным календарём. Это частая задача и в ETL (проверка, что пайплайн отработал каждый день), и в ad-hoc-аналитике («вот daily events, найди дни без событий»).

На собесах задача «найди дни, когда не было заказов» — классика middle-уровня. Ключевая идея у всех решений одна: сначала где-то взять полный список дат, потом слева приджойнить факты и оставить строки, где факта нет. Различаются способы только тем, откуда берётся этот полный список. Разберём три основных плюс обратную задачу — поиск непрерывных серий.

Способ 1: generate_series (Postgres)

generate_series генерирует полный ряд дат прямо в запросе, без отдельной таблицы:

SELECT d.day FROM (
    SELECT generate_series('2026-01-01'::DATE, '2026-04-30'::DATE, '1 day') AS day
) d
LEFT JOIN orders o ON DATE(o.created_at) = d.day
WHERE o.id IS NULL;

Механика: строим полный календарь за период, делаем LEFT JOIN с заказами, и там, где заказов не было, все колонки orders окажутся NULL. Условие WHERE o.id IS NULL оставляет ровно пропущенные дни. Это самый удобный вариант «на лету», когда справочной таблицы дат нет.

Способ 2: calendar table

Если пропуски приходится искать регулярно, удобнее один раз завести справочную таблицу дат calendar:

SELECT c.day FROM calendar c
LEFT JOIN orders o ON DATE(o.created_at) = c.day
WHERE o.id IS NULL AND c.day BETWEEN '2026-01-01' AND '2026-04-30';

Логика та же — полный календарь слева, факты справа, — но список дат берётся из готовой таблицы. Плюс подхода в том, что он работает в любой СУБД, а не только в Postgres, и в календарь можно заранее положить признаки выходных, праздников и кварталов, чтобы переиспользовать их в других отчётах.

Способ 3: LAG для поиска пропусков

Если полного календаря не нужно, а надо просто найти разрывы между уже существующими датами, помогает оконная функция LAG:

WITH days AS (
    SELECT DISTINCT DATE(created_at) AS day FROM orders
),
gaps AS (
    SELECT day,
           LAG(day) OVER (ORDER BY day) AS prev_day,        -- предыдущая дата с заказом
           day - LAG(day) OVER (ORDER BY day) AS diff        -- разрыв в днях
    FROM days
)
SELECT * FROM gaps WHERE diff > 1;

LAG подтягивает предыдущую по порядку дату, а разница между текущей и предыдущей показывает величину разрыва. Если она больше единицы — между этими датами был пропуск. Способ дешёвый (не нужен весь календарь), но у него важное ограничение: он показывает границы разрывов, а не сами отсутствующие даты, и молчит про пропуски на концах периода — если данных нет за первые числа месяца, разрыва между строками не возникнет.

Найти непрерывные серии (islands)

Обратная задача — найти непрерывные серии подряд идущих дней. Это классический паттерн «gaps and islands»:

WITH numbered AS (
    SELECT day, day - ROW_NUMBER() OVER (ORDER BY day) AS grp
    FROM days
)
SELECT MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS streak_len
FROM numbered
GROUP BY grp;

Трюк в том, что для подряд идущих дат разность day - ROW_NUMBER() постоянна: и дата, и номер строки растут на единицу шаг за шагом, поэтому их разность внутри непрерывной серии не меняется. Как только появляется пропуск, дата «перепрыгивает» вперёд, а номер строки нет — значение grp меняется, и начинается новый «остров». Группируя по grp, получаем границы и длину каждой серии. Тот же приём считает, например, стрики активности пользователя.

С почасовыми данными

Всё то же работает не только для дней. Для почасовых пропусков меняется только шаг генерации:

SELECT generate_series(
    '2026-04-22 00:00'::TIMESTAMP,
    '2026-04-22 23:00'::TIMESTAMP,
    '1 hour'
) AS hour;

Дальше — тот же LEFT JOIN к фактам и WHERE ... IS NULL. Аналогично можно искать пропуски по 15 минутам, неделям или месяцам, подставив нужный интервал.

Рекурсивный CTE (если нет generate_series)

Если generate_series в вашей СУБД нет, полный ряд дат строится рекурсивным CTE:

WITH RECURSIVE dates AS (
    SELECT '2026-01-01'::DATE AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < '2026-04-30'
)
SELECT d.d FROM dates d
LEFT JOIN orders o ON DATE(o.created_at) = d.d
WHERE o.id IS NULL;

Рекурсия стартует с первой даты и на каждом шаге добавляет следующий день, пока не дойдёт до конца периода. Дальше — знакомый LEFT JOIN. Работает в Postgres, MSSQL, SQLite и других СУБД с поддержкой рекурсивных CTE.

На собесе

Вопрос обычно звучит так: «Есть таблица orders с created_at. Найдите дни без заказов за последний месяц».

Идеальный ответ: сгенерировать полный календарь (через generate_series или calendar table), сделать LEFT JOIN к заказам и отфильтровать WHERE ... IS NULL. Проговорите вслух, почему LEFT JOIN, а не INNER: внутреннее соединение выкинет как раз те дни, которые вы ищете.

Если интервьюер добавит «а если generate_series недоступен» — предложите рекурсивный CTE или calendar table. А вопрос про непрерывные серии («сколько дней подряд были заказы») — сигнал вспомнить про паттерн gaps and islands с трюком day - ROW_NUMBER().

Частые ошибки

• INNER JOIN вместо LEFT JOIN. Внутреннее соединение оставит только дни, где заказы были, то есть выбросит ровно тот результат, который вы ищете. Нужен именно LEFT JOIN и фильтр по NULL.
• Условие на правую таблицу в WHERE, а не в ON. Если в WHERE дописать фильтр по колонке из orders (например, o.status = 'paid'), LEFT JOIN фактически превращается в INNER и пропуски исчезнут. Такие условия выносят в ON.
• LAG не ловит пропуски на краях. Метод через LAG находит разрывы только между существующими датами. Если данных нет в начале или конце периода, разрыва между строками не будет — для полной картины нужен полный календарь.
• Забыть про часовой пояс и время в timestamp. DATE(created_at) без приведения к нужной таймзоне может отнести полночные события к соседнему дню и создать «фантомные» пропуски или, наоборот, скрыть настоящие.
• Слишком узкий диапазон календаря. Если границы generate_series не покрывают весь искомый период, дни за его пределами просто не попадут в результат и останутся незамеченными.

Связанные темы

• Задачи SQL на собесе
• 50 вопросов по SQL
• Оконные функции

FAQ

Какой способ быстрее?

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

Что такое gaps and islands?

Это класс задач, где нужно найти «острова» (islands) — непрерывные последовательности значений — и разделяющие их «пропуски» (gaps). Стандартное решение — присвоить каждому острову одинаковый идентификатор группы через разность даты и номера строки (day - ROW_NUMBER()), а затем сгруппировать по нему.

Работает ли это в MySQL?

generate_series в MySQL нет (появился только в MariaDB и то как table function). Используйте calendar table, numbers table или рекурсивный CTE (поддерживается с MySQL 8.0). Логика LEFT JOIN + IS NULL при этом остаётся неизменной.

Как найти пропуски сразу по нескольким сущностям?

Например, «дни без заказов для каждого магазина». Нужно построить декартово произведение календаря и списка сущностей (CROSS JOIN calendar × stores), а затем приджойнить факты по паре (день, магазин). Тогда пропуски найдутся отдельно для каждого магазина, а не суммарно.

Чем отличается поиск пропущенных дат от gap detection через LAG?

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

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.