pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed magic for a moving statistic
4+ messages / 2 participants
[nested] [flat]
* Need magic for a moving statistic
@ 2009-10-02 01:06 Andreas <maps.on@gmx.net>
2009-10-02 05:29 ` Re: Need magic for a moving statistic A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas @ 2009-10-02 01:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need magic for a moving statistic
2009-10-02 01:06 Need magic for a moving statistic Andreas <maps.on@gmx.net>
@ 2009-10-02 05:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-10-02 12:47 ` Re: Need magic for a moving statistic Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2009-10-02 05:29 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need magic for a moving statistic
2009-10-02 01:06 Need magic for a moving statistic Andreas <maps.on@gmx.net>
2009-10-02 05:29 ` Re: Need magic for a moving statistic A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2009-10-02 12:47 ` Andreas <maps.on@gmx.net>
2009-10-02 13:10 ` Re: Need magic for a moving statistic A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas @ 2009-10-02 12:47 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
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. :)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need magic for a moving statistic
2009-10-02 01:06 Need magic for a moving statistic Andreas <maps.on@gmx.net>
2009-10-02 05:29 ` Re: Need magic for a moving statistic A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-10-02 12:47 ` Re: Need magic for a moving statistic Andreas <maps.on@gmx.net>
@ 2009-10-02 13:10 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2009-10-02 13:10 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-10-02 13:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-02 01:06 Need magic for a moving statistic Andreas <maps.on@gmx.net>
2009-10-02 05:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-10-02 12:47 ` Andreas <maps.on@gmx.net>
2009-10-02 13:10 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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