はじめに
Vertica拡張SQLのEvent Series Joinsを使用すると、時間間隔が一致しないテーブル同士をJOINして比較することができます。
例えば、株価が変動する度に書き込まれる株式のテーブル同士をJOINして比較する場合、変動するタイミングは株式(テーブル)によって異るため時刻(行数)に差異が出ますが、不足している時刻(行数)を補完することができます。
Event Seriesとは…
時系列のデータ(一般的にはタイムスタンプデータ)を格納するテーブルのことを指します。
コマンド構文
dbadmin=> SELECT 列名 FROM テーブルA FULL OUTER JOIN テーブルB
dbadmin-> ON (テーブル名Aの比較対象列 INTERPOLATE PREVIOUS VALUE テーブル名Aの比較対象列);※INTERPOLATE PREVIOUS VALUEがVertica拡張SQLの記述部分です。
INTERPOLATE PREVIOUS VALUEで比較する列は日付/時刻型の列を指定することが一般的ですが、それ以外の列も指定可能です。
利用例
株価が変動する度に書き込まれる株式のテーブルをFULL OUTER JOINして比較する場合の例を記載します。
本例では、HPQ株の情報が格納されているhticksテーブルとACME株の情報が格納されているaticksをテーブルをJOINします。
HPQ株の情報が格納されているhticksテーブル
dbadmin=> select * from hticks ;
銘柄 | 時間 | 株価
------+----------+-------
HPQ | 12:00:00 | 50.00
HPQ | 12:01:00 | 51.00
HPQ | 12:05:00 | 53.00
HPQ | 12:06:00 | 52.00
(4 rows)ACME株の情報が格納されているaticksテーブル
dbadmin=> select * from aticks ;
銘柄 | 時間 | 株価
------+----------+--------
ACME | 12:00:00 | 340.00
ACME | 12:03:00 | 340.10
ACME | 12:05:00 | 340.20
ACME | 12:05:00 | 333.80
(4 rows)上記の2つのテーブルを標準SQLでJOINした場合は、以下のように一致する時間が存在しない行については片方のテーブルの情報しか出力されません。
dbadmin=> SELECT * FROM hticks h FULL OUTER JOIN aticks a
dbadmin-> ON h.時間 = a.時間;
銘柄 | 時間 | 株価 | 銘柄 | 時間 | 株価
------+----------+-------+------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:01:00 | 51.00 | | | ★ACMEのデータは空欄
HPQ | 12:05:00 | 53.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 53.00 | ACME | 12:05:00 | 340.20
HPQ | 12:06:00 | 52.00 | | | ★ACMEのデータは空欄
| | | ACME | 12:03:00 | 340.10 ★HPQのデータは空欄
(6 rows)Event Series Joinsを使用すると、存在しないデータは、直前のデータが自動で補完されて出力されます。
これにより、何れかの株価が変更した時点の双方の株価を比較することができます。
参考情報
検証バージョンについて
この記事の内容はVertica 9.2で確認しています。
更新履歴
2019/04/22 冒頭に「Time Series」の説明追加。マニュアル情報も追加。検証バージョンを9.2に変更
2017/01/10 本記事を公開