sql - 按非固定时间间隔查询分组

Question

我想按移动的、非固定的时间间隔进行分组。也就是说，我有一些事件有开始和结束，我想计算有多少事件同时发生。

我有一张这样的桌子

       start         |          end           |   desc
-----------------------------------------------------------
'2018-03-29 13:36:52'|'2018-03-29 13:37:52+02'|'Alarm_821'
'2018-03-29 13:39:52'|'2018-03-29 13:41:52+02'|'Alarm_821'
'2018-03-29 15:44:15'|'2018-03-29 15:50:16+02'|'Alarm_819'
'2018-03-29 15:44:15'|'2018-03-29 15:51:16+02'|'Alarm_817'
'2018-03-29 16:08:18'|'2018-03-29 16:10:19+02'|'Alarm_418'
'2018-03-29 16:08:18'|'2018-03-29 16:10:19+02'|'Alarm_465'
'2018-03-29 16:11:19'|'2018-03-29 16:15:19+02'|'Alarm_418'

我想得到这个结果：

           start         |          end     |     count
-----------------------------------------------------------
'2018-03-29 13:36:52'|'2018-03-29 13:37:52+02'|     1
'2018-03-29 13:39:52'|'2018-03-29 13:41:52+02'|     1
'2018-03-29 15:44:15'|'2018-03-29 15:50:16+02'|     2
'2018-03-29 15:50:16'|'2018-03-29 15:51:16+02'|     1       <<== here start refers to the end of the first event ending when both of them started
'2018-03-29 16:08:18'|'2018-03-29 16:10:19+02'|     2
'2018-03-29 16:11:19'|'2018-03-29 16:15:19+02'|     1

我实际上不确定这是否可以仅通过使用 SQL 来完成。

标签： sqlpostgresql

Answer 1

这是一个基于表中所有时间的 UNION 的解决方案。它从该列表中创建相邻对，然后搜索间隔重叠。

select t.st, t.en, count(*)
from
(
  select lag(tm) over (order by tm) st, tm en
  from
  (
    select "start" tm from data 
    union
    select "end" tm from data
  ) r
) t
join data on t.st < data."end" and t.en > data."start"
group by t.st, t.en
order by t.st

DBFiddle 演示