cláusula MATCH_RECOGNIZE

Aplica-se a: check marked yes Databricks SQL Databricks Runtime 19.0 and above

Importante

Esse recurso está em Beta. Os administradores do workspace podem controlar o acesso a esse recurso na página Visualizações . Consulte Gerenciar visualizações do Azure Databricks.

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

Os usos típicos incluem a detecção de execuções de valores consecutivos, movimentos de preço em forma de V ou W e a sessão de fluxos de eventos.

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 Partição BY [, ...]

Uma ou mais expressões que definem os grupos de linhas em que a correspondência de padrões é executada. Se você omitir PARTITION BY, a partição conterá todas as linhas.

PARTITION BY aceita apenas referências de coluna. Se você especificar outra expressão, Azure Databricks gerará MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.
ORDER BY order_by

Especifica a ordem das linhas em cada partição. As funções de correspondência e navegação de padrão usam essa ordem.
MEDIDAS

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

Controla quantas linhas são retornadas por correspondência. O padrão é ONE ROW PER MATCH.
- ONE ROW PER MATCH
  
  Retorna uma linha por correspondência. O resultado contém somente colunas de partição e colunas de medida.
- ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]
  
  Retorna uma linha para cada linha que participa de uma correspondência. Cada linha de saída inclui as colunas de entrada correspondentes das table_referencecolunas, PARTITION BY colunas e MEASURES colunas computadas para essa correspondência.
  
  SHOW EMPTY MATCHES é aceito ALL ROWS PER MATCHem , e é o padrão quando você omite a sub-cláusula de manipulação de correspondência vazia. Esta versão não produz correspondências vazias, portanto, a palavra-chave não tem nenhum efeito observável sobre o resultado.
APÓS O MATCH row_pattern_skip_to

Especifica de qual linha continuar após a correspondência ser encontrada. Esta versão só dá SKIP PAST LAST ROW suporte. Continue com a linha imediatamente após a última linha da correspondência atual. Esse é o padrão quando você omite a AFTER MATCH cláusula.
PATTERN ( row_pattern )

Especifica o padrão a ser correspondido.
DEFINIR row_pattern_definition_list

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

Result

O resultado depende do modo de linhas por correspondência:

ONE ROW PER MATCH

Retorna PARTITION BY colunas seguidas por MEASURES colunas.
ALL ROWS PER MATCH

Retorna uma linha para cada linha que participa de uma correspondência. Cada linha de saída inclui as colunas de entrada correspondentes das table_referencecolunas, PARTITION BY colunas e MEASURES colunas computadas para essa correspondência.

Condições de erro comuns

Exemplos

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

Exemplo 1: execução de aumento consecutiva

Encontre cada execução máxima de aumentos de preços consecutivos por símbolo. A variável strt não tem entrada DEFINE , portanto, corresponde a qualquer linha e ancora a execução. up+ estende a correspondência em um ou mais aumentos consecutivos. PREV(price) lê o preço da linha imediatamente anterior em ORDER BY ordem. ONE ROW PER MATCH emite uma única linha de resumo por execuçã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', 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 V (mergulhar e recuperar)

Detecte um preço que primeiro cai e depois sobe. down+ corresponde à perna caindo e up+ à recuperação. LAST(down.tstamp) escolhe a última linha classificada como down, que é o cocho do V. Uma referência qualificada por variável, como down.tstamp permite que uma MEASURES expressão leia linhas correspondentes a 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 W)

Detecte dois mergulhos separados por uma recuperação parcial. O padrão explica quatro pernas (down1+ up1+ down2+ up2+) e nomes de variáveis distintos permitem medir ou filtrar cada cocho de forma independente. MATCH_NUMBER() números cada W encontrado 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

Recolher o fluxo de eventos de um usuário em sessões, em que uma lacuna de mais de 30 minutos entre eventos consecutivos inicia uma nova sessão. strt abre uma sessão em qualquer linha. same_session* absorve todos os eventos a seguir que ocorrem dentro de 30 minutos de seu antecessor. Quando uma lacuna excede o limite, a correspondência termina, AFTER MATCH SKIP PAST LAST ROW é retomada no próximo evento e uma nova sessão (uma nova MATCH_NUMBER()) começa. O * quantificador torna um evento solitário 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

Comentários

Esta página foi útil?

Last updated on 2026-07-10