Oracle 12cのパターンマッチングSQL(MATCH_RECOGNIZE)を使って、ズンドコ!

パターンマッチング

パターンマッチングを行うことによって、たとえば機器ログでのDouble Bottom(W)パターンを検出したり、株式変動における価格パターンを識別できます。MATCH_RECOGNIZE句を利用することによって、SQLが苦手だったテーブルの行データに対しても特定の数値のパターンや文字列の出現順序に柔軟に対応することができます。

流行りに乗って手元のOracle Database Cloud環境でズンドコキヨシを試してみたいと思います。
今回はパターンマッチングがメインなので、plsqlでのデータ作成はPL/SQLでズンドコキヨシを参考にさせていただきました。

そもそもズンドコキヨシってなんだという方はこちらにまとめ記事がありますので、ご覧下さい。元ネタはこちら。

「ズン」「ドコ」のいずれかをランダムで出力し続けて「ズン」「ズン」「ズン」「ズン」「ドコ」の配列が出たら「キ・ヨ・シ！」って出力した後終了

OracleDatabase12c

--事前にテーブルを落とします
DROP TABLE zundokokiyoshi;
--ズンドコ用テーブルを作成
CREATE TABLE zundokokiyoshi(rhythm NUMBER(30,0),word VARCHAR2(20));
--データ生成とパターンマッチングをPL/SQLで実装
DECLARE
  vstr VARCHAR2(20);
  vexit INTEGER := 0;
  i INTEGER := 0;
BEGIN
  DBMS_RANDOM.SEED(TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS.FF'));
  LOOP
    i := i + 1;
    vstr := CASE MOD(TRUNC(DBMS_RANDOM.VALUE*10), 2) WHEN 0 THEN 'ズン！' ELSE 'ドコ！' END;
    INSERT INTO zundokokiyoshi(rhythm,word) VALUES (i,vstr);
    SELECT COUNT(*) INTO vexit FROM (
      SELECT rhythm,word 
      FROM zundokokiyoshi 
      MATCH_RECOGNIZE(
        ORDER BY rhythm
        MEASURES zun.rhythm AS zunrhythm,
                 MATCH_NUMBER() AS match_num
        ALL ROWS PER MATCH
        PATTERN (zun zun zun zun doco)
        DEFINE
        zun AS zun.word = 'ズン！',
        doco AS doco.word = 'ドコ！'
      ) mr
      ORDER BY rhythm
    )T;
    EXIT WHEN vexit = 5;
  END LOOP;
  INSERT INTO zundokokiyoshi(word) VALUES ('キ・ヨ・シ！');
  COMMIT;
END;
/
--結果の確認!
SELECT word FROM zundokokiyoshi;

ポイントとなるのは、PATTERN (zun zun zun zun doco)と記したところで、ここの記述を少し変えればいろいろなパターン検出に応答できます。

OracleDatabase12cクエリ部分だけ

SELECT rhythm, word 
FROM zundokokiyoshi 
MATCH_RECOGNIZE(
    ORDER BY rhythm
    MEASURES zun.rhythm AS zunrhythm,
             MATCH_NUMBER() AS match_num
    ALL ROWS PER MATCH
    PATTERN (zun zun zun zun doco)
    DEFINE
    zun AS zun.word = 'ズン！',
    doco AS doco.word = 'ドコ！'
) mr
ORDER BY rhythm

クエリ部分出力結果例

	RHYTHM	WORD
1	50	ズン！
2	51	ズン！
3	52	ズン！
4	53	ズン！
5	54	ドコ！

PATTERN (zun zun zun zun doco)と記したところと同じパターンが抽出できています。今回はズンドコで行いましたがもちろん数字でも対応できるので、より複雑なパターンマッチングも簡単にMATCH_RECOGNIZE句で書くことができます。