Date: Wed, 24 Mar 2010 15:08:32 +0100
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: window function to sort times series data?
Message-ID: <20100324140832.GA5864@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

Hi,

I have time series data: price(id_price int, price int, created_on timestamp)

I'd like to select the latest price before, say, 2010-03-10 and the
latest price after that date.

Using "group by" and self-joins I was able to build a (quite large :)
working query. 

But I wonder if there is a cleaner, shorter solution with a window
function.

I tried something like:

select * from (select 
	first_value(p.id_price) over w as first_id_price,
	first_value(p.price) over w as first_price,
	first_value(p.created_on::date) over w as first_date,
	nth_value(p.id_price,2) over w as second_id_price,
	nth_value(p.price,2) over w as second_price,
	nth_value(p.created_on::date,2) over w as second_date,
    p.id_price
	from price p
    window w as (order by p.created_on > '2010-03-10, p.id_price desc
    rows between unbounded preceding and unbounded following)) 
    as t where first_id_price=id_price;

But this doesn't return correct results.

Thanks for any suggestions,