解锁 SQL Server 2022的时间序列数据功能-HQY 一个和谐有爱的空间

11

2024
09
10:54:07

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

SQL Server2022在处理时间序列数据时，SQL Server 提供了一些优化和功能，比如 DATE_BUCKET 函数、窗口函数（如 FIRST_VALUE 和 LAST_VALUE）以及其他时间日期函数，以便更高效地处理时间序列数据。

GENERATE_SERIES函数

SQL Server 2022 引入了一个新的函数 GENERATE_SERIES，它用于生成一个整数序列。
这个函数非常有用，可以在查询中生成一系列连续的数值，而无需创建临时表或循环。

mportant; font-size: 12px !important;">

GENERATE_SERIES ( start, stop [, step ] )
start：序列的起始值。
stop：序列的终止值。
step：每次递增或递减的步长（可选）。如果省略，默认为1。

使用场景包括快速生成一系列数据用于测试或填充表或者结合日期函数生成一系列日期值。

示例

生成的结果集将包含 20 行，每行显示从 '2019-02-28 13:45:23' 开始，按分钟递增的时间。

SELECT DATEADD(MINUTE, s.value, '2019-02-28 13:45:23') AS [Interval]FROM GENERATE_SERIES(0, 20, 1) AS s;

对于每一个 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 @date DATETIME = '2019-09-28 13:45:23';DECLARE @origin DATETIME = '2019-01-28 13:45:23'; 
SELECT 'Now' AS [BucketName], @date AS [DateBucketValue]UNION ALLSELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)UNION ALLSELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)UNION ALLSELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)UNION ALLSELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin)


--假如日期时间值如下:
Now: 2019-09-28 13:45:23--按年分组:
DATE_BUCKET(YEAR, 1, @date, @origin)
从 2019-01-28 13:45:23 开始的年度存储桶，2019-09-28 落入 2019-01-28 至 2020-01-28 的存储桶中。
结果：2019-01-28 13:45:23--按季度分组:
DATE_BUCKET(QUARTER, 1, @date, @origin)
从 2019-01-28 13:45:23 开始的季度存储桶，每个季度 3 个月。2019-09-28 落入第三个季度存储桶（即从 2019-07-28 13:45:23 到 2019-10-28 13:45:23）。
结果：2019-07-28 13:45:23--按月分组:
DATE_BUCKET(MONTH, 1, @date, @origin)
从 2019-01-28 13:45:23 开始的月度存储桶，每个月一个存储桶。2019-09-28 落入第九个存储桶（即从 2019-09-28 13:45:23 到 2019-10-28 13:45:23）。
结果：2019-09-28 13:45:23--按周分组:
DATE_BUCKET(WEEK, 1, @date, @origin)
从 2019-01-28 13:45:23 开始的每周存储桶。2019-09-28 落入从 2019-09-23 13:45:23 到 2019-09-30 13:45:23 的存储桶。
结果：2019-09-23 13:45:23

SELECT 'Now' AS [BucketName], GETDATE() AS [BucketDate]UNION ALLSELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())UNION ALLSELECT 'Quarter Hour', DATE_BUCKET (MINUTE, 15, GETDATE());



Now:
BucketName: Now
BucketDate: 2024-07-26 16:14:11.030这是当前时间，即 GETDATE() 返回的系统当前时间。5 Minute Buckets:
BucketName: 5 Minute Buckets
BucketDate: 2024-07-26 16: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 Hour
BucketDate: 2024-07-26 16: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]GOCREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventTime DATETIME);INSERT INTO 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 @origin DATETIME = '2023-01-01';SELECT
    DATE_BUCKET(WEEK, 1, EventTime, @origin) AS WeekStart,    COUNT(*) AS EventCountFROM
    EventsGROUP BY
    DATE_BUCKET(WEEK, 1, EventTime, @origin)ORDER BY
    WeekStart;

按自定义时间间隔分组
假设我们有一个传感器数据表 SensorReadings

    ,  
    ReadingTime ,  
    Value     SensorReadings (ReadingID, ReadingTime, Value) , , , , , , , , , , , , , , , ReadingTime) ((Value),)

如果是传统方法需要使用公用表表达式CTE才能完成这个需求

    --查询：按 10 分钟间隔分组并计算平均值WITH TimeIntervals AS (    SELECT
        ReadingID,
        ReadingTime,
        Value,        --将分钟数归约到最近的 10 分钟的整数倍， 从2010年到现在有多少个10分钟区间
        DATEADD(MINUTE, (DATEDIFF(MINUTE, '2000-01-01', ReadingTime) / 10) * 10, '2010-01-01') AS BucketStartTime  
    FROM
        SensorReadings
)SELECT
    BucketStartTime,    ROUND(AVG(Value), 4) AS AverageValueFROM
    TimeIntervalsGROUP BY
    BucketStartTimeORDER BY
    BucketStartTime;

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 NULLS
IGNORE NULLS: 忽略分区或窗口中的 NULL 值。
RESPECT NULLS: 默认行为，包含分区或窗口中的 NULL 值。

示例

假设我们有一个表 MachineTelemetry，包含以下数据：

CREATE TABLE MachineTelemetry (    [timestamp] DATETIME,
    SensorReading FLOAT);INSERT INTO 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 BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
        ORDER BY [timestamp] 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS [Default_FIRST_VALUE (RESPECT NULLS)],
    LAST_VALUE(SensorReading) OVER (
        PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
        ORDER BY [timestamp] 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS [Default_LAST_VALUE (RESPECT NULLS)]FROM MachineTelemetryORDER BY [timestamp];

忽略 NULL 值

--忽略 NULL 值SELECT 
    [timestamp],
    DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket],
    SensorReading,
    FIRST_VALUE(SensorReading) IGNORE NULLS OVER (
        PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
        ORDER BY [timestamp] 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS [First_Reading (IGNORE NULLS)],
    LAST_VALUE(SensorReading) IGNORE NULLS OVER (
        PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
        ORDER BY [timestamp] 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS [Last_Reading (IGNORE NULLS)]FROM MachineTelemetryORDER BY [timestamp];