使用SQL Server生成燃尽图数据：补全缺失风险等级月度记录

要满足每个月份同时存在High和Critical两条记录、无数据时沿用上月值的需求，必须创建日期表（或用CTE生成连续月份序列）——你的原表只包含有Issue Count记录的月份，窗口函数只能基于现有数据行计算，没法自动补全缺失的月份行，更没法自动关联上月的有效值。

具体实现步骤如下：

1. 生成连续月份序列
   先获取数据覆盖的所有月份范围，用递归CTE或者预先创建的日期表生成每个月的起始日期（比如2024-01-01、2024-02-01这类格式）。

2. 生成月份与风险等级的全量组合
   把连续月份和('Critical','High')两个风险等级做交叉连接，确保每个月份都对应两条记录（各一个等级）。

3. 关联原表数据并聚合
   将全量组合与原表按月份、风险等级关联，统计每个月每个等级的Issue Count（无数据时为NULL）。

4. 填充上月有效值
   用窗口函数LAG(...) IGNORE NULLS（SQL Server 2022及以上版本支持），或者递归CTE，把NULL值替换为对应等级的上月有效值。

5. 计算剩余问题数
   基于填充好的数据，按风险等级分组、月份排序，用窗口函数计算各月的剩余问题数（适配你原本rows between current row and unbounded following的逻辑）。

示例SQL代码 #

-- 生成数据覆盖的所有连续月份
WITH AllMonths AS (
    SELECT MIN(DATEFROMPARTS(YEAR(FixDate), MONTH(FixDate), 1)) AS MonthStart
    FROM YourTableName
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM AllMonths
    WHERE DATEADD(MONTH, 1, MonthStart) <= (SELECT MAX(DATEFROMPARTS(YEAR(FixDate), MONTH(FixDate), 1)) FROM YourTableName)
),
-- 生成每个月份+风险等级的全量组合
MonthRiskPairs AS (
    SELECT 
        am.MonthStart,
        r.RiskLevel
    FROM AllMonths am
    CROSS JOIN (VALUES ('Critical'), ('High')) r(RiskLevel)
),
-- 关联原表，统计每月各等级的问题数
RawMonthlyData AS (
    SELECT 
        mrp.MonthStart,
        mrp.RiskLevel,
        ISNULL(SUM(yt.IssueCount), 0) AS MonthlyIssueCount
    FROM MonthRiskPairs mrp
    LEFT JOIN YourTableName yt
        ON DATEFROMPARTS(YEAR(yt.FixDate), MONTH(yt.FixDate), 1) = mrp.MonthStart
        AND yt.RiskLevel = mrp.RiskLevel
    GROUP BY mrp.MonthStart, mrp.RiskLevel
),
-- 填充上月有效值（SQL Server 2022+支持IGNORE NULLS）
FilledData AS (
    SELECT 
        MonthStart,
        RiskLevel,
        -- 若当月无数据，用上月的数值
        LAG(MonthlyIssueCount, 1, MonthlyIssueCount) OVER (
            PARTITION BY RiskLevel 
            ORDER BY MonthStart 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS FilledIssueCount
    FROM RawMonthlyData
)
-- 计算各月剩余问题数（按风险等级分组，从当前月到后续所有月的累计，适配燃尽图逻辑）
SELECT 
    MonthStart,
    RiskLevel,
    SUM(FilledIssueCount) OVER (
        PARTITION BY RiskLevel 
        ORDER BY MonthStart DESC 
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS RemainingIssues
FROM FilledData
ORDER BY MonthStart, RiskLevel;

如果你的SQL Server版本低于2022，不支持IGNORE NULLS，可以用递归CTE逐行检查填充NULL值——核心逻辑是若当前行无数据则直接继承上一行的数值。

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