MS SQL 如何为Valid为真的行填充PreUnits与PostUnits字段

需求说明 #

当表中Valid字段值为True时，取该连续Valid区间开始前的最后一行Units值，填充到区间内所有行的PreUnits列；同时取该连续Valid区间结束后的第一行Units值，填充到区间内所有行的PostUnits列。

实现思路 #

1. 按ProductCode分区、Date字段排序，使用窗口函数对连续Valid区间做「孤岛分组」，确保同一段连续Valid的行归属同一个分组
2. 提取每个Valid=1的分组对应的前置最近非Valid行的Units值，和后置最近非Valid行的Units值
3. 将计算得到的分组级前后值回写到原表的对应字段

实现代码 #

WITH CTE_Groups AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY [Date]) AS RN,
        -- 生成连续Valid区间的分组ID
        ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY [Date]) 
        - ROW_NUMBER() OVER (PARTITION BY ProductCode, Valid ORDER BY [Date]) AS GroupID
    FROM SODATA
),
CTE_GroupValues AS (
    SELECT 
        ProductCode,
        GroupID,
        -- 获取当前Valid分组前最近一行的Units值
        LAG(Units) OVER (PARTITION BY ProductCode ORDER BY MIN(RN)) AS GroupPreUnits,
        -- 获取当前Valid分组后第一行的Units值
        LEAD(Units) OVER (PARTITION BY ProductCode ORDER BY MIN(RN)) AS GroupPostUnits
    FROM CTE_Groups
    GROUP BY ProductCode, GroupID, Valid
    HAVING Valid = 1
)
UPDATE t
SET 
    PreUnits = gv.GroupPreUnits,
    PostUnits = gv.GroupPostUnits
FROM SODATA t
INNER JOIN CTE_Groups g ON t.PKID = g.PKID
INNER JOIN CTE_GroupValues gv ON g.ProductCode = gv.ProductCode AND g.GroupID = gv.GroupID
WHERE g.Valid = 1;

注意事项 #

• 代码默认按ProductCode分区计算，不同产品的数据互不干扰，符合多产品数据存储的常规业务场景
• 如果连续Valid区间出现在所有数据的最开头，没有前置行，则PreUnits会保留NULL；如果出现在所有数据的最末尾，没有后置行，则PostUnits会保留NULL，符合逻辑边界处理规则
• 执行UPDATE操作前可先把UPDATE部分替换为SELECT查询，验证计算结果符合预期后再执行更新操作

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