mercredi 28 janvier 2015

Maximum sum() of overlapping ranges


Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the maximum sum() of applicable overlapping ranges. A simple example:



create table event (
event_id int primary key,
event_type_id int not null,
period tstzrange not null,
quantity int not null
);

insert into event (event_id, event_type_id, period, quantity) values
(1, 1,'[2016-01-06 09:00:00+00,2016-01-08 17:00:00+00]',1),
(2, 1,'[2016-01-07 09:00:00+00,2016-01-07 11:00:00+00]',1),
(3, 1,'[2016-01-07 13:00:00+00,2016-01-07 17:00:00+00]',1),
(4, 2,'[2016-01-07 12:00:00+00,2016-01-07 17:00:00+00]',1);


Given a query with the following clauses:



select ...
where event_type_id = 1
and period && '[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange
group by event_type_id


The desired result would be: 2, i.e. the maximum sum(quantity) where the ranges of the same event_type_id overlap within a given timestamp range.





Aucun commentaire:

Enregistrer un commentaire