SQL Server表补全WW列间隙：按PN&SP分组填充Pr值

现有表Table A结构与数据 #

表Table A包含PN、SP、Pr、WW四列，其中WW为工作周列（格式为YYYYWW，每年第52/53周后转为次年第01周），原始数据如下：

PN      SP      Pr      WW
-----------------------------
P1      S1      300    202301            
P1      S1      400    202305        
P1      S3      600    202302   
P1      S3      700    202305
P2      S3      700    202301   
P2      S3      800    202306   

需求说明 #

按PN与SP分组，补全相邻工作周之间缺失的周数，并用前一个可用的Pr值填充这些间隙，生成目标表Table B，示例数据如下：

PN      SP      Pr      WW
-----------------------------
P1      S1      300    202301 
P1      S1      300    202302
P1      S1      300    202303  
P1      S1      300    202304   
P1      S1      400    202305        
P1      S3      600    202302
P1      S3      600    202303 
P1      S3      600    202304  
P1      S3      700    202305
P2      S3      700    202301
P2      S3      700    202302 
P2      S3      700    202303 
P2      S3      700    202304 
P2      S3      700    202305   
P2      S3      800    202306

实现SQL查询语句 #

以下SQL语句通过递归CTE生成每个分组内的连续工作周，再结合窗口函数填充缺失的Pr值：

WITH GroupedWeeks AS (
    -- 获取每个PN+SP分组的最小和最大工作周
    SELECT 
        PN,
        SP,
        MIN(WW) AS MinWW,
        MAX(WW) AS MaxWW
    FROM TableA
    GROUP BY PN, SP
),
WeekSequence AS (
    -- 递归生成每个分组内的连续工作周
    SELECT
        PN,
        SP,
        MinWW AS CurrentWW,
        MaxWW
    FROM GroupedWeeks
    UNION ALL
    SELECT
        gs.PN,
        gs.SP,
        -- 处理工作周递增，包含跨年场景
        CASE 
            WHEN RIGHT(CurrentWW, 2) IN ('52', '53') THEN LEFT(CurrentWW, 4) + '01'
            ELSE LEFT(CurrentWW, 4) + RIGHT('0' + CAST(CAST(RIGHT(CurrentWW, 2) AS INT) + 1 AS VARCHAR(2)), 2)
        END AS CurrentWW,
        gs.MaxWW
    FROM WeekSequence ws
    JOIN GroupedWeeks gs ON ws.PN = gs.PN AND ws.SP = gs.SP
    WHERE ws.CurrentWW < gs.MaxWW
)
-- 关联原表并填充Pr值
SELECT
    ws.PN,
    ws.SP,
    -- 取当前周及之前最近的非空Pr值
    LAST_VALUE(ta.Pr) OVER (
        PARTITION BY ws.PN, ws.SP 
        ORDER BY ws.CurrentWW 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Pr,
    ws.CurrentWW AS WW
FROM WeekSequence ws
LEFT JOIN TableA ta ON ws.PN = ta.PN AND ws.SP = ta.SP AND ws.CurrentWW = ta.WW
ORDER BY ws.PN, ws.SP, ws.CurrentWW;

说明 #

1. GroupedWeeks CTE：统计每个PN+SP分组的起始和结束工作周，确定需要补全的周数范围。
2. WeekSequence CTE：递归生成该范围内的所有连续工作周，处理了跨年时周数从52/53转为01的场景。
3. 最终查询通过左连接原表，使用LAST_VALUE窗口函数向前查找最近的非空Pr值，完成间隙填充。

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