CnosDB：深入瞭解時序資料處理函式
stats_agg
用途
stats_agg 函式適用於需要對時序資料進行統計分析的場景，例如計算相關係數和協方差。
並且還可以分別計算每個維度的常見統計資料，例如平均值和標準差。
stats_agg 提供了與sum，count，corr，covar_pop，stddev 和 stddev_pop 等聚合函式相同的功能，
適用於一條SQL中，包含多個分析函式的場景。
函式用法
stats_agg(y, x)
引數型別：
- y: double 型別
- x: double 型別
返回結果：
{
n: bigint, -- count
sx: double, -- sum(x)- sum(x)
sx2: double, -- sum((x-sx/n)^2) (sum of squares)
sx3: double, -- sum((x-sx/n)^3)
sx4: double, -- sum((x-sx/n)^4)
sy: double, -- sum(y)
sy2: double, -- sum((y-sy/n)^2) (sum of squares)
sy3: double, -- sum((y-sy/n)^3)
sy4: double, -- sum((y-sy/n)^4)
sxy: double, -- sum((x-sx/n)*(y-sy/n)) (sum of products)
}
示例
想象一下，你有一張表記錄了某個測試專案的執行情況，其中 x 表示時間，y 表示對應的測試資料。你想要了解這些測試資料的分佈規律，而 stats_agg 就是你的得力助手。
create table if not exists test_stats(x bigint, y bigint); insert into test_stats(time, x, y) values (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4), (5, 1, 5), (6, 2, 1), (7, 2, 2), (8, 2, 3), (9, 2, 4), (10, 2, 5);
select stats_agg(y, x) from test_stats;
在這個例子中，stats_agg 會告訴你這些測試資料的數量（n）、時間的總和（sx）、時間的平方和（sx2），以及測試資料的總和（sy）、測試資料的平方和（sy2）等等。
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stats_agg(test_stats.y,test_stats.x) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {n: 10, sx: 15.0, sx2: 2.5, sx3: -2.7755575615628914e-16, sx4: 0.6249999999999999, sy: 30.0, sy2: 20.0, sy3: -1.7763568394002505e-15, sy4: 68.0, sxy: 0.0} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
注意事項
輸入引數都不為 NULL 時才納入聚合
gauge_agg
用途
適用於監控系統、物聯網應用和金融領域，提供時間段內的資料快照。
函式用法
gauge_agg(time, value)
引數型別：
- time: Timestamp 型別
- value: DOUBLE 型別
返回結果：
{
first: { ts: Timestamp, value: Double },
second: { ts: Timestamp, value: Double },
penultimate: { ts: Timestamp, value: Double },
last: { ts: Timestamp, value: Double },
num_elements: Bigint Unsingned
}
示例
這個 SQL 查詢使用了 gauge_agg 函式，目的是對空氣質量資料按月進行聚合，提供每個月內的壓力資料快照。
SELECT gauge_agg(time, pressure) FROM air GROUP BY date_trunc('month', time);
這條查詢的目的是從名為 "air" 的資料表中選取時間 (time) 和壓力 (pressure) 列的資料，並按照每月的時間戳 (date_trunc('month', time)) 進行分組。最後，對每個月的資料應用 gauge_agg 函式。
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gauge_agg(air.time,air.pressure) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {first: {ts: 2023-03-01T00:00:00, val: 54.0}, second: {ts: 2023-03-01T00:00:00, val: 59.0}, penultimate: {ts: 2023-03-14T16:00:00, val: 55.0}, last: {ts: 2023-03-14T16:00:00, val: 80.0}, num_elements: 13122} |
| {first: {ts: 2023-01-14T16:00:00, val: 63.0}, second: {ts: 2023-01-14T16:00:00, val: 68.0}, penultimate: {ts: 2023-01-31T23:57:00, val: 77.0}, last: {ts: 2023-01-31T23:57:00, val: 54.0}, num_elements: 16640} |
| {first: {ts: 2023-02-01T00:00:00, val: 54.0}, second: {ts: 2023-02-01T00:00:00, val: 60.0}, penultimate: {ts: 2023-02-28T23:57:00, val: 74.0}, last: {ts: 2023-02-28T23:57:00, val: 59.0}, num_elements: 26880} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
上面的資料，每行結果表示一個月的資料聚合結果。
first 到 last 分別表示每個月內的第一、第二、倒數第二和最後一條資料的時間戳和壓力數值。
num_elements 表示該月的資料點數量。
compact_state_agg/state_agg
用途
用於跟蹤狀態的持續時間，適用於裝置健康監控、事件統計等。
函式用法
compact_state_agg(time, state) state_agg(time, state)
引數型別：
- time: Timestamp 型別
- state: 任意型別
返回結果：
{
state_duration: [
{ state: Any, interval: Duration },
...
],
state_periods: [
{
state: Any,
periods: [
{ start_time: Timestamp, end_time: Timestamp },
...
]
},
...
]
}
示例
在一個名為 states 的表中插入了一些包含時間戳和狀態資訊的資料。然後，透過使用兩個不同的 SQL 查詢來呼叫 compact_state_agg 和 state_agg 函式。
CREATE TABLE IF NOT EXISTS states(state STRING);
INSERT INTO states VALUES
('2020-01-01 10:00:00', 'starting'),
('2020-01-01 10:30:00', 'running'),
('2020-01-03 16:00:00', 'error'),
('2020-01-03 18:30:00', 'starting'),
('2020-01-03 19:30:00', 'running'),
('2020-01-05 12:00:00', 'stopping');
compact_state_agg 示例
SELECT compact_state_agg(time, state) FROM states;
返回的結果是一個 JSON 格式的物件，其中包含了不同狀態的總持續時間 (state_duration)。在這個示例中，它顯示了每個狀態的總持續時間，但沒有提供每個狀態的具體切換週期。
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| compact_state_agg(states.time,states.state) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {state_duration: [{state: running, duration: 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs}, {state: error, duration: 0 years 0 mons 0 days 2 hours 30 mins 0.000000000 secs}, {state: starting, duration: 0 years 0 mons 0 days 1 hours 30 mins 0.000000000 secs}, {state: stopping, duration: 0 years 0 mons 0 days 0 hours 0 mins 0.000000000 secs}], state_periods: []} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
state_agg 示例
SELECT state_agg(time, state) FROM states;
返回的結果同樣是一個 JSON 格式的物件，其中包含了每個狀態的總持續時間 (state_duration) 和每個狀態的切換週期 (state_periods)。在這個示例中，state_periods 列表顯示了每個狀態的具體切換週期，包括開始時間和結束時間。
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| state_agg(states.time,states.state) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {state_duration: [{state: starting, duration: 0 years 0 mons 0 days 1 hours 30 mins 0.000000000 secs}, {state: running, duration: 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs}, {state: stopping, duration: 0 years 0 mons 0 days 0 hours 0 mins 0.000000000 secs}, {state: error, duration: 0 years 0 mons 0 days 2 hours 30 mins 0.000000000 secs}], state_periods: [{state: starting, periods: [{start_time: 2020-01-01T10:00:00, end_time: 2020-01-01T10:30:00}, {start_time: 2020-01-03T18:30:00, end_time: 2020-01-03T19:30:00}]}, {state: error, periods: [{start_time: 2020-01-03T16:00:00, end_time: 2020-01-03T18:30:00}]}, {state: running, periods: [{start_time: 2020-01-01T10:30:00, end_time: 2020-01-03T16:00:00}, {start_time: 2020-01-03T19:30:00, end_time: 2020-01-05T12:00:00}]}]} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
注意事項
- state_agg 會記錄每個狀態的每個持續時間段，當狀態基數較大或持續時間段較分散的情況下，會使用較大記憶體，謹慎使用。
- compact_state_agg 返回的資料中不包含 state_periods欄位。會將每個狀態的所有持續時間段彙總成總時間，佔用記憶體相對較少，兩個函式可以根據具體場景來抉擇使用。
所以，這兩個函式的區別在於 compact_state_agg 更關注總體持續時間，而 state_agg 提供了更詳細的狀態切換週期資訊。
candlestick_agg
用途
適用於金融領域，生成開盤價、收盤價、最高價的資料。
函式用法
candlestick_agg(time, price, volume)
引數型別：
- time: Timestamp 型別
- price: Double 型別
- volume: Double 型別
返回型別：
{
open: { ts: Timestamp, val: Double },
close: { ts: Timestamp, val: Double },
high: { ts: Timestamp, val: Double },
low: { ts: Timestamp, val: Double },
volume: { vol: Double, vwap: Double },
}
示例
那麼現在建立一個示例資料集。
CREATE TABLE IF NOT EXISTS tick(price bigint ,volume bigint);
INSERT tick(time, price, volume)
VALUES
('1999-12-31 00:00:00.000', 111, 444),
('1999-12-31 00:00:00.005', 222, 444),
('1999-12-31 00:00:00.010', 333, 222),
('1999-12-31 00:00:10.015', 444, 111),
('1999-12-31 00:00:10.020', 222, 555),
('1999-12-31 00:10:00.025', 333, 555),
('1999-12-31 00:10:00.030', 444, 333),
('1999-12-31 01:00:00.035', 555, 222);
下列語句查詢了關於時間序列資料的蠟燭圖形式的統計資訊，包括開盤價、收盤價
SELECT candlestick_agg(time, price, volume) FROM tick;
返回結果：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| candlestick_agg(tick.time,tick.price,tick.volume) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {open: {ts: 1999-12-31T00:00:00, val: 111.0}, close: {ts: 1999-12-31T01:00:00.035, val: 555.0}, low: {ts: 1999-12-31T00:00:00, val: 111.0}, high: {ts: 1999-12-31T01:00:00.035, val: 555.0}, volume: {vol: 2886.0, vwap: 850149.0}} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
asap_smooth
用途
ASAP_SMOOTH 是一種降取樣函式，適用於視覺化和資料探索、預測和模型訓練等場景。
函式用法
asap_smooth(time, value, resolution order by time)
引數型別：
- time: Timestamp 型別
- value: Double 型別
- resolution: Bigint 型別
返回結果：
{
time: [ Timestamp, ... ],
value: [ Double, ... ],
resolution: Int Unsigned,
}
示例
使用 asap_smooth 函式，對空氣質量資料按月進行聚合，並在壓力 (pressure) 資料上應用 ASAP (As Soon As Possible) 平滑，以得到平滑的時間序列資料。
SELECT asap_smooth(time, pressure, 10) FROM air GROUP BY date_trunc('month', time);
返回結果：
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| asap_smooth(air.time,air.pressure,Int64(10)) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {time: [2023-01-14T16:00:00, 2023-01-16T14:13:00, 2023-01-18T12:26:00, 2023-01-20T10:39:00, 2023-01-22T08:52:00, 2023-01-24T07:05:00, 2023-01-26T05:18:00, 2023-01-28T03:31:00, 2023-01-30T01:44:00, 2023-01-31T23:57:00], value: [64.79507211538461, 65.31009615384616, 65.25841346153847, 64.8485576923077, 65.09495192307692, 65.02524038461539, 64.8389423076923, 65.2421875, 65.02103365384616, 65.1141826923077], resolution: 10} |
| {time: [2023-02-01T00:00:00, 2023-02-04T02:39:40, 2023-02-07T05:19:20, 2023-02-10T07:59:00, 2023-02-13T10:38:40, 2023-02-16T13:18:20, 2023-02-19T15:58:00, 2023-02-22T18:37:40, 2023-02-25T21:17:20, 2023-02-28T23:57:00], value: [65.20982142857143, 64.90625, 64.94828869047619, 64.97916666666667, 64.88504464285714, 64.8203125, 64.64434523809524, 64.88802083333333, 65.0, 64.76004464285714], resolution: 10} |
| {time: [2023-03-01T00:00:00, 2023-03-02T12:26:40, 2023-03-04T00:53:20, 2023-03-05T13:20:00, 2023-03-07T01:46:40, 2023-03-08T14:13:20, 2023-03-10T02:40:00, 2023-03-11T15:06:40, 2023-03-13T03:33:20, 2023-03-14T16:00:00], value: [65.29115853658537, 64.58307926829268, 64.7530487804878, 64.76753048780488, 65.14405487804878, 65.4298780487805, 65.1920731707317, 65.10365853658537, 64.86356707317073, 64.83841463414635], resolution: 10} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
注意事項
- 此函式對時間順序敏感，在使用時需要新增 `order by time`。
- 返回值中的 time 和 value 為陣列型別。
- 此函式在執行時會一次性處理所有資料，所以儘可能避免讀入大量資料導致過多的記憶體開銷。
CnosDB，這可不是普通的資料庫，是你資料冒險的好夥伴，助你在資料的海洋中航行無憂！
來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/70026635/viewspace-3001286/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 深入淺出：瞭解時序資料庫 InfluxDB資料庫UX
- 大型資料集處理之道：深入瞭解Hadoop及MapReduce原理Hadoop
- Flink處理函式實戰之一：深入瞭解ProcessFunction的狀態(Flink-1.10)函式Function
- CnosDB 狂歡！全面支援 Helm 部署，輕鬆搞定你的分散式時序資料庫！分散式資料庫
- 深入瞭解下replace函式函式
- MySQL-日期和資料處理函式MySql函式
- 興盛優選：時序資料如何高效處理？
- 深入瞭解 Vue 響應式原理(資料攔截)Vue
- 一個能讓你瞭解所有函式呼叫順序以及函式耗時的Android庫 （更新版）函式Android
- 11. 使用MySQL之使用資料處理函式MySql函式
- 深入瞭解Redis資料結構Redis資料結構
- 直播預告 | 時序資料處理的雲端利器：TDengine Cloud 詳解與演示Cloud
- 深入瞭解如何對IPA包進行有效的混淆處理
- 陣列處理函式陣列函式
- fork函式的學習--深入瞭解計算機系統函式計算機
- 使用自定義函式實現資料編解碼、格式處理與業務告警函式
- 時序資料庫 TDengine 簽約華銳技術，助力行情資料處理分析資料庫
- Pandas 基礎 (6) - 用 replace () 函式處理不合理資料函式
- 深入瞭解Redis底層資料結構Redis資料結構
- 華為雲GaussDB(for Influx)：讓智慧電網時序資料處理更高效UX
- Dart函式、類和運算子-處理資訊Dart函式
- 對函式的初步瞭解函式
- 計算機程式的思維邏輯 (93) – 函式式資料處理 (下)計算機函式
- 時間序列資料的處理
- Flink處理函式實戰之四：視窗處理函式
- Sanic 處理函式修飾器函式
- mongoDB中聚合函式java處理MongoDB函式Java
- JavaScript 註冊事件處理函式JavaScript事件函式
- echarts 繫結事件處理函式Echarts事件函式
- C語言之字串處理函式C語言字串函式
- 深入瞭解 Adobe Camera Raw，打造專業級Raw格式影像處理體驗！
- async函式，瞭解一下函式
- 深入瞭解PBKDF2：密碼學中的關鍵推導函式密碼學函式
- TDengine 簽約杭州雲潤，助力某大型水錶企業時序資料處理
- Apache Flink 如何正確處理實時計算場景中的亂序資料Apache
- sql(oracle)資料處理實用總結開窗函式(over partition)使用SQLOracle函式
- 實時資料處理：Kafka 和 FlinkKafka
- 影像資料不足時的處理方法