pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmonthly statistics
2+ messages / 2 participants
[nested] [flat]
* monthly statistics
@ 2013-07-08 12:18 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2013-07-08 12:18 UTC (permalink / raw)
To: 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
...
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: monthly statistics
@ 2013-07-24 11:27 Luca Ferrari <fluca1978@infinito.it>
parent: Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Luca Ferrari @ 2013-07-24 11:27 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-07-24 11:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-08 12:18 monthly statistics Andreas <maps.on@gmx.net>
2013-07-24 11:27 ` Luca Ferrari <fluca1978@infinito.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox