oracle 累加功能，累加百分比

最近做数据分析，需要用到累加功能，发现强大的oracle还真有，用over(order by field)

例子:

数据表中最后一列就是累加的效果

累加sql:

select t.acc_pedal_pos,count(*) num,sum(count(*)) over(order by t.acc_pedal_pos) accu_sum from  GT1117CARDATA t where t.acc_pedal_pos>0 group by t.acc_pedal_pos  order by t.acc_pedal_pos

根据累计求和，进一步求占总和的百分比

sql:

--计算累计百分比，先求列和，然后嵌套求百分比
select t1.*,round(t1.accu_sum/t2.allsum*100,2)||'%' from (select t.acc_pedal_pos,
       count(*) num,
       sum(count(*)) over(order by t.acc_pedal_pos) accu_sum
  from GT1117CARDATA t
 where t.acc_pedal_pos > 0
 group by t.acc_pedal_pos
 order by t.acc_pedal_pos)t1,(select count(acc_pedal_pos) allsum from GT1117CARDATA where acc_pedal_pos>0) t2

 

转载于:https://www.cnblogs.com/yuwenfeng/p/4235632.html