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:窗口的终点是当前行，默认值，可以省略;
//
//方法二:
with cte_1 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
)
,cte_2 as(
select rownum rn,val from cte_1
)
select a.val , sum(b.val) sum_val
from cte_2 a , cte_2 b
where b.rn <= a.rn
group by a.val
/
//方法三：
//创建一个递归函数，求和
//f(n) = x + f(n-1)
create table t
as
select 1 id,1 val from dual union all
select 2,3 from dual union all
select 3,5 from dual union all
select 4,7 from dual union all
select 5,9 from dual
/
create or replace function fun_recursion(x in int)
return integer is
n integer :=0;
begin
select val into n
from t
where id=x;
if x=1 then
return n;
else
return n + fun_recursion(x-1);
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
end fun_recursion;
/
select val,fun_recursion(id) sum_val from t;
VAL SUM_VAL
---------- ----------
1 1
3 4
5 9
7 16
9 25
//
