Thread: Need magic for a moving statistic

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 ?


regards

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)

A. Kretschmer schrieb:
> [...] Or simpler:
>
> test=*# select  ((d-'2009-10-02'::date)/14) || '. period' as period, sum(value) from foo group by 1;
>   
This is tricky, because you use a text-column to sort but need numerical 
sorting since there will be more than 0-9 periods.

This is still the way to handle the periodity without pulling out some 
external skript language.
Thanks for the reply.   :)

In response to Andreas :
> 
> A. Kretschmer schrieb:
> >[...] Or simpler:
> >
> >test=*# select  ((d-'2009-10-02'::date)/14) || '. period' as period, 
> >sum(value) from foo group by 1;
> >  
> This is tricky, because you use a text-column to sort but need numerical 
> sorting since there will be more than 0-9 periods.

Yes, sure. Use ((d-'2009-10-02'::date)/14) instead for order. My fault.

> 
> This is still the way to handle the periodity without pulling out some 
> external skript language.
> Thanks for the reply.   :)

Glad to help you ;-)
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)