MATCH_RECOGNIZE句

適用対象: Databricks SQL Databricks Runtime 19.0 以降" とマークされているチェック

上記のtable_referenceの行のパターンを検索してフィルター処理 します。 MATCH_RECOGNIZE は入力をパーティション分割し、各パーティション内の行を並べ替え、その順序付けられたシーケンスに対して行パターンを照合し、一致する行数モードに応じてサマリーまたは行ごとの結果を返します。

一般的な用途としては、連続する値の実行の検出、V シェイプまたは W シェイプの価格の動き、イベント ストリームのセッション化などがあります。

Syntax

MATCH_RECOGNIZE (
  [ PARTITION BY partition [, ...] ]
  [ ORDER BY order_by ]
  [ MEASURES measures ]
  [ row_pattern_rows_per_match ]
  [ AFTER MATCH row_pattern_skip_to ]
  PATTERN ( row_pattern )
  DEFINE row_pattern_definition_list )

measures
  MEASURES { measureExpr AS measureName } [, ...]

row_pattern_rows_per_match
  { ONE ROW PER MATCH
  | ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ] }

row_pattern_skip_to
  SKIP PAST LAST ROW

Parameters

• PARTITION BY パーティション [, ...]

  パターン マッチングを実行する行のグループを定義する 1 つ以上の式。 PARTITION BYを省略した場合、パーティションにはすべての行が含まれます。

  PARTITION BY は列参照のみを受け入れます。 別の式を指定すると、Azure Databricks MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMNが発生します。

• ORDER BY order_by

  各パーティション内の行の順序を指定します。 パターン マッチングとナビゲーション関数では、この順序が使用されます。

• 対策

  必要に応じて、パターンの一致ごとに返されるメジャー列を定義します。

• row_pattern_rows_per_match

  一致ごとに返される行の数を制御します。 既定値は ONE ROW PER MATCH です。

  • ONE ROW PER MATCH

    一致ごとに 1 行を返します。 結果には、パーティション列とメジャー列のみが含まれます。

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    一致する行ごとに 1 行を返します。 各出力行には、 table_referenceからの対応する入力列、 PARTITION BY 列、およびその一致に対して計算された MEASURES 列が含まれます。

    SHOW EMPTY MATCHES は ALL ROWS PER MATCHで受け入れ、空の一致処理サブ句を省略した場合の既定値です。 このリリースでは空の一致が生成されないため、キーワードは結果に対して観測可能な影響を与えません。

• 一致後のrow_pattern_skip_to

  一致が見つかった後から続行する行を指定します。 このリリースでは、 SKIP PAST LAST ROW のみがサポートされます。 現在の一致の最後の行の直後の行に進みます。 これは、 AFTER MATCH 句を省略した場合の既定値です。

• PATTERN ( row_pattern )

  一致するパターンを指定します。

• DEFINE row_pattern_definition_list

  PATTERN句と MEASURES 句で参照されるブール変数を定義します。

Result

結果は、一致する行ごとのモードによって異なります。

• ONE ROW PER MATCH

  PARTITION BY列の後にMEASURES列が続く値を返します。

• ALL ROWS PER MATCH

  一致する行ごとに 1 行を返します。 各出力行には、 table_referenceからの対応する入力列、 PARTITION BY 列、およびその一致に対して計算された MEASURES 列が含まれます。

一般的なエラー状態

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• PARTITION BY句では列が必要です

Examples

各クエリでは、page_views(user_id, event_time)を使用する最後の例を除き、stock_ticker(symbol, tstamp, price) テーブルが使用されます。

例 1: 連続する立ち上がり実行

シンボルごとに連続する価格上昇のすべての最大実行を見つけます。 変数 strt には DEFINE エントリがないため、任意の行に一致し、実行を固定します。 up+ は、一致を 1 つ以上の連続する増加に拡張します。 PREV(price) は、直前の行の価格を注文 ORDER BY 読み取ります。 ONE ROW PER MATCH は、実行ごとに 1 つのサマリー行を出力します。

> CREATE OR REPLACE TEMP VIEW stock_ticker AS
  SELECT * FROM VALUES
    ('AAPL', TIMESTAMP '2024-01-01 09:30:00', 100.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:31:00', 102.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:32:00', 105.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:33:00', 104.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:34:00', 106.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:35:00', 108.0)
  AS t(symbol, tstamp, price);

> SELECT symbol, start_tstamp, end_tstamp, run_length
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp) AS start_tstamp,
             LAST(tstamp)  AS end_tstamp,
             COUNT(*)      AS run_length
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt up+ )
    DEFINE up AS price > PREV(price) ) AS T;
 symbol  start_tstamp           end_tstamp             run_length
 AAPL    2024-01-01 09:30:00    2024-01-01 09:32:00    3
 AAPL    2024-01-01 09:33:00    2024-01-01 09:35:00    3

例 2: V 図形 (dip と recover)

最初に下落し、次に上昇する価格を検出します。 down+ は、落下した脚と一致し、回復を up+ します。 LAST(down.tstamp) は、V の谷である down として分類された最後の行を選択します。 down.tstamp などの変数修飾参照を使用すると、 MEASURES 式は特定のパターン変数に一致する行を読み取ります。

> SELECT symbol, start_tstamp, bottom_tstamp, end_tstamp
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp)     AS start_tstamp,
             LAST(down.tstamp) AS bottom_tstamp,
             LAST(tstamp)      AS end_tstamp
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt down+ up+ )
    DEFINE down AS price < PREV(price),
           up   AS price > PREV(price) ) AS T;
 symbol  start_tstamp           bottom_tstamp          end_tstamp
 AAPL    2024-01-01 09:32:00    2024-01-01 09:33:00    2024-01-01 09:35:00

例 3: 二重底 (W 図形)

部分復旧で区切られた 2 つの dip を検出します。 このパターンは 4 本の脚 (down1+ up1+ down2+ up2+) をスペルアウトし、個別の変数名を使用すると、各トラフを個別に測定またはフィルター処理できます。 MATCH_NUMBER() は、パーティション内で見つかった各 W に番号を付けます。

> CREATE OR REPLACE TEMP VIEW stock_ticker AS
  SELECT * FROM VALUES
    ('AAPL', TIMESTAMP '2024-01-01 09:30:00', 100.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:31:00', 96.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:32:00', 92.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:33:00', 98.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:34:00', 101.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:35:00', 95.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:36:00', 90.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:37:00', 99.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:38:00', 104.0)
  AS t(symbol, tstamp, price);

> SELECT symbol, start_tstamp, end_tstamp, w_no
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp)  AS start_tstamp,
             LAST(tstamp)   AS end_tstamp,
             MATCH_NUMBER() AS w_no
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt down1+ up1+ down2+ up2+ )
    DEFINE down1 AS price < PREV(price),
           up1   AS price > PREV(price),
           down2 AS price < PREV(price),
           up2   AS price > PREV(price) ) AS T;
 symbol  start_tstamp           end_tstamp             w_no
 AAPL    2024-01-01 09:30:00    2024-01-01 09:38:00    1

例 4: セッション化

ユーザーのイベント ストリームをセッションに折りたたみ、連続するイベント間に 30 分を超える間隔で新しいセッションを開始します。 strt は、任意の行でセッションを開きます。 same_session* は、先行タスクから 30 分以内に発生するすべてのイベントを吸収します。 ギャップがしきい値を超えると、一致が終了し、次のイベントで AFTER MATCH SKIP PAST LAST ROW が再開され、新しいセッション (新しい MATCH_NUMBER()) が開始されます。 *量指定子は、1 つのイベントを有効な 1 行セッションにします。

> CREATE OR REPLACE TEMP VIEW page_views AS
  SELECT * FROM VALUES
    (1, TIMESTAMP '2024-01-01 09:00:00'),
    (1, TIMESTAMP '2024-01-01 09:15:00'),
    (1, TIMESTAMP '2024-01-01 10:00:00'),
    (1, TIMESTAMP '2024-01-01 10:10:00')
  AS t(user_id, event_time);

> SELECT user_id, session_no, session_start, session_end, event_count
  FROM page_views
  MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES MATCH_NUMBER()    AS session_no,
             FIRST(event_time) AS session_start,
             LAST(event_time)  AS session_end,
             COUNT(*)          AS event_count
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt same_session* )
    DEFINE same_session AS event_time <= PREV(event_time) + INTERVAL 30 MINUTE ) AS T;
 user_id  session_no  session_start          session_end            event_count
 1        1           2024-01-01 09:00:00    2024-01-01 09:15:00    2
 1        2           2024-01-01 10:00:00    2024-01-01 10:10:00    2

関連資料

• MATCH_RECOGNIZEの DEFINE 句
• MATCH_RECOGNIZEの MEASURES 句
• MATCH_RECOGNIZEの PATTERN 句
• MATCH_RECOGNIZE関数
• テーブル参照