From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: window function to sort times series data?
Date: Wed, 24 Mar 2010 17:29:46 +0100
Message-ID: <20100324162946.GA5845@tux> (raw)
In-Reply-To: <20100324150441.GC15442@a-kretschmer.de>
References: <20100324140832.GA5864@apartia.fr>
<20100324142936.GB15442@a-kretschmer.de>
<20100324143639.GA7853@apartia.fr>
<20100324150441.GC15442@a-kretschmer.de>
A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
Well, and now i'm using 8.4 windowing-functions:
test=*# select * from price order by price_id, d;
price_id | price | d
----------+-------+------------
1 | 10 | 2010-03-12
1 | 11 | 2010-03-19
1 | 12 | 2010-03-26
1 | 13 | 2010-04-02
1 | 14 | 2010-04-09
1 | 15 | 2010-04-16
1 | 16 | 2010-04-23
1 | 17 | 2010-04-30
2 | 20 | 2010-03-12
2 | 21 | 2010-03-19
2 | 22 | 2010-03-26
2 | 23 | 2010-04-02
(12 Zeilen)
-- now i'm searching for 2010-03-20:
Zeit: 0,319 ms
test=*# select price_id, sum(case when d < '2010-03-20'::date then price
else 0 end) as price_old, sum(case when d > '2010-03-20'::date then
price else 0 end) as price_new, max(case when d < '2010-03-20'::date
then d else null end) as date_old, max(case when d > '2010-03-20'::date
then d else null end) as date_new from (select price_id, price, d,
lag(d) over(partition by price_id order by d), lead(d) over(partition by
price_id order by d) from price) foo where '2010-03-20'::date between
lag and lead group by price_id;
price_id | price_old | price_new | date_old | date_new
----------+-----------+-----------+------------+------------
1 | 11 | 12 | 2010-03-19 | 2010-03-26
2 | 21 | 22 | 2010-03-19 | 2010-03-26
(2 Zeilen)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: window function to sort times series data?
In-Reply-To: <20100324162946.GA5845@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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