On Tue, Nov 23, 2010 at 04:19:18PM +0100, Louis-David Mitterrand wrote:
> Hi,
>
> On time series price data I'm trying to remove consecutive identical
> prices and keep only the latest. I tried:
>
> delete from price where id_price in (select t.id_price2 from (select
> first_value(p.id_price) over w as id_price1,
> nth_value(p.id_price, 2) over w as id_price2,
> first_value(p.price) over w as price1,
> nth_value(p.price,2) over w as price2
> from price p
> window w as (partition by
> p.id_rate,p.id_cabin_category,p.id_cruise
> order by p.id_price desc rows between unbounded
> preceding and
> unbounded following)) as t where price1 = price2);
>
> and it mostly works but I have to do several runs to completely
> eliminate identical consecutive prices.
Actually I found the answer to my own question. It's the WINDOW lag/lead
functions that I needed and this time one pass is enough:
delete from price where id_price in (
select t.id_price1 from (select
lead(p.id_price) over w as id_price1,
lead(p.price) over w as price1,
p.id_price, p.price
from price p
window w as (partition by
p.id_rate,p.id_cabin_category,p.id_cruise
order by p.id_price rows between unbounded preceding and
unbounded following))
as t where t.price = t.price1);
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
