Home PostgreSQL group by with interval
Reply: 0

PostgreSQL group by with interval

user4665
1#
user4665 Published in September 20, 2018, 8:31 pm

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?

share|improve this question

2 Answers 2

active oldest votes
up vote 1 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.359935 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO