计算每分钟交易成功率

select vdate ,errcnt,succcnt, round(succcnt/(succcnt+errcnt),2) * 100 ||'%' from (select vdate,length(status) - length(replace(status,'失败','-')) as errcnt ,length(status) - length(replace(status,'成功','-')) as succcnt  from (select vdate, listagg(status, ',') within group(order by status) status
  from (select /*+parallel(t 8)*/
         substr(to_char(trans_date, 'yyyy-mm-dd hh24:mi:ss'), 1, 16) as vdate,
         case t.respcode
           when '000000' then
            '成功'
           else
            '失败'
         end as status
          from esb2_trans_log t
        
         where t.trans_date >=
               to_date('2019-12-18 08:27:00', 'yyyy-mm-dd hh24:mi:ss')
           and t.trans_date <=
               to_date('2019-12-18 10:39:00', 'yyyy-mm-dd hh24:mi:ss')
           and t.logicsystem = 'COP'
           and t.flowstepid in ('4', 'E')
         order by t.trans_date desc)
group by vdate
order by vdate desc ) b ) c