CODEWITHSUNDEEP
sqlpostgresqlgroup-by
ste_kwr
ste_kwr

Reputation: 1010

Looping through table to create a new table in SQL jointly with group by Postgres

Suppose a table has the following structure

product  |  day  |  transactionid  |  saleprice  |
------------------------------------------------ |
Apple    |   1   |   239849248     |    10       |
Apple    |   2   |   239834328     |    10       |
Apple    |   2   |   239849249     |    10       |
Apple    |   3   |   239849234     |    11       |
Banana   |   1   |   239843244     |    2        |
Banana   |   2   |   239843244     |    2        |
Banana   |   3   |   239843244     |    3        |
Banana   |   4   |   239843244     |    3        |
Orange   |   1   |   239234238     |    25       |
Orange   |   2   |   239234238     |    25       |
Orange   |   3   |   239234238     |    25       |
Orange   |   3   |   239234238     |    26       |
Orange   |   3   |   239234238     |    26       |
Orange   |   4   |   239234238     |    27       |

Where a number of products are sold, every day, with multiple transactions at different prices. For each product, I am interested in a change-log of Min(SalePrice) (changelog because this rarely changes in my data). The following query gives me, for a particular product (say Orange):

SELECT max(product), day, min(saleprice)
  FROM tableabove
  where product = 'Orange'
  group by day
  order by day asc;

Gives me:

product  |  day  |  minsaleprice  |
Orange   |   1   |       25       |
Orange   |   2   |       25       |
Orange   |   3   |       25       |
Orange   |   4   |       27       |

So, I have what I need for a product I specify, but now in the way I need it. For example, for orange I only need the days when the price changed (and Day 1) which means it should have only two rows for Day 1, and Day 4. I also do not know how to iterate this over all products in the table to generate a new table that looks as follows.

product  |  day  |  minsaleprice  |
Apple    |   1   |       10       |
Apple    |   3   |       11       |
Banana   |   1   |       2        |
Banana   |   3   |       3        |
Orange   |   1   |       25       |
Orange   |   4   |       27       |

Any help is appreciated. Thanks.

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

I think you just want lag():

select t.*
from (select t.*,
             lag(saleprice) over (partition by product order by day) as prev_saleprice
      from tableabove t
     ) t
where prev_saleprice is null pr prev_saleprice <> saleprice;

EDIT:

If you only want changes day by day, then the same idea works with an additional aggregation:

select t.*
from (select t.product, t.day, min(salesprice) as min_saleprice
             lag(min(saleprice)) over (partition by product order by day) as prev_minsaleprice
      from tableabove t
      group by t.product, t.day
     ) t
where prev_minsaleprice is null pr prev_minsaleprice <> minsaleprice;

Upvotes: 1

ste_kwr
ste_kwr

Reputation: 1010

Following on guidance from Gordon Linoff, I was was able to write the query as follows:

SELECT table2.* 
FROM (SELECT table1.*, lag(table1.minsaleprice) OVER(partition by product) as prev_price
FROM (SELECT product, day, MIN(saleprice) as minsaleprice FROM tableabove 
GROUP BY day, product ORDER BY product, day) 
as table1)
as table2
WHERE prev_price IS null OR prev_fee <> minsaleprice

Upvotes: 0

Related Questions