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?