SQL查询修复：System_Idle_Process≤10时连续时长统计不重置问题

原SQL意图统计服务器CPU空闲率（System_Idle_Process）≤10%时的连续分钟数，但当前逻辑存在缺陷：当空闲率超过10%时，无法将连续计数重置为0，导致原本不连续的符合条件时段被错误合并。

原查询代码 #

if object_id('tempdb..#tp') is NOT NULL Drop Table #tp
;WITH
  -- 筛选出所有符合空闲率条件的记录
  dates(ServerName,Event_Time) AS (
    SELECT ServerName, Event_Time
    FROM [MetricData].dbo.[ServerCpuUtilization]
    Where [Event_Time] between  dateadd(hour, datediff(hour,'20110101',dateadd(Hour, -(800*24),getdate() ) ),'20110101') and 
                          dateadd(hour, datediff(hour,'20110101',dateadd(Hour, 0,getdate() ) ),'20110101')
    and System_Idle_Process <=10 
  ),
  -- 尝试通过行号与时间的差值生成分组
  groups AS (
    SELECT ServerName,
                    ROW_NUMBER() OVER (Partition by ServerName ORDER BY FORMAT([Event_Time] , 'yyy-MM-dd HH:mm:00')) AS rn,
      dateadd(mi, -ROW_NUMBER() OVER (Partition by ServerName ORDER BY FORMAT([Event_Time] , 'yyy-MM-dd HH:mm:00')),
      FORMAT([Event_Time], 'yyy-MM-dd HH:mm:00')) AS grp,
      Event_Time
    FROM dates
  )
SELECT * into #tp
FROM groups
ORDER BY ServerName,Event_Time, rn

Select * from #tp
SELECT
  COUNT(*) AS consecutiveDates,
  MIN(grp) AS minDate,
  MAX(Event_Time) AS maxDate
FROM #tp
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

示例数据 #

ServerName  Event_time
SomeServer1 4/22/2022 16:16:20:1620
SomeServer1 6/23/2022 21:47:02:472
SomeServer1 6/23/2022 21:48:02:482
SomeServer1 6/23/2022 21:49:03:493
SomeServer1 6/23/2022 21:51:03:513
SomeServer1 6/23/2022 21:52:03:523
SomeServer2 6/23/2022 22:03:05:35
SomeServer2 6/23/2022 22:04:05:45
SomeServer2 6/23/2022 22:05:05:55
SomeServer2 6/23/2022 22:06:06:66
SomeServer2 6/23/2022 22:08:06:86
SomeServer2 6/23/2022 22:11:06:116
SomeServer2 6/23/2022 22:15:06:156
SomeServer2 6/23/2022 22:16:06:166

期望结果 #

Servername  Start                   End                     Minutes
SomeServer1 6/23/2022 21:48:02:482  6/23/2022 21:52:03:523  4
SomeServer2 6/23/2022 22:03:05:35   6/23/2022 22:06:06:66   3
SomeServer2 6/23/2022 22:15:06:156  6/23/2022 22:16:06:166  1

问题根源 #

原查询的dates CTE直接过滤掉了System_Idle_Process>10的记录，导致中间的中断时段被忽略。当计算连续分组时，无法识别这些被跳过的行造成的间隔，从而错误地将不连续的符合条件时段合并。

修正后的SQL #

WITH AllRecords AS (
    -- 保留时间范围内的所有记录，标记是否符合空闲率条件
    SELECT 
        ServerName,
        Event_Time,
        CASE WHEN System_Idle_Process <= 10 THEN 1 ELSE 0 END AS IsValid
    FROM [MetricData].dbo.[ServerCpuUtilization]
    WHERE Event_Time BETWEEN 
        DATEADD(HOUR, DATEDIFF(HOUR, '20110101', DATEADD(HOUR, -(800*24), GETDATE())), '20110101') 
        AND DATEADD(HOUR, DATEDIFF(HOUR, '20110101', GETDATE()), '20110101')
),
GroupedRecords AS (
    SELECT 
        ServerName,
        Event_Time,
        IsValid,
        -- 生成分组标识：当当前行无效或与上一行时间间隔超过1分钟，或上一行无效时，开启新分组
        SUM(CASE WHEN 
            IsValid = 0 
            OR DATEDIFF(MINUTE, LAG(Event_Time) OVER (PARTITION BY ServerName ORDER BY Event_Time), Event_Time) > 1 
            OR LAG(IsValid) OVER (PARTITION BY ServerName ORDER BY Event_Time) = 0
        THEN 1 ELSE 0 END) OVER (PARTITION BY ServerName ORDER BY Event_Time) AS GroupId
    FROM AllRecords
)
-- 只统计有效分组，计算连续分钟数
SELECT 
    ServerName,
    MIN(Event_Time) AS Start,
    MAX(Event_Time) AS End,
    COUNT(*) AS Minutes
FROM GroupedRecords
WHERE IsValid = 1
GROUP BY ServerName, GroupId
HAVING COUNT(*) > 0
ORDER BY Minutes DESC, Start DESC;

修正逻辑说明 #

1. AllRecords CTE：保留时间范围内的所有数据，新增IsValid标记当前记录是否符合空闲率≤10的条件，避免丢失中断时段的信息。
2. GroupedRecords CTE：使用窗口函数生成分组ID：
   • 当当前记录无效时，开启新分组
   • 当前记录有效，但与上一条有效记录的时间间隔超过1分钟时，开启新分组
   • 当前记录有效，但上一条记录无效时，开启新分组
3. 最后通过ServerName和GroupId分组，筛选出有效分组，计算连续时段的起止时间和分钟数。

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