Message-ID: <4FBA2DA1.204@gmail.com>
Date: Mon, 21 May 2012 13:57:21 +0200
From: Mario Dankoor <m.p.dankoor@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: master/detail
References: <4FB7EEC6.1030004@gmx.net> <jp9mvc$qmu$1@reversiblemaps.ath.cx>
	<4FB8B489.3050701@gmx.net> <4FB97B70.6080604@greenpeace.org>
In-Reply-To: <4FB97B70.6080604@greenpeace.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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