Re: "compressing" consecutive values into one

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
unboundedfollowing))     as t where t.price = t.price1);