Oracle 累计求和

Oracle 累计求和

//将当前行某列的值与前面所有行的此列值相加,即累计求和:  
with t as(  
     select 1 val from dual union all  
     select 3 from dual union all  
     select 5 from dual union all  
     select 7 from dual union all  
     select 9 from dual)  
select val,  
       sum(val)  
       over (order by rownum rows between unbounded preceding and current row)  
       sum_val  
from t  
group by rownum,val  
order by rownum;  
       VAL    SUM_VAL  
---------- ----------  
         1          1  
         3          4  
         5          9  
         7         16  
         9         25  
//解析：  
//sum(val)计算累积和;  
//order by rownum 按照伪列rownum对查询的记录排序;  
//between unbounded preceding and current row:定义了窗口的起点和终点;  
//unbounded preceding:窗口的起点包括读取到的所有行;  
//current row:窗口的终点是当前行，默认值，可以省略;