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_pricefromprice p window w as (order by p.created_on > '2010-03-10, p.id_price desc rows between unbounded
precedingand unbounded following)) as t where first_id_price=id_price;
But this doesn't return correct results.
Thanks for any suggestions,