MATCH_RECOGNIZE¶

DEFINE: definição de símbolos¶

DEFINE <symbol1> AS <expr1> [ , <symbol2> AS <expr2> ]

Os símbolos (também conhecidos como “variáveis de padrão”) são os blocos de construção do padrão.

Um símbolo é definido por uma expressão. Se a expressão for avaliada como verdadeira para uma linha, o símbolo é atribuído a essa linha. A uma linha podem ser atribuídos múltiplos símbolos.

Os símbolos que não estão definidos na cláusula DEFINE, mas que são usados no padrão, são sempre atribuídos a todas as linhas. Implicitamente, eles são equivalentes ao exemplo a seguir:

...
define
    my_example_symbol as true
...

Os padrões são definidos com base em símbolos e operadores.

PATTERN: especificação do padrão a atender¶

PATTERN ( <pattern> )

O padrão define uma sequência válida de linhas que representa uma correspondência. O padrão é definido como uma expressão regular (regex) e é construído a partir de símbolos, operadores e quantificadores.

Por exemplo, suponha que o símbolo S1 seja definido como stock_price < 55, e o símbolo S2 seja definido como stock price > 55. O seguinte padrão especifica uma sequência de linhas nas quais o preço das ações aumentou de menos de 55 para obter mais de 55:

PATTERN (S1 S2)

O seguinte é um exemplo mais complexo para uma definição de padrão:

^ S1 S2*? ( {- S3 -} S4 )+ | PERMUTE(S1, S2){1,2} $

A seção seguinte descreve em detalhes os componentes individuais deste padrão.

symbol1+ any_symbol* symbol2

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

Símbolos:

Um símbolo corresponde a uma linha à qual esse símbolo foi atribuído. Os seguintes símbolos estão disponíveis:

• symbol. Por exemplo, S1, … , S4 Estes são símbolos definidos na subcláusula DEFINE e são avaliados por linha. (Eles também podem incluir símbolos que não foram definidos e são automaticamente atribuídos a todas as linhas).

• ^ (início da partição). Este é um símbolo virtual que denota o início de uma partição e não tem nenhuma linha associada a ela. Você pode usá-lo para exigir que uma correspondência comece apenas no início de uma partição.

  Para obter um exemplo, consulte Correspondência de padrões relativos ao início ou ao fim de uma partição.

• $ (fim da partição.) Este é um símbolo virtual que denota o fim de uma partição e não tem nenhuma linha associada a ela. Você pode usá-lo para exigir que uma correspondência termine apenas no fim de uma partição.

  Para obter um exemplo, consulte Correspondência de padrões relativos ao início ou ao fim de uma partição.

Quantificadores:

Um quantificador pode ser colocado depois de um símbolo ou operação. Um quantificador denota o número mínimo e máximo de ocorrências do símbolo ou operação associada. Os seguintes quantificadores estão disponíveis:

Quantificador	Significado
+	1 ou mais. Por exemplo, ( {- S3 -} S4 )+.
*	0 ou mais. Por exemplo, S2*?.
?	0 ou 1.
{n}	Exatamente n.
{n,}	n ou mais.
{,m}	0 a m.
{n, m}	n a m. Por exemplo, PERMUTE(S1, S2){1,2}.

Por padrão, os quantificadores estão em “modo ganancioso”, o que significa que eles tentam igualar a quantidade máxima, se possível. Para colocar um quantificador no “modo relutante”, no qual o quantificador tenta igualar a quantidade mínima, se possível, coloque um ? após o quantificador (por exemplo, S2*?).

Operadores:

Os operadores especificam em que ordem os símbolos ou outras operações devem ocorrer na sequência de linhas para formar uma correspondência válida. Os seguintes operadores estão disponíveis:

Operador	Significado
... ... (espaço)	Concatenação. Especifica que um símbolo ou operação deve seguir outro. Por exemplo, S1 S2 significa que a condição definida para S2 deve ocorrer após a condição definida para S1.
{- ... -}	Exclusão. Exclui da saída os símbolos ou operações contidos. Por exemplo, {- S3 -} exclui o operador S3 da saída. As linhas excluídas não aparecerão na saída, mas serão incluídas na avaliação das expressões MEASURES.
( ... )	Agrupamento. Usado para anular a precedência de um operador ou para aplicar o mesmo quantificador para símbolos ou operações no grupo. Neste exemplo, o quantificador + se aplica à sequência {- S3 -} S4, e não apenas S4.
PERMUTE(..., ...)	Permutação. Corresponde a qualquer permutação dos padrões especificados. Por exemplo, PERMUTE(S1, S2) corresponde a S1 S2 ou S2 S1. PERMUTE() tem um número ilimitado de argumentos.
... | ...	Alternativa. Especifica que ou o primeiro símbolo ou operação ou o outro deve ocorrer. Por exemplo, ( S3 S4 ) | PERMUTE(S1, S2). O operador alternativo tem precedência sobre o operador de concatenação.

ORDER BY: ordenação das linhas antes da correspondência¶

ORDER BY orderItem1 [ , orderItem2 ... ]

Onde:

orderItem ::= { <column_alias> | <expr> } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Copy

Defina a ordem das linhas como você faria para as funções de janela. Esta é a ordem em que as linhas individuais de cada partição são passadas ao operador MATCH_RECOGNIZE.

Para obter mais informações, consulte Partição e classificação das linhas.

PARTITION BY: particionamento das linhas em janelas¶

PARTITION BY <expr1> [ , <expr2> ... ]

Particionar o conjunto de linhas de entrada como você faria para funções de janela. MATCH_RECOGNIZE faz a correspondência individualmente para cada partição resultante.

A partição não apenas agrupa linhas que estão relacionadas entre si, mas também aumenta a capacidade de processamento de dados distribuídos do Snowflake, pois as partições separadas podem ser processadas em paralelo.

Para obter mais informações sobre partições, consulte Partição e classificação das linhas.

MEASURES: especificação de colunas de saída adicionais¶

MEASURES <expr1> [AS] <alias1> [ ... , <exprN> [AS] <aliasN> ]

As “Medidas” são colunas adicionais opcionais que são adicionadas à saída do operador MATCH_RECOGNIZE. As expressões na subcláusula MEASURES têm as mesmas capacidades que as expressões na subcláusula DEFINE. Para obter mais informações, consulte Símbolos.

Dentro da subcláusula MEASURES, estão disponíveis as seguintes funções específicas para MATCH_RECOGNIZE:

• MATCH_NUMBER() retorna o número sequencial da correspondência. O MATCH_NUMBER começa de 1, e é incrementado para cada correspondência.

• MATCH_SEQUENCE_NUMBER() retorna o número da linha dentro de uma correspondência. O MATCH_SEQUENCE_NUMBER é sequencial e começa a partir de 1.

• CLASSIFIER() retorna um valor TEXT que contém o símbolo correspondido pela linha. Por exemplo, se uma linha corresponder ao símbolo GT75, então a função CLASSIFIER retorna a cadeia de caracteres “GT75”.

ROW(S) PER MATCH: especificação das linhas a retornar¶

{
  ONE ROW PER MATCH  |
  ALL ROWS PER MATCH [ { SHOW EMPTY MATCHES | OMIT EMPTY MATCHES | WITH UNMATCHED ROWS } ]
}

Especifica quais linhas são retornadas para uma correspondência bem sucedida. Esta subcláusula é opcional.

• ALL ROWS PER MATCH: retorna todas as linhas na correspondência.

• ONE ROW PER MATCH: retorna uma linha de resumo para cada correspondência, independentemente de quantas linhas estejam na correspondência. Este é o padrão.

Tenha atenção aos seguintes casos especiais:

• Correspondências vazias: uma correspondência vazia acontece se um padrão corresponder a zero linhas. Por exemplo, se o padrão for definido como A* e a primeira linha no início de uma tentativa de correspondência for atribuída ao símbolo B, então é gerada uma correspondência vazia incluindo apenas essa linha, pois o quantificador * no padrão A* permite que 0 ocorrências de A sejam tratadas como uma correspondência. As expressões MEASURES são avaliadas de forma diferente para esta linha:

  • A função CLASSIFIER retorna NULL.

  • As funções de janela retornam NULL.

  • A função COUNT retorna 0.

• Linhas sem correspondência: se uma linha não corresponder ao padrão, ela é chamada de linha sem correspondência. MATCH_RECOGNIZE também pode ser configurado para retornar linhas sem correspondência. Para linhas sem correspondência, as expressões na subcláusula MEASURES retornam NULL.

• Exclusões

  A sintaxe de exclusão ({- ... -}) na definição do padrão permite que os usuários excluam determinadas linhas da saída. Se todos os símbolos combinados no padrão foram excluídos, nenhuma linha é gerada para essa correspondência se ALL ROWS PER MATCH foi especificado. Note que MATCH_NUMBER é incrementado de qualquer forma. As linhas excluídas não fazem parte do resultado, mas estão incluídas para a avaliação das expressões MEASURES.

  Ao utilizar a sintaxe de exclusão, a subcláusula ROWS PER MATCH pode ser especificada como segue:

  • ONE ROW PER MATCH (padrão)

    Retorna exatamente uma linha para cada correspondência bem sucedida. A semântica padrão da função de janela para funções de janela na subcláusula MEASURES é FINAL.

    As colunas de saída do operador MATCH_RECOGNIZE são todas as expressões dadas na subcláusula PARTITION BY e todas as expressões MEASURES. Todas as linhas resultantes de uma correspondência são agrupadas pelas expressões dadas na subcláusula PARTITION BY e MATCH_NUMBER usando a função de agregação ANY_VALUE para todas as medidas. Portanto, se as medidas são avaliadas como um valor diferente para linhas diferentes da mesma correspondência, então a saída é não determinística.

    Omitir as subcláusulas PARTITION BY e MEASURES resulta em um erro indicando que o resultado não inclui nenhuma coluna.

    Para correspondências vazias, é gerada uma linha. Linhas sem correspondência não fazem parte da saída.

  • ALL ROWS PER MATCH

    Retorna uma linha para cada linha que faz parte da correspondência, exceto para as linhas que foram correspondidas com uma parte do padrão que foi marcada para exclusão.

    Linhas excluídas ainda são levadas em conta nos cálculos da subcláusula MEASURES.

    As correspondências podem se sobrepor com base na subcláusula AFTER MATCH SKIP TO, de modo que a mesma linha pode aparecer várias vezes na saída.

    A semântica padrão da função de janela para funções de janela na subcláusula MEASURES é RUNNING.

    As colunas de saída do operador MATCH_RECOGNIZE são as colunas do conjunto de linhas a serem entradas e as colunas definidas na subcláusula MEASURES.

    As seguintes opções estão disponíveis para ALL ROWS PER MATCH:

    • SHOW EMPTY MATCHES (default) Adiciona correspondências vazias à saída. Linhas sem correspondência não fazem parte da saída.

    • OMIT EMPTY MATCHES Correspondências vazias ou linhas sem correspondência não fazem parte da saída. No entanto, o MATCH_NUMBER ainda é incrementado por uma correspondência vazia.

    • WITH UNMATCHED ROWS Adiciona correspondências vazias e linhas sem correspondência à saída. Se esta cláusula for utilizada, então o padrão não deve conter exclusões.

  Para um exemplo que utiliza a exclusão para reduzir a saída irrelevante, consulte Pesquisa de padrões em linhas não adjacentes.

AFTER MATCH SKIP: especificação de onde continuar após uma correspondência¶

AFTER MATCH SKIP
{
    PAST LAST ROW   |
    TO NEXT ROW   |
    TO [ { FIRST | LAST} ] <symbol>
}

Esta subcláusula especifica onde continuar a correspondência após ter sido encontrada uma correspondência positiva.

• PAST LAST ROW (default)

  Continuar a correspondência após a última linha da correspondência atual.

  Isso evita correspondências que tenham linhas sobrepostas. Por exemplo, se você tem um padrão de ação que contém 3 V formas em uma linha, então PAST LAST ROW encontra um padrão W e não dois.

• TO NEXT ROW

  Continuar a correspondência após a primeira linha da correspondência atual.

  Isto permite combinações que contêm linhas sobrepostas. Por exemplo, se você tem um padrão de ação que contém 3 formas V em uma linha, então TO NEXT ROW encontra dois padrões W (o primeiro padrão é baseado nas duas primeiras formas V e a segunda forma W é baseada nas segunda e terceira formas V; assim, ambos os padrões contêm o mesmo V).

• TO [ { FIRST | LAST } ] <símbolo>

  Continuar a correspondência na primeira ou última linha (padrão) que foi correspondida com o símbolo dado.

  Pelo menos uma linha precisa ser mapeada para o símbolo dado ou um erro é acusado.

  Se isso não passar da primeira linha da correspondência atual, então um erro é acusado.

Expressões nas cláusulas DEFINE e MEASURES.¶

As cláusulas DEFINE e MEASURES permitem expressões. Essas expressões podem ser complexas e podem incluir funções de janela e funções especiais de navegação (que são um tipo de função de janela).

Na maioria dos aspectos, as expressões em DEFINE e MEASURES seguem as regras para expressões em outros lugares na sintaxe SQL do Snowflake. No entanto, existem algumas diferenças, que são descritas abaixo:

Funções de janela:

As funções de navegação permitem referências a outras linhas além da linha atual. Por exemplo, para criar uma expressão que define uma queda no preço, você precisa comparar o preço em uma linha com o preço em outra linha. As funções de navegação são:

• PREV( expr [ , offset [, default ] ] ) Navegar para a linha anterior na correspondência atual na subcláusula MEASURES.

  Esta função não está disponível atualmente na subcláusula DEFINE. Em vez disso, você pode usar LAG, que navega para a linha anterior no quadro da janela atual.

• NEXT( expr [ , offset [ , default ] ] ) Navegar para a próxima linha dentro do quadro da janela atual. Esta função é equivalente a LEAD.

• FIRST( expr ) Navegar para a primeira linha da correspondência atual na subcláusula MEASURES.

  Esta função não está disponível atualmente na subcláusula DEFINE. Em vez disso, você pode usar FIRST_VALUE, que navega para a primeira linha do quadro da janela atual.

• LAST( expr ) Navegar para a última linha do quadro da janela atual. Esta função é semelhante a LAST_VALUE, mas para LAST o quadro da janela é limitado à linha atual da correspondência atual quando LAST é usado dentro da subcláusula DEFINE.

Para um exemplo que utiliza as funções de navegação, consulte Retorno de informações sobre a correspondência.

Em geral, quando uma função de janela é usada dentro de uma cláusula MATCH_RECOGNIZE, a função de janela não requer sua própria cláusula OVER (PARTITION BY ... ORDER BY ...). A janela é implicitamente determinada por PARTITION BY e ORDER BY da cláusula MATCH_RECOGNIZE. (No entanto, consulte Limitações das funções de janela utilizadas em DEFINE e MEASURES para algumas exceções).

Em geral, o quadro da janela também é explicitamente derivado do contexto atual no qual a função de janela está sendo usada. O limite inferior do quadro é definido como descrito abaixo:

Na subcláusula DEFINE:

O quadro começa no início da tentativa de correspondência atual, exceto quando se usa LAG, LEAD, FIRST_VALUE e LAST_VALUE.

Na subcláusula MEASURES:

O quadro começa no início da correspondência que foi encontrada.

As bordas do quadro da janela podem ser especificadas usando a semântica RUNNING ou FINAL.

expr ::= ... [ { RUNNING | FINAL } ] windowFunction ...

Copy

RUNNING:

Em geral, o quadro termina na linha atual. No entanto, existem as seguintes exceções:

• Na subcláusula DEFINE, para LAG, LEAD, FIRST_VALUE, LAST_VALUE e NEXT, o quadro termina na última linha da janela.

• Na subcláusula MEASURES, para PREV, NEXT, LAG e LEAD, o quadro termina na última linha da janela.

Na subcláusula DEFINE, RUNNING é a semântica padrão (e a única permitida).

Na subcláusula MEASURES, quando a subcláusula ALL ROWS PER MATCH é usada, RUNNING é o padrão.

FINAL:

O quadro termina na última linha da correspondência.

FINAL é permitido somente na subcláusula MEASURES. É o padrão lá quando ONE ROW PER MATCH se aplica.

Predicados do símbolo:

Expressões dentro das subcláusulas DEFINE e MEASURES permitem símbolos como predicados para referências de colunas.

predicatedColumnReference ::= <symbol>.<column>

Copy

O <símbolo> indica uma linha que foi correspondida, e a <coluna> identifica uma coluna específica dentro dessa linha.

Uma referência de coluna predicada significa que a função de janela ao redor só olha para linhas que foram finalmente mapeadas para o símbolo especificado.

As referências predicadas de colunas podem ser usadas fora e dentro de uma função de janela. Se usado fora de uma função de janela, <symbol>.<column> é igual a LAST(<symbol>.<column>). Dentro de uma função de janela, todas as referências de colunas precisam ser predicadas com o mesmo símbolo ou são todas não predicadas.

A seguir, explicamos como as funções relacionadas à navegação se comportam com referências de colunas predicadas:

• PREV/LAG( ... <symbol>.<column> ... , <offset>) Procura o quadro da janela para trás começando de e incluindo a linha atual (ou última linha no caso de uma semântica FINAL) para a primeira linha que foi finalmente mapeada para o <símbolo> especificado, e depois vai para trás linhas <offset> (o padrão é 1), ignorando o símbolo para o qual essas linhas foram mapeadas. Se a parte pesquisada do quadro não contiver uma linha mapeada para <símbolo> ou a pesquisa for além da borda do quadro, então NULL é retornado.

• NEXT/LEAD( ... <symbol>.<column> ... , <offset>) Procura o quadro da janela para trás começando de e incluindo a linha atual (ou última linha no caso de uma semântica FINAL) para a primeira linha que foi finalmente mapeada para o <símbolo> especificado, e depois vai para frente linha <offset> (o padrão é 1), ignorando o símbolo para o qual essas linhas foram mapeadas. Se a parte pesquisada do quadro não contiver uma linha mapeada para <símbolo> ou a pesquisa for além da borda do quadro, então NULL é retornado.

• FIRST/FIRST_VALUE( ... <symbol>.<column> ... ) Procura o quadro da janela para frente começando de e incluindo a primeira linha até e incluindo a linha atual (ou última linha no caso de uma semântica FINAL) para a primeira linha que foi finalmente mapeada para o <símbolo> especificado. Se a parte pesquisada do quadro não contiver uma linha mapeada para <símbolo>, NULL é retornado.

• LAST/LAST_VALUE( ... <symbol>.<column> ... ) Procura o quadro da janela para trás começando de e incluindo a linha atual (ou última linha no caso de uma semântica FINAL) para a primeira linha que foi finalmente mapeada para o <símbolo> especificado. Se a parte pesquisada do quadro não contiver uma linha mapeada para <símbolo>, NULL é retornado.

Nota

As restrições nas funções de janela estão documentadas na seção Limitações das funções de janela utilizadas em DEFINE e MEASURES.

Limitações das funções de janela utilizadas em DEFINE e MEASURES¶

Expressões nas subcláusulas DEFINE e MEASURES podem incluir funções de janela. No entanto, há algumas limitações no uso de funções de janela nestas subcláusulas. Estas limitações são mostradas na tabela abaixo:

Função	DEFINE (Em execução) [coluna/símbolo.coluna]	MEASURES (Em execução) [coluna/símbolo.coluna]	MEASURES (Final) [coluna/símbolo.coluna]
Coluna	✔ / ❌	✔ / ❌	✔ / ✔
PREV(…)	❌ / ❌	✔ / ❌	✔ / ❌
NEXT(…)	✔ / ❌	✔ / ❌	✔ / ❌
FIRST(…)	❌ / ❌	✔ / ❌	✔ / ✔
LAST(…)	✔ / ❌	✔ / ❌	✔ / ✔
LAG()	✔ / ❌	✔ / ❌	✔ / ❌
LEAD()	✔ / ❌	✔ / ❌	✔ / ❌
FIRST_VALUE()	✔ / ❌	✔ / ❌	✔ / ✔
LAST_VALUE()	✔ / ❌	✔ / ❌	✔ / ✔
Agregações [1]	✔ / ❌	✔ / ✔	✔ / ✔
Outras funções de janela [1]	✔ / ❌	✔ / ❌	✔ / ❌

As funções específicas de MATCH_RECOGNIZE MATCH_NUMBER(), MATCH_SEQUENCE_NUMBER() e CLASSIFIER() não estão atualmente disponíveis na subcláusula DEFINE.

Mensagem de erro: SELECT with no columns ao usar ONE ROW PER MATCH¶

Quando você utiliza a cláusula ONE ROW PER MATCH, somente colunas e expressões das subcláusulas PARTITION BY e MEASURES são permitidas na cláusula de projeção de SELECT. Se você tentar usar MATCH_RECOGNIZE sem uma cláusula PARTITION BY ou MEASURES, você receberá um erro semelhante a SELECT with no columns.

Para obter mais informações sobre ONE ROW PER MATCH vs. ALL ROWS PER MATCH, consulte Geração de uma linha vs. geração de todas as linhas para cada correspondência.

Relatório uma linha de resumo para cada forma V¶

A consulta a seguir busca todas as formas V no stock_price_history apresentado anteriormente. A saída é explicada com mais detalhes após a consulta e a saída.

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;
+---------+--------------+------------+------------+------------------+---------------+---------------+
| 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 |
| XYZ     |            1 | 2020-10-01 | 2020-10-05 |                5 |             1 |             3 |
| XYZ     |            2 | 2020-10-05 | 2020-10-08 |                4 |             2 |             1 |
| XYZ     |            3 | 2020-10-08 | 2020-10-10 |                3 |             1 |             1 |
+---------+--------------+------------+------------+------------------+---------------+---------------+

Copy

A saída mostra uma linha por correspondência (independentemente de quantas linhas faziam parte da correspondência).

A saída inclui as seguintes colunas:

• COMPANY: o símbolo das ações da empresa.

• O MATCH_NUMBER é um número sequencial que identifica qual correspondência estava dentro deste conjunto de dados (por exemplo, a primeira correspondência tem MATCH_NUMBER 1, a segunda correspondência tem MATCH_NUMBER 2 etc.). Se os dados foram particionados, então o MATCH_NUMBER é o número sequencial dentro da partição (neste exemplo, para cada empresa/ação).

• START_DATE: a data em que esta ocorrência do padrão começa.

• END_DATE: a data em que esta ocorrência do padrão termina.

• ROWS_IN_SEQUENCE: este é o número de linhas na correspondência. Por exemplo, a primeira correspondência é baseada nos preços medidos em 4 dias (1º de outubro até 4 de outubro), portanto ROWS_IN_SEQUENCE é 4.

• NUM_DECREASES: este é o número de dias (dentro da correspondência) em que o preço caiu. Por exemplo, na primeira correspondência, o preço caiu por 1 dia e depois subiu por 2 dias, portanto NUM_DECREASES é 1.

• NUM_INCREASES: este é o número de dias (dentro da correspondência) em que o preço subiu. Por exemplo, na primeira correspondência, o preço caiu por 1 dia e depois subiu por 2 dias, portanto NUM_INCREASES é 2.

Relatório de todas as linhas para todas as correspondências de uma empresa¶

Este exemplo retorna todas as linhas dentro de cada correspondência (não apenas uma linha de resumo por correspondência). Este padrão busca o aumento dos preços da empresa ‘ABCD’:

select price_date, match_number, msq, price, cl from
  (select * from stock_price_history where company='ABCD') match_recognize(
    order by price_date
    measures
        match_number() as "MATCH_NUMBER",
        match_sequence_number() as msq,
        classifier() as cl
    all rows per match
    pattern(ANY_ROW UP+)
    define
        ANY_ROW AS TRUE,
        UP as price > lag(price)
)
order by match_number, msq;
+------------+--------------+-----+-------+---------+
| PRICE_DATE | MATCH_NUMBER | MSQ | PRICE | CL      |
|------------+--------------+-----+-------+---------|
| 2020-10-02 |            1 |   1 |    36 | ANY_ROW |
| 2020-10-03 |            1 |   2 |    39 | UP      |
| 2020-10-04 |            1 |   3 |    42 | UP      |
| 2020-10-05 |            2 |   1 |    30 | ANY_ROW |
| 2020-10-06 |            2 |   2 |    47 | UP      |
| 2020-10-07 |            2 |   3 |    71 | UP      |
| 2020-10-08 |            2 |   4 |    80 | UP      |
+------------+--------------+-----+-------+---------+

Copy

Omissão de correspondências vazias¶

Isso procura por faixas de preço acima da média de todo o gráfico de uma empresa. Este exemplo omite as correspondências vazias. Observe, no entanto, que as correspondências vazias aumentam o MATCH_NUMBER:

select * from stock_price_history match_recognize(
    partition by company
    order by price_date
    measures
        match_number() as "MATCH_NUMBER"
    all rows per match omit empty matches
    pattern(OVERAVG*)
    define
        OVERAVG as price > avg(price) over (rows between unbounded
                                  preceding and unbounded following)
)
order by company, price_date;
+---------+------------+-------+--------------+
| COMPANY | PRICE_DATE | PRICE | MATCH_NUMBER |
|---------+------------+-------+--------------|
| ABCD    | 2020-10-07 |    71 |            7 |
| ABCD    | 2020-10-08 |    80 |            7 |
| ABCD    | 2020-10-09 |    75 |            7 |
| ABCD    | 2020-10-10 |    63 |            7 |
| XYZ     | 2020-10-01 |    89 |            1 |
| XYZ     | 2020-10-04 |    63 |            4 |
| XYZ     | 2020-10-05 |    65 |            4 |
| XYZ     | 2020-10-06 |    56 |            4 |
| XYZ     | 2020-10-08 |    54 |            6 |
+---------+------------+-------+--------------+

Copy

Demonstrar a opção WITH UNMATCHED ROWS¶

Este exemplo demonstra a WITH UNMATCHED ROWS option. Como o exemplo Omissão de correspondências vazias acima, este exemplo procura por faixas de preço acima do preço médio do gráfico de cada empresa. Observe que o quantificador nesta consulta é +, enquanto o quantificador na consulta anterior era *:

select * from stock_price_history match_recognize(
    partition by company
    order by price_date
    measures
        match_number() as "MATCH_NUMBER",
        classifier() as cl
    all rows per match with unmatched rows
    pattern(OVERAVG+)
    define
        OVERAVG as price > avg(price) over (rows between unbounded
                                 preceding and unbounded following)
)
order by company, price_date;
+---------+------------+-------+--------------+---------+
| COMPANY | PRICE_DATE | PRICE | MATCH_NUMBER | CL      |
|---------+------------+-------+--------------+---------|
| ABCD    | 2020-10-01 |    50 |         NULL | NULL    |
| ABCD    | 2020-10-02 |    36 |         NULL | NULL    |
| ABCD    | 2020-10-03 |    39 |         NULL | NULL    |
| ABCD    | 2020-10-04 |    42 |         NULL | NULL    |
| ABCD    | 2020-10-05 |    30 |         NULL | NULL    |
| ABCD    | 2020-10-06 |    47 |         NULL | NULL    |
| ABCD    | 2020-10-07 |    71 |            1 | OVERAVG |
| ABCD    | 2020-10-08 |    80 |            1 | OVERAVG |
| ABCD    | 2020-10-09 |    75 |            1 | OVERAVG |
| ABCD    | 2020-10-10 |    63 |            1 | OVERAVG |
| XYZ     | 2020-10-01 |    89 |            1 | OVERAVG |
| XYZ     | 2020-10-02 |    24 |         NULL | NULL    |
| XYZ     | 2020-10-03 |    37 |         NULL | NULL    |
| XYZ     | 2020-10-04 |    63 |            2 | OVERAVG |
| XYZ     | 2020-10-05 |    65 |            2 | OVERAVG |
| XYZ     | 2020-10-06 |    56 |            2 | OVERAVG |
| XYZ     | 2020-10-07 |    50 |         NULL | NULL    |
| XYZ     | 2020-10-08 |    54 |            3 | OVERAVG |
| XYZ     | 2020-10-09 |    30 |         NULL | NULL    |
| XYZ     | 2020-10-10 |    32 |         NULL | NULL    |
+---------+------------+-------+--------------+---------+

Copy

Demonstração dos predicados do símbolo na cláusula MEASURES¶

Este exemplo mostra o uso da notação <símbolo>.<coluna> com predicados do símbolos:

SELECT company, price_date, price, "FINAL FIRST(LT45.price)", "FINAL LAST(LT45.price)"
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               FINAL FIRST(LT45.price) AS "FINAL FIRST(LT45.price)",
               FINAL LAST(LT45.price)  AS "FINAL LAST(LT45.price)"
           ALL ROWS PER MATCH
           AFTER MATCH SKIP PAST LAST ROW
           PATTERN (LT45 LT45)
           DEFINE
               LT45 AS price < 45.00
           )
    WHERE company = 'ABCD'
    ORDER BY price_date;
+---------+------------+-------+-------------------------+------------------------+
| COMPANY | PRICE_DATE | PRICE | FINAL FIRST(LT45.price) | FINAL LAST(LT45.price) |
|---------+------------+-------+-------------------------+------------------------|
| ABCD    | 2020-10-02 |    36 |                      36 |                     39 |
| ABCD    | 2020-10-03 |    39 |                      36 |                     39 |
| ABCD    | 2020-10-04 |    42 |                      42 |                     30 |
| ABCD    | 2020-10-05 |    30 |                      42 |                     30 |
+---------+------------+-------+-------------------------+------------------------+

Copy