Re: [GENERAL] Need Help in query
Thanks! I did not know such a function exists. From: Nicklas Avén [mailto:nicklas.a...@jordogskog.no] Sent: Thursday, December 23, 2010 3:31 PM To: Satish Burnwal (sburnwal) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need Help in query Hallo This I think should work. To get the usernames by hour as you describe: 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 > >
Re: [GENERAL] Need Help in query
Hallo This I think should work. To get the usernames by hour as you describe: 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 > >
[GENERAL] Need Help in query
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
