Date: Mon, 21 May 2012 06:34:52 +0200 (CEST)
From: Andreas Kretschmer <andreas@a-kretschmer.de>
Reply-To: Andreas Kretschmer <andreas@a-kretschmer.de>
To: Jan Bakuwel <jan.bakuwel@greenpeace.org>, pgsql-sql@postgresql.org
Message-ID: 
 <1746662999.146825.1337574892585.JavaMail.open-xchange@ox.ims-firmen.de>
In-Reply-To: <4FB97B70.6080604@greenpeace.org>
References: <4FB7EEC6.1030004@gmx.net> <jp9mvc$qmu$1@reversiblemaps.ath.cx>
	<4FB8B489.3050701@gmx.net> <4FB97B70.6080604@greenpeace.org>
Subject: Re: master/detail
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Medium


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