SQL基于前序行值按日递推计算剩余总点数（适配Power BI燃尽图）

问题核心 #

你遇到的两个典型问题：

• 普通LAG()函数仅能提取上一行的原始字段值，无法引用上一行计算生成的剩余点数字段做递推扣减，硬写自引用逻辑会直接报错
• 无故事点完成的日期如果直接查业务表会缺记录，需要自动填充最近一次计算得到的剩余点数，适配燃尽图连续日期展示要求
  已知初始总点数为100，扣减后预期剩余值序列为99、97.5、94.5，结果需适配Power BI燃尽图展示。

实现思路 #

放弃逐行递推的计算逻辑，改用累计完成量差值法，逻辑和逐行扣减完全等价但性能更高：

1. 先构造覆盖整个迭代周期的连续日期序列，补齐无工作记录的日期
2. 关联每日实际完成的故事点，无完成记录的日期当日完成量记为0
3. 用窗口函数计算从迭代起始日到当日的累计完成点数，用初始总点数100减去累计完成值，直接得到当日剩余点数——这个逻辑天然实现了逐行扣减的递推效果，不需要自引用上一行的计算结果
4. 无完成点数的日期，累计完成值不会变动，剩余点数会自动保持上一次的计算结果，不需要额外做填充处理

可直接复用的SQL代码（适配SQL Server/MySQL 8.0/PostgreSQL等主流数据库） #

-- 按需修改迭代起止日期、业务表名、字段名即可
WITH date_range AS (
    SELECT 
        CAST('2024-01-01' AS DATE) AS iter_start_date, -- 替换为你的迭代开始日期
        CAST('2024-01-31' AS DATE) AS iter_end_date    -- 替换为你的迭代结束日期
),
-- 生成迭代周期内的连续日期序列
continuous_date AS (
    SELECT 
        DATEADD(DAY, num, iter_start_date) AS report_date
    FROM date_range
    -- 以下生成连续日期的逻辑如果是MySQL/PG可替换为自带的日期生成函数
    JOIN master..spt_values ON type = 'P' 
        AND DATEADD(DAY, num, iter_start_date) <= iter_end_date
),
-- 关联每日完成点数，无记录的日期完成量填0
daily_point AS (
    SELECT
        cd.report_date,
        ISNULL(sp.completed_points, 0) AS daily_completed
    FROM continuous_date cd
    LEFT JOIN your_story_point_biz_table sp 
        ON cd.report_date = CAST(sp.complete_time AS DATE) -- 替换为你的业务表名、完成时间字段、点数字段
)
-- 计算最终剩余点数
SELECT
    report_date,
    daily_completed,
    100 - SUM(daily_completed) OVER (
        ORDER BY report_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS remaining_points
FROM daily_point
ORDER BY report_date

结果校验 #

按照你给出的预期值验证计算逻辑：

• 首日完成1点：累计完成1，剩余100-1=99，匹配预期
• 次日完成1.5点：累计完成2.5，剩余100-2.5=97.5，匹配预期
• 第三日完成3点：累计完成5.5，剩余100-5.5=94.5，完全符合要求
• 后续连续无完成点数的日期，累计完成值保持不变，剩余点数会自动延续最近一次的计算结果，不会出现空值或断档

Power BI 原生DAX实现方案 #

如果不想在SQL层做预处理，可以直接在Power BI中搭配独立的日期维度表写度量值，效果完全一致：

Remaining Story Points = 
VAR Current_Report_Date = MAX('Dim_Date'[Date])
VAR Cumulative_Completed = CALCULATE(
    SUM('Fact_Story'[Completed_Points]),
    'Dim_Date'[Date] <= Current_Report_Date,
    ALLSELECTED('Dim_Date')
)
RETURN
100 - Cumulative_Completed

将日期维度表的日期字段拖入横轴，该度量值拖入纵轴，即可直接生成符合要求的燃尽图。

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