Guest
[Entry]
"As far as visualization, I know this is not the periodic sampling you are talking about, but I would look at all the rows for a user and choose an interval bucket, SUM within the buckets and show on a bar graph or similar. This would show a real ""distribution"", since many occurrences within a time frame may be significant.
SELECT DATEADD(day, DATEDIFF(day, 0, timefield), 0) AS bucket -- choose an appropriate granularity (days used here) ,COUNT(*) FROM entries WHERE uid = ? GROUP BY DATEADD(day, DATEDIFF(day, 0, timefield), 0) ORDER BY DATEADD(day, DATEDIFF(day, 0, timefield), 0)
Or if you don't like the way you have to repeat yourself - or if you are playing with different buckets and want to analyze across many users in 3-D (measure in Z against x, y uid, bucket):
SELECT uid ,bucket ,COUNT(*) AS measure FROM ( SELECT uid ,DATEADD(day, DATEDIFF(day, 0, timefield), 0) AS bucket FROM entries ) AS buckets GROUP BY uid ,bucket ORDER BY uid ,bucket
If I wanted to plot in 3-D, I would probably determine a way to order users according to some meaningful overall metric for the user."
|