はじめに
Vertica拡張SQLのTime Series Analyticsを使用すると、テーブル内に格納されているデータと異なる時間間隔でデータを抽出することができます。例えば、株価が変動する度に書き込まれる取引明細テーブル内で、銘柄ごとの価格変動を比較したい場合、銘柄毎で同じ時間間隔にデータを補完してデータを抽出することができます。
コマンド構文
|
1 2 3 4 |
TIMESERIES slice‑time AS '取得したい時間間隔' OVER ( PARTITION BY '比較対象の列' ORDER BY '並び替え対象の列') slice-time:TIMESERIES関数によって生成される日付データの列(列名は任意) |
またTIMESIRES関数では、開始値を指定するTS_FIRST_VALUEおよびTS_LAST_VALUEがTIMESERIES集約関数として用意されています。
|
1 2 3 4 5 6 |
TS_FIRST_VALUE ('補完対象列',['CONST' | 'LINEAR'] ) TS_LAST_VALUE ('補完対象列',['CONST' | 'LINEAR'] ) CONST(デフォルト):直前のデータと同じ値を補完 LINER:TIMESERIES関数内で指定した時間間隔に基いて線形値を補完 |
利用例
株価が変動する度に書き込まれる取引明細のテーブル内で、銘柄ごとの価格変動を比較したい場合の例を記載します。本例では、ABC株とXYZ株のデータ整形を行い、同じ時間軸で比較できるようにします。
取引明細テーブル
|
1 2 3 4 5 6 7 8 |
dbadmin=> select * from 取引明細 order by 銘柄; 日付 | 銘柄 | 入札価格 ---------------------+------+---------- 2019-01-01 03:00:00 | ABC | 7 2019-01-01 03:00:06 | ABC | 6.7 2019-01-01 03:00:00 | XYZ | 10 2019-01-01 03:00:05 | XYZ | 10.5 (4 rows) |
定数補間
TS_FIRST_VALUE関数により入札価格列の最も古い時間(03:00:00)の値(ABC:7、XYZ:10)から、2秒間隔でデータを直前のデータと同じデータを補間します。|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
dbadmin=> SELECT slice_time, 銘柄, TS_FIRST_VALUE(入札価格) AS first_bid dbadmin-> FROM 取引明細 dbadmin-> TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY 銘柄 ORDER BY 日付); slice_time | 銘柄 | first_bid ---------------------+------+----------- 2019-01-01 03:00:00 | ABC | 7 2019-01-01 03:00:02 | ABC | 7 2019-01-01 03:00:04 | ABC | 7 2019-01-01 03:00:06 | ABC | 6.7 2019-01-01 03:00:00 | XYZ | 10 2019-01-01 03:00:02 | XYZ | 10 2019-01-01 03:00:04 | XYZ | 10 (7 rows) |
線形補間
TS_LAST_VALUE関数により入札価格列の最新(ABC:03:00:06、XYZ:03:00:05)の値(ABC:6.7、XYZ:10.5)から、2秒間隔でデータを線形値で補間しています。|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
dbadmin=> SELECT slice_time, 銘柄, TS_LAST_VALUE(入札価格, 'linear') AS last_bid dbadmin-> FROM 取引明細 dbadmin-> TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY 銘柄 ORDER BY 日付); slice_time | 銘柄 | last_bid ---------------------+------+---------- 2019-01-01 03:00:00 | ABC | 6.9 2019-01-01 03:00:02 | ABC | 6.8 2019-01-01 03:00:04 | ABC | 6.7 2019-01-01 03:00:06 | ABC | ★線形値を取得できるデータポイントが無いため空白 2019-01-01 03:00:00 | XYZ | 10.2 2019-01-01 03:00:02 | XYZ | 10.4 2019-01-01 03:00:04 | XYZ | ★線形値を取得できるデータポイントが無いため空白 (7 rows) |
参考情報
Timeseries Functionshttps://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/TimeSeries/TimeseriesFunctions.htm
検証バージョンについて
この記事の内容はVertica 9.2で確認しています。更新履歴
2019/04/16 検証バージョンを9.2に変更2017/2/3 本記事を公開
