解鎖 SQL Server 2022的時間序列數據功能

来源:https://www.cnblogs.com/lyhabc/p/18326254/unlock-sql-server-time-series-data-features
-Advertisement-
Play Games

解鎖 SQL Server 2022的時間序列數據功能 SQL Server2022在處理時間序列數據時，SQL Server 提供了一些優化和功能，比如 DATE_BUCKET 函數、視窗函數（如 FIRST_VALUE 和 LAST_VALUE）以及其他時間日期函數，以便更高效地處理時間序列數據 ...


解鎖 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' 開始，按分鐘遞增的時間。

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 ALL
SELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)
UNION ALL
SELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)
UNION ALL
SELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)
UNION ALL
SELECT '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 ALL
SELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())
UNION ALL
SELECT '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]
GO

CREATE 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 EventCount
FROM
    Events
GROUP BY
    DATE_BUCKET(WEEK, 1, EventTime, @origin)
ORDER BY
    WeekStart;

 

 

 


按自定義時間間隔分組
假設我們有一個感測器數據表 SensorReadings

USE [testdb]
GO


CREATE TABLE SensorReadings (
    ReadingID INT PRIMARY KEY,  --唯一標識
    ReadingTime DATETIME,  --讀數的時間
    Value FLOAT  --讀數的值
);

INSERT INTO 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 分鐘的間隔將數據分組，並計算每個間隔的平均讀數值。
SELECT
    DATE_BUCKET(MINUTE, 10, ReadingTime) AS BucketStartTime,
    ROUND(AVG(Value),4) AS AverageValue
FROM
    SensorReadings
GROUP BY
    DATE_BUCKET(MINUTE, 10, ReadingTime)
ORDER BY
    BucketStartTime;

 

 

如果是傳統方法需要使用公用表表達式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 AverageValue
FROM
    TimeIntervals
GROUP BY
    BucketStartTime
ORDER 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 MachineTelemetry
ORDER 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 MachineTelemetry
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/

 

 
本文版權歸作者所有，未經作者同意不得轉載。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SQLServer 事務複製在發佈端（publication）和訂閱端（subscription）對應的資料庫還原之後複製異常問題的解決
    書接上文，在一個正常的事務複製環境中，如果發生了資料庫還原，事務複製會不會出問題，出問題之後又如何恢復，如果在不刪除訂閱發佈重建的情況下，如何在現有基礎上修複事務複製的異常，這個問題可以分為兩部分看： 1，如果publisher資料庫發生了還原操作，事務複製會出現什麼異常，該如何恢復？ 2，如果是s ...
  • React 的 KeepAlive 實戰指南：深度解析組件緩存機制
    Vue 的 Keep-Alive 組件是用於緩存組件的高階組件，可以有效地提高應用性能。它能夠使組件在切換時仍能保留原有的狀態信息，並且有專門的生命周期方便去做額外的處理。該組件在很多場景非常有用，比如： · tabs 緩存頁面 · 分步表單 · 路由緩存 在 Vue 中，通過 KeepAlive ...
  • 我用Awesome-Graphs看論文：解讀Pregel
    上次向大家分享了論文圖譜項目Awesome-Graphs的介紹文章，這次我們就拿圖計算系統的奠基文章Pregel開篇，沿著論文圖譜的主線，對圖計算系統的論文內容進行解讀。 ...
  • mongo副本集開啟安全認證
    1. 事物的四大特性 事務是邏輯上的一組操作，要麼都執行，要麼都不執行 原子性（Atomicity）：事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用； 一致性（Consistency）：執行事務前後，數據保持一致，例如轉賬業務中，無論事務是否成功，轉賬者和收款人 ...
  • GreatSQL 的刷新鎖
    GreatSQL 的刷新鎖 前言 因為運維小伙伴執行dump備份命令，導致資料庫卡住，很多會話都在waiting for table flush，基於這一故障，我對GreatSQL的刷新鎖進行了研究。感興趣的小伙伴請隨我一探究竟吧。 刷新鎖的癥狀 刷新鎖問題的主要癥狀是資料庫會進入嘎然而止的狀態，所 ...
  • ElasticSearch第4篇（億級中文數據量 ElasticSearch與Sphinx建索引速度、查詢速度、併發性能、實測對比）
    經過實測：1.09億的數據量進行中文檢索。ElasticSearch單機的檢索性能在0.005~5.6秒之間，此檢索速度可滿足95%的業務場景（註意：每條ES文檔平均65個漢字，數據源取自幾千本小說，大部分文檔在15~300個漢字之間，不然字數太多索引太大電腦存不下）。 前置文章 由於本文章的前置操 ...
  • 如何讓SQL Server像MySQL一樣擁有慢查詢日誌（Slow Query Log慢日誌）
    如何讓SQL Server像MySQL一樣擁有慢查詢日誌（Slow Query Log慢日誌） SQL Server一直以來被人詬病的一個問題是缺少了像MySQL的慢日誌功能，程式員和運維無法知道資料庫過去歷史的慢查詢語句。 因為SQLServer預設是不捕獲過去歷史的長時間阻塞的SQL語句，導致大 ...
  • ElasticSearch第3篇 大數據處理3大問題（“10000條”問題解決方案、hits total值統計總數不精確解決方案、大數據深度分頁性能問題3種優化方案）
    “10000條”問題（個人稱謂） 癥狀： 在數據量不大的情況下，可能還會使用from + size的傳統分頁方式，但是數量受限，只能取前10000條的數據。 緣由：ES限值10000條，是ES團隊挑選一個不大不小的數作為閾值，為了避免深度分頁的策略。 調整：max_result_window 用於控 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類