使用前序记录值填充表格中缺失的月度数据缺口

嘿，我来帮你搞定这个用前序记录填充缺失月度数据的问题！你的需求很清晰——补全那些缺失的月份行，并且用上一个已有的有效值来填充对应的Value字段对吧？

首先提个小细节：你的测试表字段名repoting_month有拼写错误，应该是reporting_month，我在下面的代码里已经修正了，避免后续关联出问题。

解决方案思路 #

要实现这个需求，核心分两步：

• 生成连续的月份序列：因为原表没有缺失月份的记录，我们得先“造”出这些月份的行，确保1到10月每个月都有一条记录。
• 填充缺失的Value值：把生成的月份序列和原表关联后，用窗口函数抓取前面最近的非空Value值，自动填充到缺失的位置。

完整SQL代码 #

-- 修正拼写错误后的测试表
DECLARE @test TABLE
(
    reporting_year DATE,
    reporting_month INTEGER,
    Value INTEGER
)

INSERT INTO @test
VALUES
('2022-01-01', 1, 3),
('2022-05-01', 5, 4),
('2022-07-01', 7, 4),
('2022-08-01', 8, 5),
('2022-09-01', 9, 5),
('2022-10-01', 10, 5);

-- 生成1到10的连续月份序列
WITH MonthSequence AS
(
    SELECT 1 AS month_num
    UNION ALL
    SELECT month_num + 1
    FROM MonthSequence
    WHERE month_num < 10
)
-- 关联原表并填充缺失值
SELECT
    CAST('2022-01-01' AS DATE) AS reporting_year,
    ms.month_num AS reporting_month,
    -- 用LAST_VALUE获取当前行之前最近的非空Value
    LAST_VALUE(t.Value) OVER (ORDER BY ms.month_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Value
FROM MonthSequence ms
LEFT JOIN @test t ON ms.month_num = t.reporting_month
ORDER BY ms.month_num;

代码解释 #

1. MonthSequence CTE：通过递归的方式生成1到10的连续数字，对应我们需要的所有月份。如果你的需求扩展到全年12月，只需要把WHERE month_num < 10改成WHERE month_num < 12就行。
2. LEFT JOIN关联：把月份序列和测试表关联，这样缺失的月份对应的Value会是NULL，但我们保留了所有月份的行。
3. LAST_VALUE窗口函数：这个函数会在窗口范围内（从第一行到当前行）抓取最后一个非空的Value值，刚好完美实现“用前序记录填充”的需求。如果你的Value字段有波动（不是递增），这个方法比用MAX()更准确，因为它取的是最近的有效值，而不是最大值。

执行这段代码后，你就能得到和@required表完全一致的结果啦！

备注：内容来源于stack exchange，提问作者Lee Murray