предложение MATCH_RECOGNIZE

Область применения:check помечена да проверка Databricks SQL помечено да Databricks Runtime 19.0 и выше

Important

Эта функция доступна в бета-версии. Администраторы рабочей области могут управлять доступом к этой функции на странице "Предварительные версии ". См. статью "Управление предварительными версиями Azure Databricks".

Находит и фильтрует шаблоны в строках предыдущей table_reference. MATCH_RECOGNIZE секционирует входные данные, упорядочивает строки в каждой секции, сопоставляет шаблон строки с упорядоченной последовательностью и возвращает сводку или результаты для каждой строки в зависимости от режима сопоставления строк.

Типичные варианты использования включают обнаружение последовательных значений, движения цен на фигуру V или W-фигуры и потоки событий сеансизации.

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 BY, раздел содержит все строки.

    PARTITION BY принимает только ссылки на столбцы. Если указать другое выражение, Azure Databricks вызывает MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

  • ORDER BY order_by

    Указывает порядок строк в каждой секции. Функции сопоставления шаблонов и навигации используют этот порядок.

  • МЕРЫ

    При необходимости определяет столбцы мер, возвращаемые для каждого соответствия шаблона.

  • row_pattern_rows_per_match

    Определяет, сколько строк возвращается на совпадение. Значение по умолчанию — ONE ROW PER MATCH.

    • ONE ROW PER MATCH

      Возвращает одну строку на совпадение. Результат содержит только столбцы секций и столбцы мер.

    • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

      Возвращает одну строку для каждой строки, которая участвует в совпадении. Каждая выходная строка включает соответствующие входные столбцы из table_referenceстолбцов, PARTITION BY столбцов и MEASURES столбцов, вычисляемых для этого соответствия.

      SHOW EMPTY MATCHES принимается в разделе ALL ROWS PER MATCHи по умолчанию используется, если не указана вложенная обработка пустого совпадения. Этот выпуск не создает пустые совпадения, поэтому ключевое слово не влияет на результат.

  • ПОСЛЕ ROW_PATTERN_SKIP_TO MATCH

    Указывает, какая строка будет продолжаться после обнаружения совпадения. Этот выпуск поддерживает SKIP PAST LAST ROW только. Перейдите к строке сразу после последней строки текущего совпадения. Это значение по умолчанию при опущении AFTER MATCH предложения.

  • PATTERN ( row_pattern )

    Указывает шаблон, соответствующий.

  • ОПРЕДЕЛЕНИЕ row_pattern_definition_list

    Определяет логические переменные, на которые ссылаются в PATTERN предложениях и MEASURES предложениях.

Результат

Результат зависит от режима сопоставления строк:

  • ONE ROW PER MATCH

    Возвращает PARTITION BY столбцы, за которыми MEASURES следует столбцы.

  • ALL ROWS PER MATCH

    Возвращает одну строку для каждой строки, которая участвует в совпадении. Каждая выходная строка включает соответствующие входные столбцы из table_referenceстолбцов, PARTITION BY столбцов и MEASURES столбцов, вычисляемых для этого соответствия.

Распространенные условия ошибки

Examples

Каждый запрос использует таблицу stock_ticker(symbol, tstamp, price) , за исключением последнего примера, который использует page_views(user_id, event_time).

Пример 1. Последовательный рост выполнения

Найдите каждый максимальный запуск последовательного увеличения цен на символ. Переменная strt не DEFINE имеет записи, поэтому она соответствует любой строке и привязывает выполнение. up+ расширяет совпадение по одному или нескольким последовательными увеличениями. PREV(price) считывает цену непосредственно предыдущей строки в ORDER BY порядке. ONE ROW PER MATCH выдает одну сводную строку для каждого запуска.

> 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

Пример 2. V-фигура (погружение и восстановление)

Определите цену, которая сначала падает, а затем поднимается. down+ соответствует падающей ноге и up+ восстановлению. LAST(down.tstamp) выбирает последнюю строку, классифицируемый как down, которая является тротым из V. Ссылка, соответствующая переменной, например down.tstamp позволяет MEASURES выражению считывать строки, соответствующие определенной переменной шаблона.

> 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

Пример 3. Двойное снизу (W-shape)

Обнаружение двух спадов, разделенных частичным восстановлением. Шаблон описывает четыре ноги (down1+ up1+ down2+ up2+), а различные имена переменных позволяют измерять или фильтровать каждый рывок независимо. MATCH_NUMBER() числа каждого W, найденного в разделе.

> 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

Пример 4. Сеансизация

Свернуть поток событий пользователя в сеансы, где разрыв в течение более чем 30 минут между последовательными событиями запускает новый сеанс. strt открывает сеанс в любой строке. same_session* поглощает каждое следующее событие, которое происходит в течение 30 минут от своего предшественника. Когда разрыв превышает пороговое значение, совпадение заканчивается, AFTER MATCH SKIP PAST LAST ROW возобновляется на следующем событии и начинается новый сеанс (новый MATCH_NUMBER()). Квантификатор * делает однострочное событие допустимым однострочном сеансом.

> 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
  • Last updated on