Объединить непрерывные строки с Postgresql

У меня есть таблица slots, как это:

   Column   |            Type             |
------------+-----------------------------+
 id         | integer                     |
 begin_at   | timestamp without time zone |
 end_at     | timestamp without time zone |
 user_id    | integer                     |

и мне нравится выбирать объединенные строки для непрерывного времени. Скажем, у меня есть (упрощенные) данные вроде:

(1, 5:15, 5:30, 1)
(2, 5:15, 5:30, 2)
(3, 5:30, 5:45, 2)
(4, 5:45, 6:00, 2)
(5, 8:15, 8:30, 2)
(6, 8:30, 8:45, 2)

Я хотел бы знать, можно ли выбрать строки, отформатированные так:

(5:15, 5:30, 1)
(5:15, 6:00, 2) // <======= rows id 2,3 and 4 merged
(8:15, 8:45, 2) // <======= rows id 5 and 6 merged

EDIT: Здесь SQLfiddle

Я использую Postgresql, версия 9.3!

Спасибо!

Ответ 1

Вот один из способов решения этой проблемы. Создайте флаг, который определяет, не перекрывается ли одна запись с предыдущей. Это начало группы. Затем возьмите кумулятивную сумму этого флага и используйте его для группировки:

select user_id, min(begin_at) as begin_at, max(end_at) as end_at
from (select s.*, sum(startflag) over (partition by user_id order by begin_at) as grp
      from (select s.*,
                   (case when lag(end_at) over (partition by user_id order by begin_at) >= begin_at
                         then 0 else 1
                    end) as startflag
            from slots s
           ) s
     ) s
group by user_id, grp;

Здесь - скрипт SQL.

Ответ 2

Гордон Линофф уже предоставил ответ (я сохранил).

Я использовал тот же подход, но хотел иметь дело с tsrange type. Поэтому я придумал эту конструкцию:

SELECT min(id) b_id, min(begin_at) b_at, max(end_at) e_at, grp, user_id
  FROM (
    SELECT t.*, sum(g) OVER (ORDER BY id) grp
      FROM (
        SELECT s.*, (NOT r -|- lag(r,1,r)
                     OVER (PARTITION BY user_id ORDER BY id))::int g
          FROM (SELECT id,begin_at,end_at,user_id,
                       tsrange(begin_at,end_at,'[)') r FROM slots) s
      ) t
  ) u
 GROUP BY grp, user_id
 ORDER BY grp;

К сожалению, на верхнем уровне нужно использовать min(begin_at) и max(end_at), так как нет никаких агрегатных функций для оператора объединения на основе диапазона +.

Я создаю диапазоны с эксклюзивными верхними границами, это позволяет мне использовать "рядом с" (-|-). Я сравниваю текущий tsrange с тем, который был в предыдущей строке, по умолчанию для текущего, если нет предыдущего. Затем я отрицаю сравнение и отбрасываю integer, что дает мне 1 в случаях, когда начинается новая группа.