OpenText Analytics Database 旧Vertica

技術情報サイト

Analytics Database

テーブル内に格納されているデータと異なる時間間隔でデータを抽出する(Time Series Analytics)

公開日：
更新日：
基本操作
#関数

はじめに

Vertica拡張SQLのTime Series Analyticsを使用すると、テーブル内に格納されているデータと異なる時間間隔でデータを抽出することができます。
例えば、株価が変動する度に書き込まれる取引明細テーブル内で、銘柄ごとの価格変動を比較したい場合、銘柄毎で同じ時間間隔にデータを補完してデータを抽出することができます。

コマンド構文

TIMESERIES slice‑time AS '取得したい時間間隔' 
OVER ( PARTITION BY '比較対象の列' ORDER BY '並び替え対象の列')

　slice-time:TIMESERIES関数によって生成される日付データの列(列名は任意)

またTIMESIRES関数では、開始値を指定するTS_FIRST_VALUEおよびTS_LAST_VALUEがTIMESERIES集約関数として用意されています。

TS_FIRST_VALUE ('補完対象列',['CONST' | 'LINEAR'] )

TS_LAST_VALUE  ('補完対象列',['CONST' | 'LINEAR'] ) 

　CONST(デフォルト):直前のデータと同じ値を補完
　LINER:TIMESERIES関数内で指定した時間間隔に基いて線形値を補完

利用例

株価が変動する度に書き込まれる取引明細のテーブル内で、銘柄ごとの価格変動を比較したい場合の例を記載します。
本例では、ABC株とXYZ株のデータ整形を行い、同じ時間軸で比較できるようにします。

取引明細テーブル

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秒間隔でデータを直前のデータと同じデータを補間します。

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秒間隔でデータを線形値で補間しています。

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 Functions
https://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　　本記事を公開

この記事をシェアする