TSQL gaps and island分区实现：为MyTable按规则新增Y列需求

需求说明 #

现有表定义如下：

CREATE TABLE MyTable (Pos INT UNIQUE, X INT)
INSERT INTO MyTable VALUES (3, 2)
INSERT INTO MyTable VALUES (5, 0)
INSERT INTO MyTable VALUES (6, 0)
INSERT INTO MyTable VALUES (9, 0)
INSERT INTO MyTable VALUES (43, 9)
INSERT INTO MyTable VALUES (53, 8)
INSERT INTO MyTable VALUES (56, 0)
INSERT INTO MyTable VALUES (81, 0)
INSERT INTO MyTable VALUES (163, 1)
INSERT INTO MyTable VALUES (9716, 0)

需要查询新增Y列，规则如下：

• 若X不等于0，Y直接取当前X的值
• 若X等于0，Y取按Pos升序排序的上一个X不等于0的对应值，不存在则为NULL

实现逻辑 #

核心是用Gaps and Islands的分组思路，给每一组「非0X+后续跟随的所有0X」划分到同一个分组，再取每组内的非0X值作为该组所有行的Y值：

1. 用窗口累加函数生成分组ID：按Pos升序排序，每遇到一个X<>0的行分组ID就+1，同组内所有后续0值行共享同一个分组ID
2. 按分组ID取组内的非0X值（直接取组内X的最大值即可，因为每组只有1个非0X值）作为Y值

最终查询SQL #

WITH GroupedData AS (
    SELECT 
        Pos,
        X,
        -- 生成分组ID：遇到非0X时分组号+1
        SUM(CASE WHEN X <> 0 THEN 1 ELSE 0 END) OVER (ORDER BY Pos ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupId
    FROM MyTable
)
SELECT 
    Pos,
    X,
    -- 每组内取最大的X即为需要的Y值
    MAX(X) OVER (PARTITION BY GroupId) AS Y
FROM GroupedData
ORDER BY Pos

查询结果验证 #

执行上述SQL后输出结果和预期完全一致：

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