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(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-3002951/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 深入淺出：瞭解時序資料庫 InfluxDB資料庫UX
- 時間處理函式函式
- 大型資料集處理之道：深入瞭解Hadoop及MapReduce原理Hadoop
- CnosDB 狂歡！全面支援 Helm 部署，輕鬆搞定你的分散式時序資料庫！分散式資料庫
- [MYSQL -11]使用函式處理資料MySql函式
- 深入瞭解下replace函式函式
- 興盛優選：時序資料如何高效處理？
- MySQL-日期和資料處理函式MySql函式
- Flink處理函式實戰之一：深入瞭解ProcessFunction的狀態(Flink-1.10)函式Function
- PHP函式處理函式例項詳解PHP函式
- 深入瞭解 Vue 響應式原理(資料攔截)Vue
- 深入瞭解Oracle資料字典Oracle
- Mysql字元處理函式詳解MySql字元函式
- 直播預告 | 時序資料處理的雲端利器：TDengine Cloud 詳解與演示Cloud
- 深入瞭解Redis資料結構Redis資料結構
- 深入瞭解Oracle資料字典(zt)Oracle
- 11. 使用MySQL之使用資料處理函式MySql函式
- 一個能讓你瞭解所有函式呼叫順序以及函式耗時的Android庫 （更新版）函式Android
- 做大資料必須瞭解的多種處理框架大資料框架
- 磁碟處理函式函式
- 字元處理函式字元函式
- oracle函式大全-字串處理函式Oracle函式字串
- Oracle中利用函式索引處理資料傾斜案例Oracle函式索引
- 深入瞭解如何對IPA包進行有效的混淆處理
- fork函式的學習--深入瞭解計算機系統函式計算機
- 使用自定義函式實現資料編解碼、格式處理與業務告警函式
- 分散式時序資料庫InfluxDB分散式資料庫UX
- 陣列處理函式陣列函式
- SqlServer——字串處理函式SQLServer字串函式
- Oracle函式-->字元處理Oracle函式字元
- 安全字串處理函式字串函式
- 檔案處理函式函式
- 時序資料庫 TDengine 簽約華銳技術，助力行情資料處理分析資料庫
- 深入瞭解Redis底層資料結構Redis資料結構
- 華為雲GaussDB(for Influx)：讓智慧電網時序資料處理更高效UX
- 對函式的初步瞭解函式
- 時間序列資料的處理
- Pandas 基礎 (6) - 用 replace () 函式處理不合理資料函式