SQL Server 2012千万级数据按条件提取连续时间范围优化方案

我最近在使用SQL Server 2012处理一张规模不小的数据表——足足35列、1000万+行，核心需求是从中找出col2值<=2的连续时间范围。先给大家看看示例数据和预期结果：

示例数据 #

Datetime               col1 col2 col3
2018-05-31 0:00        1    2    1
2018-05-31 13:00       2    2    2
2018-05-31 14:30       3    2    1
2018-05-31 15:00       4    3    1
2018-05-31 16:00       4    5    1
2018-05-31 17:00       3    2    2
2018-05-31 17:30       3    2    4
2018-05-31 18:00       2    2    4
2018-05-31 20:00       1    2    6
2018-05-31 21:00       2    2    3
2018-05-31 21:10       2    2    1
2018-05-31 22:00       1    6    3
2018-05-31 22:00       4    5    1
2018-05-31 23:59       4    7    2

预期结果 #

Start Time             End time               Time Diff
2018-05-31 0:00        2018-05-31 14:30       14:30:00
2018-05-31 17:00       2018-05-31 21:10       4:10:00

我最开始用逐行扫描的逻辑实现：先按Datetime排序，然后找到第一个满足条件的起始时间，接着一直扫描到条件不满足时记录结束时间。但面对1000万+的数据量，这个方法速度慢得让人崩溃，所以想找更高效的优化思路和实现方式。

优化方案：使用分组岛（Grouped Islands）+ 窗口函数 #

SQL Server 2012已经支持窗口函数，利用这个特性可以实现基于集合的高效查询，完全避开逐行扫描的低效操作。核心思路是把连续满足col2<=2的行归为同一个分组，然后对每个分组取最小和最大时间即可。

具体SQL实现

WITH MarkedRows AS (
    SELECT
        Datetime,
        col2,
        -- 标记当前行是否符合条件
        CASE WHEN col2 <= 2 THEN 1 ELSE 0 END AS IsValid,
        -- 计算分组ID：累计统计不符合条件的行数，连续符合条件的行将拥有相同的GroupId
        SUM(CASE WHEN col2 <= 2 THEN 0 ELSE 1 END) OVER (ORDER BY Datetime) AS GroupId
    FROM YourTableName -- 替换成你的表名
),
ValidGroups AS (
    SELECT
        GroupId,
        MIN(Datetime) AS [Start Time],
        MAX(Datetime) AS [End time]
    FROM MarkedRows
    WHERE IsValid = 1 -- 只保留符合条件的分组
    GROUP BY GroupId
)
SELECT
    [Start Time],
    [End time],
    -- 将时间差转换为HH:MM:SS格式
    CONVERT(VARCHAR, DATEADD(SECOND, DATEDIFF(SECOND, [Start Time], [End time]), 0), 108) AS [Time Diff]
FROM ValidGroups
ORDER BY [Start Time];

关键优化点

1. 避免逐行扫描：窗口函数是基于集合运算的，数据库引擎会做底层优化，比游标、循环这类逐行操作效率高几个数量级。
2. 索引优化：一定要给Datetime列建立合适的索引，最好是包含col2的非聚集索引，这样查询时可以直接走索引，不用扫描整个表：

   CREATE NONCLUSTERED INDEX IX_YourTableName_Datetime_Col2 
   ON YourTableName (Datetime) 
   INCLUDE (col2);
   

   这个索引可以让数据库直接获取需要的Datetime和col2数据，不需要回表读取其他33列，大幅减少IO开销。
3. 减少数据读取：我们只需要Datetime和col2两列，所以通过索引包含这两列，避免读取整张表的冗余数据。

若需程序端处理的伪代码（不推荐，仅作参考） #

如果一定要在程序端处理（比如业务逻辑更复杂），可以用流式读取的方式避免内存溢出，伪代码如下：

• 从数据库流式读取按Datetime排序的(Datetime, col2)数据（不要一次性加载1000万行到内存）
• 初始化变量：currentStart = null，inValidRange = false
• 遍历每一行：
  • 若col2 <= 2：
    • 如果inValidRange为false，则设置currentStart = 当前行Datetime，inValidRange = true
  • 否则：
    • 如果inValidRange为true，则记录currentStart到上一行的Datetime为一个有效时间范围，然后设置inValidRange = false
• 遍历结束后，若inValidRange仍为true，则记录最后一个有效时间范围

不过还是强烈推荐用SQL窗口函数的方案，数据库引擎的优化能力远强于程序端的逐行处理。

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