oracle 巧用 connect by 完成复杂统计

近期接到一个复杂统计的需求，对呼叫中心语音接入流水信息做出统计，要求单位时间内按30秒，60秒，300秒分区间统计接入量，用于进行中心话务运营情况分析，考虑了很长时间，开始是考虑使用游标+循环来做，觉得太复杂和耗时，最后用一句SQL实现了，效果如下：

原始表t_yyjrlsk（语音接入流水库），如下，里面的rq（日期），sj（时间）是我分区间统计的目标列

 

 

 

某时间段按30秒统计结果：

 

sql语句如下：

 

select rq,substr(sj,1,6) || 
       (
          case when b.qjks < 10 then '0' || b.qjks
          else '' || b.qjks
          end
       )
       || '~' || substr(sj,1,6) || b.qjjs as sjqj,count(*)
from t_yyjrlsk a
join (select (level - 1) * 30 as qjks,(level * 30 - 1) as qjjs from dual connect by level <= 2) b
on substr(sj,7,2) >= b.qjks and substr(sj,7,2) <= b.qjjs 
where rq >= 20110401 and rq < 20110402 and sj >= '00:00:00' and sj < '03:30:59'
group by rq,substr(sj,1,6),b.qjks,b.qjjs
order by rq,substr(sj,1,6),b.qjks

 

 

某时间段按60秒统计结果：

 

sql语句如下：

 

select rq,substr(sj,1,2)||':'||substr(sj,4,2)||':00~'||substr(sj,1,2)||':'||substr(sj,4,2)||':59' as sjqj,count(*) 
from t_yyjrlsk
where rq >= 20110401 and rq < 20110402 and sj >= '00:00:00' and sj < '03:30:59'
group by rq,substr(sj,1,2),substr(sj,4,2)
order by rq,substr(sj,1,2),substr(sj,4,2)

 

 

某时间段按300秒统计结果：

 

 

 

sql语句如下：

 

select rq,substr(sj,1,2) || ':' || 
       (
          case when b.qjks < 10 then '0' || b.qjks
          else '' || b.qjks
          end
       )       
       || ':00' || '~' || substr(sj,1,2) || ':' || 
       (
          case when b.qjjs < 10 then '0' || b.qjjs
          else '' || b.qjjs
          end
       )   
       || ':59' as sjqj,count(*)
from t_yyjrlsk a
join (select (level - 1) * 5 as qjks,(level * 5 - 1) as qjjs from dual connect by level <= 12) b
on substr(sj,4,2) >= b.qjks and substr(sj,4,2) <= b.qjjs 
where rq >= 20110401 and rq < 20110402 and sj >= '00:00:00' and sj < '03:30:59'
group by rq,substr(sj,1,2),b.qjks,b.qjjs
order by rq,substr(sj,1,2),b.qjks