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