From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | monthly statistics |
Date: | 2013-07-08 12:18:17 |
Message-ID: | 51DAAE09.9090905@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
...
From | Date | Subject | |
---|---|---|---|
Next Message | Luca Vernini | 2013-07-08 13:11:28 | Update a composite nested type variable |
Previous Message | Bruce Momjian | 2013-07-03 23:29:34 | Re: Unquoted column names fold to lower case |