MATCH_RECOGNIZE¶

DEFINE : définition des symboles¶

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

Les symboles (également appelés « variables de modèle ») sont les éléments constitutifs du modèle.

Un symbole est défini par une expression. Si l’expression est vraie pour une ligne, le symbole est attribué à cette ligne. Plusieurs symboles peuvent être attribués à une ligne.

Les symboles qui ne sont pas définis dans la clause DEFINE, mais qui sont utilisés dans le modèle, sont toujours affectés à toutes les lignes. Implicitement, ils sont équivalents à l’exemple suivant :

...
define
    my_example_symbol as true
...

Les modèles sont définis à partir de symboles et d”opérateurs.

PATTERN : spécification du modèle à faire correspondre¶

PATTERN ( <pattern> )

Le modèle définit une séquence valide de lignes qui représente une correspondance. Le modèle est défini comme une expression régulière (regex) et est construit à partir de symboles, d’opérateurs et de quantificateurs.

Par exemple, supposons que le symbole S1 soit défini comme stock_price < 55, et que le symbole S2 soit défini comme stock price > 55. Le modèle suivant spécifie une séquence de lignes dans lesquelles le prix de l’action est passé de moins de 55 à plus de 55 :

PATTERN (S1 S2)

Voici un exemple plus complexe de définition de modèle :

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

La section suivante décrit en détail les différents composants de ce modèle.

symbol1+ any_symbol* symbol2

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

Symboles:

Un symbole correspond à une ligne à laquelle ce symbole a été attribué. Les symboles suivants sont disponibles :

• symbol. Par exemple, S1, … , S4 Ce sont des symboles qui ont été définis dans la sous-clause DEFINE et qui sont évalués par ligne. (Il peut également s’agir de symboles qui n’ont pas été définis et qui sont automatiquement affectés à toutes les lignes).

• ^ (Début de la partition.) Il s’agit d’un symbole virtuel qui indique le début d’une partition et auquel aucune ligne n’est associée. Vous pouvez l’utiliser pour exiger qu’une correspondance ne commence qu’au début d’une partition.

  Pour un exemple, voir Mise en correspondance des modèles par rapport au début ou à la fin d’une partition.

• $ (Fin de la partition.) Il s’agit d’un symbole virtuel qui indique la fin d’une partition et auquel aucune ligne n’est associée. Vous pouvez l’utiliser pour exiger qu’une correspondance ne se termine qu’à la fin d’une partition.

  Pour un exemple, voir Mise en correspondance des modèles par rapport au début ou à la fin d’une partition.

Quantificateurs:

Un quantificateur peut être placé après un symbole ou une opération. Un quantificateur indique le nombre minimum et maximum d’occurrences du symbole ou de l’opération associé. Les quantificateurs suivants sont disponibles :

Quantificateur	Signification
+	1 ou plus. Par exemple, ( {- S3 -} S4 )+.
*	0 ou plus. Par exemple, S2*?.
?	0 ou 1.
{n}	Exactement n.
{n,}	n ou plus.
{,m}	0 à m.
{n, m}	n à m. Par exemple, PERMUTE(S1, S2){1,2}.

Par défaut, les quantificateurs sont en « mode gourmand », ce qui signifie qu’ils essaient de correspondre à la quantité maximale si possible. Pour mettre un quantificateur en « mode réticent », dans lequel le quantificateur essaie de correspondre à la quantité minimale si possible, placez un ? après le quantificateur (par exemple S2*?).

Opérateurs:

Les opérateurs précisent dans quel ordre les symboles ou autres opérations doivent se produire dans la séquence de lignes pour former une correspondance valide. Les opérateurs suivants sont disponibles :

Opérateur	Signification
... ... (espace)	Concaténation. Spécifie qu’un symbole ou une opération doit en suivre un autre. Par exemple, S1 S2 signifie que la condition définie pour S2 doit se produire après la condition définie pour S1.
{- ... -}	Exclusion. Exclut les symboles ou les opérations contenus de la sortie. Par exemple, {- S3 -} exclut l’opérateur S3 de la sortie. Les lignes exclues n’apparaîtront pas dans la sortie, mais seront incluses dans l’évaluation des expressions MEASURES .
( ... )	Groupement. Utilisé pour remplacer la précédence d’un opérateur ou pour appliquer le même quantificateur aux symboles ou aux opérations du groupe. Dans cet exemple, le quantificateur + s’applique à la séquence {- S3 -} S4, et pas seulement à S4.
PERMUTE(..., ...)	Permutation. Correspond à toute permutation des motifs spécifiés. Par exemple, PERMUTE(S1, S2) correspond à S1 S2 ou S2 S1. PERMUTE() accepte un nombre illimité d’arguments.
... | ...	Alternative. Spécifie que soit le premier symbole ou la première opération, soit l’autre doit se produire. Par exemple, ( S3 S4 ) | PERMUTE(S1, S2). L’opérateur alternatif a la priorité sur l’opérateur de concaténation.

ORDER BY : trier les lignes avant la mise en correspondance¶

{}

Où :

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

Copy

Définissez l’ordre des lignes comme vous le feriez pour les fonctions de fenêtre. C’est l’ordre dans lequel les lignes individuelles de chaque partition sont transmises à l’opérateur MATCH_RECOGNIZE .

Pour plus d’informations, voir Partitionnement et tri des lignes.

PARTITION BY : partitionnement des lignes en fenêtres¶

{}

Partitionnez l’ensemble de lignes d’entrée comme vous le feriez pour les fonctions de fenêtre. MATCH_RECOGNIZE effectue la correspondance individuellement pour chaque partition résultante.

Le partitionnement permet non seulement de regrouper les lignes qui sont liées les unes aux autres, mais aussi d’exploiter la capacité de traitement des données distribuées de Snowflake, car des partitions distinctes peuvent être traitées en parallèle.

Pour plus d’informations sur le partitionnement, voir Partitionnement et tri des lignes.

MEASURES : spécification des colonnes de sortie supplémentaires¶

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

Les « mesures » sont des colonnes supplémentaires facultatives qui sont ajoutées à la sortie de l’opérateur MATCH_RECOGNIZE . Les expressions de la sous-clause MEASURES ont les mêmes capacités que les expressions de la sous-clause DEFINE. Pour plus d’informations, voir Symboles.

Au sein de la sous-clause MEASURES, les fonctions suivantes, spécifiques à MATCH_RECOGNIZE , sont disponibles :

• MATCH_NUMBER() Renvoie le numéro séquentiel de la correspondance. Le MATCH_NUMBER commence à partir de 1, et est incrémenté pour chaque correspondance.

• MATCH_SEQUENCE_NUMBER() Renvoie le numéro de ligne dans une correspondance. MATCH_SEQUENCE_NUMBER est séquentiel et commence à partir de 1.

• CLASSIFIER() Renvoie une valeur TEXT qui contient le symbole correspondant à la ligne concernée. Par exemple, si une ligne correspond au symbole GT75, la fonction CLASSIFIER renvoie la chaîne « GT75 ».

ROW(S) PER MATCH : spécification des lignes à retourner¶

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

Spécifie les lignes qui sont retournées pour une correspondance réussie. La sous-clause est facultative.

• ALL ROWS PER MATCH : retourne toutes les lignes de la correspondance.

• ONE ROW PER MATCH : renvoie une ligne de résumé pour chaque correspondance, quel que soit le nombre de lignes dans la correspondance. C’est la configuration par défaut.

Soyez attentif aux cas particuliers suivants :

• Correspondances vides : une correspondance vide se produit si un modèle est capable de correspondre à zéro ligne. Par exemple, si le modèle est défini comme A* et que la première ligne au début d’une tentative de correspondance est affectée au symbole B, une correspondance vide comprenant uniquement cette ligne est générée, car le quantificateur * dans le modèle A* permet de traiter 0 occurrence de A comme une correspondance. Les expressions MEASURES sont évaluées différemment pour cette ligne :

  • La fonction CLASSIFIER retourne NULL.

  • Les fonctions de fenêtre renvoient NULL.

  • La fonction COUNT renvoie 0.

• Lignes sans correspondance : si une ligne n’a pas été mise en correspondance avec le modèle, elle est appelée « ligne sans correspondance ». MATCH_RECOGNIZE peut également être configuré pour renvoyer des lignes sans correspondance. Pour les lignes sans correspondance, les expressions de la sous-clause MEASURES renvoient NULL.

• Exclusions

  La syntaxe d’exclusion ({- ... -}) dans la définition du modèle permet aux utilisateurs d’exclure certaines lignes de la sortie. Si tous les symboles du modèle ont été exclus, aucune ligne n’est générée pour cette correspondance si ALL ROWS PER MATCH a été spécifié. Notez que MATCH_NUMBER est incrémenté de toute façon. Les lignes exclues ne font pas partie du résultat, mais sont incluses pour l’évaluation des expressions MEASURES .

  Lorsqu’on utilise la syntaxe d’exclusion, la sous-clause ROWS PER MATCH peut être spécifiée comme suit :

  • ONE ROW PER MATCH (par défaut)

    Renvoie exactement une ligne pour chaque correspondance réussie. La sémantique par défaut des fonctions de fenêtre dans la sous-clause MEASURES est FINAL.

    Les colonnes de sortie de l’opérateur MATCH_RECOGNIZE sont toutes les expressions données dans la sous-clause PARTITION BY et toutes les expressions MEASURES . Toutes les lignes résultantes d’une correspondance sont regroupées par les expressions données dans la sous-clause PARTITION BY et les MATCH_NUMBER en utilisant la fonction d’agrégation ANY_VALUE pour toutes les mesures. Par conséquent, si les mesures donnent une valeur différente pour différentes lignes de la même correspondance, la sortie est non déterministe.

    L’omission de la sous-clause PARTITION BY et MEASURES entraîne une erreur indiquant que le résultat ne comprend aucune colonne.

    Pour les correspondances vides, une ligne est générée. Les lignes sans correspondance ne font pas partie de la sortie.

  • ALL ROWS PER MATCH

    Renvoie une ligne pour chaque ligne faisant partie de la correspondance, à l’exception des lignes correspondant à une partie du modèle marqué pour l”exclusion.

    Les lignes exclues sont toujours prises en compte dans les calculs de la sous-clause MEASURES .

    Les correspondances peuvent se chevaucher en fonction de la sous-clause AFTER MATCH SKIP TO, de sorte que la même ligne puisse apparaître plusieurs fois dans la sortie.

    La sémantique par défaut des fonctions de fenêtre dans la sous-clause MEASURES est RUNNING.

    Les colonnes de sortie de l’opérateur MATCH_RECOGNIZE sont les colonnes de l’ensemble des lignes en entrée et les colonnes définies dans la sous-clause MEASURES .

    Les options suivantes sont disponibles pour ALL ROWS PER MATCH :

    • SHOW EMPTY MATCHES (default) Ajouter des correspondances vides à la sortie. Les lignes sans correspondance ne sont pas sorties.

    • OMIT EMPTY MATCHES Ni les correspondances vides ni les lignes sans correspondance ne sont dans la sortie. Cependant, le MATCH_NUMBER est toujours incrémenté par une correspondance vide.

    • WITH UNMATCHED ROWS Ajoute les correspondances vides et les lignes sans correspondance à la sortie. Si cette clause est utilisée, le modèle ne doit pas contenir d’exclusions.

  Pour un exemple qui utilise l’exclusion pour réduire les sorties non pertinentes, voir Recherche de modèles dans des lignes non adjacentes.

AFTER MATCH SKIP : spécifier où poursuivre après une correspondance¶

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

Cette sous-clause indique où poursuivre la correspondance après qu’une correspondance positive a été trouvée.

• PAST LAST ROW (default)

  Continuer la correspondance après la dernière ligne de la correspondance en cours.

  Cela empêche les correspondances qui contiennent des lignes qui se chevauchent. Par exemple, si vous avez un modèle de stock qui contient 3 V formes dans une ligne, alors PAST LAST ROW trouve un modèle W , pas deux.

• TO NEXT ROW

  Continuer la correspondance après la première ligne de la correspondance en cours.

  Cela permet des correspondances qui contiennent des lignes qui se chevauchent. Par exemple, si vous avez un modèle de stock qui contient 3 formes V dans une ligne, alors TO NEXT ROW trouve deux modèles W (le premier modèle est basé sur les deux premières formes V , et la deuxième forme W est basée sur les deuxième et troisième formes V ; ainsi les deux modèles contiennent le même V).

• TO [ { FIRST | LAST } ] <symbole>

  Continuez la mise en correspondance à la première ou à la dernière ligne (par défaut) qui correspondait au symbole donné.

  Au moins une ligne doit être associée au symbole donné, sinon une erreur est signalée.

  Si cette opération ne permet pas de dépasser la première ligne de la correspondance en cours, une erreur est signalée.

Expressions dans les clauses DEFINE et MEASURES¶

Les clauses DEFINE et MEASURES autorisent les expressions. Ces expressions peuvent être complexes et inclure des fonctions de fenêtre et des fonctions de navigation spéciales (qui sont un type de fonction de fenêtre).

Dans la plupart des cas, les expressions dans DEFINE et MEASURES suivent les règles applicables aux expressions ailleurs dans la syntaxe SQL de Snowflake. Toutefois, il existe certaines différences, décrites ci-dessous :

Fonctions de la fenêtre:

Les fonctions de navigation permettent de faire référence à d’autres lignes que la ligne en cours. Par exemple, pour créer une expression qui définit une baisse de prix, vous devez comparer le prix d’une ligne au prix d’une autre ligne. Les fonctions de navigation sont les suivantes :

• PREV( expr [ , offset [, default ] ] ) Permet de naviguer jusqu’à la ligne précédente de la correspondance actuelle dans la sous-clause MEASURES.

  Cette fonction n’est actuellement pas disponible dans la sous-clause DEFINE. Au lieu de cela, vous pouvez utiliser LAG qui navigue vers la ligne précédente dans le cadre de la fenêtre.

• NEXT( expr [ , offset [ , default ] ] ) Permet de naviguer vers la ligne suivante dans le cadre actuel de la fenêtre. Cette fonction est équivalente à LEAD.

• FIRST( expr ) Permet de naviguer jusqu’à la première ligne de la correspondance actuelle dans la sous-clause MEASURES.

  Cette fonction n’est actuellement pas disponible dans la sous-clause DEFINE. À la place, vous pouvez utiliser FIRST_VALUE qui permet de naviguer vers la première ligne du cadre actuel de la fenêtre.

• LAST( expr ) Permet de naviguer vers la dernière ligne du cadre actuel de la fenêtre. Cette fonction est similaire à LAST_VALUE, mais pour LAST le cadre de la fenêtre est limité à la ligne actuelle de la tentative de correspondance actuelle lorsque LAST est utilisé dans la sous-clause DEFINE .

Pour un exemple qui utilise les fonctions de navigation, voir Renvoi d’informations sur la correspondance.

En général, lorsqu’une fonction de fenêtre est utilisée à l’intérieur d’une clause MATCH_RECOGNIZE , la fonction de fenêtre ne nécessite pas sa propre clause OVER (PARTITION BY ... ORDER BY ...) . La fenêtre est implicitement déterminée par PARTITION BY et ORDER BY de la clause MATCH_RECOGNIZE . (Toutefois, voir Limitations des fonctions de fenêtre utilisées dans DEFINE et MEASURES pour certaines exceptions).

En général, le cadre de la fenêtre est également dérivé implicitement du contexte actuel dans lequel la fonction de fenêtre est utilisée. La limite inférieure du cadre est définie comme décrit ci-dessous :

Dans la sous-clause DEFINE :

Le cadre commence au début de la tentative de correspondance en cours, sauf en cas d’utilisation de LAG, LEAD, FIRST_VALUE et LAST_VALUE.

Dans la sous-clause MEASURES :

Le cadre commence au début de la correspondance qui a été trouvée.

Les bords du cadre de la fenêtre peuvent être spécifiés en utilisant la sémantique RUNNING ou FINAL .

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

Copy

RUNNING:

En général, le cadre se termine à la ligne actuelle. Toutefois, les exceptions suivantes existent :

• Dans la sous-clause DEFINE , pour LAG, LEAD, FIRST_VALUE, LAST_VALUE, et NEXT et , le cadre se termine à la dernière ligne de la fenêtre.

• Dans la sous-clause MEASURES , pour PREV, NEXT, LAG et LEAD, le cadre se termine à la dernière ligne de la fenêtre.

Dans la sous-clause DEFINE , RUNNING est la sémantique par défaut (et la seule autorisée).

Dans la sous-clause MEASURES , lorsque la sous-clause ALL ROWS PER MATCH est utilisée, RUNNING est la valeur par défaut.

FINAL:

Le cadre se termine à la dernière ligne de la correspondance.

FINAL n’est autorisé que dans la sous-clause MEASURES . C’est la valeur par défaut lorsque ONE ROW PER MATCH s’applique.

Prédicats de symboles:

Les expressions comprises dans les sous-clauses DEFINE et MEASURES autorisent les symboles comme prédicats pour les références de colonnes.

predicatedColumnReference ::= <symbol>.<column>

Copy

Le <symbole> indique une ligne qui a été trouvée et le symbole <colonne> identifie une colonne spécifique dans cette ligne.

Une référence de colonne avec prédicats signifie que la fonction de fenêtre environnante ne recherche que les lignes qui ont été finalement mappées au symbole spécifié.

Les références de colonnes avec prédicats peuvent être utilisées à l’extérieur et à l’intérieur d’une fonction de fenêtre. En cas d’utilisation en dehors d’une fonction de fenêtre, <symbol>.<column> est identique à LAST(<symbole>.<colonne>). À l’intérieur d’une fonction de fenêtre, toutes les références de colonnes doivent être associées à un prédicat ayant le même symbole ou toutes doivent ne pas avoir de prédicat.

Ceci explique comment les fonctions liées à la navigation se comportent avec les références de colonnes avec prédicat :

• {} Recherche dans le cadre de la fenêtre, vers l’arrière, à partir de la ligne actuelle incluse (ou de la dernière ligne dans le cas d’une sémantique FINAL ), la première ligne qui a finalement été associée au <symbole> spécifié, puis est <décalée> de lignes (la valeur par défaut est 1) vers l’arrière en ignorant le symbole auquel ces lignes ont été associées. Si la partie recherchée du cadre ne contient pas de ligne associée au <symbole> ou si la recherche devait dépasser le bord du cadre, NULL est renvoyé.

• {} Recherche dans le cadre de la fenêtre, vers l’arrière, à partir de la ligne actuelle incluse (ou de la dernière ligne dans le cas d’une sémantique FINAL ), la première ligne qui a finalement été associée au <symbole> spécifié, puis est <décalée> de lignes (la valeur par défaut est 1) vers l’arrière en ignorant le symbole auquel ces lignes ont été associées. Si la partie recherchée du cadre ne contient pas de ligne associée au <symbole> ou si la recherche devait dépasser le bord du cadre, NULL est renvoyé.

• {} Recherche dans le cadre de la fenêtre vers l’avant, à partir de la première ligne incluse (ou la dernière ligne dans le cas d’une sémantique FINAL), la première ligne qui a finalement été associée au <symbole> spécifié. Si la partie recherchée du cadre ne contient pas de ligne associée au <symbole>, NULL est renvoyé.

• {} Recherche dans le cadre de la fenêtre, vers l’arrière, à partir de la ligne actuelle incluse (ou la dernière ligne dans le cas d’une sémantique FINAL), la première ligne qui a finalement été associée au <symbole> spécifié. Si la partie recherchée du cadre ne contient pas de ligne associée au <symbole>, NULL est renvoyé.

Note

Les restrictions sur les fonctions de fenêtre sont documentées dans la section Limitations des fonctions de fenêtre utilisées dans DEFINE et MEASURES .

Limitations des fonctions de fenêtre utilisées dans DEFINE et MEASURES¶

Les expressions des sous-clauses DEFINE et MEASURES peuvent inclure des fonctions de fenêtre. Toutefois, l’utilisation des fonctions de fenêtre dans ces sous-clauses est soumise à certaines restrictions. Ces limitations sont présentées dans le tableau ci-dessous :

Fonction	DEFINE (En cours) <column/symbol.column>	MEASURES (En cours) <column/symbol.column>	MEASURES (Final) <column/symbol.column>
Colonne	✔ / ❌	✔ / ❌	✔ / ✔
PREV(…)	❌ / ❌	✔ / ❌	✔ / ❌
NEXT(…)	✔ / ❌	✔ / ❌	✔ / ❌
FIRST(…)	❌ / ❌	✔ / ❌	✔ / ✔
LAST(…)	✔ / ❌	✔ / ❌	✔ / ✔
LAG()	✔ / ❌	✔ / ❌	✔ / ❌
LEAD()	✔ / ❌	✔ / ❌	✔ / ❌
FIRST_VALUE()	✔ / ❌	✔ / ❌	✔ / ✔
LAST_VALUE()	✔ / ❌	✔ / ❌	✔ / ✔
Agrégations [1]	✔ / ❌	✔ / ✔	✔ / ✔
Autres fonctions de la fenêtre [1]	✔ / ❌	✔ / ❌	✔ / ❌

Les fonctions spécifiques à MATCH_RECOGNIZE MATCH_NUMBER(), MATCH_SEQUENCE_NUMBER() et CLASSIFIER() ne sont actuellement pas disponibles dans la sous-clause DEFINE .

Message d’erreur : SELECT with no columns Lors de l’utilisation de ONE ROW PER MATCH¶

Lorsque vous utilisez la clause ONE ROW PER MATCH , seules les colonnes et expressions des sous-clauses PARTITION BY et MEASURES sont autorisées dans la clause de projection de SELECT. Si vous essayez d’utiliser MATCH_RECOGNIZE sans une clause PARTITION BY ou MEASURES , vous obtenez une erreur similaire à SELECT with no columns.

Pour plus d’informations sur ONE ROW PER MATCH et ALL ROWS PER MATCH, voir Génération d’une ligne pour chaque correspondance et génération de toutes les lignes pour chaque correspondance.

Déclarer une ligne de résumé pour chaque forme V¶

La requête suivante recherche toutes les courbes V dans le stock_price_history présenté précédemment. La sortie est expliquée plus en détail après la requête et la sortie.

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

La sortie montre une ligne par correspondance (quel que soit le nombre de lignes faisant partie de la correspondance).

La sortie comprend les colonnes suivantes :

• COMPANY : le symbole boursier de la société.

• Le MATCH_NUMBER est un numéro séquentiel identifiant la correspondance dans cet ensemble de données (par exemple, la première correspondance a MATCH_NUMBER 1, la deuxième correspondance a MATCH_NUMBER 2, etc.) Si les données ont été partitionnées, alors le MATCH_NUMBER est le numéro séquentiel à l’intérieur de la partition (dans cet exemple, pour chaque société/action).

• START_DATE : date à laquelle cette occurrence du modèle commence.

• END_DATE : date à laquelle cette occurrence du modèle se termine.

• ROWS_IN_SEQUENCE : nombre de lignes dans la correspondance. Par exemple, la première correspondance est basée sur les prix mesurés les 4 jours (du 1er octobre au 4 octobre), donc ROWS_IN_SEQUENCE est 4.

• NUM_DECREASES : nombre de jours (dans la correspondance) où le prix a baissé. Par exemple, dans la première correspondance, le prix a baissé pendant 1 jour puis a augmenté pendant 2 jours, donc NUM_DECREASES est 1.

• NUM_INCREASES : nombre de jours (dans la correspondance) où le prix a augmenté. Par exemple, dans la première correspondance, le prix a baissé pendant 1 jour puis a augmenté pendant 2 jours, donc NUM_INCREASES est 2.

Signaler toutes les lignes de toutes les correspondances pour une entreprise¶

Cet exemple renvoie toutes les lignes de chaque correspondance (et pas seulement une ligne de résumé par correspondance). Ce modèle recherche les prix en hausse de l’entreprise 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

Omettre les correspondances vides¶

Ceci recherche les plages de prix supérieures à la moyenne de l’ensemble du graphique d’une entreprise. Cet exemple ne tient pas compte des correspondances vides. Notez, cependant, que les correspondances vides incrémentent néanmoins le 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

Démontrer l’option WITH UNMATCHED ROWS¶

Cet exemple illustre WITH UNMATCHED ROWS option . Comme l’exemple Omettre les correspondances vides ci-dessus, cet exemple recherche les plages de prix supérieures au prix moyen du graphique de chaque entreprise. Notez que le quantificateur dans cette requête est +, alors que le quantificateur dans la requête précédente était * :

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

Démonstration des prédicats de symboles dans la clause MEASURES¶

Cet exemple montre l’utilisation de la notation de colonne <symbole>.<colonne> avec des prédicats de symbole :

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