解锁 SQL Server 2022的时间序列数据功能

兜兜零元  论坛元老 | 2024-7-27 06:30:23 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1766|帖子 1766|积分 5298

解锁 SQL Server 2022的时间序列数据功能

  
SQL Server2022在处理时间序列数据时，SQL Server 提供了一些优化和功能，好比 DATE_BUCKET 函数、窗口函数（如 FIRST_VALUE 和 LAST_VALUE）以及其他时间日期函数，以便更高效地处理时间序列数据。
 
 
 GENERATE_SERIES函数
SQL Server 2022 引入了一个新的函数 GENERATE_SERIES，它用于天生一个整数序列。
这个函数非常有效，可以在查询中天生一系列连续的数值，而无需创建临时表或循环。
  1. GENERATE_SERIES ( start, stop [, step ] )
  2. start：序列的起始值。
  3. stop：序列的终止值。
  4. step：每次递增或递减的步长（可选）。如果省略，默认为1。
复制代码
 利用场景包括快速天生一系列数据用于测试或填充表或者联合日期函数天生一系列日期值。
 
示例
天生的结果集将包含 20 行，每行显示从 '2019-02-28 13:45:23' 开始，按分钟递增的时间。
  1. SELECT DATEADD(MINUTE, s.value, '2019-02-28 13:45:23') AS [Interval]
  2. FROM GENERATE_SERIES(0, 20, 1) AS s;
复制代码
对于每一个 s.value，DATEADD 函数将基准日期时间增长相应的分钟数。

DATE_BUCKET函数
SQL Server 2022 引入了一个新的函数 DATE_BUCKET，用于将日期时间值按指定的时间间隔分组（即分桶）。
这个函数在时间序列分析、数据聚合和分段分析等场景中非常有效。
  1. DATE_BUCKET ( bucket_width, datepart, startdate, date )
  2. bucket_width：时间间隔的大小，可以是整数。
  3. datepart：时间间隔的类型，例如 year, month, day, hour, minute, second 等。
  4. startdate：起始日期，用于定义时间间隔的起点。
  5. date：需要分组的日期时间值。
复制代码
利用 DATE_BUCKET 函数时，指定的时间间隔单位（如 YEAR、QUARTER、MONTH、WEEK 等）以及起始日期（origin）决定了日期时间值被分配到哪个存储桶。这种方式有助于理解时间间隔的盘算是如何基于起始日期来进行的。
 
示例
  1. DECLARE @date DATETIME = '2019-09-28 13:45:23';
  2. DECLARE @origin DATETIME = '2019-01-28 13:45:23';
  4. SELECT 'Now' AS [BucketName], @date AS [DateBucketValue]
  5. UNION ALL
  6. SELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)
  7. UNION ALL
  8. SELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)
  9. UNION ALL
  10. SELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)
  11. UNION ALL
  12. SELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin)
  15. --假如日期时间值如下:
  16. Now: 2019-09-28 13:45:23
  19. --按年分组:
  20. DATE_BUCKET(YEAR, 1, @date, @origin)
  21. 从 2019-01-28 13:45:23 开始的年度存储桶，2019-09-28 落入 2019-01-28 至 2020-01-28 的存储桶中。
  22. 结果：2019-01-28 13:45:23
  25. --按季度分组:
  26. DATE_BUCKET(QUARTER, 1, @date, @origin)
  27. 从 2019-01-28 13:45:23 开始的季度存储桶，每个季度 3 个月。
  28. 2019-09-28 落入第三个季度存储桶（即从 2019-07-28 13:45:23 到 2019-10-28 13:45:23）。
  29. 结果：2019-07-28 13:45:23
  33. --按月分组:
  34. DATE_BUCKET(MONTH, 1, @date, @origin)
  35. 从 2019-01-28 13:45:23 开始的月度存储桶，每个月一个存储桶。
  36. 2019-09-28 落入第九个存储桶（即从 2019-09-28 13:45:23 到 2019-10-28 13:45:23）。
  37. 结果：2019-09-28 13:45:23
  41. --按周分组:
  42. DATE_BUCKET(WEEK, 1, @date, @origin)
  43. 从 2019-01-28 13:45:23 开始的每周存储桶。
  44. 2019-09-28 落入从 2019-09-23 13:45:23 到 2019-09-30 13:45:23 的存储桶。
  45. 结果：2019-09-23 13:45:23
复制代码
  1. SELECT 'Now' AS [BucketName], GETDATE() AS [BucketDate]
  2. UNION ALL
  3. SELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())
  4. UNION ALL
  5. SELECT 'Quarter Hour', DATE_BUCKET (MINUTE, 15, GETDATE());
  9. Now:
  10. BucketName: Now
  11. BucketDate: 2024-07-26 16:14:11.030
  12. 这是当前时间，即 GETDATE() 返回的系统当前时间。
  15. 5 Minute Buckets:
  16. BucketName: 5 Minute Buckets
  17. BucketDate: 2024-07-26 16:10:00.000
  18. 这是将当前时间按 5 分钟间隔进行分组的结果。DATE_BUCKET(MINUTE, 5, GETDATE()) 返回当前时间所在的 5 分钟区间的起始时间。在这个例子中，16:14:11 落在 16:10:00 到 16:15:00 之间，因此返回 16:10:00。
  22. Quarter Hour:
  23. BucketName: Quarter Hour
  24. BucketDate: 2024-07-26 16:00:00.000
  25. 这是将当前时间按 15 分钟间隔进行分组的结果。DATE_BUCKET(MINUTE, 15, GETDATE()) 返回当前时间所在的 15 分钟区间的起始时间。在这个例子中，16:14:11 落在 16:00:00 到 16:15:00 之间，因此返回 16:00:00。
复制代码

 
 
更多实际场景示例
 
按自界说起始日期分组
假设我们有一系列变乱时间 EventTime，希望从'2023-01-01'日期开始，每周进行分组统计变乱数量。
  1. --创建表 Events：
  3. USE [testdb]
  4. GO
  6. CREATE TABLE Events (
  7.     EventID INT PRIMARY KEY,
  8.     EventTime DATETIME
  9. );
  11. INSERT INTO Events (EventID, EventTime) VALUES
  12. (1, '2023-01-02 14:30:00'),
  13. (2, '2023-01-08 09:15:00'),
  14. (3, '2023-01-09 17:45:00'),
  15. (4, '2023-01-15 12:00:00'),
  16. (5, '2023-01-16 08:00:00'),
  17. (6, '2023-01-22 19:30:00'),
  18. (7, '2023-01-29 11:00:00');
  19.    
  20.   
  21. --从'2023-01-01'起始日期开始，每周进行分组统计事件数量。
  22. DECLARE @origin DATETIME = '2023-01-01';
  24. SELECT
  25.     DATE_BUCKET(WEEK, 1, EventTime, @origin) AS WeekStart,
  26.     COUNT(*) AS EventCount
  27. FROM
  28.     Events
  29. GROUP BY
  30.     DATE_BUCKET(WEEK, 1, EventTime, @origin)
  31. ORDER BY
  32.     WeekStart;
复制代码
 

 
 

按自界说时间间隔分组
假设我们有一个传感器数据表 SensorReadings
  1. USE [testdb]
  2. GO
  5. CREATE TABLE SensorReadings (
  6.     ReadingID INT PRIMARY KEY,  --唯一标识
  7.     ReadingTime DATETIME,  --读数的时间
  8.     Value FLOAT  --读数的值
  9. );
  11. INSERT INTO SensorReadings (ReadingID, ReadingTime, Value) VALUES
  12. (1, '2023-07-26 10:03:00', 23.5),
  13. (2, '2023-07-26 10:05:00', 24.1),
  14. (3, '2023-07-26 10:09:00', 22.8),
  15. (4, '2023-07-26 10:15:00', 25.0),
  16. (5, '2023-07-26 10:20:00', 23.9),
  17. (6, '2023-07-26 10:27:00', 24.3),
  18. (7, '2023-07-26 10:29:00', 24.5);
  21. --我们希望按 10 分钟的间隔将数据分组，并计算每个间隔的平均读数值。
  22. SELECT
  23.     DATE_BUCKET(MINUTE, 10, ReadingTime) AS BucketStartTime,
  24.     ROUND(AVG(Value),4) AS AverageValue
  25. FROM
  26.     SensorReadings
  27. GROUP BY
  28.     DATE_BUCKET(MINUTE, 10, ReadingTime)
  29. ORDER BY
  30.     BucketStartTime;<br>
复制代码
 
 

假如是传统方法需要利用公用表表达式CTE才能完成这个需求
  1.    
  2. --查询：按 10 分钟间隔分组并计算平均值
  3. WITH TimeIntervals AS (
  4.     SELECT
  5.         ReadingID,
  6.         ReadingTime,
  7.         Value,
  8.         --将分钟数归约到最近的 10 分钟的整数倍， 从2010年到现在有多少个10分钟区间
  9.         DATEADD(MINUTE, (DATEDIFF(MINUTE, '2000-01-01', ReadingTime) / 10) * 10, '2010-01-01') AS BucketStartTime  
  10.     FROM
  11.         SensorReadings
  12. )
  13. SELECT
  14.     BucketStartTime,
  15.     ROUND(AVG(Value), 4) AS AverageValue
  16. FROM
  17.     TimeIntervals
  18. GROUP BY
  19.     BucketStartTime
  20. ORDER BY
  21.     BucketStartTime;
  22.    
复制代码
WITH TimeIntervals AS (...)公共表表达式（CTE）用于盘算每条记载的 BucketStartTime。
DATEDIFF(MINUTE, '2000-01-01', ReadingTime) / 10 盘算 ReadingTime 到基定时间 '2000-01-01' 的分钟数，然后除以 10，得到当前时间点地点的 10 分钟区间的索引。
DATEADD(MINUTE, ..., '2000-01-01') 将该索引转换回详细的时间点，即区间的起始时间。

查询主部分:
选择 BucketStartTime 和相应区间内读数值的均匀值。
利用 GROUP BY 按 BucketStartTime 分组，并盘算每个分组的均匀值。
ORDER BY 用于按照时间次序排列结果。       
       
       
FIRST_VALUE 和 LAST_VALUE 窗口函数
在 之前版本的SQL Server 中，FIRST_VALUE 和 LAST_VALUE 是窗口函数，用于在一个分区或窗口中返回第一个或最后一个值。
SQL Server 2022 引入了新的选项 IGNORE NULLS 和 RESPECT NULLS 来处理空值（NULL）的方式，从而增强了这些函数的功能。
 
基本语法
  1. FIRST_VALUE
  2. 返回指定窗口或分区中按指定顺序的第一个值。
  3. FIRST_VALUE ( [scalar_expression ] )
  4. OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
  8. LAST_VALUE
  9. 返回指定窗口或分区中按指定顺序的最后一个值。
  10. LAST_VALUE ( [scalar_expression ] )
  11. OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
  15. 新功能：IGNORE NULLS 和 RESPECT NULLS
  16. IGNORE NULLS: 忽略分区或窗口中的 NULL 值。
  17. RESPECT NULLS: 默认行为，包含分区或窗口中的 NULL 值。
复制代码
 
示例
 
假设我们有一个表 MachineTelemetry，包含以下数据：
  1. CREATE TABLE MachineTelemetry (
  2.     [timestamp] DATETIME,
  3.     SensorReading FLOAT
  4. );
  7. INSERT INTO MachineTelemetry ([timestamp], SensorReading) VALUES
  8. ('2023-07-26 10:00:00', 23.5),
  9. ('2023-07-26 10:00:15', 24.1),
  10. ('2023-07-26 10:00:30', NULL),
  11. ('2023-07-26 10:00:45', 25.0),
  12. ('2023-07-26 10:01:00', NULL),
  13. ('2023-07-26 10:01:15', 23.9),
  14. ('2023-07-26 10:01:30', NULL),
  15. ('2023-07-26 10:01:45', 24.3);
复制代码
默认行为（包含 NULL 值）
  1. --使用 FIRST_VALUE 和 LAST_VALUE 进行差距分析
  2. --默认行为（包含 NULL 值）
  3. SELECT
  4.     [timestamp],
  5.     DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket],
  6.     SensorReading,
  7.     FIRST_VALUE(SensorReading) OVER (
  8.         PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp])
  9.         ORDER BY [timestamp]
  10.         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  11.     ) AS [Default_FIRST_VALUE (RESPECT NULLS)],
  12.     LAST_VALUE(SensorReading) OVER (
  13.         PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp])
  14.         ORDER BY [timestamp]
  15.         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  16.     ) AS [Default_LAST_VALUE (RESPECT NULLS)]
  17. FROM MachineTelemetry
  18. ORDER BY [timestamp];
复制代码

 
 
 
忽略 NULL 值
  1. --忽略 NULL 值
  2. SELECT
  3.     [timestamp],
  4.     DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket],
  5.     SensorReading,
  6.     FIRST_VALUE(SensorReading) IGNORE NULLS OVER (
  7.         PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp])
  8.         ORDER BY [timestamp]
  9.         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  10.     ) AS [First_Reading (IGNORE NULLS)],
  11.     LAST_VALUE(SensorReading) IGNORE NULLS OVER (
  12.         PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp])
  13.         ORDER BY [timestamp]
  14.         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  15.     ) AS [Last_Reading (IGNORE NULLS)]
  16. FROM MachineTelemetry
  17. ORDER BY [timestamp];
复制代码

 
 总结
实际上，对于时间序列我们一般利用专业的时间序列数据库，例如InfluxDB 。
它利用 TSM（Time-Structured Merge Tree）作为存储引擎称，这是 LSM 树的一种变体，专门优化用于时间序列数据的写入和查询性能。
另外，SQL Server 的时间序列功能是利用行存储引擎（Row Store）作为其存储引擎，这意味着数据是按行进行存储和处理的。
 
在大部分场景下面，假如性能不是要求非常高，其实SQL Server 存储时间序列数据性能是完全足够的，而且额外利用InfluxDB数据库需要维护多一个技术栈，对运维要求更加高。
特别是现在追求数据库一体化的趋势背景下，无论是时间序列数据，向量数据，地理数据，json数据都最幸亏一个数据库里全部满足，减轻运维负担，复用技术栈，减少重复建立本钱是比较好的解决方案。
 
 
参考文章
https://sqlbits.com/sessions/event2024/Time_Series_with_SQL_Server_2022
https://www.microsoft.com/en-us/sql-server/blog/2023/01/12/working-with-time-series-data-in-sql-server-2022-and-azure-sql/
https://www.mssqltips.com/sqlservertip/6232/load-time-series-data-with-sql-server/
 
 
本文版权归作者全部，未经作者同意不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

兜兜零元

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表