Условие

Таблица events(user_id, start_ts, end_ts) — интервалы активности пользователя. Слить пересекающиеся или соседние интервалы в один.

Решение

Идея

Для каждой строки определяем, начинается ли новый «остров» — если её start_ts больше, чем максимум end_ts всех предыдущих. Затем cumulative sum новых островов даёт group id.

SQL

WITH ordered AS (
    SELECT
        user_id,
        start_ts,
        end_ts,
        MAX(end_ts) OVER (
            PARTITION BY user_id
            ORDER BY start_ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_max_end
    FROM events
),
flagged AS (
    SELECT
        user_id,
        start_ts,
        end_ts,
        CASE
            WHEN prev_max_end IS NULL OR start_ts > prev_max_end
            THEN 1 ELSE 0
        END AS new_group
    FROM ordered
),
grouped AS (
    SELECT
        user_id,
        start_ts,
        end_ts,
        SUM(new_group) OVER (PARTITION BY user_id ORDER BY start_ts) AS grp
    FROM flagged
)
SELECT
    user_id,
    grp,
    MIN(start_ts) AS merged_start,
    MAX(end_ts) AS merged_end
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, merged_start;

Ключевая идея

MAX(end_ts) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) — максимум end_ts среди всех предыдущих строк. Если текущий start_ts > prev_max_end, значит интервал не пересекается ни с одним предыдущим → новый остров.

Соседние интервалы (touching, not overlapping)

Если [1, 5] и [5, 10] нужно считать соединёнными (touching):

WHEN start_ts > prev_max_end + INTERVAL '0 second'  -- любое тождество

Если только overlapping (но не touching):

WHEN start_ts >= prev_max_end

Тонкости

Inclusive vs exclusive endpoints: [1, 5) и [5, 10) — disjoint, [1, 5] и [5, 10] — touching. Различайте.
Параллельные интервалы: один интервал содержит другой целиком — алгоритм правильно их склеит, потому что prev_max_end сохраняется.
NULL в end_ts: open interval, до бесконечности. Специальная обработка.

PostgreSQL range types

PostgreSQL имеет встроенные range типы и операторы:

SELECT user_id, range_agg(int8range(start_ts, end_ts))
FROM events
GROUP BY user_id;

Возвращает «multirange» — массив склеенных интервалов. Намного короче, но только для PG 14+.

Применения

Slack: одну «сессию» из множества active periods.
Time tracking: слить overlapping work hours.
Subscription analytics: слить периоды подписки (paused → resumed).
Аренда / booking: проверка коллизий.

Подводные камни

Не отсортировано по user_id: window работает в партиции, но между партициями надо сортировать в финале.
Внутри одного start_ts несколько строк — определите tie-break по end_ts.
prev_max_end IS NULL — для первой строки в партиции; обработать CASE.
На очень больших данных (миллиарды строк) — preferable to use range types и range_agg.
ClickHouse не имеет RANGE types, но имеет runningAccumulate для подобных задач.

Эталонный ответ

prev_max_end = MAX(end_ts) OVER (... UNBOUNDED PRECEDING AND 1 PRECEDING). Новый остров = start_ts > prev_max_end. SUM(new_group) OVER (...) → grp. GROUP BY user_id, grp → merged start/end. В PG 14+ есть range_agg.

Сценарий: слияние пересекающихся интервалов в SQL