am Wed, dem 20.06.2007, um 22:03:56 -0700 mailte Bryce Nesbitt folgendes:
> I have a bunch of data which is expressed in terms of start and stop dates,
> e.g.:
>
> +----------------------------------------------------------------+
> | Member | Start | Stop |
> |----------------+-----------------------+-----------------------|
> | Fred | 2007-01-01 | 2007-05-01 |
> |----------------+-----------------------+-----------------------|
> | Joe | 2005-05-04 | 2007-04-01 |
> |----------------+-----------------------+-----------------------|
> | Freddie | 2002-02-01 | 2006-04-01 |
> |----------------+-----------------------+-----------------------|
> | ... | ... | ... |
> +----------------------------------------------------------------+
>
> And what I want is a graph over time showing the number of members on each day.
>
> Thus the input is rows with time ranges, and the output is a scalar for each
> time bucket. The time bucket might be months, days, hours, or quarter hours.
> Such a data series could then be loaded into a spreadsheet or otherwise
> graphed.
>
> I've got a perl script that can do this. But is there a good and fast way to
> do this in the database? If I had views with the scalar data, then I could do
Yes, play with generate_series like this:
test=*# select * from member; name | start | stop
---------+------------+------------Fred | 2007-01-01 | 2007-05-01Joe | 2005-05-04 | 2007-04-01Freddie |
0202-02-01| 2006-04-01
(3 rows)
test=*# select foo.date, count(1) from member, (select
('2005-01-01'::date + (generate_series(0,20)||'month')::interval)::date)
foo where foo.date between start and stop group by 1 order by 1; date | count
------------+-------2005-01-01 | 12005-02-01 | 12005-03-01 | 12005-04-01 | 12005-05-01 |
12005-06-01| 22005-07-01 | 22005-08-01 | 22005-09-01 | 22005-10-01 | 22005-11-01 | 22005-12-01
| 22006-01-01 | 22006-02-01 | 22006-03-01 | 22006-04-01 | 22006-05-01 | 12006-06-01 |
12006-07-01| 12006-08-01 | 12006-09-01 | 1
(21 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
