Message-ID: <51DAAE09.9090905@gmx.net>
Date: Mon, 08 Jul 2013 14:18:17 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
 rv:17.0) Gecko/20130509 Thunderbird/17.0.6
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: monthly statistics
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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
...


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql