基于ProvinceNo补全比率表缺失值的SQL实现方案求助

问题背景 #

现有临时表#ReflectionRatio结构及数据如下：

IF OBJECT_ID('tempdb..#ReflectionRatio') IS NOT NULL DROP TABLE #ReflectionRatio
CREATE TABLE #ReflectionRatio(
    [ReflectionRatioKey] [int] IDENTITY(1,1) NOT NULL,
    [StartDate] [date] NULL,
    [ProvinceNo] [nvarchar](3) NULL,
    [Rate] [float] NULL,
    [Period] [nvarchar](10) NULL
)

INSERT INTO #ReflectionRatio VALUES
(1,'2005-01-01','712',0.0002,'2005-01'),
(2,'2005-01-01','750',0.0661,'2005-01'),
(3,'2019-06-01','712',0.000114,'2019-06'),
(4,'2019-06-01','750',0.05972,'2019-06'),
(5,'2020-05-31','712',0.0002,'2020-05'),
(6,'2020-05-01','750',0.0661,'2020-05'),
(7,'2023-05-02','712',0.000242,'2023-05'),
(8,'2023-05-02','750',0.069265,'2023-05')

需在2005-01-01至2023-12-31时段内按以下规则补全缺失值：

• 规则1：若特定ProvinceNo对应时段的Rate缺失，取该省份最近的历史Rate值填充；
• 规则2：若某省份所有Rate值均为0.0，则取前一个省份的所有Rate值填充。

注：实际涉及多个省份，并非仅712和750。

尝试过窗口函数LEAD和LAG，但仅能获取紧邻的前后记录，无法满足需求，需可行的SQL实现方案。

解决方案 #

核心思路是先生成完整的时段-省份维度表，再关联原表的Rate数据，最后用窗口函数和关联查询实现规则逻辑。

完整SQL代码 #

WITH Months AS (
    -- 生成目标时段内的所有月度起始日期
    SELECT CAST('2005-01-01' AS DATE) AS MonthStart
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM Months
    WHERE MonthStart < CAST('2023-12-01' AS DATE)
),
Periods AS (
    -- 转换为需求的Period格式（YYYY-MM）
    SELECT 
        MonthStart,
        FORMAT(MonthStart, 'yyyy-MM') AS Period
    FROM Months
),
Provinces AS (
    -- 获取所有唯一省份编号
    SELECT DISTINCT ProvinceNo
    FROM #ReflectionRatio
),
FullDimension AS (
    -- 生成所有时段-省份的完整组合，确保无遗漏
    SELECT 
        p.ProvinceNo,
        pe.Period,
        pe.MonthStart AS StartDate
    FROM Periods pe
    CROSS JOIN Provinces p
),
FilledRates AS (
    -- 按规则1填充：取当前省份最近的非零非空历史Rate
    SELECT 
        fd.ProvinceNo,
        fd.StartDate,
        fd.Period,
        COALESCE(rr.Rate, last_non_null.Rate) AS FilledRate
    FROM FullDimension fd
    LEFT JOIN #ReflectionRatio rr 
        ON fd.ProvinceNo = rr.ProvinceNo 
        AND fd.Period = rr.Period
    OUTER APPLY (
        SELECT TOP 1 Rate
        FROM #ReflectionRatio rr_hist
        WHERE rr_hist.ProvinceNo = fd.ProvinceNo
          AND rr_hist.Period <= fd.Period
          AND rr_hist.Rate IS NOT NULL 
          AND rr_hist.Rate <> 0.0
        ORDER BY rr_hist.Period DESC
    ) last_non_null
),
FinalResult AS (
    -- 按规则2处理全0省份：取排序后前一个省份的对应时段Rate
    SELECT 
        fr.ProvinceNo,
        fr.StartDate,
        fr.Period,
        CASE 
            WHEN province_all_zero.IsAllZero = 1 THEN prev_province.Rate
            ELSE fr.FilledRate
        END AS FinalRate
    FROM FilledRates fr
    -- 判断省份是否所有Rate均为0
    LEFT JOIN (
        SELECT 
            ProvinceNo,
            CASE WHEN MAX(CASE WHEN Rate <> 0.0 THEN 1 ELSE 0 END) = 0 THEN 1 ELSE 0 END AS IsAllZero
        FROM #ReflectionRatio
        GROUP BY ProvinceNo
    ) province_all_zero ON fr.ProvinceNo = province_all_zero.ProvinceNo
    -- 获取排序后的前一个省份数据
    OUTER APPLY (
        SELECT TOP 1 fp.FilledRate AS Rate
        FROM FilledRates fp
        JOIN (
            SELECT 
                ProvinceNo,
                ROW_NUMBER() OVER (ORDER BY ProvinceNo) AS ProvinceRank
            FROM Provinces
        ) pr ON fp.ProvinceNo = pr.ProvinceNo
        JOIN (
            SELECT 
                ProvinceNo,
                ROW_NUMBER() OVER (ORDER BY ProvinceNo) AS ProvinceRank
            FROM Provinces
        ) curr_pr ON fr.ProvinceNo = curr_pr.ProvinceNo
        WHERE pr.ProvinceRank = curr_pr.ProvinceRank - 1
          AND fp.Period = fr.Period
    ) prev_province
)
-- 输出最终结果，按省份和时段排序
SELECT * FROM FinalResult
ORDER BY ProvinceNo, Period;

关键逻辑说明 #

1. 完整维度生成：通过递归CTE生成目标时段内的所有月度，再与省份做笛卡尔积，确保每个省份的每个时段都有记录；
2. 最近历史值填充：使用OUTER APPLY按时段倒序查找当前省份最近的有效Rate，解决LAG只能取紧邻记录的局限；
3. 全0省份处理：先统计每个省份是否全为0，再通过省份排序获取前一个省份的对应时段Rate完成填充。

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