Re: master/detail

Jan Bakuwel <jan.bakuwel@greenpeace.org> hat am 21. Mai 2012 um 01:17
geschrieben:

> Hi,
>
> I'm trying to get my head around the following question. As an example
> take a table with products:
>
> productid (pk)
> name
>
> and productprice
>
> productpriceid (pk)
> productid (fk)
> pricedate
> price
>
> There are multiple records in productprice for each product as prices
> (often) go up and (less often) go down.
>
> I'm looking for a query that returns the following:
>
> productid, name, pricedate, current_price, difference
>
> current_price is the latest (ie. most recent date) price of the product
> and difference is the difference in price between the latest price and
> the price before the latest.
>
> Any suggestions how to do this with SQL only? I can make it work with a
> function (probably less efficient) but think this should be possible
> with SQL too...


You can use window-function, in your case something like:

test=# select * from productprice ;id | product | pricedate  | price
----+---------+------------+------- 1 |       1 | 2012-05-01 |    10 2 |       1 | 2012-05-05 |    15 3 |       1 |
2012-05-10|    12 4 |       1 | 2012-05-15 |    22
 
(4 rows)

test=*# select id, product, pricedate, price, lead(price) over (partition by
product order by pricedate desc), price - (lead(price) over (partition by
product order by pricedate desc)) from productprice;id | product | pricedate  | price | lead | ?column?
----+---------+------------+-------+------+---------- 4 |       1 | 2012-05-15 |    22 |   12 |       10 3 |       1 |
2012-05-10|    12 |   15 |       -3 2 |       1 | 2012-05-05 |    15 |   10 |        5 1 |       1 | 2012-05-01 |    10
|     |
 
(4 rows)

Regards, Andreas