【Oracle】MATCH_RECOGNIZE完全ガイド｜行パターンマッチングで時系列データのイベントを SQL で検出する

SELECT *
FROM テーブル名
MATCH_RECOGNIZE (
    PARTITION BY 列名           -- グループ分けの基準（省略可）
    ORDER BY 列名               -- 行の順序を定義する（必須）

    MEASURES                    -- マッチ結果から取り出す値を定義する
        A.列名 AS 別名,
        LAST(B.列名) AS 別名

    ONE ROW PER MATCH           -- マッチごとに1行を返す（デフォルト）
    -- ALL ROWS PER MATCH       -- マッチした全行を返す

    AFTER MATCH SKIP PAST LAST ROW  -- マッチ後の検索再開位置

    PATTERN ( A+ B+ )           -- 行パターンを正規表現で定義する

    DEFINE                      -- パターン変数の条件を定義する
        A AS A.price <= PREV(A.price),  -- A: 前の行以下
        B AS B.price > PREV(B.price)    -- B: 前の行より大きい
);

-- PATTERN で使える正規表現演算子
-- A       : パターン変数 A が1回一致
-- A+      : A が1回以上一致
-- A*      : A が0回以上一致
-- A?      : A が0回または1回一致
-- A | B   : A または B が一致
-- (A B)   : A の後に B
-- A{n}    : A が n 回一致
-- A{m,n}  : A が m 回以上 n 回以下

-- サンプルデータの想定:
-- stock_prices (ticker, trade_date, close_price)

SELECT
    ticker,
    pattern_start,
    pattern_end,
    peak_date,
    peak_price,
    start_price,
    end_price
FROM stock_prices
MATCH_RECOGNIZE (
    PARTITION BY ticker           -- 銘柄ごとにパターンを探す
    ORDER BY trade_date

    MEASURES
        FIRST(UP.trade_date)  AS pattern_start,  -- 上昇開始日
        LAST(DOWN.trade_date) AS pattern_end,     -- 下降終了日
        FIRST(DOWN.trade_date) AS peak_date,      -- 最初の下降日（頂点の翌日）
        LAST(UP.close_price)  AS peak_price,      -- 上昇最終値（頂点価格）
        FIRST(UP.close_price) AS start_price,     -- パターン開始価格
        LAST(DOWN.close_price) AS end_price       -- パターン終了価格

    ONE ROW PER MATCH
    AFTER MATCH SKIP TO LAST DOWN  -- 最後の DOWN 行から再開する

    PATTERN ( UP+ DOWN+ )          -- 1回以上の上昇後に1回以上の下降

    DEFINE
        UP   AS UP.close_price   > PREV(UP.close_price),    -- 前日より高い
        DOWN AS DOWN.close_price < PREV(DOWN.close_price)   -- 前日より低い
)
ORDER BY ticker, pattern_start;

-- 出力例:
-- TICKER  PATTERN_START  PATTERN_END  PEAK_DATE   PEAK_PRICE  START_PRICE  END_PRICE
-- AAPL    2024-01-05     2024-01-15   2024-01-10   195.0       185.0        175.0

-- サンプルデータの想定:
-- user_events (user_id, event_time, event_type)
-- event_type: LOGIN / ACTION / LOGOUT

SELECT
    user_id,
    session_start,
    session_end,
    ROUND((session_end - session_start) * 24 * 60, 1) AS duration_minutes,
    action_count
FROM user_events
MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time

    MEASURES
        LOGIN.event_time         AS session_start,    -- ログイン時刻
        LAST(LOGOUT.event_time)  AS session_end,      -- ログアウト時刻
        COUNT(ACTION.*)          AS action_count       -- セッション内の操作回数

    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW

    PATTERN ( LOGIN ACTION* LOGOUT )  -- ログイン→0回以上の操作→ログアウト

    DEFINE
        LOGIN  AS LOGIN.event_type  = 'LOGIN',
        ACTION AS ACTION.event_type = 'ACTION',
        LOGOUT AS LOGOUT.event_type = 'LOGOUT'
)
ORDER BY user_id, session_start;

-- ONE ROW PER MATCH: マッチごとに集計値を1行で返す（デフォルト）
-- ALL ROWS PER MATCH: マッチした各行に対して結果を返す（デバッグ・詳細分析に便利）

SELECT *
FROM stock_prices
MATCH_RECOGNIZE (
    PARTITION BY ticker
    ORDER BY trade_date

    MEASURES
        MATCH_NUMBER()           AS match_num,    -- 何番目のマッチか
        CLASSIFIER()             AS var_name,     -- この行がどのパターン変数か (UP/DOWN)
        close_price              AS price,
        FIRST(UP.trade_date)     AS match_start

    ALL ROWS PER MATCH            -- マッチした全行を個別に返す
    AFTER MATCH SKIP PAST LAST ROW

    PATTERN ( UP+ DOWN+ )

    DEFINE
        UP   AS UP.close_price   > PREV(UP.close_price),
        DOWN AS DOWN.close_price < PREV(DOWN.close_price)
)
WHERE ticker = 'AAPL'
ORDER BY trade_date;

-- MATCH_NUMBER(): このマッチが全体の何番目のマッチかを返す
-- CLASSIFIER(): 各行がどのパターン変数（UP/DOWN）に分類されているかを返す
-- これらは MEASURES 句または ALL ROWS PER MATCH 時に使える