In response to Andreas :
> Hi,
>
> I need some magic for a moving statistic that works on a rather big
> table starting at a given date within the table up until now.
> The statistic will count events allways on fridays over periods of 2
> weeks before ... biweekly?
> So I'd like to get a line every 2 weeks for everthing between.
>
> I sadly don't know how to spell that does:
>
> collect data where insert_date between friday1 and friday1 +
> inteval '2 week'
> collect data where insert_date between friday1 + inteval '2 week'
> + inteval '1 second' and friday1 + inteval '4 week'
> collect data where insert_date between friday1 + inteval '4 week'
> + inteval '1 second' and friday1 + inteval '6 week'
>
> Is this possible ?
I hope i understand you correctly:
test=*# select * from foo; d | value
------------+-------2009-10-02 | 12009-10-03 | 22009-10-10 | 32009-10-16 | 42009-10-20 |
52009-10-23| 62009-10-30 | 72009-11-05 | 82009-11-13 | 92009-11-20 | 10
(10 rows)
test=*#
select ('2009-10-02'::date + period*('14 days'::interval))::date::text || ' bis ' || ('2009-10-02'::date +
(period+1)*('14days'::interval))::date::text, sum
from ( select ((d-'2009-10-02'::date)/14) period, sum(value) from foo group by 1
) foo
order by period;
?column? | sum
---------------------------+-----2009-10-02 bis 2009-10-16 | 62009-10-16 bis 2009-10-30 | 152009-10-30 bis
2009-11-13| 152009-11-13 bis 2009-11-27 | 19
(4 rows)
Or simpler:
test=*# select ((d-'2009-10-02'::date)/14) || '. period' as period, sum(value) from foo group by 1; period | sum
-----------+-----0. period | 61. period | 153. period | 192. period | 15
(4 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)