Message-ID: <4AC81FB2.3050600@gmx.net>
Date: Sun, 04 Oct 2009 06:08:18 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Need even more magic. Now for tricky counts.
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

there is a vast log-table that collects several state data for objects.
(log_id, project_fk, object_fk, state_fk, log_type_fk, 
created_on::timestamp, .......)
log_id   is a sequence,
project_fk  foreign key on a project-table
object_fk  foreign key on a object-table
state_fk   can have 10 values    0, 10, 20, 30, ...
log_type_fk   describes the event that caused the entry

I need counts of states of objects for a project starting at a given 
date t0 in 14 days distances.

Because I need a row for every reporting day, I started out by creating 
a view that selects the relevant project_fk and only those log_type_fk 
that MIGHT be relevant.   state_fk = 0 is irrelevant, too.
The same view does a case when ... for every state_fk so that I can add 
them up later to get a cross-table.
e.g.
case when state_fk = 10 then 1 else 0 end as sate_10,
case when state_fk = 20 then 1 else 0 end as sate_20,
...
Then the view adds a integer-column  period_nr  that represents the nr 
of 14 day periods since t0.
In the first 14 days have period_nr = 0, in the second 14 days it is 1 
and so on.


Now I need a query that calculates the sum for every column state_10, 
state_20, ..., state_90 from t0 to the current period_nr.
t0  until  t0 + 1 * 14 days   ===>   count(state_10),  count(state_20),  
count(state_30) ...
t0  until  t0 + 2 * 14 days
...

This would be nice.
I'd be glad if you could hint me up to here.


Even nicer would be a solution that adds just the last occurance for 
every object_fk within the current t0 - period.
e.g.
object_fk = 42   might appear
in period 1 with state 50
in period 3 twice with state 40 and 20

The report should count it in period  1+2  as 50
and in period 3 and further just 1 time as 20 until the object gets 
logged again.

This might prove to be a wee bit tricky.