Как объединить пересекающиеся интервалы в SQL

Сервис записывает простои по причинам: сбой базы с 1 по 5 апреля, сбой сети со 2 по 3 апреля и повторный сбой базы с 4 по 7 апреля. Если сложить длительности трёх строк, одни и те же часы будут посчитаны несколько раз. Для отчёта о времени недоступности нужны объединённые интервалы, а не сумма отдельных заявок.

Здесь интервал задаётся полуоткрытыми границами [started, finished). Два интервала, у которых конец первого совпал с началом второго, не пересекаются по времени, но для отчёта о непрерывном простое мы решили их склеивать: между ними нет доступного времени. Это бизнес-правило следует назвать до написания сравнения.

Почему предыдущей строки недостаточно

Интервал 1–5 апреля полностью накрывает 2–3 апреля. Следующая строка 4–7 апреля пересекается с первым интервалом, хотя её начало позже конца непосредственно предыдущей строки — 3 апреля. Обычный LAG(finished) ошибочно создаст новую группу. Нужен самый дальний конец среди всех предыдущих интервалов. Его даёт оконный MAX с рамкой до строки перед текущей.

CREATE TABLE downtime (id INTEGER, started TEXT, finished TEXT);
INSERT INTO downtime VALUES
 (1,'2025-04-01','2025-04-05'),
 (2,'2025-04-02','2025-04-03'),
 (3,'2025-04-04','2025-04-07'),
 (4,'2025-04-08','2025-04-09'),
 (5,'2025-04-09','2025-04-10');
WITH ordered AS (
 SELECT id, started, finished,
        MAX(finished) OVER (
          ORDER BY started, finished, id
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS previous_end
 FROM downtime
), marked AS (
 SELECT *, CASE WHEN previous_end IS NULL OR started > previous_end
                THEN 1 ELSE 0 END AS begins_group
 FROM ordered
), grouped AS (
 SELECT *, SUM(begins_group) OVER (
   ORDER BY started, finished, id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS group_id FROM marked
)
SELECT MIN(started) AS started, MAX(finished) AS finished
FROM grouped GROUP BY group_id ORDER BY started;

Результат — два периода: 2025-04-01—2025-04-07 и 2025-04-08—2025-04-10. Последние два исходных интервала касаются 9 апреля и поэтому стали одним периодом. Между 7 и 8 апреля есть разрыв, он разделяет группы.

В CTE marked новая группа начинается, когда started > previous_end. При равенстве мы продолжаем старую. Если по правилам задачи касающиеся границей интервалы должны оставаться раздельными, замените сравнение на >=. Смена одного знака поменяет вторую строку результата, поэтому её стоит проверить на ручном примере.

Оконная сумма по флагам начала группы присваивает всем строкам непрерывного периода один номер. Внешняя агрегация берёт самое раннее начало и самый поздний конец. Эта схема работает и при вложенных интервалах, и при длинной цепочке пересечений. Для нескольких сервисов добавьте PARTITION BY service_id в оба окна и группируйте вместе с service_id, иначе простои разных сервисов склеятся.

Перед расчётом исключите или отдельно разберите строки с finished <= started, если нулевой интервал не имеет смысла. В примере даты записаны текстом в едином ISO-формате; в реальной схеме нужны согласованные timestamp и зона времени. Иначе сравнение строк может нарушить хронологический порядок.

Для длительности объединённого простоя сначала получите именно эти непересекающиеся периоды, затем сложите их продолжительность. Если суммировать исходные заявки до склейки, вложенные интервалы дадут завышение. Отчёт может дополнительно показать число исходных причин в каждой группе, но это число не равно числу отдельных простоев: три записи 1–7 апреля описывают один непрерывный участок.

У интервалов с открытым концом, когда сервис ещё не восстановился, finished может быть NULL. Для отчёта на текущий момент замените его на фиксированный момент среза, одинаковый для всех строк запроса, и пометьте группу как продолжающуюся. Не подставляйте бесконечное значение бездумно: оно поглотит все будущие интервалы и изменит смысл длительности.

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

Если исходные интервалы принадлежат разным площадкам, не забудьте разбить оба окна по площадке. Иначе простой одного сервера поглотит время другого и получится красивый, но неверный общий интервал. Итог группы тоже должен сохранять площадку. Для ответа «был недоступен хотя бы один сервер» разбиение, наоборот, не нужно — это уже другой вопрос к тем же записям.

На курсе «Продвинутый SQL» последовательности и пересекающиеся интервалы разбираются рядом с оконными функциями и временными рядами. Вы пишете запросы на SQLite и проверяете границы на разных данных. Откройте программу, если хотите уверенно переносить этот приём на рабочие журналы.

Задача поиска самих конфликтующих пар разобрана отдельно: пересечение бронирований переговорной.