如何生成SQL语句补全Good!至Bad状态间的缺失月份数据？

核心思路 #

避开递归CTE的性能瓶颈，通过窗口函数定位截止时间+非递归生成连续月份维度+关联补全的方式实现，确保百万级数据场景下的执行效率。

步骤与代码示例 #

以SQL Server为例，其他数据库（如MySQL、PostgreSQL）可调整语法适配：

1. 预处理原始数据，标记每个有效记录的截止月份

用LEAD()窗口函数获取每个分组（LotSysID+VehicleMainSysID）下，当前「Retail Inventory（Good!）」记录之后的第一个「Transferred（Bad）」记录的年月；若无后续Bad记录，则用当前年月作为截止。

WITH PreprocessedData AS (
    SELECT
        LotSysID,
        VehicleMainSysID,
        Year AS StartYear,
        Month AS StartMonth,
        Data,
        -- 把年月转成整数（如202405）方便比较，优先取下一个Bad记录的年月，无则取当前年月
        COALESCE(
            LEAD(Year * 100 + Month) OVER (
                PARTITION BY LotSysID, VehicleMainSysID
                ORDER BY Year, Month
            ),
            YEAR(GETDATE()) * 100 + MONTH(GETDATE())
        ) AS EndYearMonth
    FROM YourOriginalTable
    WHERE Description = 'Good!' -- 仅处理需要补全的Retail Inventory状态
)

2. 非递归生成连续月份维度表

借助系统自带数字表（或自建的大数字表）生成覆盖所有需要补全年份的连续年月，替代递归CTE：

, MonthDim AS (
    SELECT
        -- 生成从最早记录年月到当前年月的所有连续年月（转成整数格式）
        MIN(p.StartYear * 100 + p.StartMonth) OVER () + nums.n - 1 AS YearMonth
    FROM PreprocessedData p
    -- 生成足够多的数字，这里取1000个月（约83年）覆盖绝大多数场景
    CROSS JOIN (
        SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM master..spt_values
    ) nums
    WHERE
        MIN(p.StartYear * 100 + p.StartMonth) OVER () + nums.n - 1 
        <= YEAR(GETDATE()) * 100 + MONTH(GETDATE())
)

3. 关联补全数据并合并原始Bad记录

将预处理后的Good数据与连续月份表关联，筛选出起始到截止年月间的所有月份，再合并原始的Transferred记录：

SELECT
    p.LotSysID,
    p.VehicleMainSysID,
    FLOOR(m.YearMonth / 100) AS Year,
    m.YearMonth % 100 AS Month,
    'Retail Inventory' AS Filter,
    p.Data
FROM PreprocessedData p
JOIN MonthDim m
    ON m.YearMonth BETWEEN p.StartYear * 100 + p.StartMonth AND p.EndYearMonth
-- 合并原始的Transferred状态记录
UNION ALL
SELECT
    LotSysID,
    VehicleMainSysID,
    Year,
    Month,
    'Transferred' AS Filter,
    Data
FROM YourOriginalTable
WHERE Description = 'Bad'
-- 按分组和时间排序
ORDER BY LotSysID, VehicleMainSysID, Year, Month;

性能优化要点 #

• 复用现有日期维度表：如果数据库中有现成的日期维度表（包含Year、Month、YearMonth字段），直接替代上述MonthDim，能大幅提升效率。
• 自建数字表：系统自带的master..spt_values数据量有限，建议自建一个包含1~100000的数字表，避免因数字不足导致漏补月份。
• 添加联合索引：给原始表的LotSysID、VehicleMainSysID、Year、Month字段创建联合索引，显著加快窗口函数和关联操作的速度。
• 分批次处理：针对超大规模数据，可按LotSysID分段批量执行，避免一次性加载全量数据引发内存瓶颈。

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