如何为缺失年份复制REITSCapitalAndFundSizes表记录？

问题背景 #

我有一张按公司及年份存储数据的REITSCapitalAndFundSizes表，执行以下查询语句：

SELECT * FROM REITSCapitalAndFundSizes WHERE companyid = 4604

返回的companyid=4604记录如下：

CompanyID    REITSValue       CreatedOn       
    4604       500.0000         2015-06-09  
    4604       1633.0000        2018-03-19 

当前数据存在两处缺失：

• 2015年至2018年之间缺少2016、2017年记录，需复用2015年的REITSValue补全
• 2018年之后缺少2019-2023年记录，需复用2018年的REITSValue补全

预期输出 #

CompanyID   REITSValue  CreatedOn
4604       500.0000    2015-06-09 00:00:00.000
4604       500.0000    2016-06-09 00:00:00.000
4604       500.0000    2017-06-09 00:00:00.000
4604       1633.0000   2018-03-19 00:00:00.000
4604       1633.0000   2019-03-19 00:00:00.000
4604       1633.0000   2020-03-19 00:00:00.000

解决方案（SQL Server） #

使用递归CTE生成目标年份区间，关联原表匹配对应记录的REITSValue，自动补全缺失年份：

WITH YearSequence AS (
    -- 初始化：获取目标公司的时间范围与终点年份2023
    SELECT 
        CompanyID,
        YEAR(CreatedOn) AS CurrentYear,
        REITSValue,
        MONTH(CreatedOn) AS MonthPart,
        DAY(CreatedOn) AS DayPart,
        MAX(YEAR(CreatedOn)) OVER (PARTITION BY CompanyID) AS LastRecordYear,
        2023 AS TargetEndYear
    FROM REITSCapitalAndFundSizes
    WHERE CompanyID = 4604

    UNION ALL

    -- 递归生成后续年份
    SELECT 
        CompanyID,
        CurrentYear + 1,
        -- 超过最后记录年份后，复用最后一条的REITSValue
        CASE WHEN CurrentYear + 1 > LastRecordYear THEN REITSValue 
             ELSE (SELECT TOP 1 REITSValue FROM REITSCapitalAndFundSizes 
                   WHERE CompanyID = ys.CompanyID AND YEAR(CreatedOn) <= CurrentYear + 1 
                   ORDER BY YEAR(CreatedOn) DESC)
        END,
        -- 对应年份的月日复用最近记录的月日
        CASE WHEN CurrentYear + 1 > LastRecordYear THEN MonthPart
             ELSE (SELECT TOP 1 MONTH(CreatedOn) FROM REITSCapitalAndFundSizes 
                   WHERE CompanyID = ys.CompanyID AND YEAR(CreatedOn) <= CurrentYear + 1 
                   ORDER BY YEAR(CreatedOn) DESC)
        END,
        CASE WHEN CurrentYear + 1 > LastRecordYear THEN DayPart
             ELSE (SELECT TOP 1 DAY(CreatedOn) FROM REITSCapitalAndFundSizes 
                   WHERE CompanyID = ys.CompanyID AND YEAR(CreatedOn) <= CurrentYear + 1 
                   ORDER BY YEAR(CreatedOn) DESC)
        END,
        LastRecordYear,
        TargetEndYear
    FROM YearSequence ys
    WHERE CurrentYear < TargetEndYear
)
-- 去重并格式化日期输出
SELECT DISTINCT
    CompanyID,
    REITSValue,
    CONVERT(DATETIME, DATEFROMPARTS(CurrentYear, MonthPart, DayPart)) AS CreatedOn
FROM YearSequence
ORDER BY CreatedOn;

逻辑说明 #

1. YearSequence CTE：递归生成从最早记录年份到2023年的所有年份，同时为每个年份匹配最近的有效记录的REITSValue和月日信息。
2. 最终查询通过DISTINCT避免重复记录，将生成的日期转换为完整的DATETIME格式并按时间排序。

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