Oracle 按半小时统计sql语句

select count(*),
       (case floor((to_char(stat_date, 'mi')) / 15)
         when 0 then
          to_char(stat_date, 'yyyy.mm.dd hh24') || ':00:00'
         when 1 then
          to_char(stat_date, 'yyyy.mm.dd hh24') || ':15:00'
         when 2 then
          to_char(stat_date, 'yyyy.mm.dd hh24') || ':30:00'
         when 3 then
          to_char(stat_date, 'yyyy.mm.dd hh24') || ':45:00'
       end) as stat_date
  from TASK_RESULT s
   where s.stat_date >= to_date('20160506', 'yyyymmdd')
           and s.stat_date < to_date('20160507', 'yyyymmdd')
 group by stat_date

 

select nvl(round(avg(t.runtime)), 0) runtime,
       t.busline_id,
       (case floor((to_char(t.outstarttime, 'mi')) / 30)
         when 0 then
          to_char(t.outstarttime, 'hh24') || ':00'
         when 1 then
          to_char(t.outstarttime, 'hh24') || ':30'
       end) as abc
  from xxxx t
 where t.outstarttime >= to_date('2023-02-01', 'yyyy-mm-dd')
   and t.outstarttime < to_date('2023-03-01', 'yyyy-mm-dd')
 group by t.busline_id,
          (case floor((to_char(t.outstarttime, 'mi')) / 30)
            when 0 then
             to_char(t.outstarttime, 'hh24') || ':00'
            when 1 then
             to_char(t.outstarttime, 'hh24') || ':30'
          end)
 order by t.busline_id,
          (case floor((to_char(t.outstarttime, 'mi')) / 30)
            when 0 then
             to_char(t.outstarttime, 'hh24') || ':00'
            when 1 then
             to_char(t.outstarttime, 'hh24') || ':30'
          end)