Thread: "compressing" consecutive values into one
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 unboundedfollowing)) as t where price1 = price2); and it mostly works but I have to do several runs to completely eliminate identical consecutive prices. Is there a better, one-pass, way? Thanks,
Salut, Louis-David, Can you please state the columns belonging to price table and give a concrete example? Say, data before and data after you want to do? Thank you A bien-tôt, Oliveiros ----- Original Message ----- From: "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> To: <pgsql-sql@postgresql.org> Sent: Tuesday, November 23, 2010 3:19 PM Subject: [SQL] "compressing" consecutive values into one > 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. > > Is there a better, one-pass, way? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Tue, Nov 23, 2010 at 03:31:59PM -0000, Oliveiros d'Azevedo Cristina wrote: > Salut, Louis-David, > > Can you please state the columns belonging to price table > and give a concrete example? > Say, data before and data after you want to do? Hi Cristina, Data before: id_price | price----------------1 | 233 | 454 | 456 | 458 | 459 | 89 Data after: id_price | price----------------1 | 238 | 459 | 89 Thanks,
On Tue, Nov 23, 2010 at 10:13 AM, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: > On Tue, Nov 23, 2010 at 03:31:59PM -0000, Oliveiros d'Azevedo Cristina wrote: >> Salut, Louis-David, >> >> Can you please state the columns belonging to price table >> and give a concrete example? >> Say, data before and data after you want to do? > > Hi Cristina, > > Data before: > > id_price | price > ---------------- > 1 | 23 > 3 | 45 > 4 | 45 > 6 | 45 > 8 | 45 > 9 | 89 > > Data after: > > id_price | price > ---------------- > 1 | 23 > 8 | 45 > 9 | 89 > select max(id_price),price from price_table group by price order by 1;
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);