developersIO
BigQuery の MATCH_RECOGNIZE で行のパターンマッチングを試してみた

BigQuery の MATCH_RECOGNIZE で行のパターンマッチングを試してみた

Google Cloud (GCP)Google BigQuery
FacebookHatena blogX
2025.11.30

こんにちは！エノカワです。

BigQuery の MATCH_RECOGNIZE 句が 一般提供（GA） になりました。

You can use the MATCH_RECOGNIZE clause in your SQL queries to filter and aggregate matches across rows in a table. This feature is generally available (GA).

MATCH_RECOGNIZE は、テーブルの行に対してパターンマッチングを行う機能です。
今回は、この機能を実際に試してみましたので、その内容をご紹介します。

MATCH_RECOGNIZE とは？

MATCH_RECOGNIZE は、行に対する正規表現のような機能です。

文字列に対してパターンマッチングを行う正規表現と同様に、テーブルの行のシーケンス（連続したデータ）に対してパターンマッチングを行うことができます。

従来、連続したイベントパターンの検出には、複雑なウィンドウ関数や自己結合、再帰 CTE などを駆使する必要がありましたが、MATCH_RECOGNIZE を使えば、1つの SQL 句でシンプルに表現できます。

主なユースケース

用途 例
解約分析 「ログイン → 3日無活動 → 解約」のパターン検出
異常検知 「失敗ログインが5回連続」のパターン
ECサイト分析 「閲覧 → カート → 購入」のファネル分析
スポーツ分析 「3連勝」「5試合連続得点」などの連続記録の検出
株価分析 「V字回復」「連続上昇」などのパターン検出

基本構文

MATCH_RECOGNIZE の基本構文は以下の通りです。

SELECT *
FROM your_table
MATCH_RECOGNIZE (
  PARTITION BY partition_column    -- パーティション分割（省略可）
  ORDER BY order_column            -- 行の順序
  MEASURES                         -- 出力カラム定義
    measure_expression AS alias
  PATTERN (pattern_expression)     -- 正規表現パターン
  DEFINE                           -- パターン変数の条件
    variable AS condition
)

各句の説明

句 説明 必須 設定例
PARTITION BY データをグループ分け - PARTITION BY user_id
ORDER BY 行の順序を指定 ✓ ORDER BY event_time
MEASURES 出力カラムを定義 ✓ FIRST(A.col) AS start_col
PATTERN パターンを定義 ✓ PATTERN (A+ B)
DEFINE パターン変数の条件を定義 ✓ A AS col > 100

MEASURES / DEFINE で使用できる関数

関数 説明 使用する句
FIRST(A.col) パターン変数 A の最初の値 MEASURES
LAST(A.col) パターン変数 A の最後の値 MEASURES
COUNT(A.col) パターン変数 A の出現回数 MEASURES
PREV(col) 前の行の値 DEFINE
NEXT(col) 次の行の値 DEFINE

PATTERN で使用できる正規表現

パターン 説明
A B A の後に B が続く
A+ A が1回以上繰り返す
A* A が0回以上繰り返す
A? A が0回または1回
A{n} A がちょうど n 回
A{n,m} A が n 回以上 m 回以下
A|B A または B
(A B)+ グループの繰り返し

ここでは主要な句・関数・パターンを紹介しています。
その他のオプションや詳細については、公式ドキュメントを参照してください。

使用例

ここからは、実際に MATCH_RECOGNIZE を使った具体的なクエリ例を紹介します。

株価の「V字回復」パターンを検出する

株価データから「下落が続いた後に上昇に転じる」V字回復パターンを検出してみます。

サンプルデータの作成

CREATE OR REPLACE TABLE match_recognize_demo.stock_prices AS
SELECT * FROM UNNEST([
  STRUCT('COMPANY_A' AS ticker, DATE '2024-01-01' AS trade_date, 100.0 AS price),
  STRUCT('COMPANY_A', DATE '2024-01-02', 95.0),
  STRUCT('COMPANY_A', DATE '2024-01-03', 90.0),
  STRUCT('COMPANY_A', DATE '2024-01-04', 85.0),
  STRUCT('COMPANY_A', DATE '2024-01-05', 90.0),
  STRUCT('COMPANY_A', DATE '2024-01-06', 95.0),
  STRUCT('COMPANY_A', DATE '2024-01-07', 100.0),
  STRUCT('COMPANY_A', DATE '2024-01-08', 98.0),
  STRUCT('COMPANY_A', DATE '2024-01-09', 102.0),
  STRUCT('COMPANY_A', DATE '2024-01-10', 105.0)
]);

bigquery-match-recognize-pattern-matching_01.png

MATCH_RECOGNIZE を使ったクエリ

SELECT *
FROM match_recognize_demo.stock_prices
MATCH_RECOGNIZE (
  PARTITION BY ticker                        -- 銘柄ごとにパターンを検出
  ORDER BY trade_date                        -- 日付順に並べる
  MEASURES
    FIRST(DOWN.trade_date) AS pattern_start, -- 下落開始日
    LAST(UP.trade_date) AS pattern_end,      -- 上昇終了日
    FIRST(DOWN.price) AS start_price,        -- 下落開始時の価格
    LAST(UP.price) AS end_price              -- 上昇終了時の価格
  PATTERN (DOWN{2,} UP{2,})                  -- 2回以上下落 → 2回以上上昇
  DEFINE
    DOWN AS price < PREV(price),             -- 前日より価格が低い
    UP AS price > PREV(price)                -- 前日より価格が高い
)

結果

V字回復パターンが1件検出されました。
MEASURESで定義した通り、パターンの開始日・終了日と開始時・終了時の価格が出力されています。

| ticker    | pattern_start | pattern_end | start_price | end_price |
|-----------|---------------|-------------|-------------|-----------|
| COMPANY_A | 2024-01-02    | 2024-01-07  | 95.0        | 100.0     |

解説

このクエリでは、以下のパターンを検出しています。

  • DOWN{2,} : 価格が前日より下落する日が2回以上連続
  • UP{2,} : 価格が前日より上昇する日が2回以上連続

実際のデータでは：

  1. 01/02: 100 → 95（DOWN）
  2. 01/03: 95 → 90（DOWN）
  3. 01/04: 90 → 85（DOWN）
  4. 01/05: 85 → 90（UP）
  5. 01/06: 90 → 95（UP）
  6. 01/07: 95 → 100（UP）

このように、3日連続の下落（DOWN{3}）と3日連続の上昇（UP{3}）が検出され、V字回復パターンとしてマッチしています。

bigquery-match-recognize-pattern-matching_02.png

EC サイトの購入ファネルを分析する

ECサイトのユーザー行動ログから「閲覧 → カート追加 → 購入」という理想的な購入ファネルを完了したユーザーを検出してみます。

サンプルデータの作成

CREATE OR REPLACE TABLE match_recognize_demo.user_events AS
SELECT * FROM UNNEST([
  STRUCT('user_001' AS user_id, TIMESTAMP '2024-01-01 10:00:00' AS event_time, 'view' AS event_type),
  STRUCT('user_001', TIMESTAMP '2024-01-01 10:05:00', 'view'),
  STRUCT('user_001', TIMESTAMP '2024-01-01 10:10:00', 'cart'),
  STRUCT('user_001', TIMESTAMP '2024-01-01 10:15:00', 'purchase'),
  STRUCT('user_002', TIMESTAMP '2024-01-01 11:00:00', 'view'),
  STRUCT('user_002', TIMESTAMP '2024-01-01 11:05:00', 'view'),
  STRUCT('user_002', TIMESTAMP '2024-01-01 11:10:00', 'view'),
  STRUCT('user_003', TIMESTAMP '2024-01-01 12:00:00', 'view'),
  STRUCT('user_003', TIMESTAMP '2024-01-01 12:05:00', 'cart'),
  STRUCT('user_003', TIMESTAMP '2024-01-01 12:10:00', 'view'),
  STRUCT('user_003', TIMESTAMP '2024-01-01 12:15:00', 'cart'),
  STRUCT('user_003', TIMESTAMP '2024-01-01 12:20:00', 'purchase')
]);

bigquery-match-recognize-pattern-matching_03.png

MATCH_RECOGNIZE を使ったクエリ

SELECT *
FROM match_recognize_demo.user_events
MATCH_RECOGNIZE (
  PARTITION BY user_id                       -- ユーザーごとにパターンを検出
  ORDER BY event_time                        -- イベント発生順に並べる
  MEASURES
    FIRST(V.event_time) AS first_view_time,  -- 最初の閲覧時刻
    FIRST(C.event_time) AS cart_time,        -- カート追加時刻
    FIRST(P.event_time) AS purchase_time,    -- 購入時刻
    COUNT(V.event_time) AS view_count        -- 閲覧回数
  PATTERN (V+ C P)                           -- 閲覧1回以上 → カート → 購入
  DEFINE
    V AS event_type = 'view',                -- 閲覧イベント
    C AS event_type = 'cart',                -- カート追加イベント
    P AS event_type = 'purchase'             -- 購入イベント
)

結果

購入ファネルを完了したユーザーが2名検出されました。
MEASURESで定義した通り、各イベントの時刻と閲覧回数が出力されています。

| user_id  | first_view_time         | cart_time               | purchase_time           | view_count |
|----------|-------------------------|-------------------------|-------------------------|------------|
| user_001 | 2024-01-01 10:00:00 UTC | 2024-01-01 10:10:00 UTC | 2024-01-01 10:15:00 UTC | 2          |
| user_003 | 2024-01-01 12:10:00 UTC | 2024-01-01 12:15:00 UTC | 2024-01-01 12:20:00 UTC | 1          |

解説

このクエリでは、以下のパターンを検出しています。

  • V+ : 閲覧（view）が1回以上
  • C : カート追加（cart）が1回
  • P : 購入（purchase）が1回

user_001 は「閲覧 × 2 → カート → 購入」のパターンにマッチしています。
user_002 は閲覧のみでカート追加・購入がないため、マッチしません。
user_003 は「閲覧 → カート → 閲覧 → カート → 購入」という複雑な行動をしていますが、最後の「閲覧 → カート → 購入」のパターンにマッチしています。

bigquery-match-recognize-pattern-matching_04.png

システムログから連続エラーを検出する

サーバーのログから「エラーが3回以上連続」したパターンを検出してみます。

サンプルデータの作成

CREATE OR REPLACE TABLE match_recognize_demo.server_logs AS
SELECT * FROM UNNEST([
  STRUCT(TIMESTAMP '2024-01-01 10:00:00' AS log_time, 'INFO' AS log_level, 'Application started' AS message),
  STRUCT(TIMESTAMP '2024-01-01 10:01:00', 'INFO', 'Request processed'),
  STRUCT(TIMESTAMP '2024-01-01 10:02:00', 'ERROR', 'Database connection failed'),
  STRUCT(TIMESTAMP '2024-01-01 10:02:30', 'ERROR', 'Retry failed'),
  STRUCT(TIMESTAMP '2024-01-01 10:03:00', 'ERROR', 'Max retries exceeded'),
  STRUCT(TIMESTAMP '2024-01-01 10:03:30', 'ERROR', 'Service unavailable'),
  STRUCT(TIMESTAMP '2024-01-01 10:05:00', 'INFO', 'Connection restored'),
  STRUCT(TIMESTAMP '2024-01-01 10:06:00', 'INFO', 'Request processed'),
  STRUCT(TIMESTAMP '2024-01-01 10:07:00', 'ERROR', 'Timeout occurred'),
  STRUCT(TIMESTAMP '2024-01-01 10:08:00', 'INFO', 'Request processed')
]);

bigquery-match-recognize-pattern-matching_05.png

MATCH_RECOGNIZE を使ったクエリ

SELECT *
FROM match_recognize_demo.server_logs
MATCH_RECOGNIZE (
  ORDER BY log_time                          -- 時刻順に並べる
  MEASURES
    FIRST(E.log_time) AS error_start_time,   -- エラー開始時刻
    LAST(E.log_time) AS error_end_time,      -- エラー終了時刻
    FIRST(E.message) AS first_error_message, -- 最初のエラーメッセージ
    LAST(E.message) AS last_error_message,   -- 最後のエラーメッセージ
    COUNT(E.log_time) AS error_count         -- エラー件数
  PATTERN (E{3,})                            -- エラーが3回以上連続
  DEFINE
    E AS log_level = 'ERROR'                 -- ログレベルがERROR
)

結果

3回以上連続したエラーが1件検出されました。
MEASURESで定義した通り、エラーの開始・終了時刻、最初と最後のメッセージ、件数が出力されています。

| error_start_time        | error_end_time          | first_error_message        | last_error_message  | error_count |
|-------------------------|-------------------------|----------------------------|---------------------|-------------|
| 2024-01-01 10:02:00 UTC | 2024-01-01 10:03:30 UTC | Database connection failed | Service unavailable | 4           |

解説

このクエリでは、以下のパターンを検出しています。

  • E{3,} : エラー（ERROR）が3回以上連続

10:02:00〜10:03:30 の4件連続エラーはパターンにマッチしています。
10:07:00 のエラーは単発のため、3回以上連続の条件を満たさずマッチしません。

bigquery-match-recognize-pattern-matching_06.png

まとめ

以上、BigQuery の MATCH_RECOGNIZE を実際に試してみました。

今回は、以下の3つのユースケースを紹介しました。

  • 株価のV字回復検出：価格が連続して下落した後、上昇に転じるパターン
  • ECサイトのファネル分析：閲覧→カート→購入という行動シーケンス
  • システムログのエラー検出：連続したエラーログの検出

MATCH_RECOGNIZE を使えば、従来は LAG / LEAD やウィンドウ関数を組み合わせた複雑なクエリが必要だった連続パターンの検出を宣言的かつシンプルに記述できます。
特に、パターンの定義（PATTERN句）と条件の定義（DEFINE句）が分離されているため、可読性が高く、後からの修正も容易です。

MATCH_RECOGNIZE は SQL 標準の機能であり、Snowflake でも使用されています。
BigQuery でも GA となったことで、より多くの場面で活用できるようになりました。

今回紹介した機能以外にも、出力モードやマッチ後のスキップ動作など、さまざまなオプションが用意されています。
これらを組み合わせることで、より複雑なパターン検出にも対応できるので、機会があれば試してみたいと思います。

ぜひ皆様の分析業務でも活用してみてください！

参考

この記事をシェアする

FacebookHatena blogX

関連記事

Cloud SQL の止め忘れを Terraform テンプレートで対策する
すらぼ
2025.11.29
Privileged Access Managerで一時的に昇格されたアクセス権を付与する
渡邉 光
2025.11.27
Google CloudのログをDatadogへ連携する
渡邉 光
2025.11.26
Google CloudとDatadogをインテグレーションしてみた
渡邉 光
2025.11.20