MATCH_RECOGNIZE cláusula

Aplica-se a: Databricks SQL Runtime 19.0 e superiores

Encontra e filtra padrões nas linhas do table_reference anterior. MATCH_RECOGNIZE particiona a entrada, ordena as linhas dentro de cada partição, corresponde um padrão de linhas com essa sequência ordenada e retorna resultados resumidos ou por linha dependendo do modo linhas por correspondência.

Utilizações típicas incluem a deteção de sequências de valores consecutivos, movimentos de preços em forma de V ou W, e a sessão de fluxos de eventos.

Sintaxe

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 POR partição [, ...]

  Uma ou mais expressões que definem os grupos de linhas em que ocorre a correspondência de padrões. Se omitires PARTITION BY, a partição contém todas as linhas.

  PARTITION BY aceita apenas referências a colunas. Se especificar outra expressão, Azure Databricks levanta MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Especifica a ordem das linhas dentro de cada partição. A correspondência de padrões e as funções de navegação utilizam esta ordem.

• MEDIDAS

  Opcionalmente, define as colunas de medida devolvidas para cada correspondência de padrão.

• row_pattern_rows_per_match

  Controla quantas linhas são devolvidas por partida. A predefinição é ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Devolve uma linha por partida. O resultado contém apenas colunas de partição e colunas de medida.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Devolve uma linha por cada linha que participa num jogo. Cada linha de saída inclui as correspondentes colunas de entrada das table_reference, PARTITION BY colunas e MEASURES colunas calculadas para essa correspondência.

    SHOW EMPTY MATCHES é aceite sob ALL ROWS PER MATCH, e é o padrão quando se omite a sub-cláusula de tratamento de correspondência vazia. Esta versão não produz correspondências vazias, pelo que a palavra-chave não tem efeito observável no resultado.

• APÓS row_pattern_skip_to

  Especifica a partir de qual linha continuar depois de encontrar uma correspondência. Esta versão suporta SKIP PAST LAST ROW apenas. Continue com a fila imediatamente após a última linha do jogo atual. Este é o padrão quando se omite a AFTER MATCH cláusula.

• PADRÃO ( row_pattern )

  Especifica o padrão a corresponder.

• DEFINA row_pattern_definition_list

  Define as variáveis booleanas referenciadas nas PATTERN cláusulas e MEASURES .

Result

O resultado depende do modo linhas por partida:

• ONE ROW PER MATCH

  Devolve PARTITION BY colunas seguidas de MEASURES colunas.

• ALL ROWS PER MATCH

  Devolve uma linha por cada linha que participa num jogo. Cada linha de saída inclui as correspondentes colunas de entrada das table_reference, PARTITION BY colunas e MEASURES colunas calculadas para essa correspondência.

Condições de erro comuns

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

Examples

Cada consulta usa uma stock_ticker(symbol, tstamp, price) tabela, exceto o último exemplo, que usa page_views(user_id, event_time).

Exemplo 1: Corrida ascendente consecutiva

Encontre cada sequência máxima de aumentos consecutivos de preço por símbolo. A variável strt não DEFINE tem entrada, por isso corresponde a qualquer linha e ancora a sequência. up+ prolonga a correspondência por um ou mais aumentos consecutivos. PREV(price) lê o preço da linha imediatamente anterior por ORDER BY ordem. ONE ROW PER MATCH emite uma única linha resumida por sequência.

> 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

Exemplo 2: Forma de V (mergulho e recuperação)

Detetar um preço que primeiro desce e depois sobe. down+ Corresponde à queda da perna e up+ à recuperação. LAST(down.tstamp) escolhe a última linha classificada como down, que é o vale do V. Uma referência qualificada por variáveis, como permite, down.tstamp a uma MEASURES expressão ler linhas correspondidas por uma variável de padrão específica.

> 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

Exemplo 3: Fundo duplo (forma de W)

Detetar duas quedas separadas por uma recuperação parcial. O padrão forma quatro pernas (down1+ up1+ down2+ up2+), e nomes distintos de variáveis permitem-lhe medir ou filtrar cada valha de forma independente. MATCH_NUMBER() números de cada W encontrados dentro de uma partição.

> 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

Exemplo 4: Sessão

Colapse o fluxo de eventos de um utilizador em sessões, onde um intervalo superior a 30 minutos entre eventos consecutivos inicia uma nova sessão. strt Abre uma sessão em qualquer linha. same_session* absorve todos os eventos seguintes que ocorrem dentro de 30 minutos após o seu antecessor. Quando um intervalo ultrapassa o limiar, o jogo termina, AFTER MATCH SKIP PAST LAST ROW recomeça no evento seguinte e começa uma nova sessão (uma nova MATCH_NUMBER()). O * quantificador torna um evento isolado uma sessão válida de uma linha.

> 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

Artigos relacionados

• Cláusula DEFINE para MATCH_RECOGNIZE
• cláusula MEASURES para MATCH_RECOGNIZE
• cláusula PATTERN para MATCH_RECOGNIZE
• MATCH_RECOGNIZE Funções
• referência de tabela