SQL按时间分段分组统计数据数据库与SQL

sql中按照时间分段分组，显示及统计分段数据，最后获取总行数：

文章图片

下面sql条件中48表示时间段数（一天48个0.5小时即半小时分段统计，以此类推修改）；--注释了查询条件即只统计错误或统计时间限制（hh24miss）内的数据；最后0和500分别为分页起止数。
查询行数据

select * from (select rownum as rn, a.* from (select to_char((d.timepoint - 1 / 48),'yyyy-mm-dd hh24:mi:ss') starttime,--临时表d存储了分好了的今天的各时间段 to_char(timepoint,'yyyy-mm-dd hh24:mi:ss') endtime, t.jobid, sum(count) updateTimes, sum(decode(status, 2, 1, 0)) failTimes, sum(cost) totalCost, sum(rowsize) updateSize from kdgs_realtime_log t, (select to_date('20171017', 'YYYYMMDD') + 1 - (level - 1) / 48 timepoint from dual connect by level <= 48) d where t.jobid = 1000009 and t.kddate = 20171017 and to_date(t.updatetime, 'YYYYMMDDHH24MISS') < d.timepoint and to_date(t.updatetime, 'YYYYMMDDHH24MISS') >= d.timepoint - 1 / 48 --AND t.status = '2' --and substr(t.updatetime,9,6) >=161010 --and substr(t.updatetime,9,6) <=183000 group by d.timepoint, t.jobid order by starttime) a where rownum <= 500) tt where tt.rn > 0

总行数：

select count(1) from (select count(1) from kdgs_realtime_log t, (select to_date('20171017', 'YYYYMMDD') + 1 - (level - 1) / 48 timepoint from dual connect by level <= 48) d where t.jobid = 1000009 and to_date(t.updatetime, 'YYYYMMDDHH24MISS') < d.timepoint and to_date(t.updatetime, 'YYYYMMDDHH24MISS') >= d.timepoint - 1 / 48 --AND t.status = '2' --and substr(t.updatetime,9,6) >=161010 --and substr(t.updatetime,9,6) <=183000 group by d.timepoint, t.jobid)

【SQL按时间分段分组统计数据】