Re: GROUP BY overlapping (tsrange) entries

2016-01-30 0:25 GMT+01:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På fredag 29. januar 2016 kl. 20:33:08, skrev Misa Simic <
> misa(dot)simic(at)gmail(dot)com>:
>
>
>
> 2016-01-29 2:11 GMT+01:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>
>> På fredag 29. januar 2016 kl. 02:02:46, skrev Michael Moore <
>> michaeljmoore(at)gmail(dot)com>:
>>
>> It is unclear to me how you got from your input data to your expected
>> output. If you are "trying to count() overlapping entries" then it would
>> seem to me that you would only have only one value for the count. Either a
>> range overlaps or it does not.
>>
>>
>> Oh, sorry, the count was in wrong order.
>>
>> Let me explain,
>>
>> insert into event(name, start_time, end_time)
>> values('a', '2015-12-20', NULL)
>> , ('a', '2015-12-20', '2015-12-31')
>> , ('a', '2015-12-25', '2016-01-01')
>> , ('b', '2015-11-20', '2015-11-24')
>> , ('c', '2016-02-01', '2016-02-03')
>> , ('c', '2016-02-01', '2016-02-04') , ('c', '2016-02-01', NULL)
>> ;
>>
>> All 'a', 'b' and 'c' have points in common, with count a=3, b=1, c=3.
>>
>> Thanks.
>>
>
>
> I think data are not correct...
>
> Expected result is the same as count() group by name...
>
> But I guess you have included name column just to different ranges for
> overlap...
>
>
> Yes, as I worte in the followup:
> *"Note that the 'name'-column here is just to explain what I'm after and
> that I have no such column."*
>
>
> But actually there is just 2 ranges:name b is 1 range, name a & c are
> second range. all overlaps by first range '2015-12-20, null) - it contains
> all records named as C ranges
>
>
> Yes, my bad. Pretend the first range for 'a' was '2015-12-20" -
> "2015-12-27".
>

In that case, the result you can get by:

SELECT COUNT(1) FROM (
SELECT (SELECT tsrange(min(start_time), max(COALESCE(end_time,
'infinity'))) FROM event e WHERE e.tsrange && main.tsrange) as full_range
FROM event main
) t
GROUP BY full_range

>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>