0

This is follow-up of my previous question:

PostgreSQL group by with interval

There was a very good answer but unfortunately it is not working with PostgreSQL 8.0 - some clients still use this old version.

So I need to find another solution without using window functions

Here is what I have as a table:

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

I need to sum "quantity" grouped by "price1" and "price2" but only when they change

So the end result should look 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
Georgi Bonchev
  • 269
  • 4
  • 12

1 Answers1

2

It is not efficient, but you can implement the same logic with subqueries:

select sum(quantity), price1, price2,
       min(date) as dateStart, max(date) as dateend 
from (select d.*,
             (select count(*)
              from data d2
              where d2.date <= d.date
             ) as seqnum,
             (select count(*)
              from data d2
              where d2.price1 = d.price1 and d2.price2 = d.price2 and d2.date <= d.date
             ) as seqnum_pp
      from data d
     ) t
group by price1, price2, (seqnum - seqnum_pp)
order by dateStart
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you! Meanwhile I came with similar solution using left join. I will post it so people can compare. Tests will show which one is faster. – Georgi Bonchev Feb 21 '18 at 11:48
  • It seems your query works fine. At least if I test it on a small portion of the data. But if I run it on the real database it is over 15 minutes now and still working without result ... – Georgi Bonchev Feb 21 '18 at 13:22
  • @GeorgiBonchev . . . Your clients should update their software. The performance issue is a fixed problem. – Gordon Linoff Feb 21 '18 at 13:24