Identificação de sequências de linhas que correspondem a um padrão

Introdução

Em alguns casos, pode ser necessário identificar sequências de linhas de tabela que correspondam a um padrão. Por exemplo, você pode precisar:

  • Determinar quais usuários seguiram uma sequência específica de páginas e ações em seu site antes de abrir um tíquete de suporte ou fazer uma compra.

  • Encontrar os estoques com preços que seguiram uma recuperação em forma de V ou de W durante um período.

  • Procurar padrões em dados de sensor que possam indicar a proximidade de uma falha do sistema.

Para identificar sequências de linhas que correspondam a um padrão específico, use a subcláusula MATCH_RECOGNIZE da cláusula FROM.

Nota

Você não pode usar a cláusula MATCH_RECOGNIZE em uma expressão de tabela comum (CTE) **recursiva**.

Um exemplo simples que identifica uma sequência de linhas

Como exemplo, suponha que uma tabela contenha dados sobre preços de ações. Cada linha contém o preço de fechamento de cada ticker em um dia específico. A tabela contém as seguintes colunas:

Nome da coluna

Descrição

price_date

A data do preço de fechamento.

price

O preço de fechamento das ações nessa data.

Suponha que você queira detectar um padrão no qual o preço da ação diminui e depois aumenta, produzindo um formato em “V” no gráfico do preço da ação.

Chart of the Stock Price for "ABCD"

(Este exemplo não leva em conta os casos em que o preço das ações não muda de dia para dia).

Neste exemplo, para um determinado ticker, você quer encontrar sequências de linhas onde o valor na coluna price diminui antes de aumentar.

Sequence of rows that match the "V" pattern

Para cada sequência de linhas que corresponda a este padrão, você quer retornar:

  • Um número que identifica a sequência (a primeira sequência correspondente, a segunda sequência correspondente, etc.).

  • O dia anterior à redução do preço das ações.

  • O último dia em que o preço das ações aumentou.

  • O número de dias no padrão “V”.

  • O número de dias em que o preço das ações diminuiu.

  • O número de dias em que o preço das ações aumentou.

+---------+--------------+------------+------------+------------------+---------------+---------------+
| COMPANY | MATCH_NUMBER | START_DATE | END_DATE   | ROWS_IN_SEQUENCE | NUM_DECREASES | NUM_INCREASES |
|---------+--------------+------------+------------+------------------+---------------+---------------|
| ABCD    |            1 | 2020-10-01 | 2020-10-04 |                4 |             1 |             2 |
| ABCD    |            2 | 2020-10-04 | 2020-10-08 |                5 |             1 |             3 |
+---------+--------------+------------+------------+------------------+---------------+---------------+
Copy

A figura a seguir ilustra as reduções de preço (NUM_DECREASES) e os aumentos (NUM_INCREASES) dentro do padrão “V” que os dados retornados capturam. Note que ROWS_IN_SEQUENCE inclui uma linha inicial que não é contada em NUM_DECREASES ou NUM_INCREASES.

The number of price decreases and increases in each "V" pattern

Para produzir esta saída, você pode usar a cláusula MATCH_RECOGNIZE mostrada abaixo.

SELECT * FROM stock_price_history
  MATCH_RECOGNIZE(
    PARTITION BY company
    ORDER BY price_date
    MEASURES
      MATCH_NUMBER() AS match_number,
      FIRST(price_date) AS start_date,
      LAST(price_date) AS end_date,
      COUNT(*) AS rows_in_sequence,
      COUNT(row_with_price_decrease.*) AS num_decreases,
      COUNT(row_with_price_increase.*) AS num_increases
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO LAST row_with_price_increase
    PATTERN(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
    DEFINE
      row_with_price_decrease AS price < LAG(price),
      row_with_price_increase AS price > LAG(price)
  )
ORDER BY company, match_number;
Copy

Como mostrado acima, a cláusula MATCH_RECOGNIZE consiste em muitas subcláusulas, cada uma com um propósito diferente (por exemplo, especificar o padrão a atender, especificar os dados a retornar, etc.).

As próximas seções explicam cada uma das subcláusulas deste exemplo.

Configuração dos dados para este exemplo

Para configurar os dados utilizados neste exemplo, execute as seguintes instruções SQL:

create table stock_price_history (company TEXT, price_date DATE, price INT);
Copy
insert into stock_price_history values
    ('ABCD', '2020-10-01', 50),
    ('XYZ' , '2020-10-01', 89),
    ('ABCD', '2020-10-02', 36),
    ('XYZ' , '2020-10-02', 24),
    ('ABCD', '2020-10-03', 39),
    ('XYZ' , '2020-10-03', 37),
    ('ABCD', '2020-10-04', 42),
    ('XYZ' , '2020-10-04', 63),
    ('ABCD', '2020-10-05', 30),
    ('XYZ' , '2020-10-05', 65),
    ('ABCD', '2020-10-06', 47),
    ('XYZ' , '2020-10-06', 56),
    ('ABCD', '2020-10-07', 71),
    ('XYZ' , '2020-10-07', 50),
    ('ABCD', '2020-10-08', 80),
    ('XYZ' , '2020-10-08', 54),
    ('ABCD', '2020-10-09', 75),
    ('XYZ' , '2020-10-09', 30),
    ('ABCD', '2020-10-10', 63),
    ('XYZ' , '2020-10-10', 32);
Copy

Etapa 1: Especificação da ordem e do agrupamento de linhas

O primeiro passo para identificar uma sequência de linhas é definir o agrupamento e a ordem das linhas que se deseja pesquisar. Para o exemplo de encontrar um padrão “V” no preço das ações de uma empresa:

  • As linhas devem ser agrupadas por empresa, já que se deseja encontrar um padrão no preço para uma determinada empresa.

  • Dentro de cada grupo de linhas (os preços para uma determinada empresa), as linhas devem ser classificadas por data em ordem crescente.

Em uma cláusula MATCH_RECOGNIZE, você usa as subcláusulas PARTITION BY e ORDER BY para especificar o agrupamento e a ordem das linhas. Por exemplo:

MATCH_RECOGNIZE(
  PARTITION BY company
  ORDER BY price_date
  ...
)
Copy

Etapa 2: Definição do padrão a atender

Em seguida, determine o padrão que corresponde à sequência de linhas que você deseja encontrar.

Para especificar este padrão, você usa algo semelhante a uma expressão regular. Em expressões regulares, você usa uma combinação de literais e metacaracteres para especificar um padrão a ser atendido em uma cadeia de caracteres.

Por exemplo, para encontrar uma sequência de caracteres que inclua:

  • qualquer caractere único, seguido por

  • uma ou mais letras maiúsculas, seguidas por

  • uma ou mais letras minúsculas

você pode usar a seguinte expressão regular compatível com Perl:

.[A-Z]+[a-z]+
Copy

onde:

  • . corresponde a qualquer caractere único.

  • [A-Z]+ corresponde a uma ou mais letras maiúsculas.

  • [a-z]+ corresponde a uma ou mais letras minúsculas.

+ é um quantificador que especifica que um ou mais dos caracteres anteriores precisam corresponder.

Por exemplo, a expressão regular acima corresponde a sequências de caracteres como:

  • 1Stock

  • @SFComputing

  • %Fn

Em uma cláusula MATCH_RECOGNIZE, você usa uma expressão semelhante para especificar o padrão de linhas a corresponder. Neste caso, encontrar linhas que correspondam a um padrão “V” envolve encontrar uma sequência de linhas que inclua:

  • a linha antes que o preço das ações diminua, seguida por

  • uma ou mais linhas onde o preço das ações diminui, seguidas por

  • uma ou mais linhas onde o preço das ações aumenta

Você pode expressar isso como o seguinte padrão de linhas:

row_before_decrease row_with_price_decrease+ row_with_price_increase+
Copy

Os padrões de linhas consistem em variáveis de padrão, quantificadores (que são similares aos usados em expressões regulares), e operadores. Uma variável de padrão define uma expressão que é avaliada em relação a uma linha.

Neste padrão de linhas:

  • row_before_decrease row_with_price_decrease e row_with_price_increase são variáveis de padrão. As expressões para estas variáveis de padrão precisam ser avaliadas como:

    • qualquer linha (a linha antes que o preço das ações diminua)

    • uma linha onde o preço das ações diminui

    • uma linha onde o preço das ações aumenta

    row_before_decrease é semelhante a . em uma expressão regular. Na expressão regular a seguir, . corresponde a qualquer caractere único que apareça antes da primeira letra maiúscula no padrão.

    .[A-Z]+[a-z]+
    
    Copy

    Da mesma forma, no padrão de linhas, row_before_decrease corresponde a qualquer linha única que apareça antes da primeira linha com uma redução de preço.

  • Os quantificadores + após row_with_price_decrease e row_with_price_increase especificam que uma ou mais linhas de cada um deles devem coincidir.

Em uma cláusula MATCH_RECOGNIZE, você usa a subcláusula PATTERN para especificar o padrão de linhas a atender:

MATCH_RECOGNIZE(
  ...
  PATTERN(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
  ...
)
Copy

Para especificar as expressões para as variáveis de padrão, você usa a subcláusula DEFINE:

MATCH_RECOGNIZE(
  ...
  DEFINE
    row_with_price_decrease AS price < LAG(price)
    row_with_price_increase AS price > LAG(price)
  ...
)
Copy

onde:

  • row_before_decrease não precisa ser definido aqui porque deve ser avaliado para qualquer linha.

  • row_with_price_decrease é definido como uma expressão para uma linha com uma redução de preço.

  • row_with_price_increase é definido como uma expressão para uma linha com um aumento de preço.

Para comparar os preços em diferentes linhas, as definições dessas variáveis usam a função de navegação LAG() para especificar o preço para a linha anterior.

O padrão das linhas corresponde a duas sequências de linhas, como ilustrado abaixo:

First sequence of rows that match the "V" pattern
Second sequence of rows that match the "V" pattern

Para a primeira sequência de linhas correspondente:

  • row_before_decrease corresponde à linha com o preço de ação 50.

  • row_with_price_decrease corresponde à linha seguinte com o preço de ação 36.

  • row_with_price_increase corresponde às duas linhas seguintes com os preços de ações 39 e 42.

Para a segunda sequência de linhas correspondente:

  • row_before_decrease corresponde à linha com o preço de ação 42. (Esta é a mesma linha que se encontra no final da primeira sequência de linhas correspondente).

  • row_with_price_decrease corresponde à linha seguinte com o preço de ação 30.

  • row_with_price_increase corresponde às duas linhas seguintes com os preços de ações 47, 71 e 80.

Etapa 3: Especificação das linhas a retornar

MATCH_RECOGNIZE pode retornar:

  • uma única linha que resume cada sequência correspondente, ou

  • cada linha em cada sequência correspondente

Para este exemplo, você deseja retornar um resumo de cada sequência correspondente. Use a subcláusula ONE ROW PER MATCH para especificar que uma única linha deve ser devolvida para cada sequência correspondente.

MATCH_RECOGNIZE(
  ...
  ONE ROW PER MATCH
  ...
)
Copy

Etapa 4: Especificação das medidas a selecionar

Quando você usa ONE ROW PER MATCH, MATCH_RECOGNIZE não retorna nenhuma das colunas da tabela (exceto a coluna especificada por PARTITION BY), mesmo quando MATCH_RECOGNIZE está em uma instrução SELECT *. Para especificar os dados a serem retornados por esta instrução, você precisa definir medidas. As medidas são colunas adicionais de dados calculadas para cada sequência de linhas correspondente (por exemplo, a data de início da sequência, a data final da sequência, o número de dias na sequência, etc.).

Use a subcláusula MEASURES para especificar estas colunas adicionais a retornar na saída. O formato geral para definir uma medida é:

<expression> AS <column_name>
Copy

onde:

  • expression especifica as informações sobre a sequência que você deseja retornar. Para a expressão, você pode usar funções com colunas da tabela e variáveis de padrão que você definiu anteriormente.

  • column_name especifica o nome da coluna que será retornada na saída.

Para este exemplo, você pode definir as seguintes medidas:

  • Um número que identifica a sequência (a primeira sequência correspondente, a segunda sequência correspondente, etc.).

    Para esta medida, use a função MATCH_NUMBER(), que retorna o número da correspondência. Os números começam com 1 para a primeira correspondência de uma partição de linhas. Se houver várias partições, o número começa com 1 para cada partição.

  • O dia anterior à redução do preço das ações.

    Para esta medida, use a função FIRST(), que retorna o valor da expressão para a primeira linha na sequência correspondente. Neste exemplo, FIRST(price_date) retorna o valor da coluna price_date na primeira linha em cada sequência correspondente, que é a data antes do preço da ação ter diminuído.

  • O último dia em que o preço das ações aumentou.

    Para esta medida, use a função LAST(), que retorna o valor da expressão para a última linha na sequência correspondente.

  • O número de dias no padrão “V”.

    Para esta medida, use COUNT(*). Como você está especificando COUNT(*) na definição de uma medida, o asterisco (*) especifica que você quer contar todas as linhas em uma sequência correspondente (não todas as linhas da tabela).

  • O número de dias em que a ação diminuiu.

    Para esta medida, use COUNT(row_with_price_decrease.*). O período seguido por um asterisco (.*) especifica que você deseja contar todas as linhas em uma sequência correspondente que corresponda à variável de padrão row_with_price_decrease.

  • O número de dias em que a ação aumentou.

    Para esta medida, use COUNT(row_with_price_increase.*).

A seguir está a subcláusula MEASURES que define as medidas acima:

MATCH_RECOGNIZE(
  ...
  MEASURES
    MATCH_NUMBER() AS match_number,
    FIRST(price_date) AS start_date,
    LAST(price_date) AS end_date,
    COUNT(*) AS num_matching_rows,
    COUNT(row_with_price_decrease.*) AS num_decreases,
    COUNT(row_with_price_increase.*) AS num_increases
  ...
)
Copy

Veja a seguir um exemplo da produção com as medidas selecionadas:

+---------+--------------+------------+------------+-------------------+---------------+---------------+
| COMPANY | MATCH_NUMBER | START_DATE | END_DATE   | NUM_MATCHING_ROWS | NUM_DECREASES | NUM_INCREASES |
|---------+--------------+------------+------------+-------------------+---------------+---------------|
| ABCD    |            1 | 2020-10-01 | 2020-10-04 |                 4 |             1 |             2 |
| ABCD    |            2 | 2020-10-04 | 2020-10-08 |                 5 |             1 |             3 |
+---------+--------------+------------+------------+-------------------+---------------+---------------+
Copy

Como mencionado anteriormente, a saída inclui a coluna company porque a cláusula PARTITION BY especifica essa coluna.

Etapa 5: Especificação de onde continuar a encontrar a próxima correspondência

Após encontrar uma sequência de linhas correspondente, MATCH_RECOGNIZE continua a encontrar a próxima sequência correspondente. Você pode especificar onde MATCH_RECOGNIZE deve começar a pesquisar pela próxima sequência correspondente.

Como mostrado na ilustração de sequências correspondentes, uma linha pode ser parte de mais de uma sequência correspondente. Neste exemplo, a linha para 2020-10-04 faz parte de dois padrões em “V”.

Para este exemplo, para encontrar a próxima sequência correspondente, você pode começar de uma linha onde o preço aumentou. Para especificar isto na cláusula MATCH_RECOGNIZE, use AFTER MATCH SKIP:

MATCH_RECOGNIZE(
  ...
  AFTER MATCH SKIP TO LAST row_with_price_increase
  ...
)
Copy

onde TO LAST row_with_price_increase especifica que você quer começar a pesquisar na última linha onde o preço aumentou.

Partição e classificação das linhas

O primeiro passo para identificar padrões em linhas é ordená-las de forma a encontrar seus padrões. Por exemplo, se você quiser encontrar um padrão de alterações nos preços das ações ao longo do tempo para as ações de cada empresa:

  • Divida as linhas por empresa, para que você possa pesquisar os preços de ações de cada empresa.

  • Ordene as linhas em cada partição por data, para que você possa encontrar alterações no preço das ações de uma empresa ao longo do tempo.

Para particionar os dados e especificar a ordem das linhas, use as subcláusulas PARTITION BY e ORDER BY em MATCH_RECOGNIZE. Por exemplo:

SELECT ...
    FROM stock_price_history
        MATCH_RECOGNIZE (
            PARTITION BY company
            ORDER BY price_date
            ...
        );
Copy

(A cláusula PARTITION BY para MATCH_RECOGNIZE funciona da mesma forma que a cláusula PARTITION BY para funções de janela).

Um benefício adicional do particionamento é que ele pode tirar proveito do processamento paralelo.

Definição do padrão de linhas a atender

Com MATCH_RECOGNIZE, você pode encontrar uma sequência de linhas que correspondem a um padrão. Você especifica este padrão em termos de linhas que correspondem a condições específicas.

No exemplo da tabela de preços diários de ações para diferentes empresas, suponha que você queira encontrar uma sequência de três linhas na qual:

  • Em um determinado dia, o preço das ações de uma empresa é inferior a 45,00.

  • No dia seguinte, o preço das ações diminui em pelo menos 10%.

  • No outro dia, o preço das ações aumenta em pelo menos 3%.

Para encontrar essa sequência, você especifica um padrão que corresponde a três linhas com as seguintes condições:

  • Na primeira linha da sequência, o valor da coluna price deve ser inferior a 45,00.

  • Na segunda linha, o valor da coluna price deve ser menor ou igual a 90% do valor da linha anterior.

  • Na terceira linha, o valor da coluna price deve ser maior ou igual a 105% do valor da linha anterior.

A segunda e terceira linhas têm condições que exigem uma comparação entre os valores das colunas em diferentes linhas. Para comparar o valor em uma linha com o valor na linha anterior ou seguinte, use as funções LAG() ou LEAD():

  • LAG(column) retorna o valor de column na linha anterior.

  • LEAD(column) retorna o valor de column na linha seguinte.

Para este exemplo, é possível especificar as condições para as três linhas como:

  • A primeira linha da sequência deve ter price < 45.00.

  • A segunda linha deve ter LAG(price) * 0.90 >= price.

  • A terceira linha deve ter LAG(price) * 1.05 <= price.

Ao especificar o padrão para a sequência dessas três linhas, você usa uma variável de padrão para cada linha que tem uma condição diferente. Use a subcláusula DEFINE para definir cada variável de padrão como uma linha que deve atender a uma condição especificada. O exemplo a seguir define três variáveis de padrão para as três linhas:

define
    low_priced_stock as price < 45.00,
    decreased_10_percent as lag(price) * 0.90 >= price,
    increased_05_percent as lag(price) * 1.05 <= price
Copy

Para definir o próprio padrão, use a subcláusula PATTERN. Nessa subcláusula, use uma expressão regular para especificar o padrão a atender. Para os blocos de construção da expressão, use as variáveis de padrão que você definiu. Por exemplo, o seguinte padrão encontra a sequência de três linhas:

pattern ( low_priced_stock  decreased_10_percent  increased_05_percent )
Copy

A instrução SQL abaixo usa as subcláusulas DEFINE e PATTERN mostradas acima:

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 DECREASED_10_PERCENT INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY company, price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| ABCD    | 2020-10-04 |    42 |
| ABCD    | 2020-10-05 |    30 |
| ABCD    | 2020-10-06 |    47 |
+---------+------------+-------+
Copy

As próximas seções explicam como definir padrões que correspondem a números específicos de linhas e linhas que aparecem no início ou no final de uma partição.

Nota

MATCH_RECOGNIZE usa rastreamento inverso para atender padrões. Como é o caso de outros mecanismos de expressão regular que usam rastreamento inverso, algumas combinações de padrões e dados para correspondência podem levar muito tempo para serem executadas, o que pode resultar em altos custos de computação.

Para melhorar o desempenho, defina um padrão que seja o mais específico possível:

  • Certifique-se de que cada linha corresponda apenas a um símbolo ou a um pequeno número de símbolos

  • Evite usar símbolos que façam correspondência a cada linha (por exemplo, símbolos que não estejam na cláusula DEFINE ou símbolos que sejam definidos como verdadeiro)

  • Defina um limite superior para quantificadores (por exemplo, {,10} em vez de *).

Por exemplo, o seguinte padrão pode resultar em aumento de custos se nenhuma linha corresponder:

symbol1+ any_symbol* symbol2
Copy

Se houver um limite superior para o número de linhas que você deseja fazer a correspondência, você pode especificar esse limite nos quantificadores para melhorar o desempenho. Além disso, em vez de especificar que você deseja encontrar any_symbol depois de symbol1, você pode procurar uma linha que não seja symbol1 (not_symbol1, neste exemplo);

symbol1{1,limit} not_symbol1{,limit} symbol2
Copy

Em geral, você deve monitorar o tempo de execução da consulta para verificar se ela não está demorando mais do que o esperado.

Uso de quantificadores com variáveis de padrão

Na subcláusula PATTERN, você usa uma expressão regular para especificar um padrão de linhas a corresponder. Você usa variáveis de padrão para identificar linhas na sequência que atendam a condições específicas.

Se precisar corresponder várias linhas que atendam a uma condição específica, você pode usar um quantificador, como faria em uma expressão regular.

Por exemplo, você pode usar o quantificador + para especificar que o padrão precisa incluir uma ou mais linhas nas quais o preço da ação diminui 10%, seguida por uma ou mais linhas nas quais o preço da ação aumenta 5%:

pattern (decreased_10_percent+ increased_05_percent+)
define
    decreased_10_percent as lag(price) * 0.90 >= price,
    increased_05_percent as lag(price) * 1.05 <= price
Copy

Correspondência de padrões relativos ao início ou ao fim de uma partição

Para encontrar uma sequência de linhas relativas ao início ou ao fim de uma partição, pode-se usar os metacaracteres ^ e $ na subcláusula PATTERN. Esses metacaracteres em um padrão de linha têm um propósito semelhante que os mesmos metacaracteres têm em uma expressão regular:

  • ^ representa o início de uma partição.

  • $ representa o fim de uma partição.

O padrão a seguir corresponde a uma ação com preço maior que 75,00 no início da partição:

PATTERN (^ GT75)
DEFINE
    GT75 AS price > 75.00
Copy

Observe que ^ e $ especificam as posições e não representam as linhas nessas posições (muito parecido com ^ e $ em uma expressão regular especificando a posição e não os caracteres nessas posições). Em PATTERN (^ GT75), a primeira linha (não a segunda) deve ter um preço superior a 75,00. Em PATTERN (GT75 $), a última linha (não a penúltima) deve ser maior que 75.

Aqui está um exemplo completo com ^. Observe que embora o estoque XYZ tenha um preço superior a 60,00 em mais de uma linha nesta partição, somente a linha no início da partição é considerada uma correspondência.

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #"
           ALL ROWS PER MATCH
           PATTERN (^ GT60)
           DEFINE
               GT60 AS price > 60.00
           )
    ORDER BY "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # |
|---------+------------+-------+---------+------------------|
| XYZ     | 2020-10-01 |    89 |       1 |                1 |
+---------+------------+-------+---------+------------------+
Copy

Aqui está um exemplo completo com $. Observe que, embora a ação ABCD tenha um preço superior a 50,00 em mais de uma linha nesta partição, somente a linha no final da partição é considerada uma correspondência.

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #"
           ALL ROWS PER MATCH
           PATTERN (GT50 $)
           DEFINE
               GT50 AS price > 50.00
           )
    ORDER BY "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # |
|---------+------------+-------+---------+------------------|
| ABCD    | 2020-10-10 |    63 |       1 |                1 |
+---------+------------+-------+---------+------------------+
Copy

Especificação de linhas da saída

Instruções que utilizam MATCH_RECOGNIZE podem escolher quais linhas produzir.

Geração de uma linha vs. geração de todas as linhas para cada correspondência

Quando MATCH_RECOGNIZE encontra uma correspondência, a saída pode ser uma linha de resumo para toda a correspondência ou uma linha para cada ponto de dados no padrão.

  • ALL ROWS PER MATCH especifica que a saída inclui todas as linhas na correspondência.

  • ONE ROW PER MATCH especifica que a saída inclui apenas uma linha para cada correspondência em cada partição.

    A cláusula de projeção da instrução SELECT pode usar apenas a saída da instrução MATCH_RECOGNIZE. Com efeito, isto significa que a instrução SELECT só pode usar colunas das seguintes subcláusulas de MATCH_RECOGNIZE:

    • A subcláusula PARTITION BY.

      Todas as linhas em uma correspondência são da mesma partição e, portanto, têm o mesmo valor para as expressões da subcláusula PARTITION BY.

    • A cláusula MEASURES.

      Quando você usa MATCH_RECOGNIZE ... ONE ROW PER MATCH, a subcláusula MEASURES gera não apenas expressões que retornam o mesmo valor para todas as linhas na correspondência (por exemplo, MATCH_NUMBER()), mas também expressões que podem retornar valores diferentes para linhas diferentes na correspondência (por exemplo, MATCH_SEQUENCE_NUMBER()). Se você usa expressões que podem retornar valores diferentes para linhas diferentes na correspondência, a saída não é determinística.

    Se você está familiarizado com funções agregadas e GROUP BY, a seguinte analogia pode ser útil na compreensão de ONE ROW PER MATCH:

    • A cláusula PARTITION BY em MATCH_RECOGNIZE agrupa os dados de forma semelhante à forma como GROUP BY agrupa os dados em um SELECT.

    • A cláusula MEASURES em um MATCH_RECOGNIZE ... ONE ROW PER MATCH permite funções agregadas, tais como COUNT(), que retornam o mesmo valor para cada linha da correspondência, como faz MATCH_NUMBER().

    Se você usar apenas funções agregadas e expressões que retornam o mesmo valor para cada linha da correspondência, então ... ONE ROW PER MATCH se comporta de forma semelhante a GROUP BY e funções agregadas.

O padrão é ONE ROW PER MATCH.

Os exemplos a seguir mostram a diferença nas saídas entre ONE ROW PER MATCH e ALL ROWS PER MATCH. Estes dois exemplos de código são quase idênticos, exceto pela cláusula ...ROW(S) PER MATCH. (Em um uso típico, uma instrução SQL com ONE ROW PER MATCH tem subcláusulas MEASURES diferentes de uma instrução SQL com ALL ROWS PER MATCH).

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #",
               COUNT(*) AS "Num Rows In Match"
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 UP UP)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP AS price > LAG(price)
           )
    WHERE company = 'ABCD'
    ORDER BY "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+-------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # | Num Rows In Match |
|---------+------------+-------+---------+------------------+-------------------|
| ABCD    | 2020-10-02 |    36 |       1 |                1 |                 1 |
| ABCD    | 2020-10-03 |    39 |       1 |                2 |                 2 |
| ABCD    | 2020-10-04 |    42 |       1 |                3 |                 3 |
| ABCD    | 2020-10-05 |    30 |       2 |                1 |                 1 |
| ABCD    | 2020-10-06 |    47 |       2 |                2 |                 2 |
| ABCD    | 2020-10-07 |    71 |       2 |                3 |                 3 |
+---------+------------+-------+---------+------------------+-------------------+

-- As you can see, the MATCH_SEQUENCE_NUMBER isn't useful when using
-- "ONE ROW PER MATCH". But the COUNT(*), which wasn't very useful in
-- "ALL ROWS PER MATCH", is useful here.
SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #",
               COUNT(*) AS "Num Rows In Match"
           ONE ROW PER MATCH
           PATTERN (LESS_THAN_45 UP UP)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP AS price > LAG(price)
           )
    WHERE company = 'ABCD'
    ORDER BY "Match #", "Match Sequence #";
+---------+---------+------------------+-------------------+
| COMPANY | Match # | Match Sequence # | Num Rows In Match |
|---------+---------+------------------+-------------------|
| ABCD    |       1 |                3 |                 3 |
| ABCD    |       2 |                3 |                 3 |
+---------+---------+------------------+-------------------+
Copy

Exclusão de linhas da saída

Para algumas consultas, você pode querer incluir apenas parte do padrão na saída. Por exemplo, você pode querer encontrar padrões em que as ações subiram muitos dias seguidos, mas exibir apenas os picos e algumas informações resumidas (por exemplo, o número de dias de aumento de preços antes de cada pico).

Você pode usar uma sintaxe de exclusão no padrão para dizer a MATCH_RECOGNIZE para pesquisar uma determinada variável de padrão sem incluí-la na saída. Para incluir uma variável de padrão como parte do padrão a ser pesquisado, mas não como parte da saída, use a notação {- <variável_de_padrão> -}.

Aqui está um exemplo simples que mostra a diferença entre utilizar a sintaxe de exclusão e não utilizá-la. Este exemplo contém duas consultas, cada uma das quais pesquisa um preço de estoque que começou em menos de $45, depois diminuiu e depois aumentou. A primeira consulta não usa sintaxe de exclusão e, portanto, mostra todas as linhas. A segunda consulta usa a sintaxe de exclusão e não mostra o dia em que o preço das ações caiu.

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 DECREASED_10_PERCENT INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| ABCD    | 2020-10-04 |    42 |
| ABCD    | 2020-10-05 |    30 |
| ABCD    | 2020-10-06 |    47 |
+---------+------------+-------+
Copy
SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 {- DECREASED_10_PERCENT -} INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| ABCD    | 2020-10-04 |    42 |
| ABCD    | 2020-10-06 |    47 |
+---------+------------+-------+
Copy

O próximo exemplo é mais realista. Ele pesquisa padrões em que o preço de uma ação subiu um ou mais dias seguidos, e depois caiu um ou mais dias seguidos. Como a saída pode ser bastante grande, ele usa a exclusão para mostrar apenas o primeiro dia em que a ação subiu (se subiu mais de um dia seguido) e apenas o primeiro dia em que caiu (se caiu mais de um dia seguido). O padrão é mostrado abaixo:

PATTERN(LESS_THAN_45 UP {- UP* -} DOWN {- DOWN* -})
Copy

Este padrão pesquisa os seguintes eventos em ordem:

  • Um preço inicial inferior a 45.

  • Um UP, possivelmente seguido imediatamente por outros que não estão incluídos na saída.

  • Um DOWN, possivelmente seguido imediatamente por outros que não estão incluídos na saída.

Aqui estão o código e a saída para versões do padrão anterior sem exclusão e com exclusão:

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN ( LESS_THAN_45 UP UP* DOWN DOWN* )
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP   AS price > LAG(price),
               DOWN AS price < LAG(price)
           )
    WHERE company = 'XYZ'
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| XYZ     | 2020-10-02 |    24 |
| XYZ     | 2020-10-03 |    37 |
| XYZ     | 2020-10-04 |    63 |
| XYZ     | 2020-10-05 |    65 |
| XYZ     | 2020-10-06 |    56 |
| XYZ     | 2020-10-07 |    50 |
+---------+------------+-------+
Copy
SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN ( {- LESS_THAN_45 -}  UP  {- UP* -}  DOWN  {- DOWN* -} )
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP   AS price > LAG(price),
               DOWN AS price < LAG(price)
           )
    WHERE company = 'XYZ'
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| XYZ     | 2020-10-03 |    37 |
+---------+------------+-------+
Copy

Retorno de informações sobre a correspondência

Informações básicas sobre correspondências

Em muitos casos, você deseja que sua consulta liste não apenas informações da tabela que contém os dados, mas também informações sobre os padrões que foram encontrados. Quando você quer informações sobre as próprias correspondências, especifica essas informações na cláusula MEASURES.

A cláusula MEASURES pode incluir as seguintes funções, que são específicas de MATCH_RECOGNIZE:

  • MATCH_NUMBER(): Cada vez que uma partida é encontrada, é atribuído a ela um número de correspondência sequencial, começando em um. Esta função retorna o número de correspondência.

  • MATCH_SEQUENCE_NUMBER(): Como um padrão geralmente envolve mais de um ponto de dados, você pode querer saber qual ponto de dados está associado a cada valor da tabela. Esta função retorna o número sequencial do ponto de dados dentro da correspondência.

  • CLASSIFIER(): O classificador é o nome da variável de padrão à qual a linha corresponde.

A consulta abaixo inclui uma cláusula MEASURES com o número da correspondência, o número da sequência da correspondência e o classificador.

SELECT company, price_date, price,
       "Match #", "Match Sequence #", "Symbol Matched"
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #",
               CLASSIFIER AS "Symbol Matched"
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 DECREASED_10_PERCENT INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY company, "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+----------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # | Symbol Matched       |
|---------+------------+-------+---------+------------------+----------------------|
| ABCD    | 2020-10-04 |    42 |       1 |                1 | LESS_THAN_45         |
| ABCD    | 2020-10-05 |    30 |       1 |                2 | DECREASED_10_PERCENT |
| ABCD    | 2020-10-06 |    47 |       1 |                3 | INCREASED_05_PERCENT |
+---------+------------+-------+---------+------------------+----------------------+
Copy

A subcláusula MEASURES pode produzir muito mais informações do que isso. Para obter mais detalhes, consulte a documentação de referência de MATCH_RECOGNIZE.

Janelas, quadros de janela e funções de navegação

A cláusula MATCH_RECOGNIZE opera em uma “janela” de linhas. Se MATCH_RECOGNIZE contiver uma subcláusula PARTITION, então cada partição é uma janela. Se não houver subcláusula PARTITION, então toda a entrada é uma única janela.

A subcláusula PATTERN de MATCH_RECOGNIZE especifica os símbolos da esquerda para a direita. Por exemplo:

PATTERN (START DOWN UP)
Copy

Se você imaginar os dados como uma sequência de linhas em ordem crescente da esquerda para a direita, pode pensar em MATCH_RECOGNIZE como se movendo para a direita (por exemplo, da data mais antiga para a data mais recente no exemplo do preço de ações), pesquisando por um padrão nas linhas dentro de cada janela.

MATCH_RECOGNIZE começa com a primeira linha na janela e verifica se essa linha e as linhas subsequentes correspondem ao padrão.

No caso mais simples, depois de determinar se há uma correspondência de padrão começando na primeira linha da janela, MATCH_RECOGNIZE move-se para a direita uma linha e repete o processo, verificando se a segunda linha é o início de uma ocorrência do padrão. MATCH_RECOGNIZE continua se movendo para a direita até chegar ao final da janela.

(MATCH_RECOGNIZE pode mover-se para a direita por mais de uma linha. Por exemplo, você pode dizer a MATCH_RECOGNIZE para começar a pesquisar o próximo padrão após o final do padrão atual).

Imagine isso como se houvesse um “quadro” movendo-se para a direita dentro da janela. A borda esquerda desse quadro está na primeira linha do conjunto de linhas que estão sendo verificadas para uma correspondência. A borda direita do quadro não é definida até que uma correspondência seja encontrada; quando isso acontece, a borda direita do quadro é a última linha da correspondência. Por exemplo, se o padrão de pesquisa fosse pattern (start down up), então a linha que corresponde a up é a última linha antes da borda direita do quadro.

(Se não for encontrada nenhuma correspondência, então a borda direita do quadro nunca é definida e nunca é referenciada).

Em casos simples, você pode imaginar um quadro de janela deslizante, como ilustrado abaixo:

3-day sliding window frame in 7-day window

Você já viu funções de navegação tais como LAG() utilizadas em expressões na subcláusula DEFINE (por exemplo, DEFINE down_10_percent as LAG(price) * 0.9 >= price). A consulta a seguir mostra que as funções de navegação também podem ser usadas na subcláusula MEASURES. Neste exemplo, as funções de navegação mostram as bordas (e portanto o tamanho) do quadro da janela que contém a correspondência atual.

Cada linha de saída desta consulta inclui os valores das funções de navegação LAG(), LEAD(), FIRST(), e LAST() para aquela linha. O tamanho do quadro da janela é o número de linhas entre FIRST() e LAST(), incluindo a primeira e a última linhas propriamente ditas.

As cláusulas DEFINE e PATTERN na consulta abaixo selecionam grupos de três linhas (1-3 de outubro, 2-4 de outubro, 3-5 de outubro, etc.).

SELECT company, price_date,
       "First(price_date)", "Lag(price_date)", "Lead(price_date)", "Last(price_date)",
       "Match#", "MatchSeq#", "Classifier"
    FROM stock_price_history
        MATCH_RECOGNIZE (
            PARTITION BY company
            ORDER BY price_date
            MEASURES
                -- Show the "edges" of the "window frame".
                FIRST(price_date) AS "First(price_date)",
                LAG(price_date) AS "Lag(price_date)",
                LEAD(price_date) AS "Lead(price_date)",
                LAST(price_date) AS "Last(price_date)",
                MATCH_NUMBER() AS "Match#",
                MATCH_SEQUENCE_NUMBER() AS "MatchSeq#",
                CLASSIFIER AS "Classifier"
            ALL ROWS PER MATCH
            AFTER MATCH SKIP TO NEXT ROW
            PATTERN (CURRENT_ROW T2 T3)
            DEFINE
                CURRENT_ROW AS TRUE,
                T2 AS TRUE,
                T3 AS TRUE
            )
    ORDER BY company, "Match#", "MatchSeq#"
    ;
+---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------+
| COMPANY | PRICE_DATE | First(price_date) | Lag(price_date) | Lead(price_date) | Last(price_date) | Match# | MatchSeq# | Classifier  |
|---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------|
| ABCD    | 2020-10-01 | 2020-10-01        | NULL            | 2020-10-02       | 2020-10-01       |      1 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-02 | 2020-10-01        | 2020-10-01      | 2020-10-03       | 2020-10-02       |      1 |         2 | T2          |
| ABCD    | 2020-10-03 | 2020-10-01        | 2020-10-02      | NULL             | 2020-10-03       |      1 |         3 | T3          |
| ABCD    | 2020-10-02 | 2020-10-02        | NULL            | 2020-10-03       | 2020-10-02       |      2 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-03 | 2020-10-02        | 2020-10-02      | 2020-10-04       | 2020-10-03       |      2 |         2 | T2          |
| ABCD    | 2020-10-04 | 2020-10-02        | 2020-10-03      | NULL             | 2020-10-04       |      2 |         3 | T3          |
| ABCD    | 2020-10-03 | 2020-10-03        | NULL            | 2020-10-04       | 2020-10-03       |      3 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-04 | 2020-10-03        | 2020-10-03      | 2020-10-05       | 2020-10-04       |      3 |         2 | T2          |
| ABCD    | 2020-10-05 | 2020-10-03        | 2020-10-04      | NULL             | 2020-10-05       |      3 |         3 | T3          |
| ABCD    | 2020-10-04 | 2020-10-04        | NULL            | 2020-10-05       | 2020-10-04       |      4 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-05 | 2020-10-04        | 2020-10-04      | 2020-10-06       | 2020-10-05       |      4 |         2 | T2          |
| ABCD    | 2020-10-06 | 2020-10-04        | 2020-10-05      | NULL             | 2020-10-06       |      4 |         3 | T3          |
| ABCD    | 2020-10-05 | 2020-10-05        | NULL            | 2020-10-06       | 2020-10-05       |      5 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-06 | 2020-10-05        | 2020-10-05      | 2020-10-07       | 2020-10-06       |      5 |         2 | T2          |
| ABCD    | 2020-10-07 | 2020-10-05        | 2020-10-06      | NULL             | 2020-10-07       |      5 |         3 | T3          |
| ABCD    | 2020-10-06 | 2020-10-06        | NULL            | 2020-10-07       | 2020-10-06       |      6 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-07 | 2020-10-06        | 2020-10-06      | 2020-10-08       | 2020-10-07       |      6 |         2 | T2          |
| ABCD    | 2020-10-08 | 2020-10-06        | 2020-10-07      | NULL             | 2020-10-08       |      6 |         3 | T3          |
| ABCD    | 2020-10-07 | 2020-10-07        | NULL            | 2020-10-08       | 2020-10-07       |      7 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-08 | 2020-10-07        | 2020-10-07      | 2020-10-09       | 2020-10-08       |      7 |         2 | T2          |
| ABCD    | 2020-10-09 | 2020-10-07        | 2020-10-08      | NULL             | 2020-10-09       |      7 |         3 | T3          |
| ABCD    | 2020-10-08 | 2020-10-08        | NULL            | 2020-10-09       | 2020-10-08       |      8 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-09 | 2020-10-08        | 2020-10-08      | 2020-10-10       | 2020-10-09       |      8 |         2 | T2          |
| ABCD    | 2020-10-10 | 2020-10-08        | 2020-10-09      | NULL             | 2020-10-10       |      8 |         3 | T3          |
| XYZ     | 2020-10-01 | 2020-10-01        | NULL            | 2020-10-02       | 2020-10-01       |      1 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-02 | 2020-10-01        | 2020-10-01      | 2020-10-03       | 2020-10-02       |      1 |         2 | T2          |
| XYZ     | 2020-10-03 | 2020-10-01        | 2020-10-02      | NULL             | 2020-10-03       |      1 |         3 | T3          |
| XYZ     | 2020-10-02 | 2020-10-02        | NULL            | 2020-10-03       | 2020-10-02       |      2 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-03 | 2020-10-02        | 2020-10-02      | 2020-10-04       | 2020-10-03       |      2 |         2 | T2          |
| XYZ     | 2020-10-04 | 2020-10-02        | 2020-10-03      | NULL             | 2020-10-04       |      2 |         3 | T3          |
| XYZ     | 2020-10-03 | 2020-10-03        | NULL            | 2020-10-04       | 2020-10-03       |      3 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-04 | 2020-10-03        | 2020-10-03      | 2020-10-05       | 2020-10-04       |      3 |         2 | T2          |
| XYZ     | 2020-10-05 | 2020-10-03        | 2020-10-04      | NULL             | 2020-10-05       |      3 |         3 | T3          |
| XYZ     | 2020-10-04 | 2020-10-04        | NULL            | 2020-10-05       | 2020-10-04       |      4 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-05 | 2020-10-04        | 2020-10-04      | 2020-10-06       | 2020-10-05       |      4 |         2 | T2          |
| XYZ     | 2020-10-06 | 2020-10-04        | 2020-10-05      | NULL             | 2020-10-06       |      4 |         3 | T3          |
| XYZ     | 2020-10-05 | 2020-10-05        | NULL            | 2020-10-06       | 2020-10-05       |      5 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-06 | 2020-10-05        | 2020-10-05      | 2020-10-07       | 2020-10-06       |      5 |         2 | T2          |
| XYZ     | 2020-10-07 | 2020-10-05        | 2020-10-06      | NULL             | 2020-10-07       |      5 |         3 | T3          |
| XYZ     | 2020-10-06 | 2020-10-06        | NULL            | 2020-10-07       | 2020-10-06       |      6 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-07 | 2020-10-06        | 2020-10-06      | 2020-10-08       | 2020-10-07       |      6 |         2 | T2          |
| XYZ     | 2020-10-08 | 2020-10-06        | 2020-10-07      | NULL             | 2020-10-08       |      6 |         3 | T3          |
| XYZ     | 2020-10-07 | 2020-10-07        | NULL            | 2020-10-08       | 2020-10-07       |      7 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-08 | 2020-10-07        | 2020-10-07      | 2020-10-09       | 2020-10-08       |      7 |         2 | T2          |
| XYZ     | 2020-10-09 | 2020-10-07        | 2020-10-08      | NULL             | 2020-10-09       |      7 |         3 | T3          |
| XYZ     | 2020-10-08 | 2020-10-08        | NULL            | 2020-10-09       | 2020-10-08       |      8 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-09 | 2020-10-08        | 2020-10-08      | 2020-10-10       | 2020-10-09       |      8 |         2 | T2          |
| XYZ     | 2020-10-10 | 2020-10-08        | 2020-10-09      | NULL             | 2020-10-10       |      8 |         3 | T3          |
+---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------+
Copy

A saída desta consulta também mostra que as funções LAG() e LEAD() retornam NULL para expressões que tentam referenciar linhas fora do grupo de correspondência (ou seja, fora do quadro da janela).

As regras para funções de navegação nas cláusulas DEFINE são ligeiramente diferentes das regras para funções de navegação nas cláusulas MEASURES. Por exemplo, a função PREV() está disponível na cláusula MEASURES, mas atualmente não está na cláusula DEFINE. Em vez disso, você pode usar LAG() na cláusula DEFINE. A documentação de referência para MATCH_RECOGNIZE lista a regra correspondente para cada função de navegação.

A subcláusula MEASURES também pode incluir o seguinte:

  • Funções agregadas. Por exemplo, se o padrão pode corresponder a um número variável de linhas (por exemplo, porque corresponde a 1 ou mais preços de ações em queda), você pode querer saber o número total de linhas na correspondência; para isso, use COUNT(*).

  • Expressões gerais que operam sobre valores em cada linha da correspondência. Elas podem ser expressões matemáticas, expressões lógicas, etc. Por exemplo, você pode observar valores na linha e imprimir descritores de texto tais como “ABOVE AVERAGE“.

    Lembre-se de que, se você agrupar linhas (ONE ROW PER MATCH) e se uma coluna tiver valores diferentes para linhas diferentes no grupo, o valor selecionado para aquela coluna e para aquela correspondência é não determinístico, e expressões baseadas naquele valor provavelmente também são não determinísticas.

Para obter mais informações sobre a subcláusula MEASURES, consulte a documentação de referência para MATCH_RECOGNIZE.

Especificação de onde pesquisar a próxima correspondência

Por padrão, após MATCH_RECOGNIZE encontrar uma correspondência, ele começa a procurar a próxima imediatamente após o final da correspondência mais recente. Por exemplo, se MATCH_RECOGNIZE encontrar uma correspondência nas linhas 2, 3 e 4, então MATCH_RECOGNIZE começará a procurar a próxima correspondência na linha 5. Isto evita a sobreposição de correspondências.

No entanto, você pode escolher pontos de partida alternativos.

Considere os seguintes dados:

Month  | Price | Price Relative to Previous Day
=======|=======|===============================
     1 |   200 |
     2 |   100 | down
     3 |   200 | up
     4 |   100 | down
     5 |   200 | up
     6 |   100 | down
     7 |   200 | up
     8 |   100 | down
     9 |   200 | up
Copy

Suponha que você pesquise os dados de um padrão W (para baixo, para cima, para baixo e para cima). Existem três formas W:

  1. Meses: 1, 2, 3, 4 e 5.

  2. Meses: 3, 4, 5, 6 e 7.

  3. Meses: 5, 6, 7, 8 e 9.

Você pode usar a cláusula SKIP para especificar se deseja todos os padrões ou apenas os padrões não sobrepostos. A cláusula SKIP também aceita outras opções. A cláusula SKIP está documentada com mais detalhes em MATCH_RECOGNIZE.

Práticas recomendadas

  • Inclua uma cláusula ORDER BY em sua cláusula MATCH_RECOGNIZE.

    • Lembre-se de que este ORDER BY se aplica somente dentro da cláusula MATCH_RECOGNIZE. Se você quiser que toda a consulta retorne resultados em uma ordem específica, então use uma cláusula ORDER BY adicional no nível mais externo da consulta.

  • Nomes de variáveis de padrão:

    • Use nomes de variável significativos para tornar seus padrões mais fáceis de entender e depurar.

    • Verifique erros ortográficos nos nomes das variáveis de padrão nas cláusulas PATTERN e DEFINE.

  • Evite usar padrões para subcláusulas que tenham padrões. Torne suas escolhas explícitas.

  • Teste seu padrão com uma pequena amostra de dados antes de escalar para seu conjunto de dados completo.

  • MATCH_NUMBER(), MATCH_SEQUENCE_NUMBER() e CLASSIFIER() são muito úteis na depuração.

  • Considere o uso de uma cláusula ORDER BY no nível mais externo da consulta para forçar a saída a estar na ordem usando MATCH_NUMBER() e MATCH_SEQUENCE_NUMBER(). Se os dados de saída estiverem em outra ordem, então a saída pode não parecer corresponder ao padrão.

Como evitar erros analíticos

Correlação vs. causalidade

A correlação não garante a causalidade. MATCH_RECOGNIZE pode retornar “falsos positivos” (casos em que você vê um padrão onde há apenas uma coincidência).

A correspondência de padrões também pode resultar em “falsos negativos” (casos em que há um padrão no mundo real, mas o padrão não aparece na amostra de dados).

Na maioria dos casos, encontrar uma correspondência (por exemplo, encontrar um padrão que sugere uma fraude de seguro) é apenas o primeiro passo de uma análise.

Os seguintes fatores normalmente aumentam o número de falsos positivos:

  • Grandes conjuntos de dados.

  • Pesquisar um grande número de padrões.

  • Pesquisar padrões curtos ou simples.

Os seguintes fatores normalmente aumentam o número de falsos negativos.

  • Pequenos conjuntos de dados.

  • Não pesquisar todos os padrões relevantes possíveis.

  • Pesquisar padrões mais complexos do que o necessário.

Padrões que não diferenciam a ordem

Embora a maior parte da correspondência de padrões exija que os dados estejam em ordem (por exemplo, por hora), há exceções. Por exemplo, se uma pessoa comete uma fraude de seguro tanto em um acidente de automóvel como em um arrombamento em casa, não importa em que ordem as fraudes ocorrem.

Se o padrão que você está procurando não é afetado pela ordem, você pode usar operadores como “alternativa” (|) e PERMUTE para tornar suas pesquisas menos sujeitas à ordem.

Exemplos

Esta seção contém exemplos adicionais.

Você pode encontrar ainda mais exemplos em MATCH_RECOGNIZE.

Encontrar aumentos de preços em vários dias

A seguinte consulta encontra todos os padrões em que o preço da empresa ABCD subiu dois dias seguidos:

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #"
           ALL ROWS PER MATCH
           PATTERN (MINIMUM_37 UP UP)
           DEFINE
               MINIMUM_37 AS price >= 37.00,
               UP AS price > LAG(price)
           )
    ORDER BY company, "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # |
|---------+------------+-------+---------+------------------|
| ABCD    | 2020-10-06 |    47 |       1 |                1 |
| ABCD    | 2020-10-07 |    71 |       1 |                2 |
| ABCD    | 2020-10-08 |    80 |       1 |                3 |
| XYZ     | 2020-10-03 |    37 |       1 |                1 |
| XYZ     | 2020-10-04 |    63 |       1 |                2 |
| XYZ     | 2020-10-05 |    65 |       1 |                3 |
+---------+------------+-------+---------+------------------+
Copy

Demonstrar o operador PERMUTE

Este exemplo demonstra o operador PERMUTE no padrão. Pesquise todos os picos de aumento e redução nos gráficos limitando a dois o número de aumentos de preços:

select * from stock_price_history match_recognize(
        partition by company
        order by price_date
        measures
            match_number() as "MATCH_NUMBER",
            first(price_date) as "START",
            last(price_date) as "END",
            count(up.price) as ups,
            count(*) as "PRICE_COUNT",
            last(classifier()) = 'DOWN' up_spike
        after match skip to next row
        pattern(ANY_ROW PERMUTE(UP{2}, DOWN+))
        define
            ANY_ROW AS TRUE,
            UP as price > lag(price),
            DOWN as price < lag(price)
    )
    order by company, match_number;
+---------+--------------+------------+------------+-----+-------------+----------+
| COMPANY | MATCH_NUMBER | START      | END        | UPS | PRICE_COUNT | UP_SPIKE |
|---------+--------------+------------+------------+-----+-------------+----------|
| ABCD    |            1 | 2020-10-01 | 2020-10-04 |   2 |           4 | False    |
| ABCD    |            2 | 2020-10-02 | 2020-10-05 |   2 |           4 | True     |
| ABCD    |            3 | 2020-10-04 | 2020-10-07 |   2 |           4 | False    |
| ABCD    |            4 | 2020-10-06 | 2020-10-10 |   2 |           5 | True     |
| XYZ     |            1 | 2020-10-01 | 2020-10-04 |   2 |           4 | False    |
| XYZ     |            2 | 2020-10-03 | 2020-10-07 |   2 |           5 | True     |
+---------+--------------+------------+------------+-----+-------------+----------+
Copy

Demonstrar a opção SKIP TO NEXT ROW

Este exemplo demonstra a opção SKIP TO NEXT ROW. Esta consulta pesquisa curvas em forma de W no gráfico de cada empresa. As correspondências podem se sobrepor.

select * from stock_price_history match_recognize(
    partition by company
    order by price_date
    measures
        match_number() as "MATCH_NUMBER",
        first(price_date) as "START",
        last(price_date) as "END",
        count(*) as "PRICE_COUNT"
    after match skip to next row
    pattern(ANY_ROW DOWN+ UP+ DOWN+ UP+)
    define
        ANY_ROW AS TRUE,
        UP as price > lag(price),
        DOWN as price < lag(price)
)
order by company, match_number;
+---------+--------------+------------+------------+-------------+
| COMPANY | MATCH_NUMBER | START      | END        | PRICE_COUNT |
|---------+--------------+------------+------------+-------------|
| ABCD    |            1 | 2020-10-01 | 2020-10-08 |           8 |
| XYZ     |            1 | 2020-10-01 | 2020-10-08 |           8 |
| XYZ     |            2 | 2020-10-05 | 2020-10-10 |           6 |
| XYZ     |            3 | 2020-10-06 | 2020-10-10 |           5 |
+---------+--------------+------------+------------+-------------+
Copy

Sintaxe de exclusão

Este exemplo mostra a sintaxe de exclusão no padrão. Este padrão (como o padrão anterior) pesquisa formas W, mas a saída desta consulta exclui preços em queda. Note que, nesta consulta, a correspondência continua depois da última linha de uma correspondência:

select * from stock_price_history match_recognize(
        partition by company
        order by price_date
        measures
            match_number() as "MATCH_NUMBER",
            classifier as cl,
            count(*) as "PRICE_COUNT"
        all rows per match
        pattern(ANY_ROW {- DOWN+ -} UP+ {- DOWN+ -} UP+)
        define
            ANY_ROW AS TRUE,
            UP as price > lag(price),
            DOWN as price < lag(price)
    )
    order by company, price_date;
+---------+------------+-------+--------------+---------+-------------+
| COMPANY | PRICE_DATE | PRICE | MATCH_NUMBER | CL      | PRICE_COUNT |
|---------+------------+-------+--------------+---------+-------------|
| ABCD    | 2020-10-01 |    50 |            1 | ANY_ROW |           1 |
| ABCD    | 2020-10-03 |    39 |            1 | UP      |           3 |
| ABCD    | 2020-10-04 |    42 |            1 | UP      |           4 |
| ABCD    | 2020-10-06 |    47 |            1 | UP      |           6 |
| ABCD    | 2020-10-07 |    71 |            1 | UP      |           7 |
| ABCD    | 2020-10-08 |    80 |            1 | UP      |           8 |
| XYZ     | 2020-10-01 |    89 |            1 | ANY_ROW |           1 |
| XYZ     | 2020-10-03 |    37 |            1 | UP      |           3 |
| XYZ     | 2020-10-04 |    63 |            1 | UP      |           4 |
| XYZ     | 2020-10-05 |    65 |            1 | UP      |           5 |
| XYZ     | 2020-10-08 |    54 |            1 | UP      |           8 |
+---------+------------+-------+--------------+---------+-------------+
Copy

Pesquisa de padrões em linhas não adjacentes

Em algumas situações, você pode querer procurar padrões em linhas não contíguas. Por exemplo, se estiver analisando arquivos de log, você pode querer pesquisar todos os padrões nos quais um erro fatal foi precedido por uma sequência particular de avisos. Pode não haver uma forma natural de dividir e classificar as linhas de modo que todas as mensagens relevantes (linhas) fiquem em uma única janela e adjacentes. Nessa situação, você pode precisar de um padrão que procure eventos específicos, mas não exija que os eventos sejam contíguos nos dados.

Abaixo está um exemplo de cláusulas DEFINE e PATTERN que reconhecem linhas contíguas ou não contíguas que se encaixam no padrão. O símbolo ANY_ROW é definido como TRUE (portanto, corresponde a qualquer linha). O * após cada ocorrência de ANY_ROW diz para permitir 0 ou mais ocorrências de ANY_ROW entre o primeiro e o segundo aviso, e entre o segundo aviso e a mensagem de registro de erros fatais. Assim, todo o padrão diz para pesquisar WARNING1, seguido por qualquer número de linhas, seguido por WARNING2, seguido por qualquer número de linhas, seguido por FATAL_ERROR. Para omitir as linhas irrelevantes da saída, a consulta usa sintaxe de exclusão ({- e -}).

MATCH_RECOGNIZE (
    ...
    ORDER BY log_message_timestamp
    ...
    ALL ROWS PER MATCH
    PATTERN ( WARNING1  {- ANY_ROW* -}  WARNING2  {- ANY_ROW* -}  FATAL_ERROR )
    DEFINE
        ANY_ROW AS TRUE,
        WARNING1 AS SUBSTR(log_message, 1, 42) = 'WARNING: Available memory is less than 10%',
        WARNING2 AS SUBSTR(log_message, 1, 41) = 'WARNING: Available memory is less than 5%',
        FATAL_ERROR AS SUBSTR(log_message, 1, 11) = 'FATAL ERROR'
    )
...
Copy

Solução de problemas

Erros no uso de ONE ROW PER MATCH e na especificação de colunas na cláusula de seleção

A cláusula ONE ROW PER MATCH atua de forma semelhante a uma função agregada. Isso limita as colunas de saída que você pode usar. Por exemplo, se você usa ONE ROW PER MATCH e cada correspondência contém três linhas com datas diferentes, então você não pode especificar a coluna de data como uma coluna de saída na cláusula SELECT porque nenhuma data única é correta para todas as três linhas.

Resultados inesperados

  • Verifique se há erros ortográficos nas cláusulas PATTERN e DEFINE.

    Se um nome de variável de padrão usado na cláusula PATTERN não estiver definido na cláusula DEFINE (por exemplo, porque o nome foi digitado incorretamente na cláusula PATTERN ou DEFINE), então nenhum erro será relatado. Ao invés disso, o nome da variável de padrão é simplesmente considerado como verdadeiro para cada linha.

  • Revise a cláusula SKIP para ter certeza de que é apropriado, por exemplo, incluir ou excluir padrões sobrepostos.