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