Oracle 19c嵌套WITH子句替代实现及递归计算CALC3方案

AI 小北

2026-10-6

实现方案

Oracle 19c 支持同层级顺序定义多个WITH子句（后定义的子句可直接引用所有前置已定义的子句），完全不需要使用嵌套WITH结构即可满足你的需求。你只需要把原本嵌套的逻辑平铺展开为多段顺次定义的CTE即可，完整实现代码如下：

完整可运行SQL

WITH t AS
 (
 -- 给每个商品分组内按日期排序生成序号
 SELECT
     test.*,
     row_number() over ( partition by name order by dt ) as seq
   from test
 ),
 results_calc AS
 (
 -- 第一层递归计算calc字段
   select name, dt, value, value/5 calc, seq
   from t
   where seq = 1
   union all
   select t.name, t.dt, t.value, ( 4 * results_calc.calc + t.value ) / 5, t.seq
   from t, results_calc
   where t.seq - 1 = results_calc.seq
   and  t.name = results_calc.name
 ),
 calc2_data AS
 (
 -- 计算calc2字段
   select 
     name, 
     dt, 
     value, 
     calc, 
     seq,
     calc*3 as calc2
   from results_calc
 ),
 results_calc3 AS
 (
 -- 第二层递归计算calc3字段，匹配你给出的计算规则
   select 
     name, 
     dt, 
     value, 
     calc, 
     calc2,
     seq,
     (0 * 4 + calc2)/5 as calc3 -- 首条记录前序calc3取0
   from calc2_data
   where seq = 1
   union all
   select 
     cd.name, 
     cd.dt, 
     cd.value, 
     cd.calc, 
     cd.calc2,
     cd.seq,
     (rc3.calc3 * 4 + cd.calc2)/5 as calc3 -- 非首条取前一条calc3计算
   from calc2_data cd
   join results_calc3 rc3
     on cd.seq - 1 = rc3.seq
     and cd.name = rc3.name
 )
-- 最终输出结果，保留2位小数和示例匹配
select 
  name,
  dt,
  value,
  round(calc,2) as calc,
  round(calc2,2) as calc2,
  round(calc3,2) as calc3
from results_calc3
order by name, seq;