这样? with t1 as ( select 1 id, null c1, 18.97/100 c2 from dual union all select 2 id, 0.04/100 c1, null from dual union all select 3 id, 0.02/100 c1, null from dual union all select 4 id, 0.02/100 c1, null from dual union all select 5 id, -0.02/100 c1, null from dual union all select 6 id, 0.02/100 c1, null from dual ) select id, c1, c2 , case when id=1 then c2 else (exp(sum(ln((1+nvl(c1, 0))*(1+nvl(c2,0)))) over(order by id)))-1 end from t1
就拿第二行举例子: select (1+0.170495255)*(1-0.000999861061490215)-1 from dual 你看一下结果是多少,在计算器中也计算一下,看看结果是多少。
再执行 with t1 as ( select 1 id, null c1, 0.170495255 c2 from dual union all select 2 id, -0.000999861061490215 c1, null from dual ) select id, c1, c2 , case when id=1 then c2 else (exp(sum(ln((1+nvl(c1, 0))*(1+nvl(c2,0)))) over(order by id)))-1 end from t1 看看结果是多少。
with t1 as
(
select 1 id, null c1, 18.97/100 c2 from dual
union all
select 2 id, 0.04/100 c1, null from dual
union all
select 3 id, 0.02/100 c1, null from dual
union all
select 4 id, 0.02/100 c1, null from dual
union all
select 5 id, -0.02/100 c1, null from dual
union all
select 6 id, 0.02/100 c1, null from dual
)
select id, c1, c2
, case when id=1 then c2 else (exp(sum(ln((1+nvl(c1, 0))*(1+nvl(c2,0)))) over(order by id)))-1 end
from t1
现在想要把B列剩下的值计算出来公式为发帖所贴的截图
结果和我用EXCEL算出来有微小差异
结果和我用EXCEL算出来有微小差异
我试了,如果小数点后位数够多,结果是一样的。
结果和我用EXCEL算出来有微小差异
我试了,如果小数点后位数够多,结果是一样的。不一样,我把小数位全带进去算了截图如下,
图一为准确结果:图二为SQL计算结果
大神,帮忙再看看,这语句属实有点看不明白,感谢!
结果和我用EXCEL算出来有微小差异
我试了,如果小数点后位数够多,结果是一样的。不一样,我把小数位全带进去算了截图如下,
图一为准确结果:图二为SQL计算结果
大神,帮忙再看看,这语句属实有点看不明白,感谢!
就拿第二行举例子:
select (1+0.170495255)*(1-0.000999861061490215)-1 from dual
你看一下结果是多少,在计算器中也计算一下,看看结果是多少。
with t1 as
(
select 1 id, null c1, 0.170495255 c2 from dual
union all
select 2 id, -0.000999861061490215 c1, null from dual
)
select id, c1, c2
, case when id=1 then c2 else (exp(sum(ln((1+nvl(c1, 0))*(1+nvl(c2,0)))) over(order by id)))-1 end
from t1
看看结果是多少。