Re: window function to sort times series data?

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°