MATCH_RECOGNIZE-sats

Gäller för: Databricks Databricks Runtime 19.0 och senare

Important

Den här funktionen finns i Beta. Arbetsyteadministratörer kan styra åtkomsten till den här funktionen från sidan Förhandsversioner . Se Hantera förhandsversioner av Azure Databricks.

Söker efter och filtrerar mönster i raderna i föregående table_reference. MATCH_RECOGNIZE partitioner indata, beställer rader inom varje partition, matchar ett radmönster mot den ordnade sekvensen och returnerar sammanfattnings- eller resultat per rad beroende på rad-per-match-läge.

Vanliga användningsområden är att identifiera körningar av på varandra följande värden, V-formade eller W-formade prisrörelser och sessionisera händelseströmmar.

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

  Ett eller flera uttryck som definierar de grupper av rader där mönstermatchning körs. Om du utelämnar PARTITION BYinnehåller partitionen alla rader.

  PARTITION BY accepterar endast kolumnreferenser. Om du anger ett annat uttryck genererar Azure Databricks MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Anger ordningen på rader inom varje partition. Mönstermatchnings- och navigeringsfunktioner använder den här ordningen.

• MÅTT

  Du kan också definiera de måttkolumner som returneras för varje mönstermatchning.

• row_pattern_rows_per_match

  Styr hur många rader som returneras per matchning. Standardvärdet är ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Returnerar en rad per matchning. Resultatet innehåller endast partitionskolumner och måttkolumner.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Returnerar en rad för varje rad som deltar i en matchning. Varje utdatarad innehåller motsvarande indatakolumner från kolumnerna table_reference, PARTITION BY och MEASURES kolumnerna som beräknas för den matchningen.

    SHOW EMPTY MATCHES accepteras under ALL ROWS PER MATCH, och är standard när du utelämnar delsatsen empty-match-handling. Den här versionen ger inte tomma matchningar, så nyckelordet har ingen observerbar effekt på resultatet.

• EFTER MATCHNING row_pattern_skip_to

  Anger vilken rad som ska fortsätta från efter att en matchning har hittats. Den här versionen stöder SKIP PAST LAST ROW endast. Fortsätt med raden direkt efter den sista raden i den aktuella matchningen. Detta är standardvärdet när du utelämnar AFTER MATCH satsen.

• MÖNSTER ( row_pattern )

  Anger det mönster som ska matchas.

• DEFINIERA row_pattern_definition_list

  Definierar de booleska variabler som refereras till i PATTERN - och-satserna MEASURES .

Result

Resultatet beror på rad-per-match-läget:

• ONE ROW PER MATCH

  Returnerar PARTITION BY kolumner följt av MEASURES kolumner.

• ALL ROWS PER MATCH

  Returnerar en rad för varje rad som deltar i en matchning. Varje utdatarad innehåller motsvarande indatakolumner från kolumnerna table_reference, PARTITION BY och MEASURES kolumnerna som beräknas för den matchningen.

Vanliga felvillkor

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED (Åtgärder i MATCH_RECOGNIZE måste vara alias)
• MATCH_RECOGNIZE_PARTITION_BY_MÅSTE_VAR_KOLUMN

Examples

Varje fråga använder en stock_ticker(symbol, tstamp, price) tabell, förutom det sista exemplet som använder page_views(user_id, event_time).

Exempel 1: Efterföljande stigande körning

Hitta varje maximal körning av på varandra följande prisökningar per symbol. Variabeln strt har ingen DEFINE post, så den matchar alla rader och fäster körningen. up+ utökar matchningen över en eller flera på varandra följande ökningar. PREV(price) läser priset för den omedelbart föregående raden i ORDER BY ordning. ONE ROW PER MATCH genererar en enda sammanfattningsrad per körning.

> 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

Exempel 2: V-form (doppa och återställa)

Identifiera ett pris som först faller och sedan stiger. down+ matchar det fallande benet och up+ återhämtningen. LAST(down.tstamp) väljer den sista raden klassificerad som down, vilket är Tråg för V. En variabelkvalificerad referens, till exempel down.tstamp låter ett MEASURES uttryck läsa rader som matchas av en specifik mönstervariabel.

> 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

Exempel 3: Dubbel nederkant (W-form)

Identifiera två dips avgränsade med en partiell återställning. Mönstret stavar ut fyra ben (down1+ up1+ down2+ up2+) och distinkta variabelnamn låter dig mäta eller filtrera varje tråg oberoende av varandra. MATCH_NUMBER() siffror som varje W hittade i en partition.

> 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

Exempel 4: Sessionisering

Dölj en användares händelseström i sessioner, där ett mellanrum på mer än 30 minuter mellan efterföljande händelser startar en ny session. strt öppnar en session på valfri rad. same_session* absorberar varje följande händelse som inträffar inom 30 minuter från föregående. När ett mellanrum överskrider tröskelvärdet avslutas matchningen, AFTER MATCH SKIP PAST LAST ROW återupptas vid nästa händelse och en ny session (en ny MATCH_NUMBER()) börjar. Kvantifieraren * gör en ensam händelse till en giltig session på en rad.

> 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

Relaterade artiklar

• DEFINE-sats för MATCH_RECOGNIZE
• MÅTT-sats för MATCH_RECOGNIZE
• PATTERN-sats för MATCH_RECOGNIZE
• MATCH_RECOGNIZE funktioner
• tabellreferens