はじめに
下記のテーブルはOracle FreeSQLのHRスキーマCOUNTRIESテーブルのデータです。
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
|---|---|---|
| AR | Argentina | 20 |
| AU | Australia | 40 |
| BE | Belgium | 10 |
| BR | Brazil | 20 |
| CA | Canada | 20 |
| CH | Switzerland | 10 |
| CN | China | 30 |
| DE | Germany | 10 |
| DK | Denmark | 10 |
| EG | Egypt | 50 |
| FR | France | 10 |
| GB | United Kingdom of Great Britain and Northern Ireland | 10 |
| IL | Israel | 30 |
| IN | India | 30 |
| IT | Italy | 10 |
| JP | Japan | 30 |
| KW | Kuwait | 30 |
| ML | Malaysia | 30 |
| MX | Mexico | 20 |
| NG | Nigeria | 50 |
| NL | Netherlands | 10 |
| SG | Singapore | 30 |
| US | United States of America | 20 |
| ZM | Zambia | 50 |
| ZW | Zimbabwe | 50 |
このテーブルから、「REGION_IDが20のデータを抽出して下さい」と言われたら、どのようなSQLクエリーをかきますか？
よろしければ、Oracle FreeSQLを使用して、いま実際にかいてみて下さい。
環境構築やデータ作成、アカウント発行することなく、すぐに利用できます。
https://freesql.com/
おそらく、100人中100人が以下のSQLクエリーをかくと思います。
SELECT
*
FROM
hr.countries
WHERE
region_id = 20
ここで、同じ結果になるようなSQLクエリーをWHERE句無しで実装してと言われたらどのようにかきますか。実際のプロジェクトではこんな要求はあり得ませんが、頭の体操だと思ってやってみて下さい。
正規表現が扱える「行パターンマッチング」
結論から言うと、以下のSQLクエリーで実現することができます。
※MATCH_RECOGNIZEはOracle 12c (12.1)以降で利用可能な機能です。
SELECT
*
FROM
hr.countries
MATCH_RECOGNIZE (
ORDER BY country_id
ALL ROWS PER MATCH
PATTERN ( region20+ )
DEFINE
region20 AS region_id = 20
)
MATCH_RECOGNIZE句を分解してみる
先ほどのSQLクエリーは、一見すると呪文のように見えますが、実は4つのパーツに分解すると意外とシンプルです。
MATCH_RECOGNIZE (
ORDER BY country_id -- ① どの順番で行を見ていくか
ALL ROWS PER MATCH -- ② マッチした行をどう出力するか
PATTERN ( region20+ ) -- ③ どんな並び（パターン）を探すか
DEFINE
region20 AS region_id = 20 -- ④ ③で使った名前の「定義」
)
一つずつ見ていきましょう。
① ORDER BY ─ 行を並べる
ORDER BY country_id
MATCH_RECOGNIZEは、行を一つの「連続した流れ」として扱い、その並びの中からパターンを探すという仕組みです。そのため、「どの順番で行を並べて見ていくか」を必ず指定する必要があります。
普段使っているSELECT文のORDER BY（結果の並び順を指定するもの）とは役割が違い、ここでは「パターンマッチングをする際の判定順序」を決めています。今回はCOUNTRY_IDのアルファベット順に並べ、その順で上から見ていきます。
② ALL ROWS PER MATCH ─ マッチした行を全部出す
ALL ROWS PER MATCH
パターンにマッチした行を、1行ずつ全部出力するという指定です。
対になる指定として ONE ROW PER MATCH があり、こちらは「マッチした一連の行を1つのグループとしてまとめて1行で出力する」というモードです（集計に近いイメージ）。今回はCOUNTRY一覧をそのまま見たいので、ALL ROWS PER MATCHを使っています。
③ PATTERN ─ 探したい並びを「正規表現」で書く
PATTERN ( region20+ )
ここが今回の主役です。region20という名前(④で定義)が1回以上連続する行を探す、という意味になります。
これは正規表現に馴染みがある方なら見覚えのある書き方だと思います。
| 記号 | 意味 | 正規表現との対応 |
|---|---|---|
region20+ |
1回以上連続 |
+ と同じ |
region20* |
0回以上連続 |
* と同じ |
region20? |
0回または1回 |
? と同じ |
region20{2,4} |
2〜4回連続 |
{2,4} と同じ |
つまりMATCH_RECOGNIZEは、文字列に対してではなく「行の並び」に対して正規表現をかけている、とイメージすると理解しやすいと思います。
④ DEFINE ─ 名前の中身を定義する
DEFINE
region20 AS region_id = 20
③のPATTERNで使ったregion20という名前が、具体的にどんな条件の行を指すのかをここで定義します。「region20という名前の行とは、region_id = 20である行のことだよ」という宣言です。
ちなみにこのregion20という名前は自由に付けられるラベルで、予約語ではありません。例えばtargetでもxでも動きます。今回は分かりやすさのためにregion20としています。
まとめると
- COUNTRY_IDの順に行を並べ（①）
-
region_id = 20という条件を満たす行をregion20というラベルで定義し（④） - そのラベルが1回以上連続する場所を探し（③）
- マッチした行を1行ずつ出力する（②）
という処理をしていることになります。
「WHERE句を使わない」という制約のおかげで、普段は意識しない「行の並びに対してパターンを探す」という発想に触れられたのではないでしょうか。
ONE ROW PER MATCHでかいてみる
今度は、GROUP BYを使ったときと同じように、REGION_ID=20の件数（CNT）を求めてみます。目指すゴールは以下の結果です。
| REGION_ID | CNT |
|---|---|
| 20 | 5 |
これは以下のクエリーで取得できます。
SELECT
*
FROM
hr.countries
MATCH_RECOGNIZE (
ORDER BY
region_id
MEASURES
region_id AS region_id,
COUNT(*) AS cnt
ONE ROW PER MATCH
PATTERN (
group20+
)
DEFINE
group20 AS region_id = 20
)
先ほどとの違いを見ていきましょう。
ORDER BYがCOUNTRY_IDからREGION_IDに変わった
ORDER BY region_id
今回はCOUNTRY_IDの順ではなく、REGION_IDの順で行を並べています。ここが今回のポイントです。
REGION_ID順に並べ替えると、同じREGION_IDを持つ行は必ず隣り合わせになります。つまり、REGION_ID = 20の行(AR, BR, CA, MX, US)は、並べ替えた結果、必ず連続することになります。
MEASURESで列を定義する
MEASURES
region_id AS region_id,
COUNT(*) AS cnt
MATCH_RECOGNIZEの結果として外側（SELECT文）から参照できる列は、このMEASURES句で定義したものだけ、という決まりがあります。言い換えると、MEASURES句（とPARTITION BY句）で指定していない列は、デフォルトでは外部に出力されません。そのため、元のテーブルにREGION_IDという列がすでにあっても、region_id AS region_idのように改めて名前を付けて取り出す必要があります。COUNT(*) AS cntは、マッチした行数（=連続した行の数）を数えてCNTという列名で出力する指定です。
PATTERN・DEFINEは先ほどと同じ考え方
PATTERN ( group20+ )
DEFINE
group20 AS region_id = 20
group20というラベルをregion_id = 20と定義し、それが1回以上連続する箇所を探す、という点は先ほどと同じです（ラベル名をregion20からgroup20に変えていますが、意味は変わりません）。
ALL ROWS PER MATCHとの対比
先ほどのALL ROWS PER MATCHは、条件に合う行を1行ずつそのまま返していました。
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
|---|---|---|
| AR | Argentina | 20 |
| BR | Brazil | 20 |
| CA | Canada | 20 |
| MX | Mexico | 20 |
| US | United States of America | 20 |
一方、今回のONE ROW PER MATCHは、それを1つのかたまり(1行)に集約して返します。
| REGION_ID | CNT |
|---|---|
| 20 | 5 |
同じMATCH_RECOGNIZEを使っていても、ALL ROWS PER MATCHかONE ROW PER MATCHかによって、「明細を見たいのか」「集計結果を見たいのか」というアウトプットの粒度そのものが変わることが、この2つの結果を見比べるとよく分かると思います。
実践編：REGION_IDが変わる境目を検出する
ここまでは「REGION_ID=20」という特定の値に絞って連続を検出してきました。最後に、値を固定せず、「同じREGION_IDが続く区間」をすべて洗い出すという、より実践的な例に挑戦してみます。
これは一般に「Gaps and Islands（隙間と島）」問題と呼ばれ、実務でも「在庫切れが何日連続したか」「同じステータスが何回連続したか」といった場面でよく登場します。
以下のクエリーで実現できます。
SELECT
*
FROM
hr.countries
MATCH_RECOGNIZE (
ORDER BY
country_id
MEASURES
region_id AS region_id,
FIRST(country_id) AS start_country,
LAST(country_id) AS end_country,
COUNT(*) AS cnt
ONE ROW PER MATCH
PATTERN (
strt same*
)
DEFINE
same AS region_id = PREV(region_id)
)
ORDER BY
start_country
PATTERNとDEFINEのポイント
PATTERN ( strt same* )
DEFINE
same AS region_id = PREV(region_id)
今回、PATTERNの中に2つのラベルが登場します。
-
strt：区間の最初の1行。DEFINEで条件を指定していないので、「どんな行でもOK」という意味になります。これが新しい区間の起点です。 -
same：strtに続く行。PREV(region_id)は「1つ前の行のREGION_ID」を意味するので、same AS region_id = PREV(region_id)は「直前の行と同じREGION_IDである」という条件になります。
つまり、「まず1行拾い、その後REGION_IDが変わらない限り拾い続ける」という動きです。REGION_IDが変わった瞬間に区間が区切られ、新しいstrtから次の区間が始まります。
PREV()は、これまで出てきたFIRST()・LAST()と同じマッチした範囲内の行を参照する関数の仲間で、「1つ前の行」を指します。
結果を見てみる
このクエリを実行すると、25カ国が 18個の区間（かたまり） に分かれます。
| REGION_ID | START_COUNTRY | END_COUNTRY | CNT |
|---|---|---|---|
| 20 | AR | AR | 1 |
| 40 | AU | AU | 1 |
| 10 | BE | BE | 1 |
| 20 | BR | CA | 2 |
| 10 | CH | CH | 1 |
| 30 | CN | CN | 1 |
| 10 | DE | DK | 2 |
| 50 | EG | EG | 1 |
| 10 | FR | GB | 2 |
| 30 | IL | IN | 2 |
| 10 | IT | IT | 1 |
| 30 | JP | ML | 3 |
| 20 | MX | MX | 1 |
| 50 | NG | NG | 1 |
| 10 | NL | NL | 1 |
| 30 | SG | SG | 1 |
| 20 | US | US | 1 |
| 50 | ZM | ZW | 2 |
例えばBR〜CAはREGION_ID=20が2件連続、JP〜MLはREGION_ID=30が3件連続していたことが、1行で分かります。
この例が示すこと
このクエリをWHERE句やGROUP BYだけで書こうとすると、実は一筋縄ではいきません。「1つ前の行と値が同じかどうか」を判定するにはLAG()関数と、区間ごとに連番を振るためのSUM() OVER()を組み合わせる、いわゆる「LAG + 累積SUM」のテクニックが必要になり、コードはかなり複雑になります。
一方MATCH_RECOGNIZEでは、「起点(strt)＋継続条件(same)」という2行のDEFINEだけで、同じことを簡潔に表現できます。この発想の転換こそが、MATCH_RECOGNIZEの面白さだと思います。
おわりに
「WHERE句を使わずに条件を絞る」という頭の体操から始まり、MATCH_RECOGNIZEの基本構文（ORDER BY・ALL ROWS PER MATCH / ONE ROW PER MATCH・PATTERN・DEFINE）を一通り見てきました。最後には、実務でも頻出する「連続する区間の検出」という実践的な使いどころにも触れました。
WHERE句やGROUP BYだけでは面倒な「連続・区間・境目」を扱うデータに出会ったときは、ぜひMATCH_RECOGNIZEを思い出してみてください。
僭越ながら、MATCH_RECOGNIZEをたくさん練習できる学習サイトを作っています。
よろしければぜひご利用ください。