如何在SQL Server中实现累加式添加剂百分比计算

在Excel中可以轻松完成累加式添加剂百分比的建模计算，但在SQL Server中实现时遇到了困难。尝试过窗口函数（如LAG()），但因为新百分比的计算需要依赖上一次的计算结果，单纯的窗口函数无法满足需求。

计算逻辑说明 #

• 第1行：初始容器有200加仑溶液，加入200加仑添加剂，添加剂占比 = 200 ÷ (200+200) = 50%
• 第2行：消耗后剩余300加仑浓度为50%的溶液，加入200加仑添加剂，新占比 = (300×50% + 200) ÷ (300+200) = 70%
• 后续行以此类推，每次计算都基于上一行的添加剂占比，最终百分比会逐渐趋近100%

测试数据临时表代码 #

create table #x (starting_vat_level int, additive_added int, pct_of_additive float null)
insert into #x values (200, 200, NULL)
insert into #x values (300, 150, NULL)
insert into #x values (100, 50, NULL)
insert into #x values (100, 100, NULL)
insert into #x values (150, 50, NULL)
insert into #x values (150, 100, NULL)
insert into #x values (200, 150, NULL)
insert into #x values (300, 50, NULL)
insert into #x values (300, 100, NULL)
insert into #x values (150, 50, NULL)
insert into #x values (100, 80, NULL)
insert into #x values (50, 10, NULL)

解决方案 #

由于每一行的计算依赖前一行的结果，适合使用递归CTE来实现，具体代码如下：

WITH ranked_data AS (
    -- 给每行数据添加序号，确保计算顺序正确（若有明确排序字段，替换ORDER BY后的内容）
    SELECT 
        starting_vat_level, 
        additive_added,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM #x
),
recursive_calc AS (
    -- 初始行（第一行）的计算
    SELECT 
        starting_vat_level,
        additive_added,
        CAST(additive_added AS FLOAT) / (starting_vat_level + additive_added) AS pct_of_additive,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归计算后续行
    SELECT 
        rd.starting_vat_level,
        rd.additive_added,
        CAST((rd.starting_vat_level * rc.pct_of_additive) + rd.additive_added AS FLOAT) / (rd.starting_vat_level + rd.additive_added) AS pct_of_additive,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_calc rc ON rd.rn = rc.rn + 1
)
-- 查询最终结果，转换为百分比并保留两位小数
SELECT 
    starting_vat_level,
    additive_added,
    ROUND(pct_of_additive * 100, 2) AS pct_of_additive
FROM recursive_calc
ORDER BY rn;

-- 若需更新临时表的pct_of_additive字段，执行以下语句
-- UPDATE x
-- SET pct_of_additive = rc.pct_of_additive
-- FROM #x x
-- JOIN recursive_calc rc ON x.starting_vat_level = rc.starting_vat_level AND x.additive_added = rc.additive_added;

代码说明 #

1. 用ROW_NUMBER()给每行数据添加序号，保证计算顺序与插入顺序一致；
2. 递归CTE的锚点成员计算第一行的百分比；
3. 递归成员通过关联上一行的结果，计算当前行的添加剂占比；
4. 可直接查询结果，或更新临时表的目标字段。

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