From: Mario Dankoor <m.p.dankoor@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: master/detail
Date: Mon, 21 May 2012 13:57:21 +0200
Message-ID: <4FBA2DA1.204@gmail.com> (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>
On 2012-05-21 1:17 AM, Jan Bakuwel wrote:
> 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...
>
> cheers,
> Jan
>
>
With windowing functions:
select FRS.name
,FRS.productprice as current_price
,FRS.productprice- NXT.productprice
from
(
SELECT PRC.productid
,PRC.productprice
,ROW_NUMBER() OVER(PARTITION BY PRC.productid ORDER BY
PRC.pricedate desc) rank_nr
FROM productprice PRC
,products PRD
WHERE 1 = 1
AND PRD.productid = PRC.productid
) FRS
LEFT JOIN
(
SELECT productid
,productprice
,ROW_NUMBER() OVER(PARTITION BY productid ORDER BY pricedate
desc) rank_nr
FROM productprice
) NXT
ON FRS.productid = NXT.productid
AND NXT.rank_nr < 3
AND FRS.rank_nr <> NXT.rank_nr
WHERE 1 = 1
AND FRS.rank_nr = 1
Without windowing functions (slightly complicated, it's merely a max-mix
issue and assuming that the date includes the time)
SELECT CUR.name
,CUR.productid
,CUR.current_price
,CUR.current_price - PRV.previous_price price_difference
FROM ( --LATEST PRICE
SELECT PRD.name
,PRC.productid
,PRC.productprice current_price
FROM productprice PRC
,products PRD
,(--LATEST PRICEDATE
SELECT productid
,MAX(pricedate) pricedate
FROM productprice
GROUP BY productid
) MMX
WHERE 1 = 1
AND PRC.productid = PRD.productid
AND PRC.productid = MMX.productid
AND PRC.pricedate = MMX.pricedate
)CUR
-- IN CASE THERE'S NO PREVIOUS PRICE
LEFT JOIN
( -- PREVIOUS PRICE
SELECT PRC.productid
,PRC.productprice previous_price
FROM productprice PRC
,(--PREVIOUS PRICEDATE
SELECT PRC.productid
,MAX(PRC.pricedate) pricedate
FROM productprice PRC
,(
SELECT productid
,MAX(pricedate) pricedate
FROM productprice
GROUP BY productid
) MMX
WHERE 1 = 1
AND PRC.productid = MMX.productid
AND PRC.pricedate < MMX.pricedate
) PRV
WHERE 1 = 1
AND PRC.productid = PRV.productid
AND PRC.pricedate = PRV.pricedate
) LST
ON CUR.productid = LST.productid
cheers
Mario
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: m.p.dankoor@gmail.com
Subject: Re: master/detail
In-Reply-To: <4FBA2DA1.204@gmail.com>
* 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