oracle 按每隔5分钟统计数量

统计t_tjsj表中按照 cjsj （date类型）每5分钟统计0点到1点这个一小时内时间段内的数量：

select lpad(floor(to_char(cjsj,mi)/5)*5,2,0) cjsj, count(bh) num

from t_tjsj where cjsj>to_date(2018-04-15 00:00:00,yyyy-MM-dd hh24:mi:ss)

and cjsj<=to_date(2018-04-15 01:00:00,yyyy-MM-dd hh24:mi:ss)

group by lpad(floor(to_char(cjsj,mi)/5)*5,2,0)

order by lpad(floor(to_char(cjsj,mi)/5)*5,2,0) ;

统计预期结果：

cjsj num 05 111 10 232 15 211 20 12 25 3214 30 34 35 234 40 454 45 674 50 86 55 35

统计t_tjsj表中按照 cjsj （date类型）统计0点到24点一天内每5分钟时间段内的数量：

select to_char(cjsj,hh24)||:||lpad(floor(to_char(cjsj,mi)/5)*5,2,0) cjsj, count(bh) num

from t_tjsj where cjsj>to_date(2018-04-15 00:00:00,yyyy-MM-dd hh24:mi:ss)

and cjsj<=to_date(2018-04-15 01:00:00,yyyy-MM-dd hh24:mi:ss)

group by to_char(cjsj,hh24)||:||lpad(floor(to_char(cjsj,mi)/5)*5,2,0)

order by to_char(cjsj,hh24)||:||lpad(floor(to_char(cjsj,mi)/5)*5,2,0) ;

重点函数：

to_char(cjsj,hh24) ----时间取小时

lpad(floor(to_char(cjsj,mi)/5)*5,2,0) --时间取对应的五分钟段

统计t_tjsj表中按照 cjsj （date类型）每5分钟统计0点到1点这个一小时内时间段内的数量： select lpad(floor(to_char(cjsj,mi)/5)*5,2,0) cjsj, count(bh) num from t_tjsj where cjsj>to_date(2018-04-15 00:00:00,yyyy-MM-dd hh24:mi:ss) and cjsj<=to_date(2018-04-15 01:00:00,yyyy-MM-dd hh24:mi:ss) group by lpad(floor(to_char(cjsj,mi)/5)*5,2,0) order by lpad(floor(to_char(cjsj,mi)/5)*5,2,0) ; 统计预期结果： cjsj num 05 111 10 232 15 211 20 12 25 3214 30 34 35 234 40 454 45 674 50 86 55 35 统计t_tjsj表中按照 cjsj （date类型）统计0点到24点一天内每5分钟时间段内的数量： select to_char(cjsj,hh24)||:||lpad(floor(to_char(cjsj,mi)/5)*5,2,0) cjsj, count(bh) num from t_tjsj where cjsj>to_date(2018-04-15 00:00:00,yyyy-MM-dd hh24:mi:ss) and cjsj<=to_date(2018-04-15 01:00:00,yyyy-MM-dd hh24:mi:ss) group by to_char(cjsj,hh24)||:||lpad(floor(to_char(cjsj,mi)/5)*5,2,0) order by to_char(cjsj,hh24)||:||lpad(floor(to_char(cjsj,mi)/5)*5,2,0) ; 重点函数： to_char(cjsj,hh24) ----时间取小时 lpad(floor(to_char(cjsj,mi)/5)*5,2,0) --时间取对应的五分钟段