oracle 按每隔5分钟统计数量 博客分类: DB2database oracle
统计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) --时间取对应的五分钟段