SQL Server中高效查询连续值分组最小/最大ID的方案咨询

问题背景 #

有一张数据表，每行的Value字段仅能取0或1，同时包含自增ID列。需要查询每个连续相同Value分组对应的最小ID和最大ID，示例数据及期望输出如下：

示例数据 #

declare @tbl table (ID INT IDENTITY(1,1), Value INT);

insert into @tbl (Value)
values (1), (1), (1), (0), (0), (1), (0), (0), (1), (1), (1), (1);

表内容：

ID  Value
1   1
2   1
3   1
4   0
5   0
6   1
7   0
8   0
9   1
10  1
11  1
12  1

期望输出 #

GroupID Value MinID MaxID
1       1     1     3
2       0     4     5
3       1     6     6
4       0     7     8
5       1     9     12

原查询语句需要遍历表4次，在千万级数据量下效率不足，现提供更优实现方案。

优化方案：单次遍历分组聚合 #

使用窗口函数LAG识别连续分组的边界，通过累加标记生成分组ID，最后一次聚合即可得到结果，仅需遍历表一次：

WITH GroupedData AS (
    SELECT 
        ID,
        Value,
        -- 当当前行Value与上一行不同时，标记为新分组起点，累加生成GroupID
        SUM(CASE WHEN Value = LAG(Value, 1, -1) OVER (ORDER BY ID) THEN 0 ELSE 1 END) 
            OVER (ORDER BY ID) AS GroupID
    FROM @tbl
)
SELECT 
    GroupID,
    Value,
    MIN(ID) AS MinID,
    MAX(ID) AS MaxID
FROM GroupedData
GROUP BY GroupID, Value
ORDER BY GroupID;

原理说明 #

1. 识别分组边界：LAG(Value, 1, -1) OVER (ORDER BY ID)获取当前行的上一行Value（第一行默认-1，确保和实际Value不同），对比当前行Value，不同则标记为1（新分组起点），相同则标记为0。
2. 生成分组ID：用SUM() OVER (ORDER BY ID)对上述标记累加，得到每个行所属的分组ID，连续相同Value的行会被分配同一个GroupID。
3. 聚合计算：按GroupID和Value分组，直接取MIN(ID)和MAX(ID)，得到每个分组的起止ID。

性能优势 #

• 仅需一次全表扫描，相比原方案的四次遍历，在千万级数据量下性能提升显著；
• 窗口函数是SQL Server的原生优化特性，执行计划更高效，若ID列有聚集索引，效率会进一步提升。

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