From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: rolling statistic probaply a window function?
Date: Sun, 14 Oct 2012 19:43:13 +0200
Message-ID: <507AF9B1.6060104@gmx.net> (raw)
I have a rather big log table that collects events for objects and the
change of their states.
Say an object can have state 0-9.
Every now and then an event happens that gets logged as:
( event TIMESTAMP, object_id INTEGER, state_id INTEGER )
Now I need to get a history of the last 12 weeks that shows how many
ojects of state 0-9 existed on Fridays 12:00 o clock.
I guess it's a wee bit tricky as the friday number needs to monitor only
the last change before this date.
Probaply the last state change happened 20 weeks ago or there were 5
events within this week.
In the latter case the object probaply switched states from 0 --> 5. It
should only count as 1x state 5 within this week.
Week 12 counts every object with its last state up to this week.
Week 11 should show the change between week 12 to 11.
Week 10 should show the change between week 11 to 10 and so on.
E.g. in the 1st result for 12 weeks ago there were
10 x state 0
20 x state 1
30 x state 2
11 weeks ago there were 5 new objects with state 0
and there were 7 new state 1 while 3 objects changed from state 1 to 2
10 + 5 = 15 x state 0
20 + 7 - 3 = 24 x state 1
30 + 3 = 33 x state 2
All this for the last 12 fridays.
Is there a way to do this?
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: maps.on@gmx.net
Subject: Re: rolling statistic probaply a window function?
In-Reply-To: <507AF9B1.6060104@gmx.net>
* 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