monthly statistics
От | Andreas |
---|---|
Тема | monthly statistics |
Дата | |
Msg-id | 51DAAE09.9090905@gmx.net обсуждение исходный текст |
Ответы |
Re: monthly statistics
|
Список | 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 ...
В списке pgsql-sql по дате отправления: