cláusula MATCH_RECOGNIZE

Se aplica a: check marcado yes Comprobación de SQL de Databricks marcada como sí Databricks Runtime 19.0 y versiones posteriores

Importante

Esta característica se encuentra en su versión beta. Los administradores del área de trabajo pueden controlar el acceso a esta característica desde la página Vistas previas . Consulte Administrar versiones preliminares de Azure Databricks.

Busca y filtra patrones en las filas del table_reference anterior. MATCH_RECOGNIZE particiona la entrada, ordena filas dentro de cada partición, coincide con un patrón de fila con esa secuencia ordenada y devuelve los resultados de resumen o por fila en función del modo de fila por coincidencia.

Los usos típicos incluyen la detección de ejecuciones de valores consecutivos, movimientos de precios en forma de V o W y flujos de eventos de sesión.

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 PARTICIÓN BY [, ...]

Una o varias expresiones que definen los grupos de filas en los que se ejecuta la coincidencia de patrones. Si omite PARTITION BY, la partición contiene todas las filas.

PARTITION BY solo acepta referencias de columna. Si especifica otra expresión, Azure Databricks genera MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.
ORDER BY order_by

Especifica el orden de las filas dentro de cada partición. Las funciones de navegación y coincidencia de patrones usan este orden.
MEDIDAS

Opcionalmente, define las columnas de medida devueltas para cada coincidencia de patrón.
row_pattern_rows_per_match

Controla cuántas filas se devuelven por coincidencia. El valor predeterminado es ONE ROW PER MATCH.
- ONE ROW PER MATCH
  
  Devuelve una fila por coincidencia. El resultado contiene solo columnas de partición y columnas de medida.
- ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]
  
  Devuelve una fila para cada fila que participa en una coincidencia. Cada fila de salida incluye las columnas de entrada correspondientes de las table_referencecolumnas , PARTITION BY y MEASURES las columnas calculadas para esa coincidencia.
  
  SHOW EMPTY MATCHES se acepta en ALL ROWS PER MATCHy es el valor predeterminado cuando se omite la sub cláusula empty-match-handling. Esta versión no genera coincidencias vacías, por lo que la palabra clave no tiene ningún efecto observable en el resultado.
AFTER MATCH row_pattern_skip_to

Especifica desde qué fila continuar después de encontrar una coincidencia. Esta versión solo admite SKIP PAST LAST ROW . Continúe con la fila inmediatamente después de la última fila de la coincidencia actual. Este es el valor predeterminado cuando se omite la AFTER MATCH cláusula .
PATTERN ( row_pattern )

Especifica el patrón que se va a coincidir.
DEFINIR row_pattern_definition_list

Define las variables booleanas a las que se hace referencia en las PATTERN cláusulas y MEASURES .

Result

El resultado depende del modo de fila por coincidencia:

ONE ROW PER MATCH

Devuelve PARTITION BY columnas seguidas de MEASURES columnas.
ALL ROWS PER MATCH

Devuelve una fila para cada fila que participa en una coincidencia. Cada fila de salida incluye las columnas de entrada correspondientes de las table_referencecolumnas , PARTITION BY y MEASURES las columnas calculadas para esa coincidencia.

Condiciones de error comunes

Ejemplos

Cada consulta usa una stock_ticker(symbol, tstamp, price) tabla, excepto el último ejemplo que usa page_views(user_id, event_time).

Ejemplo 1: Ejecución ascendente consecutiva

Busca cada ejecución máxima de aumentos de precios consecutivos por símbolo. La variable strt no tiene ninguna DEFINE entrada, por lo que coincide con ninguna fila y delimita la ejecución. up+ extiende la coincidencia entre uno o varios aumentos consecutivos. PREV(price) lee el precio de la fila inmediatamente anterior en ORDER BY orden. ONE ROW PER MATCH emite una sola fila de resumen por ejecución.

> 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

Ejemplo 2: forma V (dip y recuperación)

Detecte un precio que cae primero y, a continuación, aumenta. down+ coincide con la pierna que cae y up+ la recuperación. LAST(down.tstamp) elige la última fila clasificada como down, que es el vado de la V. Una referencia calificada por variables, como down.tstamp permite que una MEASURES expresión lea filas coincidentes con una variable de patrón 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

Ejemplo 3: Doble parte inferior (forma W)

Detecte dos caídas separadas por una recuperación parcial. El patrón escribe cuatro patas (down1+ up1+ down2+ up2+) y nombres de variable distintos le permiten medir o filtrar cada canal de forma independiente. MATCH_NUMBER() numera cada W que se encuentra dentro de una partición.

> 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

Ejemplo 4: Sessionization

Contraiga el flujo de eventos de un usuario en sesiones, donde una brecha de más de 30 minutos entre eventos consecutivos inicia una nueva sesión. strt abre una sesión en cualquier fila. same_session* absorbe cada evento siguiente que se produce en un plazo de 30 minutos a partir de su predecesor. Cuando un intervalo supera el umbral, la coincidencia finaliza, AFTER MATCH SKIP PAST LAST ROW se reanuda en el siguiente evento y comienza una sesión nueva (una nueva MATCH_NUMBER()). El * cuantificador hace que un evento solitario sea una sesión de una fila válida.

> 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

Comentarios

¿Le ha resultado útil esta página?

Last updated on 2026-07-10