From: Andreas Kretschmer <andreas@a-kretschmer.de>
To: Jan Bakuwel <jan.bakuwel@greenpeace.org>
To: pgsql-sql@postgresql.org
Subject: Re: master/detail
Date: Mon, 21 May 2012 06:34:52 +0200 (CEST)
Message-ID: <1746662999.146825.1337574892585.JavaMail.open-xchange@ox.ims-firmen.de> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@a-kretschmer.de, jan.bakuwel@greenpeace.org
Subject: Re: master/detail
In-Reply-To: <1746662999.146825.1337574892585.JavaMail.open-xchange@ox.ims-firmen.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox