Re: GROUP BY overlapping (tsrange) entries

You could create a temporary table like

CREATE TABLE rng_counts (
cnt int,
during tsrange,
EXCLUDE USING gist (during WITH &&)
);

FOR cursor over event table LOOP
INSERT INTO reservation VALUES
(1, event. tsrange )
on conflict (during) do update set cnt = cnt + 1
;

LOOP END;

This is very rough but should be enough to give you an idea of what I
driving at.

On Fri, Jan 29, 2016 at 3:34 PM, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:

> På fredag 29. januar 2016 kl. 20:23:01, skrev David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com>:
>
> 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.
>
>
> Yes, I know but I'm hoping someone has a more clever idea.
>
>
>
> The only other potential query approach that comes to mind is some kind of
> recursive CTE.
>
>
> Interesting. Care to give an example of how to solve this using 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.
>
>
> Yes, but I'm using the count() for calculating the cardinality of
> something, which might change based on some other column in another table,
> which is JOIN'ed in in my real query. So a de-normalized piggy-backing
> table would be quite non-trivial to maintain.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>