CODEWITHSUNDEEP
sqlpostgresql
balsick
balsick

Reputation: 1201

Query grouping by non-fixed time intervals

I'd like to group by moving, non-fixed, time intervals. That is, I have some events having a start and an end, and I'd like to count how many events where occurring at the same time.

I have a table like this

       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'

And I'd like to obtain this result:

           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

I'm actually not sure that this can be done by only using SQL.

Upvotes: 0

Views: 47

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Here is a solution based on a UNION of all times in the table. It creates adjacent pairs from this list and then it searches of interval overlaps.

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 DEMO

Upvotes: 1

Related Questions