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)