请问下兄弟们复杂的在线时长的计算问题

Question

要求计算出每个小时的所有用户的在线时长和登陆次数,不管这个小时有没有人登陆过,不用管天。USERID   LOGINTIME              LOGOUTTIME
AA       2010-03-05 00:01:23    2010-03-05 02:04:21
AA       2010-03-05 02:07:01    2010-03-05 03:01:03
BB       2010-03-05 23:55:03    2010-03-06 00:03:01需要得到以下结果
DES      TIMES    COUNTS
0点~1点  62       2
1点~2点  60       1
2点~3点  57       1
.
.
23点~24点5        1

Answer 1

select t1.begin_time,t1.end_time,
  sum(case when t1.end_time>t2.logouttime then t2.logouttime
           when t1.end_tme <= t2.logouttime then t1.end_time end
      -
      case when t1.begin_time > t2.logintime then t1.begin_time
           when t1.begin_time <= t2.logintime ten t2.logintime end ),
  count(t1.begin_time) c1 from
(
  select rownum -1 begin_time, rownum end_time
  from dual 
  connect by rownum <= 24
) t1,
tablename t2
where t1.begin_time between to_number(to_char(t2.logintime(+),'HH24')) 
                        and to_number(to_char(t2.logouttime(+),'HH24')) 
   or t1.end_time between to_number(to_char(t2.logintime(+),'HH24')) 
                        and to_number(to_char(t2.logouttime(+),'HH24')) 

Answer 2

参考语句：
select  to_char(t1.begin_time) || '点~' || to_char(t1.end_time) || '点' period,t2.total,t2.c1
from (
select rownum -1 begin_time, rownum end_time
from dual
connect by rownum <= 24
) t1,
( select t1.begin_time, to_char(t1.begin_time) || '点~' || to_char(t1.end_time) || '点' period,
sum(case when t1.end_time>t2.logouttime then t2.logouttime
when t1.end_time <= t2.logouttime then t1.end_time end
-
case when t1.begin_time > t2.logintime then t1.begin_time
when t1.begin_time <= t2.logintime then t2.logintime end ) total,
count(t1.begin_time) c1from
(
select rownum -1 begin_time, rownum end_time
from dual
connect by rownum <= 24
) t1,
(select to_number(to_char(t2.logintime,'HH24'))logintime,
to_number(to_char(t2.logouttime,'HH24')) logouttime
 from logtab t2) t2
 where (t1.begin_time between t2.logintime and t2.logouttime)
 or (t1.end_time between t2.logintime and t2.logouttime)
 group by t1.begin_time,to_char(t1.begin_time) || '点~' || to_char(t1.end_time) || '点') t2
 where t1.begin_time = t2.begin_time(+)

Answer 3

是我没说清楚，时长要分钟的。TIMES是时间，COUNTS 是登陆次数。

Answer 4

t1.end_time>t2.logouttime  logouttime 是时间类型，楼上2位答案都不对，不过还是谢谢了。

Answer 5

晕，楼主很执着，看到另外还开了一个帖子，花了点时间给整理了一下，这次应该没问题了。select lpad(t1.begin_time,2,'0')||'点 ~ '||lpad(t1.end_time,2,'0')||'点',
sum(
  case when t1.end_time < t2.begin_time then null
       when t1.begin_time > t2.end_time then null
       when t1.begin_time < t2.begin_time and t1.end_time between t2.begin_time and t2.end_time then t1.end_mi - t2.begin_mi
       when t1.end_time > t2.end_time and t1.begin_time between t2.begin_time and t2.end_time then t2.end_mi - t1.begin_mi
       when t1.begin_time between t2.begin_time and t2.end_time and t1.end_time between t2.begin_time and t2.end_time  then t1.end_mi - t1.begin_mi
       when t1.begin_time < t2.begin_time and t1.end_time > t2.end_time then t2.end_mi - t2.begin_mi
  else null end
) c1 from
(
  select begin_time,end_time,0 begin_mi,60 end_mi
  from 
  (
    select rownum -1 begin_time, mod(rownum,24) end_time
    from dual 
    connect by rownum <= 24
   ) t
) t1,
(
  select username,(logintime - trunc(logintime)) * 24 begin_time,(logouttime - trunc(logouttime)) * 24 end_time ,
      (logintime - trunc(logintime,'HH24')) * 24 * 60 begin_mi,(logouttime - trunc(logouttime,'HH24')) * 24 * 60 end_mi
  from (   
    select 'A' username,trunc(sysdate) + dbms_random.value(0,0.5) logintime, trunc(sysdate)  + dbms_random.value(0,0.5) + dbms_random.value(0,0.5) logouttime
    from dual 
    connect by rownum < 100
  ) t
) t2
group by lpad(t1.begin_time,2,'0')||'点 ~ '||lpad(t1.end_time,2,'0')||'点'