Oracle 19c如何实现基于上一行同列数据的分组累积计算

你之前用lag函数无法实现的原因是：lag只能获取原始表的字段值，无法读取本次查询动态计算生成的上一行CALC结果，这类组内迭代计算场景可以用以下两种方案实现：

方案1：使用Oracle MODEL子句（推荐，性能最优） #

MODEL是Oracle专有的迭代计算特性，完全匹配你的计算逻辑：

SELECT dt, name, amount, ROUND(calc, 3) AS calc
FROM test
MODEL
  PARTITION BY (name) -- 按NAME分组，组内独立计算
  ORDER BY (dt) -- 组内按日期排序，保证计算顺序正确
  MEASURES (amount, 0 AS calc) -- 定义计算字段，calc初始值设为0
  RULES (
    calc[ANY] = CASE WHEN cv(dt) = MIN(dt)[OVER (PARTITION BY name)]
                     THEN amount[CV()] / 5 -- 分组首行，初始值取0代入公式
                     ELSE (calc[CV(dt) - 1] * 4 + amount[CV()]) / 5 -- 非首行用前一行计算结果代入
                END
  )
ORDER BY name, dt;

方案2：使用递归CTE（通用标准语法，兼容性好） #

如果需要兼容其他支持递归语法的数据库，可以用该方案：

WITH ranked_data AS (
  -- 先给分组内每行按日期分配序号，方便递归匹配前一行
  SELECT 
    dt, name, amount,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY dt) AS rn
  FROM test
),
recursive_calc (dt, name, amount, rn, calc) AS (
  -- 递归起点：每个分组第一行的计算逻辑
  SELECT dt, name, amount, rn, amount / 5 AS calc
  FROM ranked_data
  WHERE rn = 1
  UNION ALL
  -- 递归迭代：用前一行的CALC结果计算当前行
  SELECT r.dt, r.name, r.amount, r.rn, (rc.calc * 4 + r.amount) / 5
  FROM recursive_calc rc
  JOIN ranked_data r 
    ON rc.name = r.name AND rc.rn + 1 = r.rn
)
SELECT dt, name, amount, ROUND(calc, 3) AS calc
FROM recursive_calc
ORDER BY name, dt;

执行结果验证 #

两种方案输出结果和你的预期完全匹配，可根据精度需求调整ROUND函数的保留位数：

内容的提问来源于stack exchange，提问作者Divya