MATCH_RECOGNIZE 절

적용 대상: Databricks SQL Databricks Runtime 19.0 이상으로 표시

Important

이 기능은 베타 버전으로 제공됩니다. 작업 영역 관리자는 미리 보기 페이지에서 이 기능에 대한 액세스를 제어할 수 있습니다. Azure Databricks 미리 보기 관리를 참조하세요.

이전 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 파티션 [, ...]

  패턴 일치가 실행되는 행 그룹을 정의하는 하나 이상의 식입니다. 생략 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 ]

    일치 항목에 참여하는 각 행에 대해 하나의 행을 반환합니다. 각 출력 행에는 해당 일치 항목에 table_reference대해 계산된 열 PARTITION BY , 열의 MEASURES 해당 입력 열이 포함됩니다.

    SHOW EMPTY MATCHES 는 아래와 같이 ALL ROWS PER MATCH허용되며, 빈 일치 처리 하위 절을 생략할 때 기본값입니다. 이 릴리스에서는 빈 일치 항목이 생성되지 않으므로 키워드가 결과에 관찰 가능한 영향을 주지 않습니다.

• AFTER MATCH row_pattern_skip_to

  일치 항목을 찾은 후 계속할 행을 지정합니다. 이 릴리스는 지원됩니다 SKIP PAST LAST ROW . 현재 일치 항목의 마지막 행 바로 다음 행을 계속합니다. 절을 생략할 때 기본값입니다 AFTER MATCH .

• PATTERN ( row_pattern )

  일치시킬 패턴을 지정합니다.

• row_pattern_definition_list 정의

  and MEASURES 절에서 참조되는 부울 변수를 PATTERN 정의합니다.

Result

결과는 매치당 행 모드에 따라 달라집니다.

• ONE ROW PER MATCH

  PARTITION BY 열 뒤에 열을 반환 MEASURES 합니다.

• ALL ROWS PER MATCH

  일치 항목에 참여하는 각 행에 대해 하나의 행을 반환합니다. 각 출력 행에는 해당 일치 항목에 table_reference대해 계산된 열 PARTITION BY , 열의 MEASURES 해당 입력 열이 포함됩니다.

일반적인 오류 조건

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED (매치 인식 측정값은 별칭을 가져야 합니다)
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

예제

각 쿼리는 다음을 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

예제 2: V 셰이프(딥 및 복구)

먼저 떨어지는 가격을 감지한 다음 상승합니다. down+ 는 떨어지는 다리와 up+ 회복과 일치합니다. LAST(down.tstamp)는 V의 트로프인 마지막 행을 down선택합니다. 식이 특정 패턴 변수와 일치하는 행을 MEASURES 읽을 수 있도록 하는 것과 같은 down.tstamp 변수 정규화된 참조입니다.

> 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

예제 4: 세션화

사용자의 이벤트 스트림을 세션으로 축소합니다. 이 경우 연속 이벤트 간에 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

관련된 문서

• MATCH_RECOGNIZE 대한 DEFINE 절
• MATCH_RECOGNIZE 대한 MEASURES 절
• MATCH_RECOGNIZE 대한 PATTERN 절
• MATCH_RECOGNIZE 함수
• 테이블 참조