🕌

InfluxDB集計クエリサンプル

2020/10/04に公開

わかったこと

時間軸関連の集計クエリが書きやすい

事前知識

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