在SQL Server中生成燃尽图所需Rate列的实现方案咨询

逻辑分析 #

Rate列的核心规则可以简化为：

• 第一行Rate等于该行的A值（无前置扣除项）
• 后续行中，若B不为NULL，Rate = 初始A值减去当前及之前所有非NULL B的累计和；若B为NULL，累计和不变，Rate直接沿用前一行结果

最优SQL实现 #

用窗口函数即可高效完成，无需复杂的CASE+LAG组合：

SELECT 
    ID,
    A,
    B,
    FIRST_VALUE(A) OVER(ORDER BY ID) - SUM(COALESCE(B, 0)) OVER(ORDER BY ID) AS Rate
FROM 
    your_table
ORDER BY 
    ID;

代码解释 #

• COALESCE(B, 0)：把NULL的B转为0，保证累计求和时不会跳过这些行
• FIRST_VALUE(A) OVER(ORDER BY ID)：取数据集第一行的A作为初始燃尽值（若你的场景中A会随ID变化，可按需调整）
• SUM(...) OVER(ORDER BY ID)：按ID顺序计算所有非NULL B的累计扣除量，用初始值减去该累计量即得当前行的Rate

兼容低版本SQL的替代方案 #

若你的SQL引擎不支持FIRST_VALUE，可改用子查询获取初始值：

WITH initial_value AS (
    SELECT A AS initial FROM your_table ORDER BY ID LIMIT 1
)
SELECT 
    t.ID,
    t.A,
    t.B,
    iv.initial - SUM(COALESCE(t.B, 0)) OVER(ORDER BY t.ID) AS Rate
FROM 
    your_table t
CROSS JOIN 
    initial_value iv
ORDER BY 
    t.ID;

验证结果 #

执行上述代码后，将得到与期望完全一致的结果：

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