MATCH_RECOGNIZE záradék

A következőre vonatkozik: Databricks SQL Databricks Runtime 19.0 és újabb

Important

Ez a funkció bétaverzióban érhető el. A munkaterület rendszergazdái az Előnézetek lapon szabályozhatják a funkcióhoz való hozzáférést. Lásd: Az Azure Databricks előzetes verziójának kezelése.

Megkeresi és szűri a mintákat az előző table_reference soraiban. MATCH_RECOGNIZE particionálja a bemenetet, sorokat rendel az egyes partíciókon belül, egy sormintát ad vissza a rendezett sorozathoz, és a sorok egyeztetési módjától függően összegző vagy soronkénti eredményeket ad vissza.

A tipikus felhasználási módok közé tartozik az egymást követő értékek futásainak észlelése, a V- vagy W-alakú ármozgások, valamint az eseménystreamek munkamenetezése.

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 partíció [, ...]

  Egy vagy több kifejezés, amely meghatározza azokat a sorcsoportokat, amelyeken a mintaegyezés fut. Ha kihagyja PARTITION BY, a partíció az összes sort tartalmazza.

  PARTITION BY csak oszlophivatkozásokat fogad el. Ha másik kifejezést ad meg, Azure Databricks MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Az egyes partíciók sorainak sorrendjét határozza meg. A mintamegfeleltetési és navigációs függvények ezt a sorrendet használják.

• INTÉZKEDÉSEK

  Opcionálisan meghatározza az egyes mintaegyezésekhez visszaadott mértékoszlopokat.

• row_pattern_rows_per_match

  Meghatározza, hogy egyezésenként hány sort ad vissza a rendszer. Az alapértelmezett érték a ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Egyezésenként egy sort ad vissza. Az eredmény csak partícióoszlopokat és mértékoszlopokat tartalmaz.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Egy sort ad vissza minden olyan sorhoz, amely részt vesz egy egyezésben. Minden kimeneti sor tartalmazza a megfelelő bemeneti oszlopokat az table_referenceadott egyezéshez kiszámított, PARTITION BY oszlopokból és MEASURES oszlopokból.

    SHOW EMPTY MATCHES a rendszer elfogadja a következőt ALL ROWS PER MATCH, és ez az alapértelmezett érték, ha kihagyja az üres egyezés-kezelési al záradékot. Ez a kiadás nem hoz létre üres egyezéseket, így a kulcsszónak nincs megfigyelhető hatása az eredményre.

• EGYEZÉS UTÁN row_pattern_skip_to

  Megadja, hogy melyik sorból folytassa a folytatást egyezés után. Ez a kiadás csak azokat támogatja SKIP PAST LAST ROW . Folytassa a sort közvetlenül az aktuális egyezés utolsó sora után. Ez az alapértelmezett, ha kihagyja a záradékot AFTER MATCH .

• PATTERN (row_pattern )

  Megadja az egyező mintát.

• ROW_PATTERN_DEFINITION_LIST DEFINIÁLÁSA

  Meghatározza a záradékokban PATTERNMEASURES hivatkozott logikai változókat.

Result

Az eredmény a sorok egyeztetési módjától függ:

• ONE ROW PER MATCH

  Oszlopokat PARTITION BY , majd MEASURES oszlopokat ad vissza.

• ALL ROWS PER MATCH

  Egy sort ad vissza minden olyan sorhoz, amely részt vesz egy egyezésben. Minden kimeneti sor tartalmazza a megfelelő bemeneti oszlopokat az table_referenceadott egyezéshez kiszámított, PARTITION BY oszlopokból és MEASURES oszlopokból.

Gyakori hibafeltételek

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

Examples

Minden lekérdezés egy táblát stock_ticker(symbol, tstamp, price) használ, kivéve az utolsó példát, amely a következőt használja page_views(user_id, event_time):

1. példa: Egymást követő emelkedő futtatás

Keresse meg az egymást követő áremelések minden maximális futását szimbólumonként. A változó strt nem DEFINE tartalmaz bejegyzést, ezért egyezik a sorokkal, és horgonyozza a futtatásokat. up+ egy vagy több egymást követő emelésre kiterjeszti az egyezést. PREV(price) a közvetlenül megelőző sor árát olvassa be sorrendbe ORDER BY . ONE ROW PER MATCH Futtatásonként egyetlen összegző sort bocsát ki.

> 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. példa: V-alakzat (visszamerítés és helyreállítás)

Észlelhet egy árat, amely először csökken, majd emelkedik. down+ megfelel a leeső lábnak és up+ a helyreállításnak. LAST(down.tstamp) az utolsóként besorolt downsort választja ki, amely az V vályúja. Változóval minősített hivatkozás, például lehetővé teszi, hogy down.tstamp egy MEASURES kifejezés egy adott mintaváltozóval egyező sorokat olvasson be.

> 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. példa: Dupla alsó (W-alakzat)

Két, részleges helyreállítással elválasztott visszamerülés észlelése. A minta négy lábat (down1+ up1+ down2+ up2+) jelöl, és a különböző változónevek lehetővé teszik az egyes vályúk egymástól függetlenül történő mérését vagy szűrését. MATCH_NUMBER() egy partíción belül található összes W-t számozza.

> 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. példa: Munkamenet-szervezés

Egy felhasználó eseményfolyamának összecsukása munkamenetekbe, ahol az egymást követő események közötti 30 percnél hosszabb szünet új munkamenetet indít el. strt bármely sorban megnyit egy munkamenetet. same_session* minden következő eseményt elnyel, amely az elődjétől számított 30 percen belül következik be. Ha egy rés túllépi a küszöbértéket, a mérkőzés véget ér, AFTER MATCH SKIP PAST LAST ROW a következő eseményen folytatódik, és megkezdődik egy új munkamenet (egy új MATCH_NUMBER()). A * kvantáló egy magányos eseményt egy érvényes egysoros munkamenetként hoz létre.

> 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

Kapcsolódó cikkek

• DEFINE záradék MATCH_RECOGNIZE
• MEASURES záradék MATCH_RECOGNIZE
• PATTERN záradék MATCH_RECOGNIZE
• MATCH_RECOGNIZE függvények
• táblahivatkozás