Re: GROUP BY overlapping (tsrange) entries

On Fri, Jan 29, 2016 at 10:30 AM, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:

> På fredag 29. januar 2016 kl. 17:59:52, skrev David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com>:
>
> On Fri, Jan 29, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas(at)visena(dot)com>
> wrote:
>
>> På fredag 29. januar 2016 kl. 02:43:37, skrev Andreas Joseph Krogh <
>> andreas(at)visena(dot)com>:
>>
>> På fredag 29. januar 2016 kl. 02:11:52, skrev 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.
>>
>>
>> Note that the 'name'-column here is just to explain what I'm after and
>> that I have no such column.
>>
>>
>> Any clever hints anyone?
>>
>>
>
> ​Maybe this will help...?
>
> ​
> https://wiki.postgresql.org/wiki/Range_aggregation
>
> ​David J.​
>
>
> Yea, I've seen it, but don't like it.
>
> I was (am) hoping some clever PG-guy would step up and craft som clever
> GROUP BY stuff like "GROUP BY magic_gist_equals(tsrange with &&)"
>
>

​http://www.postgresql.org/docs/9.5/static/sql-select.html
​"""
​
GROUP BY will condense into a single row all selected rows that share the
same values for the grouped expressions
"""

​Which means the only valid comparison for GROUP BY is equals. The
processes of finding a single value upon which such an equality comparison
can be performed is the subject of the wiki page I linked.

The only other potential query approach that comes to mind is some kind of
recursive CTE.

A more structural potential approach would involve triggers and maintaining
some form of master range table that evolves as DML is executed against the
base table.

David J.