PostgreSQL group by with interval

Question

Well, I have a seemingly simple set of data but it gives me a lot of trouble.

This is an example of what my data look like:

quantity  price1  price2 date
100       1       0      2018-01-01 10:00:00
200       1       0      2018-01-02 10:00:00
50        5       0      2018-01-02 11:00:00
100       1       1      2018-01-03 10:00:00
100       1       1      2018-01-03 11:00:00
300       1       0      2018-01-03 12:00:00

I need to sum up "quantity" column grouped by "price1" and "price2" and it would be very easy but I need to take into account time changes of "price1" and "price2". Data is sorted by "date".

What I need is the last row to be not grouped with the first two although it has the same values for "price1" and "price2". Also I need to get minimal and maximal date of each interval.

The end result should looks like this:

quantity price1 price2 dateStart            dateEnd
300      1      0      2018-01-01 10:00:00  2018-01-02 10:00:00 
50       5      0      2018-01-02 11:00:00  2018-01-02 11:00:00
200      1      1      2018-01-03 10:00:00  2018-01-03 11:00:00
300      1      0      2018-01-03 12:00:00  2018-01-03 12:00:00

Any suggestions for a SQL query?

score 1 · Accepted Answer · answered Feb 14 '18 at 07:53

1

It is a gap and island problem. Use the following code:

select sum(quantity), price1, price2, min(date) dateStart, max(date) dateend 
from
(
    select *,
           row_number() over (order by date) -
           row_number() over (partition by price1, price2 order by date) grp
    from data
) t
group by price1, price2, grp
order by dateStart

dbfiddle demo

The solution is based on an identification of consecutive sequences of price1 and price2, which is done by a creation of the grp column. Once you isolate the consecutive sequences then you do a simple group by using grp as well.

answered Feb 14 '18 at 07:53

Radim Bača

10,646
1
19
33

Strange, in few cases when dates and times of two rows are exactly the same it makes new intervals. Any help with that? – Georgi Bonchev Feb 14 '18 at 09:22
@GeorgiBonchev could you prepare an example in dbfiddle? – Radim Bača Feb 14 '18 at 09:27
Never mind, I add second parameter (oid) in the "order by" and it worked – Georgi Bonchev Feb 14 '18 at 09:27
Well, unfortunatelly "over partition by" is not supported in older versions of PostgreSQL like 8.0. Is there a way to run this in PostgreSQL 8.0? – Georgi Bonchev Feb 21 '18 at 09:27
@GeorgiBonchev row_number can be simulated using a self-join: https://stackoverflow.com/questions/1895110/row-number-in-mysql – Radim Bača Feb 21 '18 at 09:30
I tried it but still cannot make it work properly. Should I open new question? – Georgi Bonchev Feb 21 '18 at 10:32
@GeorgiBonchev yes – Radim Bača Feb 21 '18 at 10:51

score 0 · Answer 2 · answered Feb 14 '18 at 09:34

I changed a little bit the accepted answer to catch the cases when "date" column of two rows next to each other are exactly the same. I added second parameter so they will be ordered in correct order (my table has "oid" column)

select sum(quantity), price1, price2, min(date) dateStart, max(date) dateend 
from
(
    select *,
           row_number() over (order by date, oid) -
           row_number() over (partition by price1, price2 order by date, oid) grp
    from data
) t
group by price1, price2, grp
order by dateStart

PostgreSQL group by with interval

2 Answers2

Linked