Re: window function to sort times series data?

A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)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°