Re: master/detail - Mailing list pgsql-sql

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