Re: Need magic for a moving statistic

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 | 1
2009-10-03 | 2
2009-10-10 | 3
2009-10-16 | 4
2009-10-20 | 5
2009-10-23 | 6
2009-10-30 | 7
2009-11-05 | 8
2009-11-13 | 9
2009-11-20 | 10
(10 rows)

test=*#
select
('2009-10-02'::date + period*('14 days'::interval))::date::text || ' bis ' || ('2009-10-02'::date + (period+1)*('14 days'::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 | 6
2009-10-16 bis 2009-10-30 | 15
2009-10-30 bis 2009-11-13 | 15
2009-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 | 6
1. period | 15
3. period | 19
2. period | 15
(4 rows)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)