少しはまったので備忘のためのメモです。
InfluxDBのGROUP BY TIMEの機能とは
InfluxDBは時系列DBなので、時間軸に沿ってデータを加工して取り出すとか得意です。
中でもGROUP BY TIME句は便利で、時系列に並んでいるデータを一定の範囲ごとに集計した結果を返してくれます。
例えば、InfluxDBに入っている生のデータとしては、1秒ごとにデータが入っているけど、データを取り出す時には、5分ごとの平均値を取り出すといったことがクエリで簡単にかけます。
例として、以下のような生データが入っている場合を考えます。
samplesテーブルからwhere句で2019/06/28 19:33:00 ～ 2019/06/28 20:03:00の30分間のデータを抽出しています。
> SELECT time,value FROM samples WHERE time > 1561717980000000000 AND time <= 1561717980000000000 + 30m;
name: samples
time value
---- -----
1561718281313633024 0.401
1561718281313664000 0.4248
1561718281313692160 0.4357
1561718281313721856 0.4717
1561718281313790208 0.4929
1561718281313818880 0.4801
1561718281313848064 0.4851
1561718634471390976 0.4756
1561718634471417856 0.4597
1561718634471447040 0.4523
1561718634471474944 0.4578
1561718634471503872 0.4532
1561718634471569152 0.4418
1561719076376609024 0.4379
1561719076376642816 0.4277
1561719076376674816 0.4436
1561719076376701952 0.4841
1561719076376730880 0.5677
1561719076376761856 0.5972
1561719431517808896 0.5972
1561719431517876992 0.5887
1561719431517908224 0.5682
1561719431517944064 0.556
1561719431517975040 0.5479
1561719431518006016 0.5321
1561719431518038016 0.5128
1561719775640753152 0.5022
1561719775640784896 0.5098
1561719775640825088 0.506
1561719775640857088 0.501
1561719775641024000 0.486
1561719775641057024 0.481
単純に上記の行数をカウントすると32件であることがわかります。
> SELECT count(*) FROM samples WHERE time > 1561717980000000000 AND time <= 1561717980000000000 + 30m;
name: samples
time count_value
---- -----------
1561717980000000001 32
これに対して、5分ごとの平均値を取り出す場合のクエリは以下のように指定します。
> SELECT MEAN(value) FROM samples WHERE time > 1561717980000000000 AND time <= 1561717980000000000 + 30m GROUP BY TIME(5m);
name: samples
time mean
---- ----
1561717800000000000
1561718100000000000 0.4559000000000001
1561718400000000000 0.4567333333333334
1561718700000000000
1561719000000000000 0.4930333333333334
1561719300000000000 0.5575571428571429
1561719600000000000 0.49766666666666665
こんな感じで5分ごとの平均値が各行として取り出せます。
上記例のように5分ごとにまとめ上げた時に該当の時間幅に値が存在しない部分も出てきます。
このような場合の取扱方もfill()を使って指定ができます。
fill()句
fillは空の行の値に対してどう埋めるかを指定できます。
- 任意の数値: 指定の数値で埋める
- linear: 空の行の前後の値をもとに線形で穴埋め
- none: 空の行はないものとして出力から除去する
- null: 空の行は空のままで出力する(default)
- previous: 直前の値で埋める
# fill(0)の場合
time mean
---- ----
1561717800000000000 0
1561718100000000000 0.4559000000000001
1561718400000000000 0.4567333333333334
1561718700000000000 0
1561719000000000000 0.4930333333333334
1561719300000000000 0.5575571428571429
1561719600000000000 0.49766666666666665
# fill(linear)の場合 最初の行はlinearを計算するための直前の値が存在しないのでnullのままです
time mean
---- ----
1561717800000000000
1561718100000000000 0.4559000000000001
1561718400000000000 0.4567333333333334
1561718700000000000 0.4748833333333334
1561719000000000000 0.4930333333333334
1561719300000000000 0.5575571428571429
1561719600000000000 0.49766666666666665
# fill(none)の場合
time mean
---- ----
1561718100000000000 0.4559000000000001
1561718400000000000 0.4567333333333334
1561719000000000000 0.4930333333333334
1561719300000000000 0.5575571428571429
1561719600000000000 0.49766666666666665
# fill(previous)の場合
time mean
---- ----
1561717800000000000
1561718100000000000 0.4559000000000001
1561718400000000000 0.4567333333333334
1561718700000000000 0.4567333333333334
1561719000000000000 0.4930333333333334
1561719300000000000 0.5575571428571429
1561719600000000000 0.49766666666666665
GROUP BY TIMEとWHEREの組み合わせ時の注意
非常に便利なGROUP BY TIMEですが、利用時に少し注意が必要です。
集計をする際のバケットの区切り方の仕様に癖があります。
先程の例での出力結果を改めて見てみます。
unixtimeだとわかりづらいのでtime列の横に日本時間の記述も入れてみます。
name: samples
time 日本時間 mean
---- ---- ----
1561717800000000000 2019/06/28 19:30:00
1561718100000000000 2019/06/28 19:35:00 0.4559000000000001
1561718400000000000 2019/06/28 19:40:00 0.4567333333333334
1561718700000000000 2019/06/28 19:45:00
1561719000000000000 2019/06/28 19:50:00 0.4930333333333334
1561719300000000000 2019/06/28 19:55:00 0.5575571428571429
1561719600000000000 2019/06/28 20:00:00 0.49766666666666665
これを見ると、最初のバケットが19:30以上、19:35未満の平均値、最後のバケットが20:00以上、20:05未満の平均値となっています。
期待としては、19:33～20:03の時間の幅を5分ずつに区切って値を算出されるかと思っていたのですが、区切り位置がすこしずれてまとめられています。
この現象ですが、InfluxDBの内部仕様として、エポック秒の0を起点として、GROUP BY TIMEで指定した時間で均等に区切った時の時間幅でバケットを作成するようです。
なので、中途半端な時間でWHERE句でフィルタしていると予期しない区切り方で丸められるというわけです。
対応策
この対応策としては、GROUP BY TIME()の第2引数でoffset値が指定できます。offset値で指定した時間分だけバケットの区切りをスライドして丸めてくれます。
例えば先程の例だと、3分ずらすと良いので、GROUP BY TIME(5m, 3m)といった具合です。
結果はこんな感じになります。
time 日本時間 mean
---- ---- ----
1561717980000000000 2019/06/28 19:33:00
1561718280000000000 2019/06/28 19:38:00 0.4559000000000001
1561718580000000000 2019/06/28 19:43:00 0.4567333333333334
1561718880000000000 2019/06/28 19:48:00 0.4930333333333334
1561719180000000000 2019/06/28 19:53:00 0.5575571428571429
1561719480000000000 2019/06/28 19:58:00 0.49766666666666665
1561719780000000000 2019/06/28 20:03:00
>
期待通りになります。
少しはまったのでメモでした。
その他にも、GROUP BY TIMEの仕様として注意したほうが良いのは、fillで埋めることはできるのですが、そもそもWHEREでフィルタした時間内に1件も値が入っていないとバケットすら作られません。
まとめ
InfluxDBのGROUP BY TIMEの使い方とハマりどころのメモでした。
少し複雑ですが、うまく活用できると便利な機能です。
この例だと、xxxから直近1日前までのデータを対象に5分毎にvalueの値の平均を取り出すクエリです。