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 some more interesting joining like comparing member
growth to advertising dollars spent, or calculating the ratios between
members and customer service calls.
It seems like a lot of data is inherently stored as time ranges. How
would you query and manipulate such data?
--
----
Visit http://www.obviously.com/