如何高效填充Article与Article_history表的月度状态报告时间间隙？

核心思路 #

要解决这个问题，关键是先生成所有Article与目标月份的完整组合，再为每个组合匹配截至该月份的最后一次状态变更记录；没有历史记录的产品，直接用Article表的当前状态填充。这种方式完全避免了游标带来的性能损耗，同时能保证数据的完整性。

完整SQL代码 #

-- 1. 生成目标时间区间内的所有月份（这里以2019-11到当前月份为例，可按需调整起始/结束时间）
WITH TheDates AS (
    SELECT DATEFROMPARTS(2019, 11, 1) AS theDate
    UNION ALL
    SELECT DATEADD(MONTH, 1, theDate)
    FROM TheDates
    WHERE DATEADD(MONTH, 1, theDate) <= GETDATE()
),
-- 2. 生成所有Article与所有月份的笛卡尔积，确保每个产品每个月份都有基础记录
ArticleMonthCombination AS (
    SELECT 
        d.theDate,
        a.id,
        a.state AS default_state
    FROM TheDates d
    CROSS JOIN Article a
),
-- 3. 为每个组合匹配截至该月份的最后一次状态变更
LastStatePerMonth AS (
    SELECT
        amc.theDate,
        amc.id,
        -- 优先取历史记录的最后状态，无历史则用产品当前状态
        COALESCE(ah.state, amc.default_state) AS state,
        -- 用ROW_NUMBER筛选每个id+月份的最新历史记录
        ROW_NUMBER() OVER (PARTITION BY amc.id, amc.theDate ORDER BY ah.datechange DESC) AS rn
    FROM ArticleMonthCombination amc
    LEFT JOIN Article_history ah
        ON ah.id = amc.id
        AND ah.datechange <= EOMONTH(amc.theDate)
)
-- 4. 输出最终结果：每个产品每个月份只保留一条有效状态记录
SELECT
    CONVERT(DATE, theDate) AS [date],
    id,
    state
FROM LastStatePerMonth
WHERE rn = 1
ORDER BY id, theDate
OPTION (MAXRECURSION 0);

代码分步解释 #

1. TheDates CTE：递归生成指定起始月份到当前月份的所有月初日期，确保覆盖需要报告的所有时间节点。
2. ArticleMonthCombination CTE：通过CROSS JOIN生成产品和月份的全量组合，这一步直接解决了“缺失无变更月份记录”的核心问题。
3. LastStatePerMonth CTE：关联历史状态表，用ROW_NUMBER()按变更时间倒序排序，筛选出每个产品在当前月份之前的最后一次状态变更；同时用COALESCE处理无历史记录的情况，直接使用Article表的当前状态。
4. 最终查询：过滤出每个组合的唯一有效记录，按产品和日期排序，得到符合要求的月度报告。

测试结果验证 #

用你提供的示例数据执行后，会得到如下结果：

完全符合预期：产品1的2019-12月份填充了最后已知状态1，产品2、3无历史记录则用Article表的默认状态填充。

性能优化建议 #

针对你提到的3k条Article记录，这个方案的性能完全够用（3k*120个月=36万条记录，SQL Server可高效处理）。若要进一步优化：

• 给Article_history表创建(id, datechange)的复合索引，加速关联和排序操作。
• 若时间区间固定，可将TheDates替换为静态日期表，避免递归带来的微小开销。

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