日本語PDF

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 株価チャート

「図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 日付とパターン変数とのマッピングを示す株価チャート

「図 21-2 日付とパターン変数とのマッピングを示す株価チャート」の説明

図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 メジャーが対応している日付を示す株価チャート

「図21-3 メジャーが対応している日付を示す株価チャート」の説明

図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句で、次のステップが実行されます。

1. 行パターン入力表がPARTITION BY句に従ってパーティション化されます。各パーティションは、パーティション化列の値と同じ値が設定された入力表の行のセットで構成されています。

2. 行パターンの各パーティションは、ORDER BY句に従って順序付けられます。

3. 順序付けられた行パターンの各パーティションは、PATTERNとの一致がないか検索されます。

4. パターン一致は、ORDER BY句で指定した順序で行パターン・パーティションの行を考慮し、最初の行に出現する一致をシークすることによって実行されます。

   一連の行でのパターン一致は増分プロセスであり、パターンに一致するかどうかを1行ずつ順次調べていきます。この増分処理モデルでは、完全なパターンが認識されるまでは、どのステップにおいても部分的な一致を認識できたにすぎず、将来どんな行が追加されるか、その行にどんな変数がマップされるかは不明です。

   最初の行で一致が見つからない場合は、パーティション内の次の行に検索が進み、その行から開始して一致が見つかるかどうか確認されます。

5. 一致が見つかると、行パターン一致によって行パターンのメジャー列が計算されます。これは、MEASURES句で定義された式に相当します。

6. 最初の例で示したように、パターン一致でONE ROW PER MATCHを使用すると、見つかった一致ごとに1行が生成されます。ALL ROWS PER MATCHを使用した場合、一致した行はすべてパターン一致の出力に含められます。

7. 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 パターン一致の詳細

この項では、パターン一致の構文で説明した項目の詳細に加えて、追加のトピックについても説明しています。内容の一部がやむを得ず複雑になっていることに留意してください。パターン一致には、難解な詳細に特別の注意が求められる場合があります。

• PARTITION BY: 行を論理的にグループに分割する

• ORDER BY: パーティション内の行を論理的に順序付ける

• [ONE ROW | ALL ROWS] PER MATCH: 一致ごとにサマリーまたは詳細を選択する

• MEASURES: 問合せに使用する計算を定義する

• PATTERN: 一致する行パターンを定義する

• SUBSET: 共用体行パターン変数を定義する

• DEFINE: プライマリ・パターン変数を定義する

• AFTER MATCH SKIP: 一致が見つかった後の一致プロセスの再開場所を定義する

• MEASURESおよびDEFINEの式

• 行パターンの出力

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句のデフォルトは実行中のセマンティクスですが、最終セマンティクスもサポートしています。この区別については、実行中セマンティクスと最終セマンティクスの比較で説明しています。

式の使用

この項では、パターン一致で式を使用する際の考慮点について説明します。内容は次のとおりです。

• MATCH_NUMBER: どの行がどの一致にあるかを検索する

• CLASSIFIER: どのパターン変数がどの行に適用されるかを検索する

• 行パターンの列参照

• 集計

• 行パターンのナビゲーション操作

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.3.10.1 相関名および行パターンの出力

行パターンの出力表に、次のような相関名を割り当てることができます。

SELECT M.Matchno
FROM Ticker MATCH_RECOGNIZE (...
     MEASURE MATCH_NUMBER() AS Matchno
     ...
) M

この例では、Mが行パターンの出力表に割り当てられた相関名です。相関名を割り当てる利点は、相関名を使用すると、この例のM.Matchnoのように、行パターンの出力表の列名を修飾できることです。FROM句に他の表がある場合、これはあいまいな列名を解決する際に特に重要です。

21.4 パターン一致の高度なトピック

この項では、次の高度なトピックについて説明します。

• パターン一致におけるPREVおよびNEXT内でのFIRSTとLASTのネスト

• パターン一致における空の一致または一致しない行の処理

• パターンの部分を出力から除外する方法

• すべての順列の表現方法

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)である必要があります。

この複合演算子は、次のように評価されます。

1. 内部演算子LASTは、パターン変数Aにマップされた行のセットのみで処理を行います。このセットで、最後から1つ前の行を検索します。(該当する行がない場合、結果はNULLになります。)
2. 外部演算子PREVは、行パターン・パーティションで、ステップ1で見つかった行から3行前まで戻ります。(該当する行がない場合、結果はNULLになります。)
3. 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 パターン一致のルールと制限

この項では、次のルールと制限について説明します。

• パターン一致における入力表の要件

• MATCH_RECOGNIZE句で禁止されたネスト

• 連結MATCH_RECOGNIZE句

• 集計の制限

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
   ...)

関連項目:

『Oracle Database SQL言語リファレンス』

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.5.4 集計の制限

集計関数COUNT、SUM、AVG、MAXおよびMINは、MEASURES句とDEFINE句の両方で使用できます。DISTINCTキーワードはサポートされていません。

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未満の少額の資金移動すべての合計を示しています。