From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Need magic for a moving statistic
Date: Fri, 2 Oct 2009 07:29:16 +0200
Message-ID: <20091002052916.GA22496@a-kretschmer.de> (raw)
In-Reply-To: <4AC55231.2080008@gmx.net>
References: <4AC55231.2080008@gmx.net>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: Need magic for a moving statistic
In-Reply-To: <20091002052916.GA22496@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox