clausola MATCH_RECOGNIZE

Si applica a: check contrassegnato come sì controllo SQL di Databricks Databricks Runtime 19.0 e versioni successive

Importante

Questa funzionalità è in versione beta. Gli amministratori dell'area di lavoro possono controllare l'accesso a questa funzionalità dalla pagina Anteprime . Vedere Gestire le anteprime di Azure Databricks.

Trova e filtra i modelli nelle righe del table_reference precedente. MATCH_RECOGNIZE partiziona l'input, ordina le righe all'interno di ogni partizione, corrisponde a un criterio di riga rispetto a tale sequenza ordinata e restituisce risultati di riepilogo o per riga a seconda della modalità rows-per-match.

Gli usi tipici includono il rilevamento di esecuzioni di valori consecutivi, movimenti di prezzo a forma di V o W e sessioni di flussi di eventi.

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

Una o più espressioni che definiscono i gruppi di righe in cui vengono eseguiti i criteri di ricerca. Se si omette PARTITION BY, la partizione contiene tutte le righe.

PARTITION BY accetta solo riferimenti a colonne. Se si specifica un'altra espressione, Azure Databricks genera MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.
ORDER BY order_by

Specifica l'ordine delle righe all'interno di ogni partizione. I criteri di ricerca e le funzioni di spostamento usano questo ordine.
MISURE

Facoltativamente, definisce le colonne di misura restituite per ogni corrispondenza del criterio.
row_pattern_rows_per_match

Controlla il numero di righe restituite per corrispondenza. Il valore predefinito è ONE ROW PER MATCH.
- ONE ROW PER MATCH
  
  Restituisce una riga per corrispondenza. Il risultato contiene solo colonne di partizione e colonne di misura.
- ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]
  
  Restituisce una riga per ogni riga che partecipa a una corrispondenza. Ogni riga di output include le colonne di input corrispondenti dalle table_referencecolonne , PARTITION BY e MEASURES dalle colonne calcolate per tale corrispondenza.
  
  SHOW EMPTY MATCHES viene accettato in ALL ROWS PER MATCHe è l'impostazione predefinita quando si omette la sottosegola vuota di gestione delle corrispondenze. Questa versione non produce corrispondenze vuote, quindi la parola chiave non ha alcun effetto osservabile sul risultato.
AFTER MATCH row_pattern_skip_to

Specifica la riga da cui continuare dopo la ricerca di una corrispondenza. Questa versione supporta SKIP PAST LAST ROW solo . Continuare con la riga immediatamente successiva all'ultima riga della corrispondenza corrente. Si tratta dell'impostazione predefinita quando si omette la AFTER MATCH clausola .
PATTERN ( row_pattern )

Specifica il criterio di corrispondenza.
DEFINE row_pattern_definition_list

Definisce le variabili booleane a cui si fa riferimento nelle PATTERN clausole e MEASURES .

Result

Il risultato dipende dalla modalità rows-per-match:

ONE ROW PER MATCH

Restituisce PARTITION BY colonne seguite da MEASURES colonne.
ALL ROWS PER MATCH

Restituisce una riga per ogni riga che partecipa a una corrispondenza. Ogni riga di output include le colonne di input corrispondenti dalle table_referencecolonne , PARTITION BY e MEASURES dalle colonne calcolate per tale corrispondenza.

Condizioni di errore comuni

Examples

Ogni query usa una stock_ticker(symbol, tstamp, price) tabella, ad eccezione dell'ultimo esempio che usa page_views(user_id, event_time).

Esempio 1: Sequenza di aumento consecutiva

Trova tutte le esecuzioni massime di aumenti di prezzo consecutivi per simbolo. La variabile strt non ha alcuna DEFINE voce, quindi corrisponde a qualsiasi riga e ancora l'esecuzione. up+ estende la corrispondenza tra uno o più aumenti consecutivi. PREV(price) legge il prezzo della riga immediatamente precedente nell'ordine ORDER BY . ONE ROW PER MATCH genera una singola riga di riepilogo per ogni esecuzione.

> 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

Esempio 2: forma V (immersione e recupero)

Rilevare un prezzo che cade per primo, quindi aumenta. down+ corrisponde alla gamba caduta e up+ al recupero. LAST(down.tstamp) seleziona l'ultima riga classificata come down, ovvero il trough della V. Un riferimento qualificato da variabile, ad down.tstamp esempio , consente a un'espressione MEASURES di leggere le righe corrispondenti a una variabile di pattern specifica.

> 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

Esempio 3: Doppio inferiore (forma W)

Rilevare due dip separati da un recupero parziale. Il modello specifica quattro gambe (down1+ up1+ down2+ up2+) e i nomi di variabili distinti consentono di misurare o filtrare ogni passaggio in modo indipendente. MATCH_NUMBER() numeri ogni W trovato all'interno di una partizione.

> 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

Esempio 4: Sessione

Comprimere il flusso di eventi di un utente in sessioni, in cui una distanza di oltre 30 minuti tra gli eventi consecutivi inizia una nuova sessione. strt apre una sessione in qualsiasi riga. same_session* assorbe ogni evento seguente che si verifica entro 30 minuti dal suo predecessore. Quando un gap supera la soglia, la corrispondenza termina, AFTER MATCH SKIP PAST LAST ROW riprende all'evento successivo e inizia una nuova sessione (un nuovo MATCH_NUMBER()). Il * quantificatore rende un evento solitario una sessione di una riga valida.

> 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

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2026-07-10