Re: master/detail

Jan Bakuwel <jan(dot)bakuwel(at)greenpeace(dot)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