pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"compressing" consecutive values into one
5+ messages / 3 participants
[nested] [flat]
* "compressing" consecutive values into one
@ 2010-11-23 15:19 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-11-23 15:31 ` Re: "compressing" consecutive values into one Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-24 20:53 ` Re: "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 2 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-11-23 15:19 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "compressing" consecutive values into one
2010-11-23 15:19 "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-11-23 15:31 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-23 18:13 ` Re: "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-11-23 15:31 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "compressing" consecutive values into one
2010-11-23 15:19 "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-11-23 15:31 ` Re: "compressing" consecutive values into one Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2010-11-23 18:13 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-11-23 18:19 ` Re: "compressing" consecutive values into one bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-11-23 18:13 UTC (permalink / raw)
To: 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 | 23
3 | 45
4 | 45
6 | 45
8 | 45
9 | 89
Data after:
id_price | price
----------------
1 | 23
8 | 45
9 | 89
Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "compressing" consecutive values into one
2010-11-23 15:19 "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-11-23 15:31 ` Re: "compressing" consecutive values into one Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-23 18:13 ` Re: "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-11-23 18:19 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: bricklen @ 2010-11-23 18:19 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "compressing" consecutive values into one
2010-11-23 15:19 "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-11-24 20:53 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 0 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-11-24 20:53 UTC (permalink / raw)
To: pgsql-sql
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);
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-24 20:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-23 15:19 "compressing" consecutive values into one Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-11-23 15:31 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-11-23 18:13 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-11-23 18:19 ` bricklen <bricklen@gmail.com>
2010-11-24 20:53 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox