按5分钟间隔抽取特定ID时序数据的SQL查询性能优化请求

数据背景 #

• 数据集包含800个不同ID的秒级时序数据，总计约48232行
• 样本数据如下：

Id        DateTime              Value
 2999   14/11/2022 9:02:43        0.84
 2999   14/11/2022 9:03:14        0.79
 2999   14/11/2022 10:03:14       67
 2999   14/11/2022 10:03:24       69    
 2999   14/11/2022 10:03:35       66    
 2999   14/11/2022 10:03:36       66
 2999   14/11/2022 11:18:24       0.73
 2999   14/11/2022 11:57:36       0.65
 2999   14/11/2022 12:00:22       0.84
 2999   14/11/2022 18:03:12       0.62
 2999   14/11/2022 19:04:27       0.75
 2999   14/11/2022 19:04:54       0.67
 4511   14/11/2022 6:12:43        0.67
 4511   14/11/2022 6:29:42        0.98
 4511   14/11/2022 6:31:05        0.84
 4511   14/11/2022 6:31:10        0.88
 4511   14/11/2022 16:39:35       66
 4511   14/11/2022 16:39:36       69   
 4511   14/11/2022 16:39:59       70
 4511   14/11/2022 16:40:00       70
 4511   14/11/2022 16:40:55       77
 4511   14/11/2022 16:40:56       78
 4511   14/11/2022 16:41:00       78    
 4511   14/11/2022 16:41:56       72
 4511   14/11/2022 16:41:58       72    
 4511   14/11/2022 16:42:00       71
 4511   14/11/2022 16:42:55       67
 4511   14/11/2022 16:42:57       66
 4511   14/11/2022 16:42:57       66
 4511   14/11/2022 16:42:59       66    
 4511   14/11/2022 16:43:01       66
 4511   14/11/2022 16:43:02       66
 4511   14/11/2022 16:43:03       66
 4511   14/11/2022 16:43:04       66
 4511   14/11/2022 17:03:15       67
 4511   14/11/2022 17:03:31       68
 4511   14/11/2022 17:03:33       69
 4511   14/11/2022 17:04:00       67
 4511   14/11/2022 17:04:00       67
 4511   14/11/2022 17:04:01       67     
 4511   14/11/2022 17:04:01       67
 4511   14/11/2022 17:04:01       67
 4511   14/11/2022 17:14:07       66

需求 #

按每5分钟间隔抽取每个ID的对应数据，期望结果示例：

Id        DateTime            Value
2999   14/11/2022 9:02:43       0.84
2999   14/11/2022 10:03:14      67
2999   14/11/2022 11:18:24      0.73
2999   14/11/2022 11:57:36      0.65
2999   14/11/2022 18:03:12      0.62
2999   14/11/2022 19:04:27      0.75
4511   14/11/2022 6:12:43       0.67
4511   14/11/2022 6:29:42       0.98
4511   14/11/2022 16:39:35      66
4511   14/11/2022 17:03:15      67
4511   14/11/2022 17:14:07      66

当前性能低下的查询 #

原查询使用递归CTE实现，执行耗时过长：

WITH rcte AS (
 SELECT curr.*
 FROM [dbo].[Test] AS curr
 WHERE NOT EXISTS (
  SELECT 1
  FROM [dbo].[Test]
  WHERE Id= curr.Id AND DateTime< curr.DateTime
 )
UNION ALL
 SELECT curr.*
 FROM rcte AS prev
 JOIN [dbo].[Test] AS curr ON prev.Id= curr.Id AND curr.DateTime>= DATEADD(MINUTE, 5, 
  prev.DateTime)
  WHERE NOT EXISTS (
   SELECT 1
   FROM [dbo].[Test]
    WHERE Id= curr.Id AND DateTime < curr.DateTime AND DateTime >= DATEADD(MINUTE, 5, 
   prev.DateTime)
   )
   )
  SELECT *, case when rcte.Value > 0.6 and rcte.Value <=1.5 then 'Accel' 
   when rcte.Value >1.6 and rcte.Value<= 2.5 then 'Brk'
  else 'nthng' end as Vt
  FROM rcte
  ORDER BY 1;

优化方案 #

1. 改用窗口函数实现 #

递归CTE会多次迭代查询表，在多ID场景下性能极差。改用窗口函数只需扫描表一次，大幅降低IO开销：

WITH ranked_data AS (
    SELECT 
        Id,
        DateTime,
        Value,
        -- 计算每条记录相对于该ID第一条数据的5分钟分组
        DATEDIFF(MINUTE, FIRST_VALUE(DateTime) OVER (PARTITION BY Id ORDER BY DateTime), DateTime) / 5 AS interval_group,
        -- 每个分组内按时间取第一条记录
        ROW_NUMBER() OVER (PARTITION BY Id, interval_group ORDER BY DateTime) AS rn
    FROM [dbo].[Test]
)
SELECT 
    Id,
    DateTime,
    Value,
    CASE 
        WHEN Value > 0.6 AND Value <= 1.5 THEN 'Accel' 
        WHEN Value > 1.6 AND Value <= 2.5 THEN 'Brk'
        ELSE 'nthng' 
    END AS Vt
FROM ranked_data
WHERE rn = 1
ORDER BY Id, DateTime;

2. 添加索引优化 #

创建复合索引，让窗口函数的分区和排序操作直接利用索引，避免额外排序：

CREATE NONCLUSTERED INDEX IX_Test_Id_DateTime ON [dbo].[Test](Id, DateTime) INCLUDE (Value);

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