21 パターン一致用SQL
一連の行でのパターン認識は要望の多い機能でしたが、現在までSQLでは使用できませんでした。多数の回避策がありましたが、これらの回避策は記述が難しく、理解しにくく、実行も非効率的でした。Oracle Database 12cからは、MATCH_RECOGNIZE
句を使用して、効率的に実行するネイティブSQLでこの機能を実現できるようになります。この章では、この実現方法について説明しており、次の項で構成されています。
21.1 データ・ウェアハウスでのパターン一致の概要
SQLでのパターン一致は、MATCH_RECOGNIZE
句を使用して実行されます。MATCH_RECOGNIZE
により、次のタスクが実行可能になります。
-
PARTITION
BY
句およびORDER
BY
句を含むMATCH_RECOGNIZE
句で使用されるデータを、論理的にパーティション化し、順序付けます。 -
MATCH_RECOGNIZE
句のPATTERN
句を使用して、シークする行のパターンを定義します。これらのパターンでは、正規表現構文が使用されます。これは、強力かつ表現力の豊かな機能であり、ユーザーが定義するパターン変数に適用されます。 -
行を
DEFINE
句にある行パターン変数にマップするために必要な論理条件を指定します。 -
MEASURES
句で、SQL問合せの他のパートで使用可能な式であるメジャーを定義します。
単純なパターン一致として、図21-1に示す株価チャートを考えてみましょう。
パターン一致によって様々なタイプの計算を実行するのみでなく、図21-1に示すV字形およびW字形のような価格パターンを識別できます。たとえば、ユーザーが実行する計算には、監視回数や下方傾向または上方傾向の平均値が含まれる場合もあります。
この項では、次の項目について説明します。
21.1.1 パターン一致を使用する理由
複数の行にわたって発生するパターンを認識する能力は、多様な作業で重要です。たとえば、一連のイベント主導によるあらゆる種類のビジネス・プロセス(異常な動作の検出が要求されるセキュリティ・アプリケーション、または価格設定、取引量およびその他の動作のパターンをシークする会計アプリケーションなど)があります。その他の一般的な用途として、詐欺行為検出アプリケーションやセンサー・データ分析があります。この総合的な領域を1つの用語で説明するのは複雑なイベント処理であるため、パターン一致の利用が大いに役立ちます。
ここで、例21-1の問合せを考えてみましょう。この問合せでは、図21-1に示す株価を使用します。この図は、後に続くCREATE
文およびINSERT
を使用してデータベースに取り込むことができます。この問合せでは、株価が底値をうって上昇したすべてのケースが検出されます。これは一般的にV字形と呼ばれます。この問合せを調べる前に、出力を調べてみましょう。3行のみ出力されていますが、これは、一致ごとに1行だけを報告するようにコードが作成されていて、3つの一致が見つかったためです。MATCH_RECOGNIZE
句では、一致ごとに1行を出力するか、一致ごとにすべての行を出力するかを選択できます。この例では、一致ごとに1行という短い出力が採用されています。
例21-1 パターン一致: 一致ごとに1行を出力する単純なV字形
CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER); INSERT INTO Ticker VALUES('ACME', '01-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '02-Apr-11', 17); INSERT INTO Ticker VALUES('ACME', '03-Apr-11', 19); INSERT INTO Ticker VALUES('ACME', '04-Apr-11', 21); INSERT INTO Ticker VALUES('ACME', '05-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '06-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '07-Apr-11', 15); INSERT INTO Ticker VALUES('ACME', '08-Apr-11', 20); INSERT INTO Ticker VALUES('ACME', '09-Apr-11', 24); INSERT INTO Ticker VALUES('ACME', '10-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '11-Apr-11', 19); INSERT INTO Ticker VALUES('ACME', '12-Apr-11', 15); INSERT INTO Ticker VALUES('ACME', '13-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '14-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '15-Apr-11', 14); INSERT INTO Ticker VALUES('ACME', '16-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '17-Apr-11', 14); INSERT INTO Ticker VALUES('ACME', '18-Apr-11', 24); INSERT INTO Ticker VALUES('ACME', '19-Apr-11', 23); INSERT INTO Ticker VALUES('ACME', '20-Apr-11', 22); SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, LAST(DOWN.tstamp) AS bottom_tstamp, LAST(UP.tstamp) AS end_tstamp ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.start_tstamp; SYMBOL START_TST BOTTOM_TS END_TSTAM ---------- --------- --------- --------- ACME 05-APR-11 06-APR-11 10-APR-11 ACME 10-APR-11 12-APR-11 13-APR-11 ACME 14-APR-11 16-APR-11 18-APR-11
この問合せでは、何を行っているのでしょうか。次に、MATCH_RECOGNIZE
句の各行について説明します。
-
PARTITION
BY
はTicker
表のデータを論理グループに分割します。論理グループの各グループには銘柄記号が1つずつ含まれています。 -
ORDER
BY
は、各論理グループのデータをtstamp
によって順序付けます。 -
MEASURES
は、V字形の始まりのタイムスタンプ(start_tstamp
)、V字形の底部のタイムスタンプ(bottom_tstamp
)およびV字形の終わりのタイムスタンプ(end_tstamp
)という3つのメジャーを定義します。メジャーbottom_tstamp
およびend_tstamp
は、LAST()
関数を使用して、取得された値が各パターン一致内のタイムスタンプの最終値であることを確認します。 -
ONE
ROW
PER
MATCH
は、検出されたパターン一致ごとに1行が出力されることを意味します。 -
AFTER
MATCH
SKIP
TO
LAST
UP
は、一致が見つかるたびに、UP
パターン変数の最終行で検索が再開されることを意味します。パターン変数とはMATCH_RECOGNIZE
文で使用される変数であり、DEFINE
句の中で定義されます。 -
PATTERN (STRT DOWN+ UP+)
は、検索中のパターンに3つのパターン変数、STRT
、DOWN
およびUP
があることを示します。DOWN
とUP
の後のプラス記号(+
)は、少なくとも1行をそれぞれにマップする必要があることを示しています。パターンでは、パターンを検索するうえで非常に表現力の豊かな正規表現が定義されます。 -
DEFINE
によって、行パターン変数STRT
、DOWN
およびUP
に行をマップするため満たす必要のある条件が指定されます。STRT
には条件がないため、任意の行をSTRT
にマップできます。なぜ条件のないパターン変数があるのでしょうか。これは、一致をテストするための開始点として使用されます。DOWN
とUP
は両方ともPREV()
関数を利用して、現在行の価格と前の行の価格とを比較します。DOWN
は、行の価格が前の行の価格よりも低い場合に一致します。したがって、V字形の下方(左)部分を定義します。行の価格が前の行の価格よりも高い場合は、その行をUP
にマップできます。
次の2つの図によって、例21-1で返された結果が理解しやすくなります。図21-2は、PATTERN
句で指定された特定のパターン変数にマップされた日付を示しています。パターン変数と日付とのマッピングが使用可能になった後、その情報はMEASURES
句で使用されてメジャー値が計算されます。メジャーの結果を、図21-3に示します。
図21-2では、パターン変数にマップされた日付ごとにラベルを付けています。このマッピングは、PATTERN
句で指定されたパターンとDEFINE
句で指定された論理条件に基づいています。細い縦線は、このパターンに関して見つかった3つの一致の境界を示しています。それぞれの一致では、最初の日付にそれにマップされたSTRT
パターン変数(Startとラベル表示)が表示され、続いてDOWN
パターン変数にマップされた1つ以上の日付、最後にUP
パターン変数にマップされた1つ以上の日付が表示されます。
問合せの中でAFTER
MATCH
SKIP
TO
LAST
UP
を指定したため、2つの隣接する一致で1つの行が共有される場合があります。つまり、1つの日付に2つの変数がマップされることがあります。たとえば、10-Aprilにパターン変数UP
とSTRT
の両方がマップされるとします。その場合、April 10は一致1の終わりであり、一致2の始まりでもあります。
図21-3のラベル表示は、問合せのMEASURES
句で定義されたメジャーのみ、すなわちSTART
(問合せのstart_tstamp
)、BOTTOM
(問合せのbottom_tstamp
)およびEND
(問合せのend_tstamp
)のみを対象としています。図21-2と同様に、細い縦線は、このパターンに関して見つかった3つの一致の境界を示しています。すべての一致には、Start
日付、Bottom
日付およびEnd
日付が定義されています。図21-2と同様に、日付10-Aprilが2つの一致に検出されました。つまり、一致1のEND
メジャー、一致2のSTART
メジャーです。図21-3のラベル表示された日付は、図21-2のパターン変数マッピングに基づいたメジャー定義にどの日付が対応しているかを示しています。
図21-3でラベル表示された日付は、例の出力で前出の9個の日付に対応しています。出力の1行目には一致1の日付が、出力の2行目には一致2の日付が、出力の3行目には一致3の日付がそれぞれ表示されます。
21.1.2 パターン一致におけるデータの処理方法
MATCH_RECOGNIZE
句で、次のステップが実行されます。
-
行パターン入力表が
PARTITION
BY
句に従ってパーティション化されます。各パーティションは、パーティション化列の値と同じ値が設定された入力表の行のセットで構成されています。 -
行パターンの各パーティションは、
ORDER
BY
句に従って順序付けられます。 -
順序付けられた行パターンの各パーティションは、
PATTERN
との一致がないか検索されます。 -
パターン一致は、
ORDER
BY
句で指定した順序で行パターン・パーティションの行を考慮し、最初の行に出現する一致をシークすることによって実行されます。一連の行でのパターン一致は増分プロセスであり、パターンに一致するかどうかを1行ずつ順次調べていきます。この増分処理モデルでは、完全なパターンが認識されるまでは、どのステップにおいても部分的な一致を認識できたにすぎず、将来どんな行が追加されるか、その行にどんな変数がマップされるかは不明です。
最初の行で一致が見つからない場合は、パーティション内の次の行に検索が進み、その行から開始して一致が見つかるかどうか確認されます。
-
一致が見つかると、行パターン一致によって行パターンのメジャー列が計算されます。これは、
MEASURES
句で定義された式に相当します。 -
最初の例で示したように、パターン一致で
ONE
ROW
PER
MATCH
を使用すると、見つかった一致ごとに1行が生成されます。ALL
ROWS
PER
MATCH
を使用した場合、一致した行はすべてパターン一致の出力に含められます。 -
AFTER
MATCH
SKIP
句は、空以外の一致が見つかった後で、行パターン・パーティション内で行パターン一致が再開される場所を判別します。前出の例では、行パターン一致は、一致が最後に見つかった行で再開されています(AFTER
MATCH
SKIP
TO
LAST
UP
)。
21.1.3 パターン一致の特別な機能について
次の機能があります。
-
正規表現は、システムでデータのパターンを検索するための堅牢で、長く確立されてきた方法です。Perl言語の正規表現の機能は、パターン一致ルールの設計上の目標として採用されており、Oracle Database 12c リリース1では、これらのルールのサブセットを実装してパターン一致に対応しています。
-
Oracleの正規表現は、行パターン変数が文字または文字のセットで定義されるのではなく、ブール条件によって定義されている点で、通常の正規表現と異なります。
-
パターン一致では正規表現の表記法を使用してパターンを表現していますが、前の行と行パターン変数とのマッピング方法に依存するようパターン変数を定義することも可能なため、これは実際には、より高度な機能です。
DEFINE
句では、パターン変数を他のパターン変数上に構築できます。 -
行パターン変数の定義とメジャーの定義では、副問合せが使用可能です。
21.2 パターン一致の基本トピック
この項では、次の内容を説明します。
21.2.1 基本的なパターン一致の例
この項では、一致するパターンの基本的な例を示しています。
例21-2 一致ごとに全行を出力する単純なV字形のパターン一致
この例の1行目は、SQL*Plusを使用している場合のフォーマッティングを改善するものです。
column var_match format a4 SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, FINAL LAST(DOWN.tstamp) AS bottom_tstamp, FINAL LAST(UP.tstamp) AS end_tstamp, MATCH_NUMBER() AS match_num, CLASSIFIER() AS var_match ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.match_num, MR.tstamp; SYMBOL TSTAMP START_TST BOTTOM_TS END_TSTAM MATCH_NUM VAR_ PRICE ---------- --------- --------- --------- --------- ---------- ---- ---------- ACME 05-APR-11 05-APR-11 06-APR-11 10-APR-11 1 STRT 25 ACME 06-APR-11 05-APR-11 06-APR-11 10-APR-11 1 DOWN 12 ACME 07-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 15 ACME 08-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 20 ACME 09-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 24 ACME 10-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 25 ACME 10-APR-11 10-APR-11 12-APR-11 13-APR-11 2 STRT 25 ACME 11-APR-11 10-APR-11 12-APR-11 13-APR-11 2 DOWN 19 ACME 12-APR-11 10-APR-11 12-APR-11 13-APR-11 2 DOWN 15 ACME 13-APR-11 10-APR-11 12-APR-11 13-APR-11 2 UP 25 ACME 14-APR-11 14-APR-11 16-APR-11 18-APR-11 3 STRT 25 ACME 15-APR-11 14-APR-11 16-APR-11 18-APR-11 3 DOWN 14 ACME 16-APR-11 14-APR-11 16-APR-11 18-APR-11 3 DOWN 12 ACME 17-APR-11 14-APR-11 16-APR-11 18-APR-11 3 UP 14 ACME 18-APR-11 14-APR-11 16-APR-11 18-APR-11 3 UP 24 15 rows selected.
この問合せでは、何を行っているのでしょうか。これは例21-1の問合せに類似していますが、MEASURES
句の項目、ALL
ROWS
PER
MATCH
の変更、および問合せの終わりにあるORDER
BY
の変更が異なります。MEASURES
句では、次のように追加されています。
-
MATCH_NUMBER()
AS
match_num
この例では一致ごとに複数行が出力されるため、どの行がどの一致のメンバーであるかを把握する必要があります。
MATCH_NUMBER
では、特定の一致の各行に同じ数値が割り当てられます。たとえば、行パターン・パーティションで最初に見つかった一致のすべての行に、match_num
の値として1が割り当てられます。一致の番号付けは、行パターン・パーティションごとに繰り返し1から開始します。 -
CLASSIFIER()
AS
var_match
どの行がどの変数にマップされたかを把握するには、
CLASSIFIER
関数を使用します。この例では、いくつかの行がSTRT
変数にマップされ、別のいくつかの行がDOWN
変数にマップされ、さらに別の行がUP
変数にマップされます。 -
FINAL
LAST()
FINAL
を指定し、bottom_tstamp
に対してLAST()
関数を使用することによって、一致ごとの各行でV字形の底部に対して同じ日付が表示されます。同様に、FINAL
LAST()
をend_tstamp
メジャーに適用すると、一致ごとの各行でV字形の終わりに対して同じ日付が表示されます。この構文が使用されない場合、表示される日付は行ごとの実行値になります。
他の2つの行で、次のように変更されました。
-
ALL
ROWS
PER
MATCH
: 例21-1では一致ごとに1行のみを出力するサマリーが行ONE
ROW
PER
MATCH
を使用して表示されましたが、この例では一致ごとにすべての行を表示するよう要求しています。 -
最終行の
ORDER
BY
: これはMATCH_NUM
を利用するように変更されたため、同じ一致内のすべての行が時系列にまとめられます。
April 10の行は2つのパターン一致に存在するため、2回表示されます。これは、最初の一致の最終日と2番目の一致の初日に相当しています。
例21-3 変数に集計を使用するパターン一致
例21-3では、パターン一致問合せで集計関数が使用されていることを強調しています。
SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES MATCH_NUMBER() AS match_num, CLASSIFIER() AS var_match, FINAL COUNT(UP.tstamp) AS up_days, FINAL COUNT(tstamp) AS total_days, RUNNING COUNT(tstamp) AS cnt_days, price - STRT.price AS price_dif ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.match_num, MR.tstamp; SYMBOL TSTAMP MATCH_NUM VAR_ UP_DAYS TOTAL_DAYS CNT_DAYS PRICE_DIF PRICE ------ --------- --------- ---- ------- ---------- -------- --------- ----- ACME 05-APR-11 1 STRT 4 6 1 0 25 ACME 06-APR-11 1 DOWN 4 6 2 -13 12 ACME 07-APR-11 1 UP 4 6 3 -10 15 ACME 08-APR-11 1 UP 4 6 4 -5 20 ACME 09-APR-11 1 UP 4 6 5 -1 24 ACME 10-APR-11 1 UP 4 6 6 0 25 ACME 10-APR-11 2 STRT 1 4 1 0 25 ACME 11-APR-11 2 DOWN 1 4 2 -6 19 ACME 12-APR-11 2 DOWN 1 4 3 -10 15 ACME 13-APR-11 2 UP 1 4 4 0 25 ACME 14-APR-11 3 STRT 2 5 1 0 25 ACME 15-APR-11 3 DOWN 2 5 2 -11 14 ACME 16-APR-11 3 DOWN 2 5 3 -13 12 ACME 17-APR-11 3 UP 2 5 4 -11 14 ACME 18-APR-11 3 UP 2 5 5 -1 24 15 rows selected.
この問合せでは、何を行っているのでしょうか。これは、集計関数COUNT()
を使用する3つのメジャーを追加して、例21-2上に作成されています。また、式で修飾列と非修飾列を使用できる方法を示すメジャーも追加されています。
-
up_days
メジャー(FINAL
COUNT
を使用)によって、一致ごとにUP
パターン変数にマップされる日数が表示されます。これを確認するには、図21-2に示す一致ごとのUP
のラベル数をカウントします。 -
total_days
メジャー(これもFINAL
COUNT
を使用)によって、非修飾列の使用が導入されます。このメジャーではパターン変数なしでFINAL
count(tstamp)
を指定してtstamp
列を修飾しているため、1つの一致に含まれているすべての行のカウント数が返されます。 -
cnt_days
メジャーにより、RUNNING
キーワードが導入されます。このメジャーは、1つの一致内で行を区別するのに役立つ実行数を示します。tstamp
列を修飾するパターン変数もないため、これは1つの一致のすべての行に適用されます。この場合、これはデフォルトであるため、RUNNING
キーワードを明示的に使用する必要はありません。詳細は、実行中および最終セマンティクスとキーワードの比較を参照してください。 -
price_dif
メジャーは、一致の初日の株価と比較した各日の株価の差異を表示します。式price - STRT.price
では、非修飾列price
が修飾列STRT.price
とともに使用されているケースが見られます。
例21-4 W字形のパターン一致
この例は、W字形を示しています。
SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES MATCH_NUMBER() AS match_num, CLASSIFIER() AS var_match, STRT.tstamp AS start_tstamp, FINAL LAST(UP.tstamp) AS end_tstamp ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+ DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.match_num, MR.tstamp; SYMBOL TSTAMP MATCH_NUM VAR_ START_TST END_TSTAM PRICE ---------- --------- ---------- ---- --------- --------- ---------- ACME 05-APR-11 1 STRT 05-APR-11 13-APR-11 25 ACME 06-APR-11 1 DOWN 05-APR-11 13-APR-11 12 ACME 07-APR-11 1 UP 05-APR-11 13-APR-11 15 ACME 08-APR-11 1 UP 05-APR-11 13-APR-11 20 ACME 09-APR-11 1 UP 05-APR-11 13-APR-11 24 ACME 10-APR-11 1 UP 05-APR-11 13-APR-11 25 ACME 11-APR-11 1 DOWN 05-APR-11 13-APR-11 19 ACME 12-APR-11 1 DOWN 05-APR-11 13-APR-11 15 ACME 13-APR-11 1 UP 05-APR-11 13-APR-11 25
この問合せでは、何を行っているのでしょうか。これは、例21-1に導入されたコンセプトに基づいて作成されており、データ内のV字形ではなくW字形をシークしていきます。問合せ結果は1つのW字形として表示されます。W字形を見つけるために、PATTERN
正規表現を定義している行がDOWN
の後にUP
が続く2回連続したパターン(PATTERN
(STRT DOWN+ UP+ DOWN+ UP+)
)をシークできるように変更されました。このパターン指定は、2つのV字形が間を空けていないW字形のみと一致できることを示しています。たとえば、価格が変動しないフラットな間隔が存在し、その間隔が2つのV字形の間に発生する場合、このパターンとデータの一致は生じません。返されたデータを説明するために、出力はALL
ROWS
PER
MATCH
に設定されます。MEASURES
句のFINAL
LAST(UP.tstamp)
は、UP
に最後にマップされた行のタイムスタンプ値を返します。
21.2.2 パターン一致のタスクとキーワード
この項では、パターン一致における次のタスクとキーワードについて説明します。
PARTITION BY: 行を論理的にグループに分割する
通常は、入力データを分析のために論理グループに分割する必要が生じます。株価の例では、一度に1つの株価のみに適用されるようにパターン一致を分割しています。これは、PARTITION
BY
キーワードを使用して行います。PARTITION
BY
を使用して、行パターン入力表の行を1列ごと、または複数列ごとにパーティション化されるように指定します。一致はパーティション内で見つかり、パーティションの境界を超えることはありません。
PARTITION
BY
が存在しない場合は、行パターン入力表のすべての行で1つの行パターン・パーティションが構成されます。
ORDER BY: パーティション内の行を論理的に順序付ける
入力データを論理パーティションに分割した後で、それぞれのパーティションの中でデータを順序付ける必要が生じます。行の順序付けが行われないと、パターン一致を確認するための信頼できるシーケンスが得られません。ORDER
BY
キーワードを使用して、1つの行パターン・パーティションでの行の順序が指定されます。
[ONE ROW | ALL ROWS] PER MATCH: 一致ごとにサマリーまたは詳細を選択する
一致に関してサマリー・データが必要な場合と、詳細が必要な場合があります。そのような場合は、次のSQLキーワードを使用して対応できます。
-
ONE
ROW
PER
MATCH
一致ごとに1つのサマリー行が生成されます。これはデフォルトです。
-
ALL
ROWS
PER
MATCH
複数行にわたる一致の場合、その一致で行ごとに1つの出力行が生成されます。
この出力については、行パターンの出力で説明しています。
MEASURES: エクスポートの計算をパターン一致から定義する
パターン一致句の使用によって、広範囲の分析に役立つ式を作成できます。これらの句を出力の列として表示するには、MEASURES
句を使用します。MEASURES
句では行パターンのメジャーの列が定義され、その値は特定の一致に関連する式を評価して計算されます。
PATTERN: 一致する行パターンを定義する
PATTERN
句の使用により、一致する必要のあるパターン変数、そのパターン変数の一致順序、および一致する必要のある行数を定義することができます。PATTERN
句では、一致検索に対する正規表現が指定されます。
行パターン一致は、1つの行パターン・パーティション内で連続する行のセットで構成されます。一致の各行はそれぞれ1つのパターン変数にマップされます。行とパターン変数とのマッピングは、PATTERN
句の正規表現に準拠している必要があり、DEFINE
句のすべての条件を満たす必要があります。
DEFINE: プライマリ・パターン変数を定義する
PATTERN
句はパターン変数に依存するため、これらの変数を定義する句を用意する必要があります。これらの変数はDEFINE
句で指定されます。
DEFINE
は必須の句であり、行を特定のパターン変数にマップするために満たしている必要のある条件の指定に使用されます。
パターン変数は定義を必要としません。任意の行を未定義のパターン変数にマップできます。
AFTER MATCH SKIP: 一致が見つかった後で一致プロセスを再開する
問合せで一致が見つかった後に、次の一致を正しい場所で検索する必要があります。前の一致の終わりが次の一致の始まりと重なる一致を検索する必要があるでしょうか。その他のタイプの一致が必要ですか。パターン一致は、再開場所の指定に非常に柔軟に対応できます。AFTER
MATCH
SKIP
句では、空以外の一致が見つかった後の行パターン一致を再開する場所が判別されます。この句のデフォルトはAFTER
MATCH
SKIP
PAST
LAST
ROW
であり、現在の一致の最終行の次の行からパターン一致が再開されます。
MATCH_NUMBER: どの行がどの一致のメンバーであるかを検索する
指定した行パーティションの中で、パターンに対する一致が多数見つかる場合があります。これらのすべての一致をどのように見分ければよいでしょうか。これを実行するには、MATCH_NUMBER
関数を使用します。1つの行パターン・パーティション内の一致には、見つかった順に1から順次番号が付けられます。行パターン・パーティション間では順序付けが継承されないため、一致の番号付は行パターン・パーティションごとに1から繰り返し開始されます。
CLASSIFIER: どのパターン変数がどの行に適用されるかを検索する
現在どのMATCH_NUMBER
が表示されているかを知るだけでなく、特定の行にパターンのどの部分が当てはまるのかを知る必要が生じることもあります。これを実行するには、CLASSIFIER
関数を使用します。行の分類子は、行パターン一致によってその行がマップされているパターン変数になります。CLASSIFIER
関数は、行のマップ先である変数の名前を値として持つ文字列を返します。
21.2.3 パターン一致の構文
パターン一致の構文は次のとおりです。
table_reference ::= {only (query_table_expression) | query_table_expression }[flashback_query_clause] [pivot_clause|unpivot_clause|row_pattern_recognition_clause] [t_alias] row_pattern_recognition_clause ::= MATCH_RECOGNIZE ( [row_pattern_partition_by ] [row_pattern_order_by ] [row_pattern_measures ] [row_pattern_rows_per_match ] [row_pattern_skip_to ] PATTERN (row_pattern) [ row_pattern_subset_clause] DEFINE row_pattern_definition_list ) row_pattern_partition_by ::= PARTITION BY column[, column]... row_pattern_order_by ::= ORDER BY column[, column]... row_pattern_measures ::= MEASURES row_pattern_measure_column[, row_pattern_measure_column]... row_pattern_measure_column ::= expression AS c_alias row_pattern_rows_per_match ::= ONE ROW PER MATCH | ALL ROWS PER MATCH row_pattern_skip_to ::= AFTER MATCH { SKIP TO NEXT ROW | SKIP PAST LAST ROW | SKIP TO FIRST variable_name | SKIP TO LAST variable_name | SKIP TO variable_name} row_pattern ::= row_pattern_term | row_pattern "|" row_pattern_term row_pattern_term ::= row_pattern_factor | row_pattern_term row_pattern_factor row_pattern_factor ::= row_pattern_primary [row_pattern_quantifier] row_pattern_quantifier ::= *[?] |+[?] |?[?] |"{"[unsigned_integer ],[unsigned_integer]"}"[?] |"{"unsigned_integer "}" row_pattern_primary ::= variable_name |$ |^ |([row_pattern]) |"{-" row_pattern"-}" | row_pattern_permute row_pattern_permute ::= PERMUTE (row_pattern [, row_pattern] ...) row_pattern_subset_clause ::= SUBSET row_pattern_subset_item [, row_pattern_subset_item] ... row_pattern_subset_item ::= variable_name = (variable_name[ , variable_name]...) row_pattern_definition_list ::= row_pattern_definition[, row_pattern_definition]... row_pattern_definition ::= variable_name AS condition
パターン一致の中で行われる行パターンの操作の構文は、次のとおりです。
function ::= single_row_function | aggregate_function | analytic_function | object_reference_function | model_function | user_defined_function | OLAP_function | data_cartridge_function | row_pattern_recognition_function row_pattern_recognition_function ::= row_pattern_classifier_function | row_pattern_match_number_function | row_pattern_navigation_function | row_pattern_aggregate_function row_pattern_classifier_function ::= CLASSIFIER( ) row_pattern_match_number_function ::= MATCH_NUMBER( ) row_pattern_navigation_function ::= row_pattern_navigation_logical | row_pattern_navigation_physical | row_pattern_navigation_compound row_pattern_navigation_logical ::= [RUNNING|FINAL] {FIRST|LAST} (expression[,offset]) row_pattern_navigation_physical ::= {PREV|NEXT}(expression[, offset]) row_pattern_navigation_compound ::= {PREV | NEXT} ( [RUNNING| FINAL] {FIRST|LAST} (expression[, offset]) [,offset])
パターン一致句の中でのset関数指定の構文は、次のとおりです。
row_pattern_aggregate_function ::= [RUNNING | FINAL] aggregate_function
21.3 パターン一致の詳細
この項では、パターン一致の構文で説明した項目の詳細に加えて、追加のトピックについても説明しています。内容の一部がやむを得ず複雑になっていることに留意してください。パターン一致には、難解な詳細に特別の注意が求められる場合があります。
21.3.1 PARTITION BY: 行を論理的にグループに分割する
通常は、分析のために入力データを論理グループに分割する必要があります。株価の例では、一度に1つの株価のみに適用されるようにパターン一致が分割されています。これを実行するには、PARTITION
BY
句を使用します。PARTITION
BY
は、入力表の行が1列または複数列のパーティションに区分されるように指定します。一致はパーティション内で見つかり、パーティションの境界を超えることはありません。
PARTITION
BY
が存在しない場合は、行パターン入力表のすべての行で1つの行パターン・パーティションが構成されます。
21.3.2 ORDER BY: パーティション内の行を論理的に順序付ける
ORDER
BY
句は、1つの行パターン・パーティション内での行の順序の指定に使用します。行パターン・パーティションの2つの行の順序がORDER
BY
によって確定されていない場合、MATCH_RECOGNIZE
句の結果は非確定的になります。つまり、問合せが実行されるたびに一貫性のある結果が得られない場合があります。
21.3.3 [ONE ROW | ALL ROWS] PER MATCH: 一致ごとにサマリーまたは詳細を選択する
一致に関してサマリー・データが必要な場合と、詳細が必要な場合があります。これは、次のSQLを使用して処理できます。
-
ONE
ROW
PER
MATCH
一致ごとに1つのサマリー行が生成されます。これはデフォルトです。
-
ALL
ROWS
PER
MATCH
複数行にわたる一致の場合、その一致で行ごとに1つの出力行が生成されます。
この出力については、行パターンの出力で説明しています。
MATCH_RECOGNIZE
句では、行がまったく出力されない一致が見つかることがあります。空の一致の場合、ONE
ROW
PER
MATCH
はサマリー行を返します。つまり、PARTITION
BY
列では空の一致が発生した行から値が取り込まれ、メジャー列は行の空のセットに対して評価されます。
ALL
ROWS
PER
MATCH
には、次の3つのサブオプションがあります。
-
ALL
ROWS
PER
MATCH
SHOW
EMPTY
MATCHES
-
ALL
ROWS
PER
MATCH
OMIT
EMPTY
MATCHES
-
ALL
ROWS
PER
MATCH
WITH
UNMATCHED
ROWS
これらのオプションについては、パターン一致の高度なトピックで説明しています。
21.3.4 MEASURES: 問合せに使用する計算を定義する
MEASURES
句は、パターン出力表の列のリストを定義します。各パターンのメジャー列は、対応するパターン・メジャー式により指定された値の列名を使用して定義されます。
値の式は、パターン変数に関連して定義されます。値の式には、set関数、パターン・ナビゲーション操作、CLASSIFIER()
、MATCH_NUMBER()
、および入力表の任意の列に対する列参照を含めることができます。詳細は、MEASURESおよびDEFINEの式を参照してください。
21.3.5 PATTERN: 一致する行パターンを定義する
PATTERN
キーワードは、パーティション内の行の順序どおりにパターンが認識されるように指定します。パターンの各変数名はブール条件に対応しています。ブール条件は、構文のDEFINE
コンポーネントを使用して後で指定されます。
PATTERN
句は、正規表現の指定に使用します。正規表現の概念と詳細についての説明は、本資料では対象外です。正規表現に習熟していない場合は、他の資料を利用して習熟するようにしてください。
PATTERN
句の正規表現は、丸括弧で囲まれています。PATTERN
では、次の演算子を使用する場合があります。
-
連結
連結は、一致の対象であるパターン内の2つ以上の項目を順序どおりにリストするために使用します。2つの連続する項目の間に演算子符号がない場合、項目は連結されます。例:
PATTERN (A B C)
。 -
数量詞
数量子は、一致で受け入れられる反復数を定義するPOSIX演算子です。POSIX拡張正規表現の構文は、従来のUNIX正規表現の構文に似ています。数量子の選択肢を次に示します。
-
*
: 0以上の反復 -
+
: 1以上の反復 -
?
: 0または1の反復 -
{n}
:n
個の反復(n > 0) -
{n,}
:n
個以上の反復(n >= 0) -
{n,m}
:n
からm
個(これを含む)の間の反復(0 <= n
<= m
、0 < m
) -
{,m}
: 0からm
個(これを含む)の間の反復(m > 0
) -
最短一致数量子: 数量子の後に疑問符を追加して示します
(*?、+?、??、{n,}?、{ n, m }?、{,m}?)
。最短一致の数量子と非最短一致の数量子の相違は、最短一致数量子と強欲な数量子の比較を参照してください。
次に、数量子演算子の使用例を示します。
-
A*
は、A
の0以上の反復に一致します。 -
A{3,6}
は、A
の3から6個の反復に一致します。 -
A{,4}
は、A
の0から4個の反復に一致します。
-
-
代替
代替では、複数の使用可能な正規表現のリスト内の1つの正規表現に一致させます。代替リストは、それぞれの正規表現の間に縦線(|)を入れて作成されます。代替項目は、指定された順序で優先されます。一例として、
PATTERN (A | B | C)
は、最初にA
との一致を試みます。A
が一致しない場合は、B
との一致を試みます。B
が一致しない場合は、C
との一致を試みます。 -
グループ化
グループ化では、正規表現の一部分を単一ユニットとして扱い、数量子などの正規表現演算子をそのグループに適用できるようにします。グループ化はカッコを使って作成されます。一例として、
PATTERN ((A B){3} C)
はグループ(A B)
との一致を3回試みて、C
の1回の出現を探します。 -
PERMUTE
詳細は、すべての順列の表現方法を参照してください。
-
除外
ALL
ROWS
PER
MATCH
の出力から除外されるパターンの部分は、{-と-}で括られます。パターンの部分を出力から除外する方法.を参照してください。 -
アンカー
アンカーは、行ではなく位置に関して機能します。アンカーは、パーティションの始まりまたは終わりの位置と一致します。
-
^
は、パーティションの最初の行の前の位置と一致します。 -
$
は、パーティションの最後の行の後の位置と一致します。
たとえば、
PATTERN (^A+$)
は、パーティションのすべての行がA
の条件を満たす場合のみ、一致します。その結果、パーティション全体にわたって一致します。 -
-
空のパターン()は、空の行セットと一致します。
この項では、次の項目について説明します。
21.3.5.1 最短一致数量子と強欲な数量子の比較
パターン数量子を強欲な数量子といい、適用されている正規表現で最大限のインスタンスの一致を試みます。その例外が、接尾辞として疑問符?
を持つパターン数量子です。これらは、最短一致数量子と呼ばれます。これらは、適用されている正規表現で最小限のインスタンスの一致を試みます。
1つのパターン変数に付加される強欲な数量子と最短一致数量子の相違は、「A*
はできるかぎり多くの行をA
にマップしようとするのに対し、A*?
はできるかぎり少ない行をA
にマップしようとする」のように説明できます。次に例を示します。
PATTERN (X Y* Z)
このパターンは3つの変数名、X
、Y
およびZ
で構成され、Y
は*
で数量化されています。これは、連続する入力行が次の条件を満たしたときに、パターン一致が認識され、報告されることを示しています。
-
1つの行が変数
X
を定義する条件を満たし、その後に変数Y
を定義する条件を満たす0行以上の行が続き、さらにその後に変数Z
を定義する条件を満たす行が1つ続く。
パターン一致プロセスの際、1つの行がX
にマップされ、0行以上の行がY
にマップされた後、それに続く行を変数Y
とZ
の両方にマップできる場合(これはY
とZ
の両方の定義条件を満たします)、このとき、Y
の数量子*
は強欲な数量子であるので、その行はZ
よりもY
に優先的にマップされます。この強欲という特性のため、Y
はZ
よりも優先され、Y
に対してより多くの行がマップされます。パターン表現がY
で最短一致数量子*?
を使用するPATTERN (X Y*? Z)
であった場合、Z
はY
よりも優先されます。
21.3.5.2 演算子の優先順位
正規表現の要素の優先順位は、降順で次のとおりです。
-
row_pattern_primary
これらの要素には、プライマリ・パターン変数(SUBSET: 共用体行パターン変数を定義するで説明する
SUBSET
句を使用して作成されていないパターン変数)、アンカー、PERMUTE
、挿入句、除外構文および空パターンがあります。 -
数量詞
row_pattern_primary
は、0または1つの数量子を持つ場合があります。 -
連結
-
代替
代替の優先順位はPATTERN
(A B | C D)
で説明できます。これは、PATTERN
((A B) | (C D))
と同等です。ただし、PATTERN
(A (B | C) D)
と同等ではありません。
数量子の優先順位はPATTERN
(A B *)
によって説明できます。これは、PATTERN
(A (B*))
と同等です。ただし、PATTERN
((A B)*)
と同等ではありません。
数量子が別の数量子の直後に続かない場合もあります。たとえば、PATTERN
(A**)
は禁止されています。
プライマリ・パターン変数がパターン内に複数回出現することは許可されています(例: IPATTERN
(X Y X)
)。
21.3.6 SUBSET: 共用体行パターン変数を定義する
それ自身の変数名を使って参照可能な複数のパターン変数のグループ化を作成すると便利な場合があります。このようなグループ化を共用体行パターン変数といい、SUBSET
句を使って作成できます。SUBSET
で作成された共用体行パターン変数は、MEASURES
句およびDEFINE
句に使用できます。SUBSET
句はオプションです。これは、共用体行パターン変数の宣言に使用します。たとえば、SUBSET
を使用して、STRT
変数とDOWN
変数(この場合のSTRT
はパターンの開始点、DOWN
はV字形の下方(左)部分)の共用体にマップされるすべての行に基づいて平均値を計算する問合せがあるとします。
例21-5では、共用体行パターン変数の作成について説明しています。
例21-5 共用体行パターン変数の定義
SELECT * FROM Ticker MATCH_RECOGNIZE( PARTITION BY symbol ORDER BY tstamp MEASURES FIRST(STRT.tstamp) AS strt_time, LAST(DOWN.tstamp) AS bottom, AVG(STDN.Price) AS stdn_avgprice ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) SUBSET STDN= (STRT, DOWN) DEFINE UP AS UP.Price > PREV(UP.Price), DOWN AS DOWN.Price < PREV (DOWN.Price) ); SYMBOL STRT_TIME BOTTOM STDN_AVGPRICE ------ --------- --------- ------------- ACME 05-APR-11 06-APR-11 18.5 ACME 10-APR-11 12-APR-11 19.6666667 ACME 14-APR-11 16-APR-11 17
この例では、1つの共用体行パターン変数、STDN
を宣言し、それをSTRT
にマップされる行とDOWN
にマップされる行の共用体として定義しています。1つの問合せに複数の共用体行パターン変数を使用できます。次に例を示します。
PATTERN (W+ X+ Y+ Z+) SUBSET XY = (X, Y), WZ = (W, Z)
SUBSET
項目の右側は、カッコで括られた個別のプライマリ行パターン変数のカンマ区切りリストです。これは、(左辺の)共用体行パターン変数を(右辺の)プライマリ行パターン変数の共用体として定義します。
右側のパターン変数のリストには、共用体行パターン変数がまったく含まれない場合があります(共用体の共用体がないため)。
一致ごとに、ユニバーサル行パターン変数と呼ばれる暗黙的な共用体行パターン変数が1つ存在します。ユニバーサル行パターン変数は、すべてのプライマリ行パターン変数の共用体です。たとえば、パターンにプライマリ・パターン変数A
、B
およびC
が存在する場合、ユニバーサル行パターン変数は引数(A, B, C)
を持つSUBSET
句と同等になります。したがって、一致のすべての行はユニバーサル行パターン変数にマップされます。MEASURES
句またはDEFINE
句の中の非修飾列参照はすべて、ユニバーサル行パターン変数によって暗黙的に修飾されます。ユニバーサル行パターン変数を明示的に指定するキーワードはありません。
21.3.7 DEFINE: プライマリ・パターン変数を定義する
DEFINE
は必須の句であり、プライマリ・パターン変数を定義する条件の指定に使用されます。この例では次のようになります。
DEFINE UP AS UP.Price > PREV(UP.Price), DOWN AS DOWN.Price < PREV(DOWN.Price)
UP
は条件UP.Price > PREV (UP.Price)
によって定義され、DOWN
は条件DOWN.Price < PREV (DOWN.Price)
によって定義されます。(PREV
は、前の行の式を評価する行パターンのナビゲーション操作です。行パターンのナビゲーション操作の完全なセットについては、行パターンのナビゲーション操作を参照してください。)
パターン変数は定義を必要としません。定義が存在しない場合は、パターン変数に任意の行をマップできます。
共用体行パターン変数(SUBSET: 共用体行パターン変数を定義するのSUBSET
の説明を参照)はDEFINE
では定義できませんが、パターン変数の定義で参照できます。
パターン変数の定義は、別のパターン変数を参照できます。これについては、例21-6で説明しています。
例21-6 パターン変数の定義
SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY Symbol FROM Ticker MATCH_RECOGNIZE ( PARTITION BY Symbol ORDER BY tstamp MEASURES FIRST (A.tstamp) AS A_Firstday, LAST (D.tstamp) AS D_Lastday, AVG (B.Price) AS B_Avgprice, AVG (D.Price) AS D_Avgprice PATTERN (A B+ C+ D) SUBSET BC = (B,C) DEFINE A AS Price > 100, B AS B.Price > A.Price, C AS C.Price < AVG (B.Price), D AS D.Price > MAX (BC.Price) ) M
この例では、次のようになります。
-
A
の定義は、ユニバーサル行パターン変数を暗黙的に参照しています(Price
が非修飾列参照のため)。 -
B
の定義は、パターン変数A
を参照しています。 -
C
の定義は、パターン変数B
を参照しています。 -
D
の定義は、共用体行パターン変数BC
を参照しています。
条件が試行一致でのパーティションの連続行で評価され、現在の行がこのパターンで許可されているパターン変数に試験的にマップされます。正常にマップされるためには、条件がtrueに評価される必要があります。
前述の例では次のようになります。
A AS Price > 100
Price
は現在行のPrice
を参照します。なぜなら、プライマリ行パターン変数にマップされている最後の行が現在行であり、これがA
に試験的にマップされているからです。この例では、かわりに、A.Price
を使用しても同じ結果になります。
B AS B.Price > A.Price
B.Price
は現在行のPrice
を参照しています(B
が定義中であるため)が、A.Price
はA
に最後にマップされた行を参照しています。このパターンを調べてみると、A
にマップされた行は、マッピングの対象である最初の行のみです。
C AS C.Price < AVG(B.Price)
ここでは、C.Price
は現在の行でPrice
を参照していますが、これはC
が定義中であるためです。集計AVG
(つまり、挿入Price
)は、すでにB
にマップされたすべての行の平均値として計算されています。
D AS D.Price > MAX(BC.Price)
パターン変数D
はパターン変数C
と類似していますが、ブール条件における共用体行パターン変数の使用について説明しています。このケースでは、MAX(BC.Price)
は、変数B
または変数C
にマップされている行の最大価格値を返します。ブール条件のセマンティクスについては、MEASURESおよびDEFINEの式で詳しく説明しています。
21.3.8 AFTER MATCH SKIP: 一致が見つかった後の一致プロセスの再開場所を定義する
AFTER
MATCH
SKIP
句では、空以外の一致が見つかった後の行パターン一致を再開する場所が判別されます。この句のデフォルトは、AFTER
MATCH
SKIP
PAST
LAST
ROW
です。使用できるオプションは次のとおりです。
-
AFTER
MATCH
SKIP
TO
NEXT
ROW
現在の一致の最初の行の後の行でパターン一致を再開します。
-
AFTER
MATCH
SKIP
PAST
LAST
ROW
現在の一致の最後の行の次の行でパターン一致を再開します。
-
AFTER
MATCH
SKIP
TO
FIRST
pattern_variable
パターン変数にマップされた最初の行でパターン一致を再開します。
-
AFTER
MATCH
SKIP
TO
LAST
pattern_variable
パターン変数にマップされた最後の行でパターン一致を再開します。
-
AFTER
MATCH
SKIP
TO
pattern_variable
AFTER
MATCH
SKIP
TO
LAST
pattern_variable
と同じです。
AFTER
MATCH
SKIP
TO
FIRST
またはAFTER
MATCH
SKIP
TO
[LAST]
を使用すると、pattern_variableに行がマップされないことがあります。次に例を示します。
AFTER MATCH SKIP TO A PATTERN (X A* X),
例に示したパターン変数A
には、A
にマップされた行がない場合があります。A
にマップされた行がない場合は、スキップする行も存在しないため、ランタイム例外が生成されます。別の問題として、AFTER
MATCH
SKIP
が最後の一致が開始された行と同じ行でパターン一致を再開しようとすることがあります。次に例を示します。
AFTER MATCH SKIP TO X PATTERN (X Y+ Z),
この例では、AFTER
MATCH
SKIP
TO
X
は、前の一致が見つかった行と同じ行でパターン一致を再開しようとしています。その結果、無限ループに入り、このシナリオに対してランタイム例外が生成されます。
AFTER
MATCH
SKIP
構文は、空以外の一致の後で一致のスキャン再開場所を判別するのみです。空の一致が見つかった場合は、1行をスキップします(SKIP
TO
NEXT
ROW
が指定された場合と同じ)。したがって、空の一致によってこれらの例外が発生することはありません。これらの例外のいずれか1つを取得する問合せは、たとえば次のように再作成してください。
AFTER MATCH SKIP TO A PATTERN (X (A | B) Y)
これにより、行がB
にマップされたときに実行時エラーが発生しますが、これはA
.にマップされた行がないためです。A
またはB
のいずれかにスキップする場合は、次のようにします。
AFTER MATCH SKIP TO C PATTERN (X (A | B) Y) SUBSET C = (A, B)
修正された例では、A
またはB
のどちらが一致しているかに関係なく、実行時エラーが生じる可能性はありません。
もう1つ、例を示します。
AFTER MATCH SKIP TO FIRST A PATTERN (A* X)
この例では、最初の一致の後で、一致の最初の行にスキップしたため(A*
が一致している場合)、または存在しない行にスキップしたために(A*
が一致していない場合)、例外を取得しています。この例の場合は、SKIP
TO
NEXT
ROW
を選択することをお薦めします。
ALL
ROWS
PER
MATCH
をAFTER
MATCH
SKIP
PAST
LAST
ROW
以外のスキップ・オプションとともに使用すると、連続する一致が重なることがあります。その場合、行パターン入力表の行R
が複数の一致に出現する可能性があります。その場合、行パターン出力表には、行が関係している一致ごとに1行ずつ表示されます。行パターン入力表の行が複数の一致に関係している場合は、MATCH_NUMBER
関数を使用してそれぞれの一致を区別できます。行が複数の一致に関係している場合は、一致ごとに異なる分類子を使用できます。
21.3.9 MEASURESおよびDEFINEの式
パターン一致では、次のように、行パターン一致に固有のスカラー式が使用できます。
-
行パターンのナビゲーション操作(関数
PREV
、NEXT
、FIRST
およびLAST
を使用)。行パターンのナビゲーション操作については、行パターンのナビゲーション操作で説明しています。 -
行パターン・パーティション内の行パターン一致の連番を返す
MATCH_NUMBER
関数(MATCH_NUMBER: どの行がどの一致にあるかを検索するで説明)。 -
行のマップ先であるプライマリ行パターン変数の名前を返す
CLASSIFIER
関数(CLASSIFIER: どのパターン変数がどの行に適用されるかを検索するで説明)。
MEASURES
句とDEFINE
句の式の構文とセマンティクスは、次の例外を除き、同じです。
-
DEFINE
句は実行中のセマンティクスのみをサポートします。 -
MEASURES
句のデフォルトは実行中のセマンティクスですが、最終セマンティクスもサポートしています。この区別については、実行中セマンティクスと最終セマンティクスの比較で説明しています。
式の使用
この項では、パターン一致で式を使用する際の考慮点について説明します。内容は次のとおりです。
21.3.9.1 MATCH_NUMBER: どの行がどの一致にあるかを検索する
1つの行パターン・パーティション内の一致には、見つかった順に1から順次番号が付けられます。行パターン・パーティション間では順序付けが継承されないため、一致の番号付は行パターン・パーティションごとに1から繰り返し開始されます。MATCH_NUMBER()
は、行パターン・パーティション内の一致の連番を表すスケール0(ゼロ)の数値を返す関数です。
MATCH_NUMBER()
を使用している前の例では、これはMEASURES
句で使用されています。MATCH_NUMBER()
をDEFINE
句で使用することもできます。その場合は、一致番号に依存する条件の定義に使用できます。
21.3.9.2 CLASSIFIER: どのパターン変数がどの行に適用されるかを検索する
CLASSIFIER
関数は、行のマップ先であるパターン変数の名前を値として持つ文字列を返します。CLASSIFIER
関数は、MEASURES
句とDEFINE
句の両方で使用可能です。
DEFINE
句では、CLASSIFIER
関数は現在行のマップ先であるプライマリ・パターン変数の名前を返します。
MEASURES
句の場合:
-
ONE
ROW
PER
MATCH
が指定された場合、問合せはMEASURES
句の処理時に一致の最後の行を使用しているため、CLASSIFIER
関数は一致の最後の行のマップ先であるパターン変数の名前を返します。 -
ALL
ROWS
PER
MATCH
が指定された場合、CLASSIFIER
関数は、見つかった一致の行ごとに、行のマップ先であるパターン変数の名前を返します。
空の一致の開始行に対する分類子はNULL値になります。
21.3.9.3 行パターンの列参照
行パターン列参照は、次に示すような明示的または暗黙的なパターン変数によって修飾された列名です。
A.Price
A
はパターン変数であり、Price
は列名です。修飾子のない列名(Price
など)は、一致のすべての行のセットを参照するユニバーサル行パターン変数により暗黙的に修飾されます。列参照は、その他の構文要素、特に集計とナビゲーション演算子の中にネストできます。(ただし、行パターン一致でのネストは、MATCH_RECOGNIZE句で禁止されたネストでFROM
句に関して説明する制限を受けます。)
パターン列参照は、次のように分類されています。
-
集計(
SUM
など)の中でネストする場合: 集計行パターンの列参照。 -
行パターンのナビゲーション操作(
PREV
、NEXT
、FIRST
およびLAST
)の中でネストする場合: ナビゲートされた行パターンの列参照。 -
その他: 通常の行パターンの列参照。
集計または行パターンのナビゲーション操作内のパターン列参照はすべて、同じパターン変数で修飾される必要があります。次に例を示します。
PATTERN (A+ B+) DEFINE B AS AVG(A.Price + B.Tax) > 100
この例では、A
とB
が別々のパターン変数であるため、構文エラーになります。集計セマンティクスには単一の行セットが必要です。A.Price + B.Tax
を評価する際に使用される単一の行セットを構成する方法はありません。ただし、次の方法は受け入れられます。
DEFINE B AS AVG (B.Price + B.Tax) > 100
この例では、集計内のすべてのパターン列参照がB
によって修飾されています。
非修飾の列参照が、一致内のすべての行のセットを参照するユニバーサル行パターン変数により暗黙的に修飾されます。次に例を示します。
DEFINE B AS AVG(Price + B.Tax) > 1000
この例では、非修飾の列参照Price
がユニバーサル行パターン変数によって暗黙的に修飾されているのに対し、B.Tax
はB
によって明示的に修飾されているため、構文エラーが生じます。ただし、次の方法は受け入れられます。
DEFINE B AS AVG (Price + Tax) > 1000
この例では、Price
とTax
が両方とも、ユニバーサル行パターン変数によって暗黙的に修飾されています。
21.3.9.4 集計
集計(COUNT
、SUM
、AVG
、MAX
およびMIN
)は、MEASURES
句とDEFINE
句の両方で使用できます。DISTINCT
キーワードはサポート対象外であることに留意してください。行パターン一致で使用される場合、集計は、実行中または最終のセマンティクスのいずれかを使用して特定のパターン変数にマップされた行のセット上で動作します。次に例を示します。
MEASURES SUM (A.Price) AS RunningSumOverA, FINAL SUM(A.Price) AS FinalSumOverA ALL ROWS PER MATCH
この例では、A
がパターン変数です。最初のパターン・メジャー、RunningSumOverA
はRUNNING
またはFINAL
のいずれも指定していないため、RUNNING
がデフォルトになります。これは、現在の一致によってA
にマップされる、現在まで(現在行も含む)の行におけるPrice
の合計として計算されることを示しています。2番目のパターン・メジャー、FinalSumOverA
は、現在の一致によってA
にマップされるすべての行(現在行より後になる行も含む)にわたるPrice
の合計を計算します。最終的な集計は、DEFINE
句ではなく、MEASURES
句のみで使用可能です。
集計に含まれている非修飾の列参照は、現在のパターン一致のすべての行を参照するユニバーサル行パターン変数によって暗黙的に修飾されます。次に例を示します。
SUM (Price)
現在の行パターン一致のすべての行にわたるPrice
の実行中の合計が計算されます。
集計に含まれている列参照はすべて、同じパターン変数で修飾する必要があります。次に例を示します。
SUM (Price + A.Tax)
Price
はユニバーサル行パターン変数によって暗黙的に修飾されているのに対し、A.Tax
は A
によって明示的に修飾されているため、構文エラーが発生します。
COUNT
集合にはパターン一致用に特別の構文があるため、COUNT(A.*)
を指定できます。COUNT(A.*)
は、現在のパターン一致によってパターン変数Aにマップされる行数です。COUNT(*)
については、*
がユニバーサル行パターン変数の行を暗黙的にカバーするため、COUNT(*)
は現在のパターン一致の行数になります。
21.3.9.5 行パターンのナビゲーション操作
4つの関数、PREV
、NEXT
、FIRST
およびLAST
があり、物理的または論理的なオフセットによって行パターン内でのナビゲーションを可能にします。
21.3.9.5.1 PREVとNEXT
PREV
関数は、パーティション内で前の行を使用する式の評価に使用できます。これは物理行に関して動作し、特定の変数にマップされた行に制限されません。前の行が存在しない場合は、NULL値が返されます。次に例を示します。
DEFINE A AS PREV (A.Price) > 100
この例によると、現在行の前の行の価格が100より大きい場合は、現在行をA
にマップできます。前の行が存在しない場合(つまり、現在行が行パターン・パーティションの第1行である場合)、PREV(A.Price)
はNULLになり、条件はTrue
ではありません。したがって、第1行はA
にマップできません。
パターン変数A
を定義する際に別のパターン変数(B
など)を使用できます。さらに、条件によってPREV()
関数をその別のパターン変数に適用できます。これは、次の状況に似ています。
DEFINE A AS PREV (B.PRICE) > 100
この場合、PREV()
関数でナビゲーション用に使用される開始行は、パターン変数B
にマップされた最後の行になります。
PREV
関数はオプションの負以外の整数の引数を受け入れて、前の行に対して物理オフセットを指定できます。次のようになります:
-
PREV (A.Price, 0)
はA.Price
と同等になります。 -
PREV (A.price, 1)
は、PREV (A.Price)
と同等になります。ノート: 1はデフォルトのオフセットです。 -
PREV (A.Price, 2)
は、実行中セマンティクスによる、Aで表した行の2行前の行にあるPrice
の値です。(Aにマップされた行がない場合、または前に2行が存在しない場合、PREV (A.Price, 2)
はNULLになります。)
オフセットは、列や副問合せではなく、ランタイム定数(リテラル、バインド変数およびそれらを含む式)である必要があります。
NEXT
関数は、PREV
関数の前方向バージョンです。これを使用して、物理オフセットを使用し、行パターン・パーティション内で前方向に行を参照することができます。構文はPREV
と同じですが、関数の名前が異なります。次に例を示します。
DEFINE A AS NEXT (A.Price) > 100
この例では、行パターン・パーティションで前方向に1行を参照しています。パターン一致では、行とパターン変数との将来的なマッピング予測が困難であるため、DEFINE
句で現在行より後を参照する集計はサポートされません。NEXT
関数は、将来的な行のマッピングを知る必要のない物理オフセットを基準として将来の行にナビゲートするため、この原則に違反しません。
たとえば、前後それぞれ2行の平均値の2倍以上の値を持つ単独行を見つけるには、NEXT
を使用して表現できます。
PATTERN ( X ) DEFINE X AS X.Price > 2 * ( PREV (X.Price, 2) + PREV (X.Price, 1) + NEXT (X.Price, 1) + NEXT (X.Price, 2) ) / 4
PREV
またはNEXT
を評価している行は、引数内のパターン変数にマップされるとはかぎりません。たとえば、この例では、PREV (X.Price, 2)
を評価している行は一致に含まれていません。パターン変数の目的は、最終的に到達する行ではなく、オフセット元となる行を特定することです。(パターン変数の定義がPREV()
またはNEXT()
の中でそれ自身を参照している場合、オフセット元の行として現在行を参照していることになります。)これについては、パターン一致におけるPREVおよびNEXT内でのFIRSTとLASTのネストで詳しく説明しています。
PREV
とNEXT
は、複数の列参照とともに使用することもできます。次に例を示します。
DEFINE A AS PREV (A.Price + A.Tax) < 100
PREV
またはNEXT
の最初の引数として複雑な式を使用する場合、すべての修飾子は同じパターン変数である必要があります(この例では、A
)。
PREV
とNEXT
は、常に実行中セマンティクスを持っています。キーワードRUNNING
とFINAL
をPREV
またはNEXT
とともに使用することはできません。(実行中および最終セマンティクスとキーワードの比較の項を参照してください。)最終セマンティクスを取得するには、たとえば、パターン一致におけるPREVおよびNEXT内でのFIRSTとLASTのネストで説明するように、PREV
(FINAL
LAST (A.Price))
を使用します。
21.3.9.5.1.1 FIRSTとLAST
PREV
およびNEXT
関数とは対照的に、FIRST
およびLAST
関数は、パターン変数にマップされた行のみをナビゲートします。つまり、これらの関数は物理オフセットではなく、論理オフセットを使用します。FIRST
は、パターン変数にマップされた行グループの最初の行で評価された式の値を返します。次に例を示します。
FIRST (A.Price)
A
にマップされている行がない場合、この値はNULLになります。
同様に、LAST
は、パターン変数にマップされた行のグループの最後の行で評価された式の値を返します。次に例を示します。
LAST (A.Price)
この例では、A
にマップされた最終行でA.Price
が評価されます(このような行が存在しない場合は、NULLになります)。
FIRST
およびLAST
演算子は、オプションの負以外の整数の引数を受け入れて、パターン変数にマップされた行のセット内で論理オフセットを指定できます。次に例を示します。
FIRST (A.Price, 1)
この行では、A
にマップされた2番目の行でPrice
を評価します。表21-1のデータセットとマッピングについて考えてみます。
表21-1 パターンおよび行
行 | 価格 | マッピング |
---|---|---|
R1 |
10 |
A |
R2 |
20 |
B |
R3 |
30 |
A |
R4 |
40 |
C |
R5 |
50 |
A |
次のようになります。
-
FIRST (A.Price)
=FIRST (A.Price, 0)
=LAST (A.Price, 2) = 10
-
FIRST (A.Price, 1)
=LAST (A.Price, 1) = 30
-
FIRST (A.Price, 2)
=LAST (A.Price, 0) = LAST (A.Price) = 50
-
FIRST (A.Price, 3)
およびLAST (A.Price, 3)
はNULLです。
オフセットは、パターン変数A
にマップされる行のセット{R1, R3, R5}
の中で移動する論理オフセットです。PREV
またはNEXT
の場合のような物理オフセットではありません。
オプションの整数引数は、列や副問合せではなく、ランタイム定数(リテラル、バインド変数およびそれらを含む式)である必要があります。
FIRST
またはLAST
の最初の引数には、少なくとも1つの行パターンの列参照が設定されている必要があります。したがって、FIRST(1)
は構文エラーになります。
FIRST
またはLAST
の最初の引数には、複数の行パターンの列参照が設定されていることがありますが、その場合は、すべての修飾子は同じパターン変数である必要があります。たとえば、FIRST (A.Price + B.Tax)
は構文エラーですが、FIRST (A.Price + A.Tax)
は受け入れられます。
FIRST
およびLAST
は、実行中セマンティクスと最終セマンティクスを両方ともサポートしています。RUNNING
キーワードはデフォルトであり、DEFINE
句で唯一サポートされているオプションです。MEASURES
で最終セマンティクスにアクセスするには、次のようにキーワードFINAL
を使用します。
MEASURES FINAL LAST (A.Price) AS FinalPrice ALL ROWS PER MATCH
21.3.9.6 実行中および最終セマンティクスとキーワードの比較
この項では、RUNNING
およびFINAL
を使用する際の留意点について説明します。
21.3.9.6.1 実行中セマンティクスと最終セマンティクスの比較
一連の行でのパターン一致は、通常は増分プロセスと見なされ、行がパターンに一致するかどうかを1行ずつ順次に調べていきます。この増分処理モデルでは、完全なパターンが認識されるまでは、どのステップにおいても部分的な一致を認識できたにすぎず、将来どんな行が追加されるか、その行がどの変数にマップされるかは不明です。したがって、パターン一致では、DEFINE
句のブール条件にある行パターンの列参照には実行中セマンティクスが設定されます。つまり、パターン変数によって表現される行セットは、すでにパターン変数にマップされたものであり、現在行も含めて現在行までが対象となりますが、将来の行は対象外です。
完全な一致が確立されると、最終セマンティクスを持つことができるようになります。最終セマンティクスは、一致に成功した最終行では実行中セマンティクスと同じです。DEFINE
では完全一致が達成されたかどうか不確かなため、最終セマンティクスはMEASURES
でのみ使用可能です。
キーワードRUNNING
およびFINAL
は、それぞれ実行中セマンティクスまたは最終セマンティクスの指定に使用します。これらのキーワードのルールは、RUNNINGおよびFINALキーワードの比較で説明しています。
MEASURES
およびDEFINE
での式評価の基本ルールは次のとおりです。
-
パターン変数に関連する式を行グループに対して計算する場合は、そのパターン変数にマップされた行のセットが使用されます。セットが空の場合、
COUNT
は0になり、パターン変数に関連するその他の式はすべてNULLになります。 -
式で単一行での評価が必要な場合は、セットの最後の行が使用されます。セットが空の場合、式はNULLになります。
たとえば、次の図21-7の表と問合せを考えてみます。
図21-7 実行中セマンティクスと最終セマンティクスの比較
SELECT M.Symbol, M.Tstamp, M.Price, M.RunningAvg, M.FinalAvg FROM TICKER MATCH_RECOGNIZE ( PARTITION BY Symbol ORDER BY tstamp MEASURES RUNNING AVG (A.Price) AS RunningAvg, FINAL AVG (A.Price) AS FinalAvg ALL ROWS PER MATCH PATTERN (A+) DEFINE A AS A.Price >= AVG (A.Price) ) M ;
表21-2に示す次のデータの順序付き行パターン・パーティションを考えてみます。
表21-2 パターンおよびパーティション化されたデータ
行 | 記号 | タイムスタンプ | 価格 |
---|---|---|---|
R1 |
XYZ |
09-Jun-09 |
10 |
R2 |
XYZ |
10-Jun-09 |
16 |
R3 |
XYZ |
11-Jun-09 |
13 |
R4 |
XYZ |
12-Jun-09 |
9 |
次のロジックを使用して一致を見つけることができます。
-
行パターン・パーティションの最初の行で、行
R1
をパターン変数A
に試験的にマップします。この時点で、変数A
にマップされた行のセットは{R1}
になります。このマッピングが正常に行われたかどうかを確認するには、次のように述語を評価します。A.Price >= AVG (A.Price)
左辺の
A.Price
は、セットの最終行である単一行で、実行中セマンティクスを使用して評価する必要があります。セットの最後の行はR1
であるため、A.Price
は10になります。右辺の
AVG (A.Price)
は、セットの行を使用して計算される集計です。この平均は10/1 = 10となります。述語が
10 >= 10
であるかと尋ねるので、答は「はい」となり、マッピングは成功します。ただし、パターンA+
は強欲なので、問合せではできるかぎり多くの行を一致させようとする必要があります。 -
行パターン・パーティションの2番目の行では、
R2
をパターン変数A
に試験的にマップします。この時点では2つの行がA
にマップされているため、セットは{R1, R2}
となります。述語を評価して、マッピングが成功したかどうかを確認してください。A.Price >= AVG (A.Price)
左辺の
A.Price
は、セットの最終行である単一行で、実行中セマンティクスを使用して評価する必要があります。セットの最終行はR2
であるため、A.Price
は16になります。右辺のAVG (A.Price)
は、セットの行を使用して計算される集計です。この平均値は(10+16)/2 = 13です。したがって、述語は16 >= 13かどうかと尋ねてきます。答は「はい」になるため、マッピングは成功しています。 -
行パターン・パーティションの3番目の行では、
R3
をパターン変数A
に試験的にマップします。現在3つの行がA
にマップされているため、セットは{R1, R2, R3}
となります。述語を評価して、マッピングが成功したかどうかを確認してください。A.Price >= AVG (A.Price)
左辺の
A.Price
はR3
で評価されます。したがって、A.Price
は13です。右辺の
AVG (A.Price)
は、セットの行を使用して計算される集計です。この平均値は(10+16+13)/3 = 13です。したがって、述語は13 >= 13かどうかと尋ねてきます。答は「はい」になるため、マッピングは成功しています。 -
行パターン・パーティションの4番目の行では、
R4
をパターン変数A
に試験的にマップします。この時点で、セットは{R1, R2, R3, R4}
となります。述語を評価して、マッピングが成功したかどうかを確認してください。A.Price >= AVG (A.Price)
左辺の
A.Price
はR4
で評価されます。したがって、A.Price
は9です。右辺の
AVG (A.Price)
は、セットの行を使用して計算される集計です。この平均値は(10+16+13+9)/4 = 12です。したがって、述語は9 >= 12かどうかと尋ねてきます。答えは「いいえ」となるため、マッピングは成功しません。
R4
はA
の定義を満たさなかったため、A+
との最長一致は{R1, R2, R3}
になります。A+
は強欲な数量子を持っているため、この一致が優先されます。
DEFINE
句で計算された平均値は、実行中の平均値です。MEASURES
では、特にALL
ROWS
PER
MATCH
が指定されている場合に、最終集計と実行中の集計を区別できます。MEASURES
句にキーワードRUNNING
およびFINAL
が使用されていることに留意してください。この区別は、表21-3の例の結果で確認できます。
表21-3 行パターンのナビゲーション
記号 | タイムスタンプ | 価格 | 実行中の平均値 | 最終平均値 |
---|---|---|---|---|
XYZ |
2009-06-09 |
10 |
10 |
13 |
XYZ |
2009-06-10 |
16 |
13 |
13 |
XYZ |
2009-06-11 |
13 |
13 |
13 |
パターン変数にマップされた行のセットが空の場合があります。空のセットで評価する場合:
-
COUNT
は0です。 -
その他のすべての集計、行パターンのナビゲーション操作または通常のパターンの列参照はNULLになります。
次に例を示します。
PATTERN ( A? B+ ) DEFINE A AS A.Price > 100, B AS B.Price > COUNT (A.*) * 50
前の例を使用して、次の表21-4のデータの順序付き行パターン・パーティションを考えてみます。
表21-4 パターンおよび行
行 | 価格 |
---|---|
R1 |
60 |
R2 |
70 |
R3 |
40 |
次のようにして、このデータで一致を見つけることができます。
-
行
R1
をパターン変数A
に試験的にマップします。(数量子?
は、最初にA
との単独の一致を検索し、それが失敗した場合は、空の一致が一致するA?
として取り込まれることを意味します)。マッピングが成功したかどうかを確認するために、述語A.Price > 100
が評価されます。A.Price
が60であるため、述語はfalseとなり、A
とのマッピングは成功しません。 -
A
とのマッピングが失敗したため、空の一致が一致するA?
として取り込まれます。 -
行
R1
をB
に試験的にマップします。このマッピングを確認するための述語は、B.Price > COUNT (A.*) * 50
です。A
にマップされた行がないため、COUNT (A.*)
は0になります。B.Price = 60
は0よりも大きいため、マッピングは成功です。 -
同様に、行
R2
およびR3
も正常にB
.にマップできます。さらに行が存在しないため、これが完全な一致になります。つまり、A
にマップされた行はなく、行{R1, R2, R3}がB
にマップされています。
パターン変数は前方参照、つまり、まだ一致していないパターン変数の参照を行うことができます。次に例を示します。
PATTERN (X+ Y+) DEFINE X AS COUNT (Y.*) > 3, Y AS Y.Price > 10
この例は有効な構文です。ただし、1行がX
にマップされた時点で、Y
にマップされた行が存在していなかったため、この例では一致しません。したがって、COUNT(Y.*)
は0で、3よりも小さくなります。将来、4つの行がY
に正常にマップされることがあっても、これは変わりません。表21-5でこのデータセットを考えてみます。
表21-5 パターンおよび行
行 | 価格 |
---|---|
R1 |
2 |
R2 |
11 |
R3 |
12 |
R4 |
13 |
R5 |
14 |
{R2, R3, R4, R5}
のY
へのマッピングは成功します。その理由は、この4つの行がすべてY
に定義されているブール条件を満たすためです。その場合、R1
をX
にマップして、完全一致を成功させることができるとも考えられます。ただし、パターンX+ Y+
によると、Y
に行がマップされる前に少なくとも1行がX
にマップされている必要があるため、パターン一致のルールではこの一致は見つかりません。
21.3.9.6.2 RUNNINGおよびFINALキーワードの比較
RUNNING
およびFINAL
は、実行中または最終セマンティクスのいずれが要求されているかを指定するキーワードです。RUNNING
とFINAL
は、集計および、行パターンのナビゲーション操作FIRST
およびLAST
とともに使用できます。
集計FIRST
およびLAST
は、行パターン一致の問合せで次の場所に発生させることができます。
-
DEFINE
句の中。DEFINE
句を処理している場合、問合せはまだ一致の認識中であるため、実行中セマンティクスのみがサポートされます。 -
MEASURES
句の中。MEASURES
句を処理している場合、問合せは一致の認識を終了したため、最終セマンティクスを考慮できるようになります。次の2つのサブケースがあります。-
ONE
ROW
PER
MATCH
が指定された場合、問合せは概念上、一致の最後の行に配置されるため、実行中セマンティクスと最終セマンティクスの相違は事実上なくなります。 -
ALL
ROWS
PER
MATCH
が指定された場合、行パターンの出力表には一致の行ごとに1行が表示されます。この状況では、ユーザーが実行中の値と最終の値の両方を確認したい場合があるため、パターン一致ではキーワードRUNNING
とFINAL
を用意して、その区別に対応しています。
-
この分析に基づいて、パターン一致は次のように指定します。
-
MEASURES
では、キーワードRUNNING
およびFINAL
を使用して、集計FIRST
またはLAST
に必要なセマンティクスを指定できます。キーワードは演算子の前に記述します。たとえば、RUNNING
COUNT
(A.*)
またはFINAL
SUM
(B.Price)
とします。 -
MEASURES
およびDEFINE
のいずれでも、デフォルトはRUNNING
です。 -
DEFINE
では、FINAL
は使用できません。必要に応じて、RUNNING
を使用してより明確にすることもできます。 -
MEASURES
でONE
ROW
PER
MATCH
を指定した場合、すべての集計FIRST
およびLAST
は、一致の最後の行が認識された後で計算されるため、デフォルトのRUNNING
セマンティクスはFINAL
セマンティクスと事実上同じになります。ユーザーは、このような場合にFINAL
がデフォルトになる式を作成したり、FINAL
を記述して明確さを高めることもできます。 -
通常の列参照には、実行中セマンティクスが設定されています。(
ALL
ROWS
PER
MATCH
の場合、MEASURES
で最終セマンティクスを取得するには、通常の列参照ではなく、FINAL
LAST
行パターンのナビゲーション操作を使用してください。)
21.3.9.6.3 通常の行パターンの列参照
通常の行パターンの列参照とは、集計もナビゲーションも行われないものです。次に例を示します。
A.Price
RUNNINGおよびFINALキーワードの比較では、通常の行パターンの列参照には常に実行中セマンティクスが設定されていると説明しています。これは次のことを意味します。
-
DEFINE
では、通常の列参照は、現在行も含めて現在行までで、パターン変数にマップされた最後の行を参照します。このような行がない場合、値はNULLになります。 -
MEASURES
には、次の2つのサブケースがあります。-
ALL
ROWS
PER
MATCH
が指定された場合は、現在行も表記され、セマンティクスはDEFINE
の場合と同じになります。 -
ONE
ROW
PER
MATCH
が指定された場合、問合せは概念上、一致の最後の行に配置されます。通常の列参照は、パターン変数にマップされた最後の行を参照します。変数がどの行にもマップしていない場合、値はNULLになります。
-
これらのセマンティクスはLAST
演算子と同じであり、RUNNING
が暗黙的にデフォルトになります。したがって、X.Price
などの通常の列参照はRUNNING LAST (X.Price)
と同等になります。
21.3.10 行パターンの出力
MATCH_RECOGNIZE
の結果を行パターンの出力表といいます。行パターンの出力表の形状(行タイプ)は、ONE
ROW
PER
MATCH
またはALL
ROWS
PER
MATCH
の選択によって異なります。
ONE
ROW
PER
MATCH
が指定されたか示唆された場合、行パターンの出力表の列は宣言順に行パターンのパーティション化列になり、その後に行パターンのメジャー列が宣言順に続きます。表には少なくとも1つの列が必要なため、少なくとも行パターンのパーティション化列が1つ、または行パターンのメジャー列が1つ存在している必要があります。
ALL
ROWS
PER
MATCH
が指定された場合、行パターンの出力表の列として、宣言順による行パターンのパーティション化列、宣言順による順序付け列、宣言順による行パターンのメジャー列、そして最後に行パターンの入力表のその他の列が行パターンの入力表での発生順に表示されます。
パターンのメジャー列の名前と宣言されたタイプは、MEASURES
句によって判別されます。メジャー以外の列の名前と宣言されたタイプは、パターンの入力表の対応する列から継承されます。
関連項目:
相関名を行パターン出力に割り当てる方法については相関名および行パターンの出力を参照してください。
21.4 パターン一致の高度なトピック
この項では、次の高度なトピックについて説明します。
21.4.1 パターン一致のPREVおよびNEXT内のFIRSTとLASTのネスト
FIRST
およびLAST
は、特定のパターン変数にすでにマップしている行のセット内でのナビゲーションを提供します。PREV
およびNEXT
は、特定の行の物理オフセットを使用するナビゲーションを提供します。これらの種類のナビゲーションは、PREV
またはNEXT
内でFIRST
またはLAST
をネストすることによって、組み合せられます。これによって、次のような式が使用可能になります。
PREV (LAST (A.Price + A.Tax, 1), 3)
この例では、A
はパターン変数である必要があります。行パターンの列参照が設定されている必要があり、複合演算子のすべてのパターン変数が同等(この例ではA
)である必要があります。
この複合演算子は、次のように評価されます。
- 内部演算子
LAST
は、パターン変数A
にマップされた行のセットのみで処理を行います。このセットで、最後から1つ前の行を検索します。(該当する行がない場合、結果はNULLになります。) - 外部演算子
PREV
は、行パターン・パーティションで、ステップ1で見つかった行から3行前まで戻ります。(該当する行がない場合、結果はNULLになります。) R
を、ステップ2で見つかった行を参照する実装依存の範囲変数にします。式A.Price + A.Tax
で、パターン変数A
のすべての出現をR
に置換します。この結果得られた式R.Price + R.Tax
が評価され、複合ナビゲーション操作の値が決定します。
たとえば、表21-6のデータセットとマッピングを考えてみます。
表21-6 データセットとマッピング
行 | 価格 | 税金 | マッピング |
---|---|---|---|
R1 |
10 |
1 |
|
R2 |
20 |
2 |
A |
R3 |
30 |
3 |
B |
R4 |
40 |
4 |
A |
R5 |
50 |
5 |
C |
R6 |
60 |
6 |
A |
PREV (LAST (A.Price + A.Tax, 1), 3)
を評価するには、次のステップを使用できます。
-
A
にマップされた行のセットは{R2, R4, R6}
となります。LAST
はこのセットで処理を行い、終わりからオフセットして行R4
に到達します。 -
PREV
はR4
の3行前の物理オフセットを実行し、R1
に到達します。 -
R
を、R1
を指し示す範囲変数にします。R.Price + R.Tax
が評価されて、10+1 = 11となります。
このネストは、ネストした関数の一般的な評価として定義されていないことに留意してください。内部演算子LAST
は、実際には式A.Price + A.Tax
を評価しません。これは、この式を使用してパターン変数(A)
を指定した後で、その変数にマップされた行内をナビゲートします。外部演算子PREV
は、行に対してさらに物理ナビゲーションを実行します。式A.Price + A.Tax
は実際にはほとんど評価されません。これは、最終的に到達される行がパターン変数A
にマップされるとはかぎないためです。この例では、R1
はどのパターン変数にもマップされません。
21.4.2 パターン一致での空の一致または一致しない行の処理
ALL
ROWS
PER
MATCH
には、次の3つのサブオプションがあります。
-
ALL
ROWS
PER
MATCH
SHOW
EMPTY
MATCHES
-
ALL
ROWS
PER
MATCH
OMIT
EMPTY
MATCHES
-
ALL
ROWS
PER
MATCH
WITH
UNMATCHED
ROWS
これらのオプションについては、次の内容で説明します。
21.4.2.1 パターン一致での空の一致の処理
パターンの中には、空の一致が使用できるものがあります。たとえば、PATTERN (A*)
は、A
にマップされた0行以上の行による一致が可能です。
空の一致では行とパターン変数とのマッピングは行われませんが、空の一致には開始行が存在します。たとえば、パーティションの1行目に空の一致が存在することも、2行目またはそれ以降に空の一致が存在することも考えられます。空の一致には、他のすべての一致と同じように、開始行の順序位置に基づいて順次一致番号が割り当てられます。
ONE
ROW
PER
MATCH
を使用した場合、空の一致によって出力表に1行が表示されます。空の一致に対する行パターンのメジャーは、次のように計算されます。
-
MATCH_NUMBER()
の値は、空の一致の順次一致番号です。 -
どの
COUNT
も0になります。 -
その他のすべての集計、行パターンのナビゲーション操作または通常の行パターンの列参照はNULLになります。
ALL
ROWS
PER
MATCH
に関しては、空の一致には行が存在しないため、空の一致に対して出力行を生成するかどうかという問題が生じます。これに対応するには、次の2つのオプションがあります。
-
ALL
ROWS
PER
MATCH
SHOW
EMPTY
MATCHES
: このオプションを使用すると、空の一致では行パターンの出力表に1つの行が生成されます。 -
ALL
ROWS
PER
MATCH
OMIT
EMPTY
MATCHES
: このオプションを使用すると、空の一致は行パターンの出力表から省略されます。(このため、順次一致番号付けでギャップが生じる場合があります。)
ALL
ROWS
PER
MATCH
のデフォルトはSHOW
EMPTY
MATCHES
です。このオプションを使用すると、空の一致では行パターンの出力行に1行が生成されます。この行では、次のようになります。
-
CLASSIFIER()
関数の値はNULLになります。 -
MATCH_NUMBER
()
関数の値は、空の一致の順次一致番号になります。 -
通常の行パターンの列参照の値は、NULLになります。
-
集計または行パターンのナビゲーション操作の値は、空の行セットを使って計算されます(したがって、どの
COUNT
も0になり、他のすべての集計および行パターンのナビゲーション操作はNULLになります)。 -
行パターンの入力表の列に対応している列の値は、空の一致の開始行の対応する列と同じです。
21.4.2.2 パターン一致での一致しない行の処理
行パターンの入力表の行の中には、空の一致の開始行にもならず、空以外の一致によってマップされることもないものがあります。このような行を、一致しない行といいます。
オプションALL
ROWS
PER
MATCH
WITH
UNMATCHED
ROWS
は、空の一致と一致しない行の両方を表示します。空の一致は、SHOW
EMPTY
MATCHES
の場合と同様に処理されます。一致しない行を表示する場合、すべての行パターンのメジャーはNULLになります。これは、外部結合のNULL拡張側に類似しています。したがって、COUNT
およびMATCH_NUMBER
を使用して、一致しない行と空の一致の開始行を区別できる場合があります。除外構文{- -}は、WITH
UNMATCHED
ROWS
の意図に反して、使用が禁止されています。詳細は、パターンの部分を出力から除外する方法を参照してください。
パターンで空の一致を使用したり、一致しない行をパターンに含めることはできません。その理由は、行パターンの入力表の行をプライマリ行パターン変数にマップできない場合、その行は、パターンで空の一致が使用できることを前提とした場合に、空の一致の開始行のまま残り、一致しない行とは見なされないためです。そのため、パターンで空の一致が使用できる場合は、ALL
ROWS
PER
MATCH
SHOW
EMPTY
MATCHES
を使用する出力はALL
ROWS
PER
MATCH
WITH
UNMATCHED
ROWS
を使用する出力と同じになります。したがって、WITH
UNMATCHED
ROWS
は主として、空の一致が使用できないパターンで使用されます。ただし、パターンに空の一致または一致しない行があるかどうか不確かな場合は、WITH
UNMATCHED
ROWS
を指定する場合もあります。
ALL
ROWS
PER
MATCH
WITH
UNMATCHED
ROWS
をデフォルトのスキップ動作(AFTER
MATCH
SKIP
PAST
LAST
ROW
)とともに使用する場合、入力の行ごとに1行ずつが出力に発生します。
その他のスキップ動作はWITH
UNMATCHED
ROWS
を使用して実行できます。その場合、行は複数の一致とのマップが可能であり、行パターンの出力表に複数回表示できます。一致しない行は、出力に1回のみ表示されます。
21.4.3 パターンの部分を出力から除外する方法
ALL
ROWS
PER
MATCH
をOMIT
EMPTY
MATCHES
またはSHOW
EMPTY
MATCHES
サブオプションのいずれかとともに使用する場合、PATTERN
に部分的に一致する行が行パターンの出力表から除外されることがあります。除外された部分は、PATTERN
句の中で{-
と-}
で括られます。
たとえば、次の例では、価格が最低でも10から始まる価格上昇の最長期間を検出しています。
例21-8 価格上昇期間
SELECT M.Symbol, M.Tstamp, M.Matchno, M.Classfr, M.Price, M.Avgp FROM Ticker MATCH_RECOGNIZE ( PARTITION BY Symbol ORDER BY tstamp MEASURES FINAL AVG(S.Price) AS Avgp, CLASSIFIER() AS Classfr, MATCH_NUMBER() AS Matchno ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST B PATTERN ( {- A -} B+ {- C+ -} ) SUBSET S = (A,B) DEFINE A AS A.Price >= 10, B AS B.Price > PREV(B.Price), C AS C.Price <= PREV(C.Price) ) M ORDER BY symbol, tstamp; SYMBOL TSTAMP MATCHNO CLAS PRICE AVGP ---------- --------- ---------- ---- ---------- ---------- ACME 02-APR-11 1 B 17 18.8 ACME 03-APR-11 1 B 19 18.8 ACME 04-APR-11 1 B 21 18.8 ACME 05-APR-11 1 B 25 18.8 ACME 07-APR-11 2 B 15 19.2 ACME 08-APR-11 2 B 20 19.2 ACME 09-APR-11 2 B 24 19.2 ACME 10-APR-11 2 B 25 19.2 ACME 13-APR-11 3 B 25 20 ACME 17-APR-11 4 B 14 16.6666667 ACME 18-APR-11 4 B 24 16.6666667
行パターンの出力表にはB
にマップされた行のみが表示され、A
およびC
にマップされた行は出力から除外されます。除外された行は行パターンの出力表に表示されませんが、共用体パターン変数の定義または、DEFINE
またはMEASURES
のスカラー式の計算から除外されることはありません。たとえば、プライマリ・パターン変数A
およびC
の定義、共用体パターン変数S
の定義、または前出の例のAvgp
行パターンのメジャーを参照してください。
除外構文はALL
ROWS
PER
MATCH
WITH
UNMATCHED
ROWS
では使用できません。
除外構文はONE
ROW
PER
MATCH
では使用できますが、この場合は一致ごとに1つのサマリー行しか存在しないため、効果はありません。
21.4.4 すべての順列の表現方法
PERMUTE
構文を使用して、より単純なパターンの順列であるパターンを表現できます。たとえば、PATTERN (PERMUTE (A, B, C))
は、次のように、3つのパターン変数A
、B
およびC
のすべての順列の代替と同じです。
PATTERN (A B C | A C B | B A C | B C A | C A B | C B A)
PERMUTE
は辞書編集上で展開され、並べ替える各要素は他の要素からカンマで区切る必要があります。(この例では、3つのパターン変数A
、B
およびC
はアルファベット順にリストされているため、これは展開された順列もアルファベット順にリストされる辞書編集式の展開に基づいています。)これは、展開に記述された順序で代替が試みられることを示しているため、重要です。したがって、 (A B C)
との一致が(
A C B)
との一致の前に試みられるというように順次処理されます。最初に成功した試みを勝者と呼ぶことができます。
別の例を示します。
PATTERN (PERMUTE (X{3}, B C?, D))
これは、次のルールと同等です。
PATTERN ((X{3} B C? D) | (X{3} D B C?) | (B C? X{3} D) | (B C? D X{3}) | (D X{3} B C?) | (D B C? X{3}))
パターン要素B C?
はカンマで区切られていないため、単一ユニットとして処理されます。
21.5 パターン一致のルールと制限
この項では、次のルールと制限について説明します。
21.5.1 パターン一致の入力表の要件
行パターンの入力表はMATCH_RECOGNIZE
に対する入力引数です。表またはビュー、あるいは名前付き問合せ(WITH
句で定義)を使用できます。行パターンの入力表は、導出表(インライン・ビューとしても知られています)にもできます。次に例を示します。
FROM (SELECT S.Name, T.Tstamp, T.Price FROM Ticker T, SymbolNames S WHERE T.Symbol = S.Symbol) MATCH_RECOGNIZE (...) M
行パターンの入力表は結合表にはできません。回避策として、次のような導出表を使用できます。
FROM (SELECT * FROM A LEFT OUTER JOIN B ON (A.X = B.Y)) MATCH_RECOGNIZE (...) M
パターン入力表の列名は明確である必要があります。SQLではベース表またはビューにあいまいな列名を使用できないため、行パターンの入力表がベース表またはビューの場合は問題ありません。行パターンの入力表が導出表の場合のみ、問題が生じます。たとえば、Emp
およびDept
という2つの表の結合があり、それぞれの表にName
という列があるとします。次は、構文エラーです。
FROM (SELECT D.Name, E.Name, E.Empno, E.Salary FROM Dept D, Emp E WHERE D.Deptno = E.Deptno) MATCH_RECOGNIZE ( PARTITION BY D.Name ...)
この例は、変数D
がMATCH_RECOGNIZE
句の中に表示されていないため、エラーになります(D
の有効範囲は導出表のみです)。次のように書き直しても効果的ではありません。
FROM (SELECT D.Name, E.Name, E.Empno, E.Salary FROM Dept D, Emp E WHERE D.Deptno = E.Deptno) MATCH_RECOGNIZE ( PARTITION BY Name ...)
この書き直しによって、MATCH_RECOGNIZE
句での変数D
の使用がなくなります。ただし、今回は導出表にName
という列が2つ存在するため、Name
があいまいになるというエラーが生じます。この対処方法は、次に示すように、導出表自体の中の列名を明確にすることです。
FROM (SELECT D.Name AS Dname, E.Name AS Ename, E.Empno, E.Salary FROM Dept D, Emp E WHERE D.Deptno = E.Deptno) MATCH_RECOGNIZE ( PARTITION BY Dname ...)
21.5.2 MATCH_RECOGNIZE句で禁止されたネスト
次の種類のネストは、MATCH_RECOGNIZE
句では禁止されています。
-
1つの
MATCH_RECOGNIZE
句を別のこの句の中にネストすること。 -
MEASURES
句またはDEFINE
副次句での外部参照。つまり、MATCH_RECOGNIZE
句は、行パターンの入力表を除き、外部問合せブロック内の表を参照できません。 -
相関副問合せは、
MEASURES
またはDEFINE
では使用できません。また、MEASURES
またはDEFINE
の副問合せはパターン変数を参照できません。 -
MATCH_RECOGNIZE
句は、再帰的問合せで使用できません。 -
SELECT
FOR
UPDATE
文では、MATCH_RECOGNIZE
句を使用できません。
21.5.3 連結MATCH_RECOGNIZE句
次の例のように、1つのMATCH_RECOGNIZE
句の出力を別のこの句の入力に投入することは可能です。
SELECT ... FROM ( SELECT * FROM Ticker MATCH_RECOGNIZE (...) ) MATCH_RECOGNIZE (...)
この例では、最初のMATCH_RECOGNIZE
句が導出表にあり、これが2番目のMATCH_RECOGNIZE
に入力を提供します。
21.6 パターン一致の例
この項では、次のタイプの高度なパターン一致の例について説明します。
21.6.1 パターン一致の例: 株式市場
この項では、株価とパターンに関連した共通タスクに基づくパターン一致の例について説明します。
例21-9 指定規模の株価下落
例21-9の問合せでは、現在の株価が前日の終値を特定の率(この例では8%)以上下回っている株式を示しています。
CREATE TABLE Ticker3Wave (SYMBOL VARCHAR2(10), tstamp DATE, PRICE NUMBER); INSERT INTO Ticker3Wave VALUES('ACME', '01-Apr-11', 1000); INSERT INTO Ticker3Wave VALUES('ACME', '02-Apr-11', 775); INSERT INTO Ticker3Wave VALUES('ACME', '03-Apr-11', 900); INSERT INTO Ticker3Wave VALUES('ACME', '04-Apr-11', 775); INSERT INTO Ticker3Wave VALUES('ACME', '05-Apr-11', 900); INSERT INTO Ticker3Wave VALUES('ACME', '06-Apr-11', 775); INSERT INTO Ticker3Wave VALUES('ACME', '07-Apr-11', 900); INSERT INTO Ticker3Wave VALUES('ACME', '08-Apr-11', 775); INSERT INTO Ticker3Wave VALUES('ACME', '09-Apr-11', 800); INSERT INTO Ticker3Wave VALUES('ACME', '10-Apr-11', 550); INSERT INTO Ticker3Wave VALUES('ACME', '11-Apr-11', 900); INSERT INTO Ticker3Wave VALUES('ACME', '12-Apr-11', 800); INSERT INTO Ticker3Wave VALUES('ACME', '13-Apr-11', 1100); INSERT INTO Ticker3Wave VALUES('ACME', '14-Apr-11', 800); INSERT INTO Ticker3Wave VALUES('ACME', '15-Apr-11', 550); INSERT INTO Ticker3Wave VALUES('ACME', '16-Apr-11', 800); INSERT INTO Ticker3Wave VALUES('ACME', '17-Apr-11', 875); INSERT INTO Ticker3Wave VALUES('ACME', '18-Apr-11', 950); INSERT INTO Ticker3Wave VALUES('ACME', '19-Apr-11', 600); INSERT INTO Ticker3Wave VALUES('ACME', '20-Apr-11', 300); SELECT * FROM Ticker3Wave MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES B.tstamp AS timestamp, A.price AS Aprice, B.price AS Bprice, ((B.price - A.price)*100) / A.price AS PctDrop ONE ROW PER MATCH AFTER MATCH SKIP TO B PATTERN (A B) DEFINE B AS (B.price - A.price) / A.price < -0.08 ); SYMBOL TIMESTAMP APRICE BPRICE PCTDROP ------ --------- ---------- ------- ---------- ACME 02-APR-11 1000 775 -22.5 ACME 04-APR-11 900 775 -13.888889 ACME 06-APR-11 900 775 -13.888889 ACME 08-APR-11 900 775 -13.888889 ACME 10-APR-11 800 550 -31.25 ACME 12-APR-11 900 800 -11.111111 ACME 14-APR-11 1100 800 -27.272727 ACME 15-APR-11 800 550 -31.25 ACME 19-APR-11 950 600 -36.842105 ACME 20-APR-11 600 300 -50.0 10 rows selected.
例21-10 元値に戻った時点での指定規模の価格下落
例21-10の問合せは、例21-9で定義したパターンを拡張したものです。8%を超える価格下落を示す株式を見つけます。また、株価が元値を下回ったままであるゼロ以上の追加日数をシークします。そして、株価が初期値と等しいか、それを超えるまで上昇した時点が特定されます。このパターンが発生する日数を知っておくと役立つため、これがここに含められています。start_price
列は一致の開始値で、end_price
列は開始値以上の株価になったときの一致の終値です。
SELECT * FROM Ticker3Wave MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES A.tstamp as start_timestamp, A.price as start_price, B.price as drop_price, COUNT(C.*)+1 as cnt_days, D.tstamp as end_timestamp, D.price as end_price ONE ROW PER MATCH AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C* D) DEFINE B as (B.price - A.price)/A.price < -0.08, C as C.price < A.price, D as D.price >= A.price ); SYMBOL START_TIM START_PRICE DROP_PRICE CNT_DAYS END_TIMES END_PRICE ---------- --------- ----------- ---------- -------- --------- ---------- ACME 01-APR-11 1000 775 11 13-APR-11 1100 ACME 14-APR-11 800 550 1 16-APR-11 800
例21-11 取引履歴でV字形とU字形の両方を検索する
例21-11では、パターンを定義する際に、考えられるすべてのデータ動作を考慮にいれることの重要性を示しています。表TickerVU
は、最初の例の表Ticker
とほとんど同じですが、3つ目の底部の行に2日間の等価日、April 16および17がある点が異なります。このように底部がフラットな価格下落をU字形といいます。元の例、例21-1では、変更されたデータがV字形に似たロットであることを認識し、その出力にU字形を含めることができるでしょうか。いいえ、できません。問合せを次のように変更する必要があります。
CREATE TABLE TickerVU (SYMBOL VARCHAR2(10), tstamp DATE, PRICE NUMBER); INSERT INTO TickerVU values('ACME', '01-Apr-11', 12); INSERT INTO TickerVU values('ACME', '02-Apr-11', 17); INSERT INTO TickerVU values('ACME', '03-Apr-11', 19); INSERT INTO TickerVU values('ACME', '04-Apr-11', 21); INSERT INTO TickerVU values('ACME', '05-Apr-11', 25); INSERT INTO TickerVU values('ACME', '06-Apr-11', 12); INSERT INTO TickerVU values('ACME', '07-Apr-11', 15); INSERT INTO TickerVU values('ACME', '08-Apr-11', 20); INSERT INTO TickerVU values('ACME', '09-Apr-11', 24); INSERT INTO TickerVU values('ACME', '10-Apr-11', 25); INSERT INTO TickerVU values('ACME', '11-Apr-11', 19); INSERT INTO TickerVU values('ACME', '12-Apr-11', 15); INSERT INTO TickerVU values('ACME', '13-Apr-11', 25); INSERT INTO TickerVU values('ACME', '14-Apr-11', 25); INSERT INTO TickerVU values('ACME', '15-Apr-11', 14); INSERT INTO TickerVU values('ACME', '16-Apr-11', 12); INSERT INTO TickerVU values('ACME', '17-Apr-11', 12); INSERT INTO TickerVU values('ACME', '18-Apr-11', 24); INSERT INTO TickerVU values('ACME', '19-Apr-11', 23); INSERT INTO TickerVU values('ACME', '20-Apr-11', 22);
例21-1の元の問合せを、この表名を使用するように変更して実行した場合、どうなるでしょうか。
SELECT * FROM TickerVU MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, DOWN.tstamp AS bottom_tstamp, UP.tstamp AS end_tstamp ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.start_tstamp; SYMBOL START_TST BOTTOM_TS END_TSTAM ---------- --------- --------- --------- ACME 05-APR-11 06-APR-11 10-APR-11 ACME 10-APR-11 12-APR-11 13-APR-11
この問合せは、出力の3つの行(価格下落ごとに1つ)を表示するかわりに、2つの行のみを表示します。これは、価格下落の底部でのフラットなデータの広がりを処理する変数が定義されていないためです。ここで、DEFINE
句にフラットなデータを処理する変数を追加し、その変数をPATTERN
句に使用して変更した問合せを使用してみます。
SELECT * FROM TickerVU MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, DOWN.tstamp AS bottom_tstamp, UP.tstamp AS end_tstamp ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ FLAT* UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), FLAT AS FLAT.price = PREV(FLAT.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.start_tstamp; SYMBOL START_TST BOTTOM_TS END_TSTAM ---------- --------- --------- --------- ACME 05-APR-11 06-APR-11 10-APR-11 ACME 10-APR-11 12-APR-11 13-APR-11 ACME 14-APR-11 16-APR-11 18-APR-11
3つの価格下落がすべてデータに含まれている出力が得られます。ここで学んだことは、データ・シーケンスで可能なバリエーションをすべて考慮し、それらの可能性を必要に応じてPATTERN
句、DEFINE
句およびMEASURES
句に含めることです。
表21-12 エリオット波動パターンの検索: 逆V字形の複数のインスタンス
例21-12は、逆V字形の複数の連続パターンを持つエリオット波動という、単純な株価パターンのクラスを示しています。この特別なケースにおいて、パターン式は1日以上の上昇の後に1日以上の下落のパターンを検索します。このシーケンスは5回連続して、途切れずに現れる必要があります。つまり、このパターンは/\/\/\/\/\のようになります。
SELECT MR_ELLIOTT.* FROM Ticker3Wave MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES COUNT(*) as CNT, COUNT(P.*) AS CNT_P, COUNT(Q.*) AS CNT_Q, COUNT(R.*) AS CNT_R, COUNT(S.*) AS CNT_S, COUNT(T.*) AS CNT_T, COUNT(U.*) AS CNT_U, COUNT(V.*) AS CNT_V, COUNT(W.*) AS CNT_W, COUNT(X.*) AS CNT_X, COUNT(Y.*) AS CNT_Y, COUNT(Z.*) AS CNT_Z, CLASSIFIER() AS CLS, MATCH_NUMBER() AS MNO ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST Z PATTERN (P Q+ R+ S+ T+ U+ V+ W+ X+ Y+ Z+) DEFINE Q AS Q.price > PREV(Q.price), R AS R.price < PREV(R.price), S AS S.price > PREV(S.price), T AS T.price < PREV(T.price), U AS U.price > PREV(U.price), V AS V.price < PREV(V.price), W AS W.price > PREV(W.price), X AS X.price < PREV(X.price), Y AS Y.price > PREV(Y.price), Z AS Z.price < PREV(Z.price) ) MR_ELLIOTT ORDER BY symbol, tstamp;
SYMB TSTAMP CNT CNT_P CNT_Q CNT_R CNT_S CNT_T CNT_U CNT_V CNT_W CNT_X CNT_Y CNT_Z CLS MNO PRICE ---- --------- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- --- ----- ACME 02-APR-11 1 1 0 0 0 0 0 0 0 0 0 0 P 1 775 ACME 03-APR-11 2 1 1 0 0 0 0 0 0 0 0 0 Q 1 900 ACME 04-APR-11 3 1 1 1 0 0 0 0 0 0 0 0 R 1 775 ACME 05-APR-11 4 1 1 1 1 0 0 0 0 0 0 0 S 1 900 ACME 06-APR-11 5 1 1 1 1 1 0 0 0 0 0 0 T 1 775 ACME 07-APR-11 6 1 1 1 1 1 1 0 0 0 0 0 U 1 900 ACME 08-APR-11 7 1 1 1 1 1 1 1 0 0 0 0 V 1 775 ACME 09-APR-11 8 1 1 1 1 1 1 1 1 0 0 0 W 1 800 ACME 10-APR-11 9 1 1 1 1 1 1 1 1 1 0 0 X 1 550 ACME 11-APR-11 10 1 1 1 1 1 1 1 1 1 1 0 Y 1 900 ACME 12-APR-11 11 1 1 1 1 1 1 1 1 1 1 1 Z 1 800 11 rows selected.
例21-13 エリオット波動の検索と受入れ可能な行カウントの範囲の指定
例21-12と同様、例21-13でも逆V字形のエリオット波形が指定されますが、この場合は正規表現を使用して、一致させる連続行数をパターン変数ごとに指定します。これは、範囲として指定されます。構文"{3,4}"
を使用して、3つまたは4つの連続一致をシークするように各パターン変数を設定します。出力にはパターンの1つの完全一致に対してすべての行が表示され、各パターン変数の始まりと終わりの正確なタイミングが表示されます。変数W
およびX
ではそれぞれ4行が一致しているのに対し、変数Y
およびZ
ではそれぞれ3行しか一致していない点に留意してください。
CREATE TABLE tickerwavemulti (symbol VARCHAR2(10), tstamp DATE, price NUMBER); INSERT INTO tickerwavemulti VALUES('ACME', '01-May-10', 36.25 ); INSERT INTO tickerwavemulti VALUES('BLUE', '01-May-10', 177.85); INSERT INTO tickerwavemulti VALUES('EDGY', '01-May-10', 27.18); INSERT INTO tickerwavemulti VALUES('ACME', '02-May-10', 36.47); INSERT INTO tickerwavemulti VALUES('BLUE', '02-May-10', 177.25); INSERT INTO tickerwavemulti VALUES('EDGY', '02-May-10', 27.41); INSERT INTO tickerwavemulti VALUES('ACME', '03-May-10', 36.36); INSERT INTO tickerwavemulti VALUES('BLUE', '03-May-10', 176.16); INSERT INTO tickerwavemulti VALUES('EDGY', '03-May-10', 27.43); INSERT INTO tickerwavemulti VALUES('ACME', '04-May-10', 36.25); INSERT INTO tickerwavemulti VALUES('BLUE', '04-May-10', 176.28); INSERT INTO tickerwavemulti VALUES('EDGY', '04-May-10', 27.56); INSERT INTO tickerwavemulti VALUES('ACME', '05-May-10', 36.36); INSERT INTO tickerwavemulti VALUES('BLUE', '05-May-10', 177.72); INSERT INTO tickerwavemulti VALUES('EDGY', '05-May-10', 27.31); INSERT INTO tickerwavemulti VALUES('ACME', '06-May-10', 36.70); INSERT INTO tickerwavemulti VALUES('BLUE', '06-May-10', 178.36); INSERT INTO tickerwavemulti VALUES('EDGY', '06-May-10', 27.23); INSERT INTO tickerwavemulti VALUES('ACME', '07-May-10', 36.50); INSERT INTO tickerwavemulti VALUES('BLUE', '07-May-10', 178.93); INSERT INTO tickerwavemulti VALUES('EDGY', '07-May-10', 27.08); INSERT INTO tickerwavemulti VALUES('ACME', '08-May-10', 36.66); INSERT INTO tickerwavemulti VALUES('BLUE', '08-May-10', 178.18); INSERT INTO tickerwavemulti VALUES('EDGY', '08-May-10', 26.90); INSERT INTO tickerwavemulti VALUES('ACME', '09-May-10', 36.98); INSERT INTO tickerwavemulti VALUES('BLUE', '09-May-10', 179.15); INSERT INTO tickerwavemulti VALUES('EDGY', '09-May-10', 26.73); INSERT INTO tickerwavemulti VALUES('ACME', '10-May-10', 37.08); INSERT INTO tickerwavemulti VALUES('BLUE', '10-May-10', 180.39); INSERT INTO tickerwavemulti VALUES('EDGY', '10-May-10', 26.86); INSERT INTO tickerwavemulti VALUES('ACME', '11-May-10', 37.43); INSERT INTO tickerwavemulti VALUES('BLUE', '11-May-10', 181.44); INSERT INTO tickerwavemulti VALUES('EDGY', '11-May-10', 26.78); INSERT INTO tickerwavemulti VALUES('ACME', '12-May-10', 37.68); INSERT INTO tickerwavemulti VALUES('BLUE', '12-May-10', 183.11); INSERT INTO tickerwavemulti VALUES('EDGY', '12-May-10', 26.59); INSERT INTO tickerwavemulti VALUES('ACME', '13-May-10', 37.66); INSERT INTO tickerwavemulti VALUES('BLUE', '13-May-10', 181.50); INSERT INTO tickerwavemulti VALUES('EDGY', '13-May-10', 26.39); INSERT INTO tickerwavemulti VALUES('ACME', '14-May-10', 37.32); INSERT INTO tickerwavemulti VALUES('BLUE', '14-May-10', 180.65); INSERT INTO tickerwavemulti VALUES('EDGY', '14-May-10', 26.31); INSERT INTO tickerwavemulti VALUES('ACME', '15-May-10', 37.16); INSERT INTO tickerwavemulti VALUES('BLUE', '15-May-10', 179.51); INSERT INTO tickerwavemulti VALUES('EDGY', '15-May-10', 26.53); INSERT INTO tickerwavemulti VALUES('ACME', '16-May-10', 36.98); INSERT INTO tickerwavemulti VALUES('BLUE', '16-May-10', 180.00); INSERT INTO tickerwavemulti VALUES('EDGY', '16-May-10', 26.76); INSERT INTO tickerwavemulti VALUES('ACME', '17-May-10', 37.19); INSERT INTO tickerwavemulti VALUES('BLUE', '17-May-10', 179.24); INSERT INTO tickerwavemulti VALUES('EDGY', '17-May-10', 26.63); INSERT INTO tickerwavemulti VALUES('ACME', '18-May-10', 37.45); INSERT INTO tickerwavemulti VALUES('BLUE', '18-May-10', 180.48); INSERT INTO tickerwavemulti VALUES('EDGY', '18-May-10', 26.84); INSERT INTO tickerwavemulti VALUES('ACME', '19-May-10', 37.79); INSERT INTO tickerwavemulti VALUES('BLUE', '19-May-10', 181.21); INSERT INTO tickerwavemulti VALUES('EDGY', '19-May-10', 26.90); INSERT INTO tickerwavemulti VALUES('ACME', '20-May-10', 37.49); INSERT INTO tickerwavemulti VALUES('BLUE', '20-May-10', 179.79); INSERT INTO tickerwavemulti VALUES('EDGY', '20-May-10', 27.06); INSERT INTO tickerwavemulti VALUES('ACME', '21-May-10', 37.30); INSERT INTO tickerwavemulti VALUES('BLUE', '21-May-10', 181.19); INSERT INTO tickerwavemulti VALUES('EDGY', '21-May-10', 27.17); INSERT INTO tickerwavemulti VALUES('ACME', '22-May-10', 37.08); INSERT INTO tickerwavemulti VALUES('BLUE', '22-May-10', 179.88); INSERT INTO tickerwavemulti VALUES('EDGY', '22-May-10', 26.95); INSERT INTO tickerwavemulti VALUES('ACME', '23-May-10', 37.34); INSERT INTO tickerwavemulti VALUES('BLUE', '23-May-10', 181.21); INSERT INTO tickerwavemulti VALUES('EDGY', '23-May-10', 26.71); INSERT INTO tickerwavemulti VALUES('ACME', '24-May-10', 37.54); INSERT INTO tickerwavemulti VALUES('BLUE', '24-May-10', 181.94); INSERT INTO tickerwavemulti VALUES('EDGY', '24-May-10', 26.96); INSERT INTO tickerwavemulti VALUES('ACME', '25-May-10', 37.69); INSERT INTO tickerwavemulti VALUES('BLUE', '25-May-10', 180.88); INSERT INTO tickerwavemulti VALUES('EDGY', '25-May-10', 26.72); INSERT INTO tickerwavemulti VALUES('ACME', '26-May-10', 37.60); INSERT INTO tickerwavemulti VALUES('BLUE', '26-May-10', 180.72); INSERT INTO tickerwavemulti VALUES('EDGY', '26-May-10', 26.47); INSERT INTO tickerwavemulti VALUES('ACME', '27-May-10', 37.93); INSERT INTO tickerwavemulti VALUES('BLUE', '27-May-10', 181.54); INSERT INTO tickerwavemulti VALUES('EDGY', '27-May-10', 26.73); INSERT INTO tickerwavemulti VALUES('ACME', '28-May-10', 38.17); INSERT INTO tickerwavemulti VALUES('BLUE', '28-May-10', 182.93); INSERT INTO tickerwavemulti VALUES('EDGY', '28-May-10', 26.89); SELECT MR_EW.* FROM tickerwavemulti MATCH_RECOGNIZE ( PARTITION by symbol ORDER by tstamp MEASURES V.tstamp AS START_T, Z.tstamp AS END_T, COUNT(V.price) AS CNT_V, COUNT(W.price) AS UP__W, COUNT(X.price) AS DWN_X, COUNT(Y.price) AS UP__Y, COUNT(Z.price) AS DWN_Z, MATCH_NUMBER() AS MNO ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST Z PATTERN (V W{3,4} X{3,4} Y{3,4} Z{3,4}) DEFINE W AS W.price > PREV(W.price), X AS X.price < PREV(X.price), Y AS Y.price > PREV(Y.price), Z AS Z.price < PREV(Z.price) ) MR_EW ORDER BY symbol, tstamp; SYMB TSTAMP START_T END_T CNT_V UP__W DWN_X UP__Y DWN_Z MNO PRICE ---- --------- --------- --------- ----- ----- ----- ----- ----- ----- ------- ACME 08-MAY-10 08-MAY-10 1 0 0 0 0 1 36.66 ACME 09-MAY-10 08-MAY-10 1 1 0 0 0 1 36.98 ACME 10-MAY-10 08-MAY-10 1 2 0 0 0 1 37.08 ACME 11-MAY-10 08-MAY-10 1 3 0 0 0 1 37.43 ACME 12-MAY-10 08-MAY-10 1 4 0 0 0 1 37.68 ACME 13-MAY-10 08-MAY-10 1 4 1 0 0 1 37.66 ACME 14-MAY-10 08-MAY-10 1 4 2 0 0 1 37.32 ACME 15-MAY-10 08-MAY-10 1 4 3 0 0 1 37.16 ACME 16-MAY-10 08-MAY-10 1 4 4 0 0 1 36.98 ACME 17-MAY-10 08-MAY-10 1 4 4 1 0 1 37.19 ACME 18-MAY-10 08-MAY-10 1 4 4 2 0 1 37.45 ACME 19-MAY-10 08-MAY-10 1 4 4 3 0 1 37.79 ACME 20-MAY-10 08-MAY-10 20-MAY-10 1 4 4 3 1 1 37.49 ACME 21-MAY-10 08-MAY-10 21-MAY-10 1 4 4 3 2 1 37.30 ACME 22-MAY-10 08-MAY-10 22-MAY-10 1 4 4 3 3 1 37.08
15 rows selected.
例21-14 一致の中間にスキップして重複する一致を確認する
例21-14では、AFTER
MATCH
SKIP
TO
句の重複している一致の検索能力を強調しています。これが使用するパターンは、パターン変数Q
、R
、S
およびT
で構成されるW字形をシークするという単純なものです。W字の各辺に対し、行は1行または複数行にできます。この一致では、AFTER
MATCH
SKIP
TO
句も活用します。一致が見つかると、W字形の中間点である最後のR
値のみにスキップします。これによって、問合せは、W字形の後半部分が次に重なるW字形の前半部分となる一致を見つけることができます。次の出力では、一致1がApril 5に終わっていても、一致2は重複していてApril 3に始まっていることがわかります。
SELECT MR_W.* FROM Ticker3Wave MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES MATCH_NUMBER() AS MNO, P.tstamp AS START_T, T.tstamp AS END_T, MAX(P.price) AS TOP_L, MIN(Q.price) AS BOTT1, MAX(R.price) AS TOP_M, MIN(S.price) AS BOTT2, MAX(T.price) AS TOP_R ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST R PATTERN ( P Q+ R+ S+ T+ ) DEFINE Q AS Q.price < PREV(Q.price), R AS R.price > PREV(R.price), S AS S.price < PREV(S.price), T AS T.price > PREV(T.price) ) MR_W ORDER BY symbol, mno, tstamp; SYMB TSTAMP MNO START_T END_T TOP_L BOTT1 TOP_M BOTT2 TOP_R PRICE ---- --------- ----- --------- --------- ----- ----- ----- ----- ----- ----- ACME 01-APR-11 1 01-APR-11 1000 1000 ACME 02-APR-11 1 01-APR-11 1000 775 775 ACME 03-APR-11 1 01-APR-11 1000 775 900 900 ACME 04-APR-11 1 01-APR-11 1000 775 900 775 775 ACME 05-APR-11 1 01-APR-11 05-APR-11 1000 775 900 775 900 900 ACME 03-APR-11 2 03-APR-11 900 900 ACME 04-APR-11 2 03-APR-11 900 775 775 ACME 05-APR-11 2 03-APR-11 900 775 900 900 ACME 06-APR-11 2 03-APR-11 900 775 900 775 775 ACME 07-APR-11 2 03-APR-11 07-APR-11 900 775 900 775 900 900 ACME 05-APR-11 3 05-APR-11 900 900 ACME 06-APR-11 3 05-APR-11 900 775 775 ACME 07-APR-11 3 05-APR-11 900 775 900 900 ACME 08-APR-11 3 05-APR-11 900 775 900 775 775 ACME 09-APR-11 3 05-APR-11 09-APR-11 900 775 900 775 800 800 ACME 07-APR-11 4 07-APR-11 900 900 ACME 08-APR-11 4 07-APR-11 900 775 775 ACME 09-APR-11 4 07-APR-11 900 775 800 800 ACME 10-APR-11 4 07-APR-11 900 775 800 550 550 ACME 11-APR-11 4 07-APR-11 11-APR-11 900 775 800 550 900 900 ACME 09-APR-11 5 09-APR-11 800 800 ACME 10-APR-11 5 09-APR-11 800 550 550 ACME 11-APR-11 5 09-APR-11 800 550 900 900 ACME 12-APR-11 5 09-APR-11 800 550 900 800 800 ACME 13-APR-11 5 09-APR-11 13-APR-11 800 550 900 800 1100 1100 ACME 11-APR-11 6 11-APR-11 900 900 ACME 12-APR-11 6 11-APR-11 900 800 800 ACME 13-APR-11 6 11-APR-11 900 800 1100 1100 ACME 14-APR-11 6 11-APR-11 900 800 1100 800 800 ACME 15-APR-11 6 11-APR-11 900 800 1100 550 550 ACME 16-APR-11 6 11-APR-11 16-APR-11 900 800 1100 550 800 800 ACME 17-APR-11 6 11-APR-11 17-APR-11 900 800 1100 550 875 875 ACME 18-APR-11 6 11-APR-11 18-APR-11 900 800 1100 550 950 950 33 rows selected.
例21-15 指定した時間間隔内で発生する大量取引を検索する
例21-15では、取引の多い株式、つまり、連結期間内で大量のトランザクションがあった株式を見つけます。この例で、大量の取引は、1時間以内に3つのトランザクションが発生し、各トランザクションでは30,000を超える株が取引されると定義されています。パターンが条件を満たさない取引を受け入れられるように、B
などのパターン変数を含めることが重要です。B
変数がないと、パターンでは、条件を満たしているトランザクションが3回連続して発生したケースのみが一致します。
この例の問合せは、表stockT04
を使用しています。
CREATE TABLE STOCKT04 (symbol varchar2(10), tstamp TIMESTAMP, price NUMBER, volume NUMBER); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.00.00.000000 PM', 35, 35000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.05.00.000000 PM', 35, 15000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.10.00.000000 PM', 35, 5000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.11.00.000000 PM', 35, 42000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.16.00.000000 PM', 35, 7000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.19.00.000000 PM', 35, 5000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.20.00.000000 PM', 35, 5000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.33.00.000000 PM', 35, 55000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.36.00.000000 PM', 35, 15000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.48.00.000000 PM', 35, 15000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.59.00.000000 PM', 35, 15000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 01.09.00.000000 PM', 35, 55000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 01.19.00.000000 PM', 35, 55000); INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 01.29.00.000000 PM', 35, 15000); SELECT * FROM stockT04 MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES FIRST (A.tstamp) AS in_hour_of_trade, SUM (A.volume) AS sum_of_large_volumes ONE ROW PER MATCH AFTER MATCH SKIP PAST LAST ROW PATTERN (A B* A B* A) DEFINE A AS ((A.volume > 30000) AND ((A.tstamp - FIRST (A.tstamp)) < '0 01:00:00.00' )), B AS ((B.volume <= 30000) AND ((B.tstamp - FIRST (A.tstamp)) < '0 01:00:00.00')) ); SYMBOL IN_HOUR_OF_TRADE SUM_OF_LARGE_VOLUMES ------ ----------------------------- -------------------- ACME 01-JAN-10 12.00.00.000000 PM 132000 1 row selected.
21.6.2 パターン一致の例: セキュリティ・ログの分析
この項の例では、エラー・メッセージを発行し、認証チェックを行って、イベントをシステム・ファイルに格納するコンピュータ・システムについて説明しています。セキュリティ上の問題やその他の問題があるかどうかを判別するには、システム・ファイルを分析する必要があります。このアクティビティは、ソフトウェアでファイルを精査して問題点を検索するために、ログ精査とも呼ばれます。これらの例のソース・データは非常に多くの場所をとるため、表示されていません。これらの例では、AUTHENLOG
表はログ・ファイルに由来しています。
例21-16 4つ以上の連続する同一メッセージ
この例の問合せでは、考えられる3つの'errtype'
値であるerror
、notice
およびwarn
のセットからの4つ以上の連続する同一メッセージの発生をシークします。
SELECT MR_SEC.ERRTYPE, MR_SEC.MNO AS Pattern, MR_SEC.CNT AS Count, SUBSTR(MR_SEC.MSG_W, 1, 30) AS Message, MR_SEC.START_T AS Starting_on, MR_SEC.END_T AS Ending_on FROM AUTHENLOG MATCH_RECOGNIZE( PARTITION BY errtype ORDER BY tstamp MEASURES S.tstamp AS START_T, W.tstamp AS END_T, W.message AS MSG_W, COUNT(*) AS CNT, MATCH_NUMBER() AS MNO ONE ROW PER MATCH AFTER MATCH SKIP PAST LAST ROW PATTERN ( S W{3,} ) DEFINE W AS W.message = PREV (W.message) ) MR_SEC ORDER BY ErrType, Pattern;
ERRTYP PATTERN COUNT MESSAGE STARTING_ON ENDING_ON ------ ------- ----- ------------------- ---------------------------- ---------------------------- error 1 4 script not found or 09-JAN-10 12.00.06.000006 PM 09-JAN-10 12.00.15.000015 PM error 2 4 File does not exist 04-FEB-10 12.00.18.000018 PM 04-FEB-10 12.00.23.000023 PM error 3 4 File does not exist 06-FEB-10 12.00.25.000025 PM 06-FEB-10 12.00.33.000033 PM error 4 4 File does not exist 13-FEB-10 12.00.19.000019 PM 14-FEB-10 12.00.07.000007 PM error 5 5 File does not exist 28-FEB-10 12.00.27.000027 PM 28-FEB-10 12.00.34.000034 PM error 6 4 script not found or 05-APR-10 12.00.19.000019 PM 05-MAR-10 12.00.23.000023 PM error 7 4 File does not exist 07-MAR-10 12.00.31.000031 PM 08-MAR-10 12.00.02.000002 PM error 8 4 File does not exist 14-MAR-10 12.00.19.000019 PM 15-MAR-10 12.00.00.000000 PM error 9 4 File does not exist 20-MAR-10 12.00.02.000002 PM 20-MAR-10 12.00.06.000006 PM error 10 5 File does not exist 28-APR-10 12.00.24.000024 PM 28-APR-10 12.00.31.000031 PM error 11 5 script not found or 01-MAY-10 12.00.15.000015 PM 02-MAY-10 12.00.11.000011 PM error 12 5 user jsmith: authen 02-MAY-10 12.00.54.000054 PM 03-MAY-10 12.00.11.000011 PM error 13 4 File does not exist 09-MAY-10 12.00.46.000046 PM 10-MAY-10 12.00.01.000001 PM error 14 4 File does not exist 20-MAY-10 12.00.42.000042 PM 20-MAY-10 12.00.47.000047 PM error 15 4 user jsmith: authen 21-MAY-10 12.00.08.000008 PM 21-MAY-10 12.00.18.000018 PM error 16 4 File does not exist 24-MAY-10 12.00.07.000007 PM 25-MAY-10 12.00.01.000001 PM error 17 4 user jsmith: authen 12-JUN-10 12.00.00.000000 PM 12-JUN-10 12.00.07.000007 PM error 18 4 script not found or 12-JUN-10 12.00.18.000018 PM 13-JUN-10 12.00.01.000001 PM error 19 4 File does not exist 17-JUN-10 12.00.23.000023 PM 17-JUN-10 12.00.30.000030 PM error 20 5 File does not exist 21-JUN-10 12.00.31.000031 PM 22-JUN-10 12.00.01.000001 PM error 21 4 user jsmith: authen 22-JUN-10 12.00.36.000036 PM 22-JUN-10 12.00.56.000056 PM error 22 4 File does not exist 08-JUL-10 12.00.29.000029 PM 08-JUL-10 12.00.32.000032 PM error 23 6 user jsmith: authen 10-JUL-10 12.00.43.000043 PM 11-JUL-10 12.00.06.000006 PM error 24 4 File does not exist 12-JUL-10 12.00.09.000009 PM 12-JUL-10 12.00.22.000022 PM error 25 4 File does not exist 26-JUL-10 12.00.18.000018 PM 27-JUL-10 12.00.04.000004 PM error 26 4 File does not exist 03-AUG-10 12.00.02.000002 PM 03-AUG-10 12.00.11.000011 PM error 27 4 File does not exist 23-AUG-10 12.00.04.000004 PM 23-AUG-10 12.00.18.000018 PM error 28 5 File does not exist 24-AUG-10 12.00.09.000009 PM 26-AUG-10 12.00.00.000000 PM error 29 4 script not found or 09-SEP-10 12.00.03.000003 PM 09-SEP-10 12.00.09.000009 PM error 30 4 script not found or 11-SEP-10 12.00.22.000022 PM 11-SEP-10 12.00.31.000031 PM error 31 4 script not found or 23-SEP-10 12.00.09.000009 PM 23-SEP-10 12.00.16.000016 PM error 32 5 script not found or 17-OCT-10 12.00.02.000002 PM 18-OCT-10 12.00.09.000009 PM error 33 4 File does not exist 20-OCT-10 12.00.35.000035 PM 21-OCT-10 12.00.00.000000 PM error 34 5 File does not exist 21-OCT-10 12.00.16.000016 PM 21-OCT-10 12.00.35.000035 PM error 35 4 File does not exist 26-OCT-10 12.00.25.000025 PM 26-OCT-10 12.00.35.000035 PM error 36 4 user jsmith: authen 26-OCT-10 12.00.43.000043 PM 26-OCT-10 12.00.49.000049 PM error 37 4 user jsmith: authen 01-NOV-10 12.00.35.000035 PM 01-NOV-10 12.00.39.000039 PM error 38 4 File does not exist 09-NOV-10 12.00.46.000046 PM 10-NOV-10 12.00.09.000009 PM error 39 4 user jsmith: authen 11-NOV-10 12.00.14.000014 PM 11-NOV-10 12.00.30.000030 PM error 40 4 user jsmith: authen 22-NOV-10 12.00.46.000046 PM 23-NOV-10 12.00.07.000007 PM error 41 4 script not found or 03-DEC-10 12.00.14.000014 PM 03-DEC-10 12.00.27.000027 PM error 42 5 File does not exist 07-DEC-10 12.00.02.000002 PM 07-DEC-10 12.00.37.000037 PM error 43 4 user jsmith: authen 11-DEC-10 12.00.06.000006 PM 11-DEC-10 12.00.11.000011 PM error 44 4 user jsmith: authen 19-DEC-10 12.00.26.000026 PM 20-DEC-10 12.00.04.000004 PM error 45 4 user jsmith: authen 25-DEC-10 12.00.11.000011 PM 25-DEC-10 12.00.17.000017 PM error 46 4 File does not exist 04-JAN-11 12.00.09.000009 PM 04-JAN-11 12.00.19.000019 PM error 47 4 user jsmith: authen 10-JAN-11 12.00.23.000023 PM 11-JAN-11 12.00.03.000003 PM error 48 4 File does not exist 11-JAN-11 12.00.14.000014 PM 11-JAN-11 12.00.24.000024 PM notice 1 4 Child 3228: Release 08-JAN-10 12.00.38.000038 PM 09-JAN-10 12.00.02.000002 PM notice 2 4 Child 3228: Release 16-JAN-10 12.00.10.000010 PM 17-JAN-10 12.00.13.000013 PM notice 3 4 Child 1740: Startin 28-JAN-10 12.00.17.000017 PM 28-JAN-10 12.00.22.000022 PM notice 4 4 Child 1740: Child p 08-MAR-10 12.00.37.000037 PM 08-MAR-10 12.00.40.000040 PM notice 5 4 Child 3228: All wor 19-APR-10 12.00.10.000010 PM 19-APR-10 12.00.15.000015 PM notice 6 4 Child 1740: Acquire 02-MAY-10 12.00.38.000038 PM 02-MAY-10 12.00.46.000046 PM notice 7 4 Child 1740: Starting 09-MAY-10 12.00.03.000003 PM 09-MAY-10 12.00.08.000008 PM notice 8 4 Child 3228: Child pr 18-MAY-10 12.00.38.000038 PM 18-MAY-10 12.00.45.000045 PM notice 9 4 Child 3228: All work 25-JUL-10 12.00.04.000004 PM 25-JUL-10 12.00.09.000009 PM notice 10 4 Child 3228: All work 24-AUG-10 12.00.11.000011 PM 24-AUG-10 12.00.18.000018 PM notice 11 4 Child 1740: Starting 19-SEP-10 12.00.05.000005 PM 19-SEP-10 12.00.15.000015 PM notice 12 4 Child 1740: Acquired 06-OCT-10 12.00.07.000007 PM 06-OCT-10 12.00.13.000013 PM notice 13 4 Child 1740: Starting 09-JAN-11 12.00.12.000012 PM 09-JAN-11 12.00.18.000018 PM warn 1 3448 The ScriptAlias dire 01-JAN-10 12.00.00.000000 PM 17-JAN-11 12.00.18.000018 PM 62 rows selected.
例21-17 4つ以上の連続する認証失敗
この例では、IP開始アドレスに関係なく、4つ以上の連続する認証失敗を検索します。出力には、最初が5行、最後のものが4行の2つの一致が表示されています。
SELECT MR_SEC2.ERRTYPE AS Authen, MR_SEC2.MNO AS Pattern, MR_SEC2.CNT AS Count, MR_SEC2.IPADDR AS On_IP, MR_SEC2.TSTAMP AS Occurring_on FROM AUTHENLOG MATCH_RECOGNIZE( PARTITION BY errtype ORDER BY tstamp MEASURES COUNT(*) AS CNT, MATCH_NUMBER() AS MNO ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST W PATTERN ( S W{3,} ) DEFINE S AS S.message LIKE '%authenticat%', W AS W.message = PREV (W.message) ) MR_SEC2 ORDER BY Authen, Pattern, Count;
AUTHEN PATTERN COUNT ON_IP OCCURRING_ON ------ ------- --------- ------------ ---------------------------- error 1 1 10.111.112.3 02-MAY-10 12.00.54.000054 PM error 1 2 10.111.112.6 03-MAY-10 12.00.07.000007 PM error 1 3 10.111.112.6 03-MAY-10 12.00.08.000008 PM error 1 4 10.111.112.6 03-MAY-10 12.00.09.000009 PM error 1 5 10.111.112.6 03-MAY-10 12.00.11.000011 PM error 2 1 10.111.112.5 21-MAY-10 12.00.08.000008 PM error 2 2 10.111.112.6 21-MAY-10 12.00.16.000016 PM error 2 3 10.111.112.4 21-MAY-10 12.00.17.000017 PM error 2 4 10.111.112.6 21-MAY-10 12.00.18.000018 PM error 3 1 10.111.112.5 12-JUN-10 12.00.00.000000 PM error 3 2 10.111.112.4 12-JUN-10 12.00.04.000004 PM error 3 3 10.111.112.3 12-JUN-10 12.00.06.000006 PM error 3 4 10.111.112.3 12-JUN-10 12.00.07.000007 PM error 4 1 10.111.112.5 22-JUN-10 12.00.36.000036 PM error 4 2 10.111.112.5 22-JUN-10 12.00.50.000050 PM error 4 3 10.111.112.5 22-JUN-10 12.00.53.000053 PM error 4 4 10.111.112.6 22-JUN-10 12.00.56.000056 PM error 5 1 10.111.112.4 10-JUL-10 12.00.43.000043 PM error 5 2 10.111.112.6 10-JUL-10 12.00.48.000048 PM error 5 3 10.111.112.6 10-JUL-10 12.00.51.000051 PM error 5 4 10.111.112.3 11-JUL-10 12.00.00.000000 PM error 5 5 10.111.112.5 11-JUL-10 12.00.04.000004 PM error 5 6 10.111.112.3 11-JUL-10 12.00.06.000006 PM error 6 1 10.111.112.4 26-OCT-10 12.00.43.000043 PM error 6 2 10.111.112.4 26-OCT-10 12.00.47.000047 PM error 6 3 10.111.112.4 26-OCT-10 12.00.48.000048 PM error 6 4 10.111.112.5 26-OCT-10 12.00.49.000049 PM error 7 1 10.111.112.3 01-NOV-10 12.00.35.000035 PM error 7 2 10.111.112.5 01-NOV-10 12.00.37.000037 PM error 7 3 10.111.112.5 01-NOV-10 12.00.38.000038 PM error 7 4 10.111.112.3 01-NOV-10 12.00.39.000039 PM error 8 1 10.111.112.6 11-NOV-10 12.00.14.000014 PM error 8 2 10.111.112.5 11-NOV-10 12.00.20.000020 PM error 8 3 10.111.112.6 11-NOV-10 12.00.24.000024 PM error 8 4 10.111.112.3 11-NOV-10 12.00.30.000030 PM error 9 1 10.111.112.5 22-NOV-10 12.00.46.000046 PM error 9 2 10.111.112.5 22-NOV-10 12.00.51.000051 PM error 9 3 10.111.112.3 23-NOV-10 12.00.06.000006 PM error 9 4 10.111.112.3 23-NOV-10 12.00.07.000007 PM error 10 1 10.111.112.5 11-DEC-10 12.00.06.000006 PM error 10 2 10.111.112.4 11-DEC-10 12.00.07.000007 PM error 10 3 10.111.112.5 11-DEC-10 12.00.08.000008 PM error 10 4 10.111.112.6 11-DEC-10 12.00.11.000011 PM error 11 1 10.111.112.5 19-DEC-10 12.00.26.000026 PM error 11 2 10.111.112.5 20-DEC-10 12.00.01.000001 PM error 11 3 10.111.112.4 20-DEC-10 12.00.03.000003 PM error 11 4 10.111.112.3 20-DEC-10 12.00.04.000004 PM error 12 1 10.111.112.4 25-DEC-10 12.00.11.000011 PM error 12 2 10.111.112.4 25-DEC-10 12.00.12.000012 PM error 12 3 10.111.112.4 25-DEC-10 12.00.16.000016 PM error 12 4 10.111.112.3 25-DEC-10 12.00.17.000017 PM error 13 1 10.111.112.6 10-JAN-11 12.00.23.000023 PM error 13 2 10.111.112.6 11-JAN-11 12.00.00.000000 PM error 13 3 10.111.112.3 11-JAN-11 12.00.02.000002 PM error 13 4 10.111.112.4 11-JAN-11 12.00.03.000003 PM 55 rows selected.
例21-18 同じIPアドレスからの認証失敗
例21-18の問合せは例21-17とほぼ同じですが、3回以上連続して発生した認証失敗を同じIP開始アドレスから検索しています。
SELECT MR_S3.MNO AS Pattern, MR_S3.CNT AS Count, MR_S3.ERRTYPE AS Type, MR_S3.IPADDR AS On_IP_addr, MR_S3.START_T AS Starting_on, MR_S3.END_T AS Ending_on FROM AUTHENLOG MATCH_RECOGNIZE( PARTITION BY errtype ORDER BY tstamp MEASURES S.tstamp AS START_T, W.tstamp AS END_T, W.ipaddr AS IPADDR, COUNT(*) AS CNT, MATCH_NUMBER() AS MNO ONE ROW PER MATCH AFTER MATCH SKIP TO LAST W PATTERN ( S W{2,} ) DEFINE S AS S.message LIKE '%authenticat%', W AS W.message = PREV (W.message) AND W.ipaddr = PREV (W.ipaddr) ) MR_S3 ORDER BY Type, Pattern;
PATTERN COUNT TYPE ON_IP_ADDR STARTING_ON ENDING_ON ------- ----- ----- ------------ ---------------------------- ---------------------------- 1 4 error 10.111.112.6 03-MAY-10 12.00.07.000007 PM 03-MAY-10 12.00.11.000011 PM 2 3 error 10.111.112.5 22-JUN-10 12.00.36.000036 PM 22-JUN-10 12.00.53.000053 PM 3 3 error 10.111.112.4 27-JUN-10 12.00.03.000003 PM 27-JUN-10 12.00.08.000008 PM 4 3 error 10.111.112.6 19-JUL-10 12.00.15.000015 PM 19-JUL-10 12.00.17.000017 PM 5 3 error 10.111.112.4 26-OCT-10 12.00.43.000043 PM 26-OCT-10 12.00.48.000048 PM 6 3 error 10.111.112.4 25-DEC-10 12.00.11.000011 PM 25-DEC-10 12.00.16.000016 PM 7 3 error 10.111.112.5 12-JAN-11 12.00.01.000001 PM 12-JAN-11 12.00.08.000008 PM 7 rows selected.
21.6.3 パターン一致の例: セッション化
セッション化とは、通常は1つのセッションで複数のイベントが関与するユーザー・アクティビティの個別のセッションを定義するプロセスです。パターン一致によって、セッション化のための問合せの表現が容易になります。たとえば、Webサイトへのビジターが標準的なセッション中に何ページを閲覧するか把握したい場合があります。通信プロバイダの場合は、2人のユーザー間の電話セッションで接続が切れてユーザーがリダイアルする場合の特徴を把握することもあります。企業は、ユーザー・セッション動作を理解することによって重要な価値を引き出すことができます。これは、サービスの提供と向上、価格設定、マーケティングなどを定義するうえで、企業にとって役立つためです。
次の例では、Webサイトのクリックストリームに関連したセッション化の導入例を2つ示し、さらに電話に関わる例を示しています。
例21-19 クリックストリーム・データに関する単純なセッション化
例21-19では、クリックストリーム・データを分析するための単純なセッション化を説明しています。一連の行の目的は、セッションを検出し、各セッションにセッションIDを割り当てて、各入力行をセッションIDとともに表示することです。次のデータは、すべてのページ・リクエストを追跡するWebサーバーのシステム・ログから得られます。それぞれの行がページをリクエストするユーザーのイベントである行セットから開始します。この単純な例では、ユーザーIDであるパーティション・キーと、ユーザーがページをリクエストした時間を示すタイムスタンプがデータに含まれています。Webシステム・ログでは、指定のページをユーザーがリクエストした時期が表示されますが、そのページの閲覧をユーザーが停止した時期は示されません。
例21-19では、セッションは、同じパーティション・キー(User_ID
)を持つ1つ以上の時系列の行のシーケンスで、タイムスタンプ間の時間差は指定のしきい値未満であるものとして定義されています。この場合、しきい値は10時間単位です。行のタイムスタンプが10単位より離れている場合は、別個のセッションの行と見なされます。ここで使用されている10単位のしきい値は任意の数字です。実際のケースごとに、アナリストの判断で最適なしきい値の時間差を決める必要があります。Webサイト訪問のセッションを分離する場合、履歴的にみて、30分という時間差が一般にしきい値として使用されています。
最初に、クリックストリーム・イベントの表を作成してください。
CREATE TABLE Events( Time_Stamp NUMBER, User_ID VARCHAR2(10) );
次に、データを挿入します。次の挿入文は順序付けられており、読み取りやすいように空白を入れて、パーティションとセッションを確認できるようにしています。実際には、イベントはタイムスタンプの順序で到着し、異なるユーザー・セッションの行が混在しています。
INSERT INTO Events(Time_Stamp, User_ID) VALUES ( 1, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (11, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (23, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (34, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (44, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (53, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (63, 'Mary'); INSERT INTO Events(Time_Stamp, User_ID) VALUES ( 3, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (13, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (23, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (33, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (43, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (54, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (63, 'Richard'); INSERT INTO Events(Time_Stamp, User_ID) VALUES ( 2, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (12, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (22, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (32, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (43, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (47, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (48, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (59, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (60, 'Sam'); INSERT INTO Events(Time_Stamp, User_ID) VALUES (68, 'Sam');
次の行パターン一致の問合せでは、それぞれの入力行がSession_ID
とともに表示されます。前述したように、イベントの分離が10時間単位以下であれば、それらのイベントは同じセッションに含まれていると見なされます。このセッションしきい値は、パターン変数のDEFINE
句で表されます。
SELECT time_stamp, user_id, session_id FROM Events MATCH_RECOGNIZE (PARTITION BY User_ID ORDER BY Time_Stamp MEASURES match_number() AS session_id ALL ROWS PER MATCH PATTERN (b s*) DEFINE s AS (s.Time_Stamp - prev(Time_Stamp) <= 10) ) ORDER BY user_id, time_stamp;
出力は次のようになります。
TIME_STAMP USER_ID SESSION_ID ---------- ---------- ---------- 1 Mary 1 11 Mary 1 23 Mary 2 34 Mary 3 44 Mary 3 53 Mary 3 63 Mary 3 3 Richard 1 13 Richard 1 23 Richard 1 33 Richard 1 43 Richard 1 54 Richard 2 63 Richard 2 2 Sam 1 12 Sam 1 22 Sam 1 32 Sam 1 43 Sam 2 47 Sam 2 48 Sam 2 59 Sam 3 60 Sam 3 68 Sam 3 24 rows selected.
例21-20 集計による単純なセッション化
例21-19に示すように詳細レベルの行にセッション番号を割り当てると、ただちに分析プロセスが開始されます。セッション化データのビジネス値は、セッション別の集計後でないと出力されません。
この例では、データが集計され、セッションごとに、Session_ID
、User_ID
、セッションごとの集計イベント数および合計セッション期間の列を含む行が1行出力されます。この出力によって、各ユーザーがセッションごとに行ったクリック回数と、各セッションの持続時間を容易に確認できます。また、この問合せからのデータを利用して、セッション期間の最大値、最小値、平均値など、その他の多くの分析を実行することもできます。
SELECT session_id, user_id, start_time, no_of_events, duration FROM Events MATCH_RECOGNIZE (PARTITION BY User_ID ORDER BY Time_Stamp MEASURES MATCH_NUMBER() session_id, COUNT(*) AS no_of_events, FIRST(time_stamp) start_time, LAST(time_stamp) - FIRST(time_stamp) duration PATTERN (b s*) DEFINE s AS (s.Time_Stamp - PREV(Time_Stamp) <= 10) ) ORDER BY user_id, session_id;
出力は次のようになります。
SESSION_ID USER_ID START_TIME NO_OF_EVENTS DURATION ---------- ---------- ---------- ------------ ---------- 1 Mary 1 2 10 2 Mary 23 1 0 3 Mary 34 4 29 1 Richard 3 5 40 2 Richard 54 2 9 1 Sam 2 4 30 2 Sam 43 3 5 3 Sam 59 3 9 8 rows selected.
例21-21 接続の切断を伴う電話のセッション化
クリックストリーム・データを示した例21-19および例21-20では、ページ閲覧の終了時間を示す明示的な終了点がソース・データにありませんでした。ユーザー・アクティビティの明確な終了点が指定されたとしても、終了点では、ユーザーがセッションを終了しようとしていることを示していない場合もあります。携帯電話サービスの使用中に接続が切れたユーザーを考えてみます。通常、このユーザーはリダイアルして通話を続けます。このシナリオでは、同じ電話番号のペアが関連する複数の通話が1回の電話セッションに含まれると考える必要があります。
例21-21に、電話のセッション化を示します。この例では通話の詳細レコード・データをセッション化のベースに使用します。ここで通話データ・レコード行にはStart_Time
、End_Time
、Caller_ID
、Callee_ID
が含まれます。後に示す問合せでは、次の処理が行われます。
-
データを
caller_id
およびcallee_id
によってパーティション化します。 -
連続する通話間の時間差が60秒というしきい値以内の場合に、発信者から受信者への通話を1つのセッションにグループ化するセッションを検索します。このしきい値は、パターン変数
B
のDEFINE
句で指定されます。 -
セッションごとに、次の値を返します(
MEASURES
句を参照してください)。-
session_id
、発信者および受信者 -
セッション内で通話が再開された回数
-
合計有効通話時間(セッション中に電話が接続されていた合計時間)
-
合計中断期間(セッション中に電話が切断されていた合計時間)
-
SELECT Caller, Callee, Start_Time, Effective_Call_Duration, (End_Time - Start_Time) - Effective_Call_Duration AS Total_Interruption_Duration, No_Of_Restarts, Session_ID FROM my_cdr MATCH_RECOGNIZE ( PARTITION BY Caller, Callee ORDER BY Start_Time MEASURES A.Start_Time AS Start_Time, End_Time AS End_Time, SUM(End_Time - Start_Time) AS Effective_Call_Duration, COUNT(B.*) AS No_Of_Restarts, MATCH_NUMBER() AS Session_ID PATTERN (A B*) DEFINE B AS B.Start_Time - PREV(B.end_Time) < 60 );
前出の問合せでは大量のデータを意味のあるものにする必要があり、それには相当の領域が消費されるため、ここではINSERT
文は含まれていません。サンプル出力は次のようになります。
SQL> desc my_cdr Name Null? Type -------------- ---------- ---------- CALLER NOT NULL NUMBER(38) CALLEE NOT NULL NUMBER(38) START_TIME NOT NULL NUMBER(38) END_TIME NOT NULL NUMBER(38) SELECT * FROM my_cdr ORDER BY 1, 2, 3, 4; CALLER CALLEE START_TIME END_TIME ------ ------ ---------- --------- 1 7 1354 1575 1 7 1603 1829 1 7 1857 2301 1 7 2320 2819 1 7 2840 2964 1 7 64342 64457 1 7 85753 85790 1 7 85808 85985 1 7 86011 86412 1 7 86437 86546 1 7 163436 163505 1 7 163534 163967 1 7 163982 164454 1 7 214677 214764 1 7 214782 215248 1 7 216056 216271 1 7 216297 216728 1 7 216747 216853 1 7 261138 261463 1 7 261493 261864 1 7 261890 262098 1 7 262115 262655 1 7 301931 302226 1 7 302248 302779 1 7 302804 302992 1 7 303015 303258 1 7 303283 303337 1 7 383019 383378 1 7 383407 383534 1 7 424800 425096 30 rows selected. CALLER CALLEE START_TIME EFFECTIVE_CALL TOTAL_INTERUPTION NO_OF_RE SESSION_ID ------ ------- --------- -------------- ----------------- -------- ---------- 1 7 1354 1514 96 4 1 1 7 64342 115 0 0 2 1 7 85753 724 69 3 3 1 7 163436 974 44 2 4 1 7 214677 553 18 1 5 1 7 216056 752 45 2 6 1 7 261138 1444 73 3 7 1 7 301931 1311 95 4 8 1 7 383019 486 29 1 9 1 7 424800 296 0 0 10 10 rows selected.
21.6.4 パターン一致の例: 会計トラッキング
一般的な会計アプリケーションでは、疑わしい会計パターンを検索します。例21-22は、異常であると定義した特定の基準を満たしているため、疑わしく見える資金移動を検出する方法を示しています。
例21-22 疑わしい資金移動
例21-22では、資金の移動時に疑わしく見えるパターンを検索しています。このケースでは、それを、30日以内に3回以上の少額($2000未満)の資金移動があり、続けて最後の少額の資金移動から10日以内に大量の($1,000,000を上回る)資金移動が発生した場合と定義しています。話を分かりやすくするために、表とデータは非常に基本的なものになっています。
最初に、必要なデータを含む表を作成します。
CREATE TABLE event_log ( time DATE, userid VARCHAR2(30), amount NUMBER(10), event VARCHAR2(10), transfer_to VARCHAR2(10));
その後、データをevent_log
に挿入します。
INSERT INTO event_log VALUES (TO_DATE('01-JAN-2012', 'DD-MON-YYYY'), 'john', 1000000, 'deposit', NULL); INSERT INTO event_log VALUES (TO_DATE('05-JAN-2012', 'DD-MON-YYYY'), 'john', 1200000, 'deposit', NULL); INSERT INTO event_log VALUES (TO_DATE('06-JAN-2012', 'DD-MON-YYYY'), 'john', 1000, 'transfer', 'bob'); INSERT INTO event_log VALUES (TO_DATE('15-JAN-2012', 'DD-MON-YYYY'), 'john', 1500, 'transfer', 'bob'); INSERT INTO event_log VALUES (TO_DATE('20-JAN-2012', 'DD-MON-YYYY'), 'john', 1500, 'transfer', 'allen'); INSERT INTO event_log VALUES (TO_DATE('23-JAN-2012', 'DD-MON-YYYY'), 'john', 1000, 'transfer', 'tim'); INSERT INTO event_log VALUES (TO_DATE('26-JAN-2012', 'DD-MON-YYYY'), 'john', 1000000, 'transfer', 'tim'); INSERT INTO event_log VALUES (TO_DATE('27-JAN-2012', 'DD-MON-YYYY'), 'john', 500000, 'deposit', NULL);
次に、この表を問い合せることができます。
SELECT userid, first_t, last_t, amount FROM (SELECT * FROM event_log WHERE event = 'transfer') MATCH_RECOGNIZE (PARTITION BY userid ORDER BY time MEASURES FIRST(x.time) first_t, y.time last_t, y.amount amount PATTERN ( x{3,} y ) DEFINE x AS (event='transfer' AND amount < 2000), y AS (event='transfer' AND amount >= 1000000 AND LAST(x.time) - FIRST(x.time) < 30 AND y.time - LAST(x.time) < 10)); USERID FIRST_T LAST_T AMOUNT ---------- --------- --------- ------- john 06-JAN-12 26-JAN-12 1000000
この文では、1つ目の太字テキストが少額の資金移動を示し、2つ目の太字テキストが大量の資金移動を示し、3つ目の太字テキストが30日以内に少額の資金移動が発生したことを示し、4つ目の太字テキストが最後の少額の資金移動から10日以内に大量の資金移動が発生したことを示しています。
この文をさらに改良して、次のように、疑わしい資金移動の受取人を含めるようにすることができます。
SELECT userid, first_t, last_t, amount, transfer_to FROM (SELECT * FROM event_log WHERE event = 'transfer') MATCH_RECOGNIZE (PARTITION BY userid ORDER BY time MEASURES z.time first_t, y.time last_t, y.amount amount, y.transfer_to transfer_to PATTERN ( z x{2,} y ) DEFINE z AS (event='transfer' AND amount < 2000), x AS (event='transfer' AND amount <= 2000 AND PREV(x.transfer_to) <> x.transfer_to), y AS (event='transfer' AND amount >= 1000000 AND LAST(x.time) - z.time < 30 AND y.time - LAST(x.time) < 10 AND SUM(x.amount) + z.amount < 20000); USERID FIRST_T LAST_T AMOUNT TRANSFER_TO ---------- --------- --------- ------- ----------- john 15-JAN-12 26-JAN-12 1000000 tim
この文では、1つ目の太字テキストが最初の少額の資金移動を示し、次の太字テキストが異なる口座への2回以上の少額の資金移動を示し、3つ目の太字テキストが$20,000未満の少額の資金移動すべての合計を示しています。