Thread: monthly statistics

Hi,

I need to show a moving statistic of states of objects for every month 
since beginning of 2013.

There are tables like
objects ( id integer, name text );
state ( id integer, state text );           10=A, 20=B ... 60=F
history ( object_id integer, state_id, ts timestamp );

Every event that changes the state of an object is recorded in the 
history table.

I need to count the numbers of As, Bs, ... on the end of month.
The subquery x finds the last state before a given date, here february 1st.

select    s.status,    count(*)
from    (        select  distinct on ( object_id )            status_id        from            history        where
      ts < '2013/02/01'        order by            object_id,            ts   desc    )   as  x    join    status  as
s on  x.status_id = s.id
 
group by    s.status
order by    s.status;

Now I need this for a series of months.

This would give me the relevant dates.
select generate_series ( '2013/02/01'::date, current_date + interval '1 
month', interval '1 month' )

How could I combine those 2 queries so that the date in query 1 would be 
replaced dynamically with the result of the series?

To make it utterly perfect the final query should show a crosstab with 
the states as columns.
It is possible that in some months not every state exists so in this 
case the crosstab-cell should show a 0.

Month                   A        B      C      ...
2013/02/01
2013/03/01
...

On Mon, Jul 8, 2013 at 2:18 PM, Andreas <maps.on@gmx.net> wrote:

> How could I combine those 2 queries so that the date in query 1 would be
> replaced dynamically with the result of the series?
>


Surely I'm missing something, but maybe this is something to work on:

WITH
RECURSIVE months(number) AS ( SELECT 1 UNION SELECT number + 1 FROM
months WHERE number < 12 )
SELECT m.number, s.id, s.name, count( h.state_id )
FROM state s JOIN history h ON s.id = h.state_id
JOIN months m ON m.number = date_part( 'month', h.ts )


Luca