sql - 使用 Postgresql 合并连续的行

Question

我有一个像这样的 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

编辑:这是 SQLfiddle

我正在使用 Postgresql，版本 9.3!

谢谢!

Answer 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;

Here是一个 SQL fiddle 。