はじめに
センサーデータやログデータといったのストリームデータ(時系列データ)を使用してリアルタイムに監視等を行う際、データ1件1件を監視するのではなく、以下のように一定の時間間隔(ウィンドウ)の集計結果を利用したい場合があります。
例）
・センサーログ：2秒間毎のセンサーの値を合計し、20以上の場合は機械に異常が発生していると判断する
・アクセスログ：5秒間毎のにログイン失敗回数を集計し、1000回以上の場合は不正アクセスの疑いがあると判断する
センサーの値を2秒間毎のウィンドウで集計するイメージ図
このような場合、VerticaではTIME_SLICE関数を利用することで一定時間間隔でデータをスライスできます。
TIME_SLICE
コマンド構文
dbadmin=> SELECT TIME_SLICE( expression,slice-length
-> [, 'time-‑unit' [, 'start‑or‑end' ] ] )
-> FROM テーブル名;| パラメータ | 内容 |
|---|---|
| expression | スライス処理をする対象のTIMESTAMP列 |
| slice-length | スライスする時間の間隔 |
| time-‑unit | slice-lengthの時間の単位 ・HOUR ・MINUTE ・SECOND (デフォルト) ・MILLISECOND ・MICROSECOND |
| 'start‑or‑end' | slice-lengthの時間の先頭(START)か終端(END)どちらを起点とするか ・START (デフォルト) ・END |
利用例
センサーデータがロードされるsensor_logテーブルを例にします。
ts列がログの発生時間、val列がセンサーの値です。
センサーデータの格納テーブル
dbadmin=> SELECT * FROM sensor_log ;
ts | val
------------------------+-----
2017-09-26 09:00:00.01 | 2
2017-09-26 09:00:00.34 | 5
2017-09-26 09:00:00.78 | 3
2017-09-26 09:00:01.05 | 2
2017-09-26 09:00:01.06 | 2
2017-09-26 09:00:01.41 | 4
2017-09-26 09:00:01.82 | 1
2017-09-26 09:00:02.23 | 1
2017-09-26 09:00:02.36 | 2
2017-09-26 09:00:03.25 | 5
2017-09-26 09:00:03.78 | 7
2017-09-26 09:00:03.98 | 8
2017-09-26 09:00:04.1 | 2
2017-09-26 09:00:04.21 | 2
2017-09-26 09:00:04.33 | 2
2017-09-26 09:00:05.02 | 2
2017-09-26 09:00:05.76 | 2
2017-09-26 09:00:05.92 | 2
(18 rows)TIME_SLICE関数とSUM関数を使用して2秒間隔毎のval列の合計値を求めます。
dbadmin=> SELECT TIME_SLICE(ts,2,'SECOND','START') AS timeslice,
-> SUM(val)
-> FROM sensor_log
-> GROUP BY timeslice
-> ORDER BY timeslice;
timeslice | SUM
---------------------+-----
2017-09-26 09:00:00 | 19
2017-09-26 09:00:02 | 23
2017-09-26 09:00:04 | 12
(3 rows)このように、TIME_SLICE関数を使用することで、ストリームデータ(時系列データ)を簡単に一定の時間間隔に区切って処理することができます。
検証バージョンについて
この記事の内容はVertica 9.1で確認しています。