Gaps and Islands в SQL: как найти непрерывные серии дат

Gaps and Islands — класс задач на поиск непрерывных серий внутри упорядоченных данных. Island, или «остров», — несколько соседних значений без разрыва. Gap — пропуск между ними. В SQL такой запрос собирает дни бесперебойной работы оборудования, последовательные номера документов или интервалы активности пользователя.

Ниже разберём серии календарных дат на компактном наборе проверок оборудования. У практической задачи про gaps and islands другие таблицы и контракт результата: готового запроса для неё здесь нет.

Исходные данные и ожидаемые серии

Таблица хранит дни, когда станок прошёл контроль. Пара (machine_id, checked_on) уникальна: у одного станка не бывает двух записей за один календарный день.

CREATE TEMP TABLE machine_checks (
  machine_id text NOT NULL,
  checked_on date NOT NULL,
  PRIMARY KEY (machine_id, checked_on)
);

INSERT INTO machine_checks (machine_id, checked_on) VALUES
  ('A-17', DATE '2026-01-02'),
  ('A-17', DATE '2026-01-03'),
  ('A-17', DATE '2026-01-04'),
  ('A-17', DATE '2026-01-07'),
  ('A-17', DATE '2026-01-08'),
  ('B-04', DATE '2026-01-03'),
  ('B-04', DATE '2026-01-05');

У A-17 две серии: 2–4 и 7–8 января. У B-04 обе даты стоят отдельно, поэтому каждая образует остров длиной один день. Нужна выдача из четырёх строк:

Обычный GROUP BY не знает, где заканчивается одна серия и начинается следующая. Сначала каждой строке нужен ключ острова, и только потом строки с одинаковым ключом можно группировать.

Как получить ключ острова и собрать серии

Шаг 1. Пронумеровать даты внутри каждого станка

row_number() выдаёт последовательные номера от единицы. PARTITION BY machine_id запускает нумерацию заново для каждого станка, а ORDER BY checked_on располагает его даты по времени.

SELECT machine_id,
       checked_on,
       row_number() OVER (
         PARTITION BY machine_id
         ORDER BY checked_on
       ) AS position
FROM machine_checks
ORDER BY machine_id, checked_on;

Порядок внутри OVER управляет расчётом окна, но не сортирует готовую выдачу. Поэтому внешний ORDER BY здесь отдельный. Подробно PARTITION BY, порядок и рамки разобраны в статье про оконные функции.

Шаг 2. Получить постоянный ключ для соседних дат

Внутри непрерывной серии дата и номер строки растут одновременно: дата — на сутки, номер — на единицу. Их разность остаётся постоянной.

Для первых трёх дат A-17 расчёт выглядит так:

2026-01-02 - 1 = 2026-01-01
2026-01-03 - 2 = 2026-01-01
2026-01-04 - 3 = 2026-01-01

После пропуска 5 и 6 января ключ меняется:

2026-01-07 - 4 = 2026-01-03
2026-01-08 - 5 = 2026-01-03

PostgreSQL вычитает из date целое число как количество дней. Но row_number() возвращает bigint, а оператор date - integer ждёт integer, поэтому позицию приводим явно:

checked_on - position::integer AS island_key

Сам island_key не обязан совпадать с началом серии. Это техническая метка: важно лишь, что внутри одного острова она одинакова, а после разрыва меняется.

Шаг 3. Сгруппировать строки по ключу острова

Оконный расчёт и агрегацию удобнее разнести по двум CTE. Первый назначает номера, второй вычисляет ключ. Финальный запрос находит границы и длину каждой группы.

WITH numbered AS (
  SELECT machine_id,
         checked_on,
         row_number() OVER (
           PARTITION BY machine_id
           ORDER BY checked_on
         ) AS position
  FROM machine_checks
),
marked AS (
  SELECT machine_id,
         checked_on,
         checked_on - position::integer AS island_key
  FROM numbered
)
SELECT machine_id,
       min(checked_on) AS started_on,
       max(checked_on) AS ended_on,
       count(*) AS days_count
FROM marked
GROUP BY machine_id, island_key
ORDER BY machine_id, started_on;

В GROUP BY входят и станок, и ключ. Если оставить только island_key, совпавшая техническая дата может склеить серии разных станков. Если оставить только machine_id, все острова одного станка сольются в одну строку.

Два CTE нужны для порядка вычислений, а не для хранения данных. Они живут один запрос. Как PostgreSQL обрабатывает такие именованные этапы, разобрано в материале про CTE.

Где классический сдвиг ломается

Формула значение - row_number() работает, когда соседство имеет фиксированный шаг. Для дат выше шаг равен одному календарному дню. Перед применением запроса проверь четыре условия.

Дубли меняют номер строки

Две одинаковые даты получат разные номера. У второй разность сдвинется, хотя календарного разрыва не было. Лучше закрепить уникальность ограничением, как в примере. Если источник изменить нельзя, убери дубли до окна:

WITH clean AS (
  SELECT DISTINCT machine_id, checked_on
  FROM raw_machine_checks
  WHERE checked_on IS NOT NULL
)

Если повторы несут смысл — например, это отдельные события внутри дня, — сначала реши, что считать единицей серии. Механически удалять их нельзя.

Разные сущности требуют PARTITION BY

Нумерация без PARTITION BY machine_id пойдёт через всю таблицу. Дата первого события B-04 продолжит номер последней строки A-17, и ключ потеряет смысл. Поле сущности должно присутствовать и в окне, и в финальной группировке.

Timestamp надо привести к бизнес-дню

Для timestamp with time zone сначала выбери часовой пояс, в котором определяется календарный день. Простое checked_at::date зависит от параметра TimeZone текущей сессии. Явное преобразование не меняется между окружениями:

(checked_at AT TIME ZONE 'Europe/Moscow')::date AS checked_on

После преобразования убери возможные дубли дня и только затем запускай нумерацию. Если серия измеряется часами, не приводи значение к дате: вычитай из timestamp интервал нужного шага.

Календарный и рабочий день — разные шаги

Пятница и понедельник разделены тремя календарными сутками, но могут считаться соседними рабочими днями. Формула с датой отметит разрыв. Для рабочего календаря нужна таблица дат с последовательным workday_number; остров строится уже по этому номеру. Та же оговорка относится к праздникам и сменному графику.

Когда лучше LAG и накопительная сумма

Если правило звучит «новая серия начинается после разрыва больше N дней», удобнее сначала сравнить строку с предыдущей через lag(). Затем каждую границу отмечают единицей, а накопительная сумма превращает отметки в номер острова.

WITH previous AS (
  SELECT machine_id,
         checked_on,
         lag(checked_on) OVER (
           PARTITION BY machine_id
           ORDER BY checked_on
         ) AS previous_on
  FROM machine_checks
),
marked AS (
  SELECT machine_id,
         checked_on,
         CASE
           WHEN previous_on IS NULL OR checked_on - previous_on > 1 THEN 1
           ELSE 0
         END AS starts_island
  FROM previous
),
grouped AS (
  SELECT machine_id,
         checked_on,
         sum(starts_island) OVER (
           PARTITION BY machine_id
           ORDER BY checked_on
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS island_id
  FROM marked
)
SELECT machine_id,
       min(checked_on) AS started_on,
       max(checked_on) AS ended_on,
       count(*) AS days_count
FROM grouped
GROUP BY machine_id, island_id
ORDER BY machine_id, started_on;

На исходных данных запрос возвращает те же четыре серии. Порог > 1 означает: разрыв хотя бы в один отсутствующий день начинает новый остров. Если допустимы два отсутствующих дня, условие меняется на checked_on - previous_on > 3. Такой подход длиннее сдвинутого ключа, зато правило разрыва видно прямо в CASE.

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

• Единица соседства определена явно: календарный день, рабочий день, час или последовательный номер.
• Дубли и NULL обработаны до оконной функции.
• PARTITION BY содержит все поля, которые разделяют независимые последовательности.
• Финальный GROUP BY содержит и поля сущности, и ключ острова.
• Результат сортируется внешним ORDER BY, если порядок нужен приложению или отчёту.
• На большой таблице проверен план через EXPLAIN. Индекс (machine_id, checked_on) совпадает с порядком окна, но решение об индексном чтении всё равно принимает планировщик.

Следующий шаг — решить задачу Gaps and Islands на другом наборе данных. Для более широкого набора упражнений по окнам, группировкам и датам открой путь «SQL для собеседований».