MATCH_RECOGNIZE-component

Van toepassing op: Databricks Databricks Runtime 19.0 en hoger

Important

Deze functie bevindt zich in de bètaversie. Werkruimtebeheerders kunnen de toegang tot deze functie beheren vanaf de pagina Previews . Zie Azure Databricks previews beheren.

Hiermee worden patronen in de rijen van de voorgaande table_reference gevonden en gefilterd. MATCH_RECOGNIZE partitioneert de invoer, orders rijen binnen elke partitie, komt overeen met een rijpatroon op basis van die geordende volgorde en retourneert samenvattings- of resultaten per rij, afhankelijk van de rij-per-overeenkomstmodus.

Typische toepassingen zijn het detecteren van uitvoeringen van opeenvolgende waarden, V-vormige of W-vormige prijsbewegingen en het sessien van gebeurtenisstromen.

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

  Een of meer expressies waarmee de groepen rijen worden gedefinieerd waarop patroonkoppelingen worden uitgevoerd. Als u weglaat PARTITION BY, bevat de partitie alle rijen.

  PARTITION BY accepteert alleen kolomverwijzingen. Als u een andere expressie opgeeft, wordt Azure Databricks MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Hiermee geeft u de volgorde van rijen binnen elke partitie. Patroonkoppelings- en navigatiefuncties gebruiken deze volgorde.

• METINGEN

  Definieer eventueel de metingkolommen die worden geretourneerd voor elke patroonovereenkomst.

• row_pattern_rows_per_match

  Hiermee bepaalt u hoeveel rijen per overeenkomst worden geretourneerd. De standaardwaarde is ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Retourneert één rij per overeenkomst. Het resultaat bevat alleen partitiekolommen en metingkolommen.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Retourneert één rij voor elke rij die deelneemt aan een overeenkomst. Elke uitvoerrij bevat de bijbehorende invoerkolommen van de table_referencekolommen, PARTITION BY kolommen en MEASURES kolommen die voor die overeenkomst zijn berekend.

    SHOW EMPTY MATCHES wordt geaccepteerd onder ALL ROWS PER MATCHen is de standaardwaarde wanneer u de subcomponent empty-match-handling weglaat. Deze release produceert geen lege overeenkomsten, dus het trefwoord heeft geen waarneembaar effect op het resultaat.

• ROW_PATTERN_SKIP_TO ROW_PATTERN_SKIP_TO

  Hiermee geeft u op van welke rij moet worden voortgezet nadat een overeenkomst is gevonden. Deze release ondersteunt SKIP PAST LAST ROW alleen. Ga door met de rij direct na de laatste rij van de huidige overeenkomst. Dit is de standaardinstelling wanneer u de AFTER MATCH component weglaat.

• PATTERN ( row_pattern )

  Hiermee geeft u het patroon dat moet overeenkomen.

• DEFINIËREN row_pattern_definition_list

  Definieert de Booleaanse variabelen waarnaar wordt verwezen in de PATTERN en MEASURES componenten.

Resultaat

Het resultaat is afhankelijk van de rij-per-overeenkomstmodus:

• ONE ROW PER MATCH

  Retourneert PARTITION BY kolommen gevolgd door MEASURES kolommen.

• ALL ROWS PER MATCH

  Retourneert één rij voor elke rij die deelneemt aan een overeenkomst. Elke uitvoerrij bevat de bijbehorende invoerkolommen van de table_referencekolommen, PARTITION BY kolommen en MEASURES kolommen die voor die overeenkomst zijn berekend.

Algemene foutvoorwaarden

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

Voorbeelden

Elke query maakt gebruik van een stock_ticker(symbol, tstamp, price) tabel, met uitzondering van het laatste voorbeeld dat wordt gebruikt page_views(user_id, event_time).

Voorbeeld 1: Opeenvolgende stijgende uitvoering

Zoek elke maximale uitvoering van opeenvolgende prijsverhogingen per symbool. De variabele strt heeft geen DEFINE vermelding, dus deze komt overeen met een rij en ankert de uitvoering. up+ breidt de overeenkomst uit op een of meer opeenvolgende verhogingen. PREV(price) leest de prijs van de direct voorafgaande rij in ORDER BY volgorde. ONE ROW PER MATCH verzendt één samenvattingsrij per uitvoering.

> 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

Voorbeeld 2: V-shape (dip en herstel)

Detecteer een prijs die eerst daalt en vervolgens stijgt. down+ komt overeen met het valbeen en up+ het herstel. LAST(down.tstamp) kiest de laatste rij die is geclassificeerd als down, wat de doorgang van de V is. Een variabele gekwalificeerde verwijzing, zoals down.tstamp laat een MEASURES expressie rijen lezen die overeenkomen met een specifieke patroonvariabele.

> 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

Voorbeeld 3: Dubbele onderzijde (W-shape)

Detecteer twee dips, gescheiden door een gedeeltelijk herstel. Met het patroon worden vier benen (down1+ up1+ down2+ up2+) en afzonderlijke variabelenamen beschreven, zodat u elke sleuf onafhankelijk kunt meten of filteren. MATCH_NUMBER() elke W die in een partitie is gevonden, wordt genummerd.

> 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

Voorbeeld 4: Sessionization

Vouw de gebeurtenisstroom van een gebruiker samen in sessies, waarbij een tussenruimte van meer dan 30 minuten tussen opeenvolgende gebeurtenissen een nieuwe sessie start. strt opent een sessie op een willekeurige rij. same_session* absorbeert elke volgende gebeurtenis die binnen 30 minuten na zijn voorganger plaatsvindt. Wanneer een hiaat de drempelwaarde overschrijdt, eindigt de overeenkomst, AFTER MATCH SKIP PAST LAST ROW wordt de volgende gebeurtenis hervat en wordt er een nieuwe sessie (een nieuwe MATCH_NUMBER()) gestart. De * kwantificator maakt een eenzame gebeurtenis een geldige sessie met één rij.

> 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

Verwante artikelen

• DEFINE-component voor MATCH_RECOGNIZE
• METING-component voor MATCH_RECOGNIZE
• PATTERN-component voor MATCH_RECOGNIZE
• MATCH_RECOGNIZE functies
• tabelreferentie