SQL Server 2016无需存储过程递推填充零值的实现方案

问题核心原因 #

你使用的LAG()属于窗口函数，计算时依赖查询执行前的原始表快照数据，同一SELECT层中计算出的新VALUE值不会被窗口函数捕获，因此无法直接取到上一行的计算结果。

解决方案：递归公用表表达式（CTE） #

无需创建存储过程，用递归CTE即可实现逐行迭代计算，逻辑如下：

1. 先对需要填充的预测行（PERIODE_FORECAST > PERIODE）按PERIODE_FORECAST升序排序，分配连续行号
2. 递归CTE的锚点成员计算第一行（2021-09-01）的初始值
3. 递归成员每次关联上一行的计算结果，乘当前行的系数得到当前行值
4. 最后合并不需要填充的原始行和计算完成的预测行

示例代码 #

WITH RankedForecast AS (
    -- 构造带行号的待计算数据集
    SELECT 
        PERIODE,
        PERIODE_FORECAST,
        VALUE,
        COEFFICIENT, -- 存储当前行对应系数的字段，固定系数可直接替换为对应数值
        ROW_NUMBER() OVER (ORDER BY PERIODE_FORECAST ASC) AS rn
    FROM MyTable
    WHERE PERIODE_FORECAST > PERIODE
),
RecursiveCalc AS (
    -- 锚点：计算第一行初始值
    SELECT 
        PERIODE,
        PERIODE_FORECAST,
        CAST(39814.047 * 0.5 AS DECIMAL(18,6)) AS VALUE,
        rn
    FROM RankedForecast
    WHERE rn = 1
    UNION ALL
    -- 递归迭代计算后续行
    SELECT 
        r.PERIODE,
        r.PERIODE_FORECAST,
        CAST(rc.VALUE * r.COEFFICIENT AS DECIMAL(18,6)) AS VALUE,
        r.rn
    FROM RankedForecast r
    INNER JOIN RecursiveCalc rc ON r.rn = rc.rn + 1
)
-- 合并结果输出
SELECT PERIODE, PERIODE_FORECAST, VALUE 
FROM MyTable 
WHERE PERIODE_FORECAST <= PERIODE
UNION ALL
SELECT PERIODE, PERIODE_FORECAST, VALUE 
FROM RecursiveCalc
ORDER BY PERIODE_FORECAST ASC
-- 若待计算行数超过100，取消下面注释解除递归深度限制
-- OPTION (MAXRECURSION 0)

注意事项 #

• 如果系数是固定规则（比如第二行固定乘0.7，后续统一乘某个值），可以直接替换r.COEFFICIENT为对应固定值，也可以通过CASE WHEN rn = 2 THEN 0.7 ELSE 其他系数 END的方式按行号指定系数
• 统一对计算结果做CAST转换可以避免浮点精度丢失问题
• 递归CTE默认递归深度为100，超过100行待计算数据需要加OPTION (MAXRECURSION 0)参数，0代表不限制递归深度

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