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

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]+

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+

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+)
  ...
)

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:

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
  ...
)

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>

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
  ...
)

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 |
+---------+--------------+------------+------------+-------------------+---------------+---------------+

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
  ...
)

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

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

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

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

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 |
+---------+---------+------------------+-------------------+

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* -})

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

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)

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:

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          |
+---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------+

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.

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.

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 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

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'
    )
...

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.