rdbms - User Count for Hour -
i need find count of user in different time eg:
table:
start_time | end_time | user_id ---------------------------------------------------------- 1) 2014-11-25 01:23:00 | 2014-11-25 06:37:01 | 254 2) 2014-11-25 01:54l33 | 2014-11-25 02:25:31 | 365 3) 2014-11-25 01:55:36 | 2014-11-25 02:26:32 | 547 4) 2014-11-25 05:16:21 | | 485 5) 2014-11-25 05:29:03 | 2014-11-25 06:32:46 | 123
required result:
time | count -------------- 1 | 3 5 | 3
the user signed in particular interval should calculated in count interval hours eg: user 254 logged in @ 1:23 , logged out @ 06:37. count should in 1,2,3,4,5,6 hrs.
reply me if explanation not clear.
thanks in advance
here's query works, might not efficient.
-- count how many of each count quantity appears select qty, count(*) (-- count number of different hours each user select user_id, count(*) qty -- don't need user_id here, helps debugging ( -- list every hour+user combination select distinct hour, user_id (-- list every hours on period of interest select '2014-11-24 00:00:00'::timestamp + generate_series(0,48)*interval '1 hour' hour) periods join flow on (start_time < hour + interval '1 hour' , end_time > hour) ) hours group hours.user_id ) user_count group qty
you'll have adjust number of hours (48) cover range of interest.
basically works way:
- create series of hours of interest (assume clock hours rather user hours)
- list 1 line per user-hour combination
- group user, count of number of hours in each user participated
- group hour count, , see how many of each count used
Comments
Post a Comment