解锁 SQL Server 2022的时间序列数据功能
SQL Server2022在处理时间序列数据时，SQL Server 提供了一些优化和功能，比如 DATE_BUCKET 函数、窗口函数（如 FIRST_VALUE 和 LAST_VALUE）以及其他时间日期函数，以便更高效地处理时间序列数据。
GENERATE_SERIES函数
SQL Server 2022 引入了一个新的函数 GENERATE_SERIES，它用于生成一个整数序列。
这个函数非常有用，可以在查询中生成一系列连续的数值，而无需创建临时表或循环。
GENERATE_SERIES ( start, stop [, step ] ) start：序列的起始值。 stop：序列的终止值。 step：每次递增或递减的步长（可选）。如果省略，默认为1。
使用场景包括快速生成一系列数据用于测试或填充表或者结合日期函数生成一系列日期值。
示例
生成的结果集将包含 20 行，每行显示从 '2019-02-28 13:45:23' 开始，按分钟递增的时间。
SELECTDATEADD(MINUTE, s.value, '2019-02-28 13:45:23')AS[Interval]FROMGENERATE_SERIES(0,20,1)ASs;
对于每一个 s.value，DATEADD 函数将基准日期时间增加相应的分钟数。
DATE_BUCKET函数
SQL Server 2022 引入了一个新的函数 DATE_BUCKET，用于将日期时间值按指定的时间间隔分组（即分桶）。
这个函数在时间序列分析、数据聚合和分段分析等场景中非常有用。
DATE_BUCKET ( bucket_width, datepart, startdate, date )bucket_width：时间间隔的大小，可以是整数。datepart：时间间隔的类型，例如year,month,day, hour, minute, second 等。startdate：起始日期，用于定义时间间隔的起点。date：需要分组的日期时间值。
使用 DATE_BUCKET 函数时，指定的时间间隔单位（如 YEAR、QUARTER、MONTH、WEEK 等）以及起始日期（origin）决定了日期时间值被分配到哪个存储桶。这种方式有助于理解时间间隔的计算是如何基于起始日期来进行的。
示例
DECLARE@dateDATETIME='2019-09-28 13:45:23';DECLARE@originDATETIME='2019-01-28 13:45:23';SELECT'Now'AS[BucketName],@dateAS[DateBucketValue]UNIONALLSELECT'Year', DATE_BUCKET (YEAR,1,@date,@origin)UNIONALLSELECT'Quarter', DATE_BUCKET (QUARTER, 1,@date,@origin)UNIONALLSELECT'Month', DATE_BUCKET (MONTH,1,@date,@origin)UNIONALLSELECT'Week', DATE_BUCKET (WEEK, 1,@date,@origin)--假如日期时间值如下:Now:2019-09-2813:45:23--按年分组:DATE_BUCKET(YEAR,1,@date,@origin)从2019-01-2813:45:23开始的年度存储桶，2019-09-28落入2019-01-28至2020-01-28的存储桶中。结果：2019-01-2813:45:23--按季度分组:DATE_BUCKET(QUARTER,1,@date,@origin)从2019-01-2813:45:23开始的季度存储桶，每个季度3个月。2019-09-28落入第三个季度存储桶（即从2019-07-2813:45:23到2019-10-2813:45:23）。结果：2019-07-2813:45:23--按月分组:DATE_BUCKET(MONTH,1,@date,@origin)从2019-01-2813:45:23开始的月度存储桶，每个月一个存储桶。2019-09-28落入第九个存储桶（即从2019-09-2813:45:23到2019-10-2813:45:23）。结果：2019-09-2813:45:23--按周分组:DATE_BUCKET(WEEK,1,@date,@origin)从2019-01-2813:45:23开始的每周存储桶。2019-09-28落入从2019-09-2313:45:23到2019-09-3013:45:23的存储桶。结果：2019-09-2313:45:23
SELECT'Now'AS[BucketName],GETDATE()AS[BucketDate]UNIONALLSELECT'5 Minute Buckets', DATE_BUCKET (MINUTE, 5,GETDATE())UNIONALLSELECT'Quarter Hour', DATE_BUCKET (MINUTE, 15,GETDATE());Now:BucketName: NowBucketDate: 2024-07-2616:14:11.030这是当前时间，即GETDATE() 返回的系统当前时间。5 Minute Buckets:BucketName: 5 Minute BucketsBucketDate: 2024-07-2616:10:00.000这是将当前时间按5分钟间隔进行分组的结果。DATE_BUCKET(MINUTE,5,GETDATE()) 返回当前时间所在的 5分钟区间的起始时间。在这个例子中，16:14:11落在16:10:00到16:15:00之间，因此返回16:10:00。Quarter Hour:BucketName: Quarter HourBucketDate: 2024-07-2616:00:00.000这是将当前时间按15分钟间隔进行分组的结果。DATE_BUCKET(MINUTE,15,GETDATE()) 返回当前时间所在的 15分钟区间的起始时间。在这个例子中，16:14:11落在16:00:00到16:15:00之间，因此返回16:00:00。
更多实际场景示例
按自定义起始日期分组
假设我们有一系列事件时间 EventTime，希望从'2023-01-01'日期开始，每周进行分组统计事件数量。
--创建表 Events：USE[testdb]GOCREATETABLE Events ( EventID INTPRIMARYKEY, EventTime DATETIME);INSERTINTO Events (EventID, EventTime) VALUES(1,'2023-01-02 14:30:00'),(2,'2023-01-08 09:15:00'),(3,'2023-01-09 17:45:00'),(4,'2023-01-15 12:00:00'),(5,'2023-01-16 08:00:00'),(6,'2023-01-22 19:30:00'),(7,'2023-01-29 11:00:00');--从'2023-01-01'起始日期开始，每周进行分组统计事件数量。DECLARE@originDATETIME='2023-01-01';SELECTDATE_BUCKET(WEEK,1, EventTime, @origin)ASWeekStart,COUNT(*)ASEventCountFROMEventsGROUPBYDATE_BUCKET(WEEK,1, EventTime, @origin)ORDERBYWeekStart;
按自定义时间间隔分组
假设我们有一个传感器数据表 SensorReadings
USE[testdb]GOCREATETABLE SensorReadings ( ReadingID INTPRIMARYKEY,--唯一标识ReadingTimeDATETIME,--读数的时间ValueFLOAT--读数的值);INSERTINTO SensorReadings (ReadingID, ReadingTime, Value) VALUES(1,'2023-07-26 10:03:00',23.5),(2,'2023-07-26 10:05:00',24.1),(3,'2023-07-26 10:09:00',22.8),(4,'2023-07-26 10:15:00',25.0),(5,'2023-07-26 10:20:00',23.9),(6,'2023-07-26 10:27:00',24.3),(7,'2023-07-26 10:29:00',24.5);--我们希望按 10 分钟的间隔将数据分组，并计算每个间隔的平均读数值。SELECTDATE_BUCKET(MINUTE,10, ReadingTime) ASBucketStartTime,ROUND(AVG(Value),4)ASAverageValueFROMSensorReadingsGROUPBYDATE_BUCKET(MINUTE,10, ReadingTime)ORDERBYBucketStartTime;
如果是传统方法需要使用公用表表达式CTE才能完成这个需求
--查询：按 10 分钟间隔分组并计算平均值WITHTimeIntervalsAS(SELECT ReadingID, ReadingTime, Value, --将分钟数归约到最近的 10 分钟的整数倍， 从2010年到现在有多少个10分钟区间DATEADD(MINUTE, (DATEDIFF(MINUTE,'2000-01-01', ReadingTime) /10)*10,'2010-01-01')ASBucketStartTimeFROMSensorReadings)SELECTBucketStartTime,ROUND(AVG(Value),4)ASAverageValueFROMTimeIntervalsGROUPBYBucketStartTimeORDERBYBucketStartTime;
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）的方式，从而增强了这些函数的功能。
基本语法
FIRST_VALUE返回指定窗口或分区中按指定顺序的第一个值。FIRST_VALUE ( [scalar_expression])OVER([partition_by_clause]order_by_clause[rows_range_clause] )LAST_VALUE返回指定窗口或分区中按指定顺序的最后一个值。LAST_VALUE ( [scalar_expression])OVER([partition_by_clause]order_by_clause[rows_range_clause] )新功能：IGNORE NULLS 和 RESPECT NULLSIGNORE NULLS: 忽略分区或窗口中的 NULL 值。RESPECT NULLS: 默认行为，包含分区或窗口中的 NULL值。
示例
假设我们有一个表 MachineTelemetry，包含以下数据：
CREATETABLE MachineTelemetry ( [timestamp]DATETIME, SensorReading FLOAT);INSERTINTO MachineTelemetry ([timestamp], SensorReading) VALUES('2023-07-26 10:00:00',23.5),('2023-07-26 10:00:15',24.1),('2023-07-26 10:00:30',NULL),('2023-07-26 10:00:45',25.0),('2023-07-26 10:01:00',NULL),('2023-07-26 10:01:15',23.9),('2023-07-26 10:01:30',NULL),('2023-07-26 10:01:45',24.3);
默认行为（包含 NULL 值）
--使用 FIRST_VALUE 和 LAST_VALUE 进行差距分析--默认行为（包含 NULL 值）SELECT[timestamp], DATE_BUCKET(MINUTE, 1,[timestamp])AS[timestamp_bucket], SensorReading, FIRST_VALUE(SensorReading) OVER ( PARTITION BYDATE_BUCKET(MINUTE,1,[timestamp])ORDERBY[timestamp]ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS[Default_FIRST_VALUE (RESPECT NULLS)], LAST_VALUE(SensorReading) OVER ( PARTITION BYDATE_BUCKET(MINUTE,1,[timestamp])ORDERBY[timestamp]ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS[Default_LAST_VALUE (RESPECT NULLS)]FROMMachineTelemetryORDERBY[timestamp];
忽略 NULL 值
--忽略 NULL 值SELECT[timestamp], DATE_BUCKET(MINUTE, 1,[timestamp])AS[timestamp_bucket], SensorReading, FIRST_VALUE(SensorReading) IGNORE NULLS OVER ( PARTITION BYDATE_BUCKET(MINUTE,1,[timestamp])ORDERBY[timestamp]ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS[First_Reading (IGNORE NULLS)], LAST_VALUE(SensorReading) IGNORE NULLS OVER ( PARTITION BYDATE_BUCKET(MINUTE,1,[timestamp])ORDERBY[timestamp]ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS[Last_Reading (IGNORE NULLS)]FROMMachineTelemetryORDERBY[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/
本文版权归作者所有，未经作者同意不得转载。