PostgreSQL Group by array of daterange

Question

I have a massive table with records that all have a date and a price:

id | date | price | etc...

And then I have a list of random date ranges, never with the same length:

ARRAY [
    daterange('2020-11-02','2020-11-05'), 
    daterange('2020-11-15','2020-11-20')
]

How would I most efficiently go about summing and grouping the records by their existence in one of the ranges, like so:

range                   | sum
------------------------------------------
[2020-11-02,2020-11-05) | 125.55
[2020-11-15,2020-11-20) | 566.12

GMB · Accepted Answer · 2020-11-22 00:19:41Z

2

You can unnest the array, left join the table on dates that are contained in ranges, and finally aggregate:

select x.rg, sum(t.price) sum_price
from unnest($1) x(rg)
left join mytable t on x.rg @> t.date 
group by x.rg

$1 represents the array of dateranges that you want to pass to the query.

answered Nov 22, 2020 at 0:19

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Perfect! Thank you!