SELECT h.hour, usrlog.userid (select generate_series(1,24) as hour) as h inner join usrlog on h.hour >= usrlog.loginhr and h.hour <= usrlog.logouthr order by h.hour, usrlog.userid;
To get the number of users per hour :
Select h.hour, count(*) asNumberOfUsers (select generate_series(1,24) as hour) h inner join usrlog on h.hour >= usrlog.loginhr and h.hour <= usrlog.logouthr group by h.hour;
HTH
Nicklas
2010-12-23 skrev Satish Burnwal (sburnwal) :
I need a help in writing a query. I have data as follows: > >mydb=# select * from usrlog ; > logid | userid | loginhr | logouthr >-------+----------+---------+---------- > 0 | sburnwal | 0 | 1 > 1 | rickyrs | 1 | 5 > 2 | satishbn | 1 | 6 > 3 | taohujin | 2 | 4 > 4 | jospehm | 4 | 5 > > >Table captures the login and logout time (taking hour here to simplify) >of users and my aim to find the number of logged-in users (online users) >at every hr (1st hr i.e. 0-1, 2nd hrs i.e. 1-2, 3rd hr i.e. 2-3 and ... >so on). As the data indicates, use is not logging out in same hr as hr >of login. A user can be logged-in for more than one hr. For example, >here user rickyrs is logged-in for 1st, 2nd, 3rd, 4th and 5th hr. My >query needs to find out in the last 24 hrs, how many users were >logged-in at each hr. I want the result this way: > >Nth-hr user >--------------------------- >1 sburnwal >2 rickyrs >2 satishbn >3 rickyrs >3 satishbn >3 taohujin >4 rickyrs >4 satishbn >4 taohujin >4 josephm > >Appreciate your response in advance. For me, even the count of users on >hourly basis is fine. > >Thanks >-Satish > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general > >
