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