表 CIM_CUSTOMER_ORDER， "ID_" VARCHAR2(32),"ACCEPT_TIME" VARCHAR2(24)
insert into CIM_CUSTOMER_ORDER values ('1','2017-02-17 10:03:33')
insert into CIM_CUSTOMER_ORDER values ('2','2017-02-17 10:13:33')
insert into CIM_CUSTOMER_ORDER values ('3','2017-02-17 10:23:33')
insert into CIM_CUSTOMER_ORDER values ('4','2017-02-17 10:33:33')
insert into CIM_CUSTOMER_ORDER values ('5','2017-02-17 10:43:33')
想统计出 2017-02-17 10:00:00到2017-02-17 10:45:33这段时间内，每隔五分钟的数据量
结果如：
2017-02-17 10:05:00 1
2017-02-17 10:10:00 1
2017-02-17 10:15:00 2
2017-02-17 10:20:00 2
2017-02-17 10:25:00 3
2017-02-17 10:30:00 3
2017-02-17 10:35:00 4
..........
with e as(
SELECT to_date('2017-02-17 10:00:00', 'yyyy-MM-dd hh24:mi:ss') + ((ROWNUM - 1) * ('5')) / (24 * 60) date1
FROM dual CONNECT BY ROWNUM <= (to_date('2017-02-17 10:45:33', 'yyyy-MM-dd hh24:mi:ss') - to_date('2017-02-17 10:00:00', 'yyyy-MM-dd hh24:mi:ss')) * (24 * 60 / ('5'))+1)
select date1, (SELECT COUNT(*) FROM CIM_CUSTOMER_ORDER where ACCEPT_TIME > date1-1/(24 * 60 / (5)) and ACCEPT_TIME <= date1) as ff
from e
group by e.date1
1.先使用
SELECT to_date('2017-02-17 10:00:00', 'yyyy-MM-dd hh24:mi:ss') + ((ROWNUM - 1) * ('5')) / (24 * 60) date1
FROM dual CONNECT BY ROWNUM <= (to_date('2017-02-17 10:45:33', 'yyyy-MM-dd hh24:mi:ss') - to_date('2017-02-17 10:00:00', 'yyyy-MM-dd hh24:mi:ss')) * (24 * 60 / ('5'))+1 计算出时间的等差数列
2.再使用with as作为界面去计算每次时间间隔的count数
select ACCEPT_TIME,count(*)
from (
select ID_, substr(d,1,15)||(case when to_number(substr(d,16,1))<5 then '0' else '5' end)||':00' as ACCEPT_TIME
from CIM_CUSTOMER_ORDER
)group by ACCEPT_TIME