klauzule MATCH_RECOGNIZE

Platí pro: Databricks SQL Databricks Runtime 19.0 and above

Important

Tato funkce je v beta verzi. Správci pracovního prostoru můžou řídit přístup k této funkci ze stránky Previews . Viz Manage Azure Databricks preview.

Vyhledá a filtruje vzory v řádcích předchozího table_reference. MATCH_RECOGNIZE rozdělí vstup, objednává řádky v rámci každého oddílu, odpovídá vzoru řádku s tímto seřazeným pořadím a vrátí souhrnné výsledky nebo výsledky pro jednotlivé řádky v závislosti na režimu shody řádků.

Mezi typické způsoby použití patří zjišťování běhů po sobě jdoucích hodnot, pohybu cen ve tvaru V nebo W a relačních datových proudů událostí.

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 [, ...]

  Jeden nebo více výrazů, které definují skupiny řádků, na kterých se spouští porovnávání vzorů. Pokud vynecháte PARTITION BY, oddíl obsahuje všechny řádky.

  PARTITION BY přijímá pouze odkazy na sloupce. Pokud zadáte jiný výraz, Azure Databricks vyvolá MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Určuje pořadí řádků v rámci každého oddílu. Toto pořadí používají funkce porovnávání vzorů a navigace.

• OPATŘENÍ

  Volitelně definuje sloupce měr vrácené pro každou shodu vzorů.

• row_pattern_rows_per_match

  Určuje, kolik řádků se vrátí podle shody. Výchozí hodnota je ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Vrátí jeden řádek na shodu. Výsledek obsahuje pouze sloupce oddílů a sloupce měr.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Vrátí jeden řádek pro každý řádek, který se účastní shody. Každý výstupní řádek obsahuje odpovídající vstupní sloupce ze table_referencesloupců , PARTITION BY sloupců a MEASURES sloupců vypočítaných pro danou shodu.

    SHOW EMPTY MATCHES je přijat v části ALL ROWS PER MATCH, a je výchozí, pokud vynecháte sub-klauzuli zpracování prázdné shody. Tato verze nevygeneruje prázdné shody, takže klíčové slovo nemá žádný pozorovatelný vliv na výsledek.

• PO ROW_PATTERN_SKIP_TO SHODY

  Určuje, od kterého řádku se má pokračovat po nalezení shody. Tato verze podporuje SKIP PAST LAST ROW pouze. Pokračujte řádkem bezprostředně za posledním řádkem aktuální shody. Toto je výchozí nastavení při vynechání AFTER MATCH klauzule.

• PATTERN ( row_pattern )

  Určuje vzor, který se má shodovat.

• DEFINICE row_pattern_definition_list

  Definuje logické proměnné odkazované v PATTERN klauzulích a MEASURES klauzulích.

Výsledek

Výsledek závisí na režimu shody řádků:

• ONE ROW PER MATCH

  Vrátí PARTITION BY sloupce následované sloupci MEASURES .

• ALL ROWS PER MATCH

  Vrátí jeden řádek pro každý řádek, který se účastní shody. Každý výstupní řádek obsahuje odpovídající vstupní sloupce ze table_referencesloupců , PARTITION BY sloupců a MEASURES sloupců vypočítaných pro danou shodu.

Běžné chybové podmínky

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

Examples

Každý dotaz používá stock_ticker(symbol, tstamp, price) tabulku s výjimkou posledního příkladu, který používá page_views(user_id, event_time).

Příklad 1: Po sobě jdoucí vzestupné spuštění

Najděte každý maximální běh po sobě jdoucích cen na symbol. Proměnná strt nemá žádnou DEFINE položku, takže odpovídá libovolnému řádku a ukotvení spuštění. up+ rozšiřuje shodu napříč jedním nebo více po sobě jdoucím nárůstem. PREV(price) přečte cenu bezprostředně předcházejícího řádku v ORDER BY pořadí. ONE ROW PER MATCH generuje jeden souhrnný řádek pro každé spuštění.

> 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

Příklad 2: Obrazec V (dip a obnovení)

Detekujte cenu, která nejprve klesne, pak se zvýší. down+ odpovídá padající noze a up+ obnovení. LAST(down.tstamp) vybere poslední řádek klasifikovaný jako down, což je koryto V. Proměnným kvalifikovaný odkaz, například down.tstamp umožňuje výrazu MEASURES číst řádky, které odpovídají konkrétní proměnné vzoru.

> 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

Příklad 3: Dvojitá dolní část (obrazec W)

Detekujte dvě propady oddělené částečnou obnovou. Vzor vytyčuje čtyři nohy (down1+ up1+ down2+ up2+) a různé názvy proměnných umožňují měřit nebo filtrovat jednotlivé části nezávisle. MATCH_NUMBER() čísla každé hodnoty W nalezené v oddílu.

> 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

Příklad 4: Sessionization

Sbalení datového proudu událostí uživatele do relací, kdy nová relace začíná mezerou delší než 30 minut mezi po sobě jdoucími událostmi. strt otevře relaci na libovolném řádku. same_session* absorbuje každou následující událost, která nastane do 30 minut od svého předchůdce. Když mezera překročí prahovou hodnotu, shoda skončí, AFTER MATCH SKIP PAST LAST ROW obnoví se při další události a zahájí se nová relace (nová MATCH_NUMBER()). Kvantifikátor * vytvoří lone událost platnou relaci s jedním řádkem.

> 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

Související články

• Klauzule DEFINE pro MATCH_RECOGNIZE
• Klauzule MEASURES pro MATCH_RECOGNIZE
• Klauzule PATTERN pro MATCH_RECOGNIZE
• MATCH_RECOGNIZE funkce
• Referenční informace k tabulce