🕌
InfluxDB集計クエリサンプル
わかったこと
- 時間軸関連の集計クエリが書きやすい
事前知識
サンプルデータの投入
100 行のランダムな値を CPU 使用率というテイで INSERT
for i in {0..99}
do
CPU_LOAD=`echo "scale=4 ; ${RANDOM}/32767" |bc |sed 's/^\./0./'`
curl -i -XPOST 'http://localhost:8086/write?db=mydb' \
--data-binary 'cpu_load value='${CPU_LOAD}
done
結果確認
curl -G 'http://localhost:8086/query?pretty=true' \
--data-urlencode "db=mydb" \
--data-urlencode "q=SELECT * FROM \"cpu_load\""
{
"results": [
{
"statement_id": 0,
"series": [
{
"name": "cpu_load",
"columns": ["time", "value"],
"values": [
["2020-10-04T08:36:12.671537758Z", 0.9032],
(...)
["2020-10-04T08:36:15.091175859Z", 0.7763]
]
}
]
}
]
}
行数の集計
SELECT COUNT(*) FROM "cpu_load"
結果
> SELECT COUNT(*) FROM "cpu_load"
name: cpu_load
time count_value
---- -----------
0 100
参考
移動平均
SELECT MOVING_AVERAGE(*, 90) FROM "cpu_load"
結果
> SELECT MOVING_AVERAGE(*, 90) FROM "cpu_load"
name: cpu_load
time moving_average_value
---- --------------------
1601800574820206745 0.4943844444444444
1601800574846612083 0.48882555555555557
1601800574870040842 0.49437777777777786
1601800574894859551 0.49993000000000004
1601800574917741214 0.49437222222222227
1601800574942842050 0.4999255555555556
1601800574966557091 0.4943666666666667
1601800574993723979 0.49991888888888897
1601800575020139407 0.49436000000000013
1601800575051769135 0.4945100000000001
1601800575091175859 0.5028344444444446
参考
区分求積
SELECT INTEGRAL(*) FROM "cpu_load"
結果
> SELECT INTEGRAL(*) FROM "cpu_load"
name: cpu_load
time integral_value
---- --------------
0 1.2008889189050498
参考
傾きを求める
SELECT DERIVATIVE(*) FROM "cpu_load"
結果
> SELECT DERIVATIVE(*) FROM "cpu_load"
name: cpu_load
time derivative_value
---- ----------------
1601800572696192715 -17.469103677609333
(...)
1601800575091175859 7.739795878490179
参考
Discussion