2023年10月17日火曜日

SQLのMATCH_RECOGNIZEを呼び出すサンプル・アプリケーションを作る

SQLでは正規表現によるパターン一致を行うために、MATCH_RECOGNIZE句が定義されています。オラクル・データベースでは、12cからMATCH_RECOGNIZE句が使えるようになりました。

MATCH_RECOGNIZE句の使い方は、以下のドキュメントに記載されています。

データ・ウェアハウス・ガイド Release 19

AskTOMのOffice Hourで、Chris Saxonさんが初心者向けの解説を行っています。

Visualizing SQL Pattern Matching (MATCH_RECOGNIZE) - A Beginner's Guide
https://asktom.oracle.com/pls/apex/asktom.search?oh=16823

MATCH_RECOGNIZE句ではパターン一致の検出に正規表現を使っていることもあり、なかなか理解が難しいです。

MATCH_RECOGNIZE句を理解するために、テスト・データを生成するAPEXアプリケーションを作成してみました。

MATCH_RECOGNIZE句を含んだSQLとして以下を記述します。申請が２回以上却下され、また、却下される度に金額を増加させて再申請し、最終的に申請が通ったケースを見つけています。

select mr_rt.*
from mrec_request_transitions_v
match_recognize(
partition by request_id
order by transition_date
measures
count(*) as cnt,
classifier() as cls,
match_number() as mno
all rows per match
after match skip to last p
pattern ( (r s){2,} a p )
define
r as r.status = 'REJECTED'
,s as (s.status = 'SUBMITTED' and amount > prev(amount))
,a as a.status = 'APPROVED'
,p as p.status = 'PAID'
) mr_rt
view raw match_recognize_sample.sql hosted with ❤ by GitHub
作成したアプリケーションは以下のように動作します。


本記事では、MATCH_RECOGNIZE句を使うアプリケーションの作り方を紹介します。MATCH_RECOGNIZE句自体については、前出のドキュメントやAskTOMのセミナーなどを参照してください。

最初に、アプリケーションで使用する表を準備します。

クイックSQLの以下のモデルから、表MREC_REQUESTS、MREC_REQUEST_TRANSITIONS、MREC_STATUSES、MREC_STATUS_CATEGORIESを作成します。

# prefix: mrec
statuses
status vc16 /nn /check SUBMITTED,APPROVED,REJECTED,PAID,WITHDRAWN
prev_status_id /fk statuses
status_categories
status_id /fk statuses
category vc80 /check OPEN,CLOSED
requests
requested_by vc80 /nn
reason vc200 /nn
amount num /nn
approved_by vc80
status_id /fk statuses
request_transitions
request_id /fk requests
reason vc200 /nn
amount num /nn
approved_by vc80
status_id /fk statuses
transition_date /nn /default sysdate
view raw model-for-match-recognize-sample.model hosted with ❤ by GitHub
それぞれの表の役割は以下になります。
  • 表MREC_REQUESTSは、提出された申請を保持します。
  • 表MREC_REQUEST_TRANSITIONSは、申請に対して行われた更新の履歴を保持します。
  • 表MREC_STATUSESは、申請が取り得るステータスを保持するマスター表です。
  • 表MREC_STATUS_CATEGORIESは、申請のステータスの分類分けを保持します。
APEXアプリケーションは、表MREC_REQUESTSに保存されている申請を更新します。更新を行なった後で、更新履歴を表MREC_REQUEST_TRANSITIONSに保存します。MATCH_RECOGNIZEを使ったパターン一致は、表MREC_REQUEST_TRANSITIONSに保存されている時系列のデータを対象にして実施します。

SQLワークショップのクイックSQLを開き左ペインにモデルを記述し、SQLの生成、SQLスクリプトを保存、レビューおよび実行を行います。


SQLスクリプトが開きます。スクリプトの変更は不要なので、そのまま実行をクリックします。確認のダイアログが開くので、即時実行をクリックします。


４つの表と５つの付帯する索引が作成されます。


この画面では、アプリケーションの作成は行いません。

アプリケーション作成ウィザードを起動します。アプリケーションの名前はMATCH_RECOGNIZEサンプルとします。ホームの編集を開き、ホーム・ページを削除します。


ページの追加をクリックし、追加ページに含まれている複数のレポートを選択します。


表のプリフィックスであるMREC_で検索し、検索されたMREC_REQUESTS、MREC_REQUEST_TRANSITIONS、MREC_STATUSES、MREC_STATUS_CATEGORIESをチェックします。

ページの追加を実行します。


選択した４つの表をソースとした、フォーム付きの対話モード・レポートのページが追加されます。


ページ名がRequests（ソースが表MREC_REQUESTS）の編集を開き、詳細のホームページとして設定にチェックが入っていることを確認します。


Requests以外のページは、すべて詳細の管理ページとして設定にチェックを入れます。


ページのアイコンがそれぞれ家またはスパナになっていることが確認します。

アプリケーションの作成を実行します。


アプリケーションが作成されます。

今回の作業では、表MREC_REQUESTSの更新を行なうフォームのページである、ページ番号２のページだけを編集します。その他のページはそのまま使用します。


アプリケーションを実行し、表MREC_STATUSESとMREC_STATUS_CATEGORIESにデータを投入します。

ナビゲーション・メニューの管理からStatusesを開きます。


作成をクリックします。


StatusとしてSUBMITTEDを入力します。この時点では他のステータスは登録されていないため、Prev Status（SUBMITTEDに遷移できるステータスを設定します）は空白にします。

作成をクリックします。


続けて作成を行います。

StatusとしてAPPROVED、Prev StatusとしてSUBMITTEDを選択し、作成を実行します。


同様に、以下の組み合わせでステータスを作成します。

Status - REJECTED, Prev Status SUBMITTED
Status - PAID, Prev Status APPROVED
Status - WITHDRAWN, Prev Status REJECTED

最後にステータスSUBMITTEDを開き、Prev StatusとしてREJECTEDを設定します。


表MREC_STATUSESに保存されたデータは、以下のようになります。申請の状態遷移の定義になっています。


表MREC_STATUS_CATEGORIESについても、データを入力します。

管理のStatus Categoriesを開きます。


作成をクリックします。


StatusとしてAPPROVEDを選択し、CategoryにOPENを入力します。作成をクリックします。


作成を繰り返し、以下のデータを入力します。

Status - APPROVED, Category - OPEN
Status - PAID, Category - CLOSED
Status - REJECTED, Category - OPEN
Status - SUBMITTED, Category - OPEN
Status - WITHDRAWN, Category - CLOSED

表MREC_STATUS_CATEGORIESに保存されたデータは以下のようになります。

以上でステータスに関するマスター・データの登録は完了しました。

ページ・デザイナでページ番号２のRequestのページを開きます。

現時点でのステータスを保持するページ・アイテムP2_PREV_STATUS_IDを作成します。

タイプは非表示、セッション・ステートのストレージとしてリクエストごと(メモリーのみ)を選択します。

この後に記述するコードでP2_PREV_STATUS_IDを参照するため、保存をクリックします。


レンダリング前のヘッダー前にあるプロセス初期化フォームRequestの直下に、新たにプロセスを作成します。

識別の名前はステータスの保存、タイプとしてコードを実行を選択します。ソースのPL/SQLコードとして以下を記述します。

:P2_PREV_STATUS_ID := :P2_STATUS_ID;

現時点でのステータスを、ページ・アイテムP2_PREV_STATUS_IDへ保存しています。


ページ・アイテムP2_STATUS_IDを選択します。

LOVのタイプをSQL問合せに変更し、SQL問合せとして以下を記述します。現在のステータスに従って、LOVで選択可能なステータスを制限します。
select status d, id r from mrec_statuses
where
    (:P2_ID is null and status = 'SUBMITTED')
    or
    (:P2_ID is not null and prev_status_id = :P2_PREV_STATUS_ID)
追加値の表示、NULL値の表示はともにオフにします。


申請のステータスに応じて、ページ・アイテムの入力を制限します。

ページ・アイテムP2_REQUESTED_BYの読取り専用は、タイプにアイテムはNULLではないを選択し、アイテムにP2_IDを指定します。ページ・アイテムP2_IDに値がないのは申請を新規作成するときだけです。一旦申請を作成した後は、申請者は変更不可になります。


ページ・アイテムP2_REASONとP2_AMOUNTは、申請を新規作成するときと却下された後に再申請を行なうときに限り、編集できるようにします。

２つのページ・アイテムを選択します。読取り専用のタイプとして行が返されない、SQL問合せとして以下を記述します。
select 1 from dual where :P2_ID is null
union
select 1 from mrec_statuses
where id = :P2_PREV_STATUS_ID and status = 'REJECTED'

ページ・アイテムP2_APPROVED_BYを選択します。

読取り専用のタイプは行が返されない、SQL問合せとして以下を記述します。
select 1 from mrec_statuses
where id = :P2_PREV_STATUS_ID and status = 'SUBMITTED'
申請のステータスがSUBMITTEDのときに1が返されます。ステータスSUBMITTEDからAPPROVEDもしくはREJECTEDに状態遷移するため、その際にはAPPROVED_BYを入力できます。


左ペインでプロセス・ビューを開きます。

申請の更新履歴を保存するプロセスを作成します。作成したプロセスはプロセスプロセス・フォームRequestの直下に配置します。

識別の名前は更新履歴保存とします。タイプはコードを実行、ソースのPL/SQLコードとして以下のINSERT文を記述します。
insert into mrec_request_transitions(
    request_id, reason, amount, approved_by, status_id
) values (
    :P2_ID, :P2_REASON, :P2_AMOUNT, :P2_APPROVED_BY, :P2_STATUS_ID
);
サーバー側の条件のタイプとしてリクエストは値に含まれるを選択し、値としてCREATE SAVEを指定します。ボタンCREATEまたはSAVEをクリックしたときに実行されます（DELETEでは実行しない）。


以上でテスト・データを入力する部分はできました。

MATCH_RECOGNIZE句を使った問合せを行なうページを作成します。

SELECT文の記述を簡素化するため、ビューMREC_REQUEST_TRANSITIONS_Vを作成します。

以下のコードを実行します。

create or replace view mrec_request_transitions_v
as
select
t.id
,t.request_id
,r.requested_by
,t.reason
,t.amount
,t.approved_by
,t.transition_date
,s.status
,c.category
from mrec_request_transitions t
join mrec_requests r on t.request_id = r.id
join mrec_statuses s on t.status_id = s.id
join mrec_status_categories c on s.id = c.status_id
view raw mrec_request_transitions_v.sql hosted with ❤ by GitHub


MATCH_RECOGNIZEを使ったレポートのページを追加します。

ページの作成を実行します。


対話モード・レポートを選択します。


ページ定義の名前はMREC_RECOGNIZEとします。ページ・モードは標準、フォーム・ページを含めるはオフです。

データ・ソースのソース・タイプとしてSQL問合せを選択し、SQL SELECT文を入力に記事の先頭にあるMATCH_RECOGINIZE句を含んだSELECT文を記述します。ナビゲーションはデフォルトから変更せず、ブレッドクラムの使用、ナビゲーションの使用ともにオンとします。

ページの作成をクリックします。


ページが作成されると、アプリケーションは完成です。

アプリケーションを実行すると記事の先頭のGIF動画のように動作します。

現在のアプリケーションでは、MATCH_RECOGNIZE句の正規表現を含む条件の変更は、ページ・デザイナより実施する必要があります。対話モード・レポートのソースのタイプをSQL問合せからSQL問合せを返すファンクション本体に変更することで、ソースとして実行するSELECT文を動的に変えることができるようになります。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/sample-match-recognize.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
投稿者 時刻:
ラベル: