Date: Fri, 2 Oct 2009 07:29:16 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Need magic for a moving statistic
Message-ID: <20091002052916.GA22496@a-kretschmer.de>
References: <4AC55231.2080008@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4AC55231.2080008@gmx.net>
User-Agent: Mutt/1.5.9i

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)