OpenText Analytics Database 旧Vertica

技術情報サイト

Analytics Database

指定列の値が条件を満たす場合にカウントアップする列を追加する(Event Based Windows)

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

はじめに

Vertica拡張SQLのEvent Based Windowsを使用すると、指定列の値が条件を満たす場合にカウントアップする列を追加できます。

CONDITIONAL_CHANGE_EVENT

CONDITIONAL_CHANGE_EVENTを使用すると、指定列の値に変化があった場合に、カウントアップする列を追加できます。
例えば、株価を一定間隔で記録するテーブルがあった場合、株価が変動したタイミングでカウントアップする列を追加できます。

コマンド構文

dbadmin=> SELECT CONDITIONAL_CHANGE_EVENT(対象列名)    
       ->   OVER ([PARTITION BY 対象パーティション列] ORDER BY ソート対象列) AS カウントアップ列名
       -> FROM テーブル名;

利用例

株価を一定間隔で記録するTickstore3テーブルを例にします。
本例では、XYZ株の株価が変動した場合にカウントアップするcce列を追加します。

株価を一定間隔で記録するTickstore3テーブル

dbadmin=> SELECT * FROM Tickstore3 ORDER BY ts;
         ts          | symbol | bid
---------------------+--------+------
 2009-01-01 03:00:00 | XYZ    |   10
 2009-01-01 03:00:03 | XYZ    |   11
 2009-01-01 03:00:06 | XYZ    | 10.5
 2009-01-01 03:00:09 | XYZ    |   11
 2009-01-01 03:00:12 | XYZ    |   11
 2009-01-01 03:00:15 | XYZ    |    9
(6 rows)

CONDITIONAL_CHANGE_EVENTを使用すると、bid列(株価)に変動があった場合のみ、cce列の値がカウントアップされます。

dbadmin=> SELECT ts, symbol, bid, CONDITIONAL_CHANGE_EVENT(bid)
dbadmin->   OVER(ORDER BY ts) AS cce
dbadmin-> FROM Tickstore3;
         ts          | symbol | bid  | cce
---------------------+--------+------+-----
 2009-01-01 03:00:00 | XYZ    |   10 |   0
 2009-01-01 03:00:03 | XYZ    |   11 |   1
 2009-01-01 03:00:06 | XYZ    | 10.5 |   2
 2009-01-01 03:00:09 | XYZ    |   11 |   3
 2009-01-01 03:00:12 | XYZ    |   11 |   3　★株価に変動がない場合はcce列はカウントアップされません
 2009-01-01 03:00:15 | XYZ    |    9 |   4
(6 rows)

CONDITIONAL_TRUE_EVENT

CONDITIONAL_TRUE_EVENTを使用すると、指定列の値が評価式の条件を満たした場合に、カウントアップする列を追加できます。
例えば、株価を一定間隔で記録するテーブルがあった場合、株価がある値を超えていればカウントアップする列を追加できます。

コマンド構文

dbadmin=> SELECT CONDITIONAL_TRUE_EVENT(評価式)    
       ->   OVER (PARTITION BY 対象パーティション列 ORDER BY ソート対象列) AS カウントアップ列名
       -> FROM テーブル名;

利用例

株価を一定間隔で記録するTickstore3テーブルを例にします。
本例では、XYZ株の株価が10.5を超える場合にカウントアップするcte列を追加します。

株価を一定間隔で記録するTickstore3テーブル

dbadmin=> SELECT * FROM Tickstore3 ORDER BY ts;
         ts          | symbol | bid
---------------------+--------+------
 2009-01-01 03:00:00 | XYZ    |   10
 2009-01-01 03:00:03 | XYZ    |   11
 2009-01-01 03:00:06 | XYZ    | 10.5
 2009-01-01 03:00:09 | XYZ    |   11
 2009-01-01 03:00:12 | XYZ    |   11
 2009-01-01 03:00:15 | XYZ    |    9
(6 rows))

CONDITIONAL_TRUE_EVENTを使用すると、bid列(株価)の値が10.5を超える場合のみ、cte列の値がカウントアップされます。

dbadmin=> SELECT ts, symbol, bid, CONDITIONAL_TRUE_EVENT(bid > 10.5)
dbadmin->   OVER(ORDER BY ts) AS cte
dbadmin-> FROM Tickstore3;
         ts          | symbol | bid  | cte
---------------------+--------+------+-----
 2009-01-01 03:00:00 | XYZ    |   10 |   0
 2009-01-01 03:00:03 | XYZ    |   11 |   1
 2009-01-01 03:00:06 | XYZ    | 10.5 |   1　★株価が10.5以下の場合はcte列はカウントアップされません
 2009-01-01 03:00:09 | XYZ    |   11 |   2
 2009-01-01 03:00:12 | XYZ    |   11 |   3
 2009-01-01 03:00:15 | XYZ    |    9 |   3　★株価が10.5以下の場合はcte列はカウントアップされません
(6 rows)

またCONDITIONAL_TRUE_EVENTの評価式でLAG関数を使用することで、n個前の値と比較することも可能です。
以下の例ではbid列(株価)の値が前回の値を下回る場合のみ、cte列の値がカウントアップされます。

dbadmin=> SELECT ts, symbol, bid, CONDITIONAL_TRUE_EVENT(bid < LAG(bid,1))
dbadmin->   OVER(ORDER BY ts) AS cte
dbadmin-> FROM Tickstore3;
         ts          | symbol | bid  | cte
---------------------+--------+------+-----
 2009-01-01 03:00:00 | XYZ    |   10 |   0
 2009-01-01 03:00:03 | XYZ    |   11 |   0　★株価が前回の値を下回る場合はcte列はカウントアップされません
 2009-01-01 03:00:06 | XYZ    | 10.5 |   1
 2009-01-01 03:00:09 | XYZ    |   11 |   1　★株価が前回の値を下回る場合はcte列はカウントアップされません
 2009-01-01 03:00:12 | XYZ    |   11 |   1　★株価が前回の値を下回る場合はcte列はカウントアップされません
 2009-01-01 03:00:15 | XYZ    |    9 |   2
(6 rows)

検証バージョンについて

この記事の内容はVertica 8.1で確認しています。

この記事をシェアする