oracle 时间分段查询

/*建表*/
create table test1(serviceid number,
callerno number,
begintime date,
endtime date
);




/*设置数据库时间格式*/
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';






/*某个时段查询*/
select to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') +  (n - 1) / 48 sec_begin,
       to_date('2009-06-01 00:00:00', 'yyyy-mm-ddhh24:mi:ss') + n / 48 sec_end,
       count(callerno) callnum, 
       nvl(sum(endtime - begintime),0) * 24 * 60 * 60 "calllength(s)"
       
from test1 t, (select rownum n from dual connect by level < = 48) integers


where t.begintime 
between
      to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + (n - 1) / 48  
      and
      to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + n / 48
      
group by n;



/*全天分段查询*/
select to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + (n - 1) / 48 sec_begin,
        to_date('2009-06-01 00:00:00', 'yyyy-mm-ddhh24:mi:ss') + n / 48 sec_end,
        count(callerno) callnum,
        nvl(sum(endtime - begintime),0) * 24 * 60 * 60 "calllength(s)"
        
from test1 t 
  
  right join (select rownum n from dual connect by level < = 48) integers
  on t.begintime 
  between
        to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + (n - 1) / 48 
        and
        to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + n / 48
        
group by n;