MATCH_RECOGNIZE條款

適用於：勾選標記為是 Databricks SQL 勾 選為是 Databricks 執行時間 19.0 及以上版本

Important

這項功能位於 測試版 (Beta) 中。 工作區管理員可以從 「預覽 」頁面控制對此功能的存取。 請參閱 管理 Azure Databricks 預覽。

在前一 列的table_reference中尋找並篩選圖案。 MATCH_RECOGNIZE 分割輸入、在每個分割區內排序列、將列模式與該序列匹配，並根據列匹配模式回傳摘要或每列結果。

典型用途包括偵測連續價值的連串、V 形或 W 形價格變動，以及事件串流的時段化。

語法

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分區[， ...]

    一個或多個表達式，定義模式匹配所執行的列群。 若省略 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

      每場比賽回傳一排。 結果僅包含分割欄與測度欄。

    • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

      每行參與比賽的行返回一列。 每個輸出列包含該匹配計算出的 、 欄位PARTITION BY和MEASURES欄位對應的輸入欄位table_reference。

      SHOW EMPTY MATCHES 在 下被接受 ALL ROWS PER MATCH，且當你省略空匹配處理子句時，是預設值。 此釋出不會產生空匹配，因此關鍵字對結果沒有可觀察的影響。

  • 賽後row_pattern_skip_to

    指定在找到匹配後，從哪一列繼續。 本版本僅支援。SKIP PAST LAST ROW 繼續進行緊接當前比賽最後一排之後的那一列。 這是省略 AFTER MATCH 該條款時的預設方式。

  • 模式 （row_pattern）

    指定相符的圖案。

  • 定義 row_pattern_definition_list

    定義了 PATTERN 和 MEASURES 子句中所引用的布林變數。

Result

結果取決於每匹配列數模式：

  • ONE ROW PER MATCH

    回傳 PARTITION BY 欄位，接著 MEASURES 欄位。

  • ALL ROWS PER MATCH

    每行參與比賽的行返回一列。 每個輸出列包含該匹配計算出的 、 欄位PARTITION BY和MEASURES欄位對應的輸入欄位table_reference。

常見錯誤條件

範例

每個查詢都使用一個 stock_ticker(symbol, tstamp, price) 表格，唯獨最後一個範例使用 page_views(user_id, event_time)。

範例 1：連續上升跑

找出每個符號連續價格上漲的最大連續走勢。 該變數 strt 沒有 DEFINE 項目，因此它會匹配任意一列並錨定執行。 up+ 將配對延伸至一個或多個連續的加分。 PREV(price) 依序讀取緊接前一列 ORDER BY 的價格。 ONE ROW PER MATCH 每次執行會發出一列摘要。

> 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

範例二：V字形（下沉與回退）

偵測一個價格先下跌，然後又上漲。 down+ 與跌倒腿 up+ 及恢復相符。 LAST(down.tstamp) 選擇最後一列分類為 down，即 V 的谷底。變數限定的參考，例如 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 形）

偵測到兩個凹陷，中間有部分回復。 這個模式拼出四條腿（），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

範例四：會話化

將使用者的事件串流合併成多個會話，連續事件之間有超過 30 分鐘的間隔，即可啟動一個新的會話。 strt 在任一排開啟一場遊戲。 same_session* 吸收前一個事件後30分鐘內發生的所有事件。 當差距超過門檻時，比賽結束，下一 AFTER MATCH SKIP PAST LAST ROW 場比賽繼續，並重新開始一場新賽事 MATCH_NUMBER()。 *量詞使單一事件成為有效的一列會話。

> 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
  • Last updated on