用Postgresql合并连续的行 [英] Merge continuous rows with Postgresql
本文介绍了用Postgresql合并连续的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个插槽
表,如下所示:
I have a slots
table like this :
Column | Type |
------------+-----------------------------+
id | integer |
begin_at | timestamp without time zone |
end_at | timestamp without time zone |
user_id | integer |
,我想选择连续的合并行。假设我有(简化)的数据,例如:
and I like to select merged rows for continuous time. Let's say I have (simplified) data like :
(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)
我想知道是否可以选择以下格式的行:
I would like to know if it's possible to select rows formatted like :
(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
我正在使用9.3版的Postgresql!
I'm using Postgresql, version 9.3!
谢谢!
推荐答案
这是解决此问题的一种方法。创建一个标志,确定一条记录是否与前一条记录 not 重叠。这是一个小组的开始。然后取该标志的累积总和并将其用于分组:
Here is one method for solving this problem. Create a flag that determines if a one record does not overlap with the previous one. This is the start of a group. Then take the cumulative sum of this flag and use that for grouping:
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提琴。
这篇关于用Postgresql合并连续的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文