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°