oracle求累积收益率,解决报表sql中的累计收益率问题？换个姿势，再来一次~

原标题：解决报表sql中的累计收益率问题？换个姿势，再来一次~

最近在做券商资产分析业务的时候，碰到个报表需求，要求计算从20170301到20170831期间，大约40w客户(表数据量5000w)的按天累计收益率的报表。

计算公式如下：

累计收益率=(1+上日累计收益率)*(1+当日收益率)-1

小知识：累积收益率不仅可以按天分割累积，如果想要更精确，还可以按小时累积，如果是很粗略的计算，也可以按月累计。

好的设计算法，应该是从第一天开始，每天都算一次到当天的累计收益率，然后想算到哪天，直接跟一个where条件busi_date就好，然鹅这种方法我们必须拍一个起始值，且如果碰到产品经理突发奇想，拍脑袋定义了一个日期，从该日期算起，就两眼一懵了。

当日收益率是存在于我们的表中，我将其称为原材料数据。结合上面的计算公式，这就很像一个乘法的斐波那契算法，每一天的累计收益率，都是需要上一天的累计收益率(第一天的累计收益率，我们定为0)。于是脑海中想到一个恐怖的词汇：

递归

姿势一：pl/sql匿名块

代码：略，思路见优点，这里就不展开了

优点：可控，我们可以按天loop循环计算，每计算一天以后，将结果输出到一张表中，便于我们估算运算进度和运行时间。

缺点：过多的上下文切换，性能对比一条sql语句来说，较差。也不适用"能用sql搞定，就不要用plsql"的itpub的信条(笑)

姿势二：sql(递归with)

这里我截取最主要的部分代码(整体代码超过300行)，直接把tmp1视图当作实体表即可，我也做了

实体化hint。重点是tmp2，这里没有选择使用connect by做递归是考虑到数学公式中的连乘需求，选择了oracle的CTE递归。

知识点引申：oracle支持connect by，CTE递归；mssql支持cte递归，不支持connect by；dm支持connect by，正在支持cte递归；

sqllite支持cte递归，不支持connect by；mysql貌似都不支持，只能用存储过程替代。

代码及计划：

withtmp1as

(select/*+PARALLEL(a 8) materialize*/a.busi_date,

case

whena.ord_dybgn_tot_net_ast + a.crd_dybgn_tot_net_ast +

nvl(b.ord_time_cptl_flw_ent,0) +

nvl(b.crd_time_cptl_flw_ent,0) <>0then

(a.ord_d_prft_amt + a.crd_d_prft_amt) /

(a.ord_dybgn_tot_net_ast + a.crd_dybgn_tot_net_ast +

nvl(b.ord_time_cptl_flw_ent,0) +

nvl(b.crd_time_cptl_flw_ent,0)) *100

else

0

endasprft_rto_100,

case

whena.ord_dybgn_tot_net_ast + a.crd_dybgn_tot_net_ast +

nvl(b.ord_time_cptl_flw_ent,0) +

nvl(b.crd_time_cptl_flw_ent,0) <>0then

(a.ord_d_prft_amt + a.crd_d_prft_amt) /

(a.ord_dybgn_tot_net_ast + a.crd_dybgn_tot_net_ast +

nvl(b.ord_time_cptl_flw_ent,0) +

nvl(b.crd_time_cptl_flw_ent,0))

else

0

endasprft_rto,

a.client_id,

row_number() over(partitionbya.client_id orderbya.busi_date) rn

from(select*

fromddw.t_ddw_f21_c_d_ast_prft_n

w