[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
Re: [SQL] "compressing" consecutive values into one
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" To: 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "compressing" consecutive values into one
On Tue, Nov 23, 2010 at 03:31:59PM -, 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 Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "compressing" consecutive values into one
On Tue, Nov 23, 2010 at 10:13 AM, Louis-David Mitterrand wrote: > On Tue, Nov 23, 2010 at 03:31:59PM -, 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; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
