oracle 巧用 connect by 完成复杂统计

蓟莺

　　近期接到一个复杂统计的需求，对呼叫中心语音接入流水信息做出统计，要求单位时间内按30秒，60秒，300秒分区间统计接入量，用于进行中心话务运营情况分析，考虑了很长时间，开始是考虑使用游标+循环来做，觉得太复杂和耗时，最后用一句SQL实现了，效果如下：
　　原始表t_yyjrlsk（语音接入流水库），如下，里面的rq（日期），sj（时间）是我分区间统计的目标列
　　
http://hiphotos.baidu.com/pandong8183/pic/item/956c7bcf3b7792a653664fa6.jpg
　　
　　某时间段按30秒统计结果：
http://hiphotos.baidu.com/pandong8183/pic/item/8f4d01e9b7dd271479f055ab.jpg
　　
　　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秒统计结果：
http://hiphotos.baidu.com/pandong8183/pic/item/bf3f645501742b7f43a75bb9.jpg
　　
　　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)

　　
　　
http://hiphotos.baidu.com/pandong8183/pic/item/c8452dce11052e1df31fe781.jpg
　　某时间段按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