Identifizieren von Sequenzen von Zeilen, die einem Muster entsprechen¶

Einrichten der Daten für dieses Beispiel¶

Um die in diesem Beispiel verwendeten Daten einzurichten, führen Sie die folgenden SQL-Anweisungen aus:

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

Schritt 1: Festlegen der Reihenfolge und Gruppierung von Zeilen¶

Der erste Schritt bei der Identifizierung einer Zeilensequenz besteht darin, die Gruppierung und die Sortierreihenfolge der zu durchsuchenden Zeilen festzulegen. Für das Beispiel der Suche nach einem V-Muster im Aktienkurs für ein Unternehmen sähe dies wie folgt aus:

• Die Zeilen sollten nach Firma gruppiert werden, da Sie ein Muster im Kurs einer bestimmten Firma finden möchten.

• Innerhalb jeder Gruppe von Zeilen (die Kurse einer bestimmten Firma) sollten die Zeilen nach Datum in aufsteigender Reihenfolge sortiert sein.

In einer MATCH_RECOGNIZE-Klausel verwenden Sie die Unterklauseln PARTITION BY und ORDER BY, um die Gruppierung und Reihenfolge der Zeilen anzugeben. Beispiel:

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

Copy

Schritt 2: Definieren des zu übereinstimmenden Musters¶

Legen Sie als Nächstes das Muster fest, das mit der zu suchenden Zeilensequenz übereinstimmt.

Um dieses Muster anzugeben, verwenden Sie etwas Ähnliches wie einen regulären Ausdruck. In regulären Ausdrücken verwenden Sie eine Kombination aus Literalen und Metazeichen, um ein Muster anzugeben, das in einer Zeichenfolge übereinstimmen soll.

Im folgenden Beispiel soll eine Sequenz von Zeichen gesucht werden, die Folgendes enthält:

• ein beliebiges Einzelzeichen, gefolgt von

• einem oder mehreren Großbuchstaben, gefolgt von

• einem oder mehreren Kleinbuchstaben

Dafür können Sie den folgenden Perl-kompatiblen regulären Ausdruck verwenden:

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

Wobei:

• Ein Punkt (.) stimmt mit jedem beliebigen Einzelzeichen überein.

• [A-Z]+ stimmt mit einem oder mehreren Großbuchstaben überein.

• [a-z]+ stimmt mit einem oder mehreren Kleinbuchstaben überein.

+ ist ein Quantifizierer, der angibt, dass eines oder mehrere der vorangehenden Zeichen übereinstimmen müssen.

Beispielsweise passt der obige reguläre Ausdruck auf Zeichensequenzen wie:

• 1Stock

• @SFComputing

• %Fn

In einer MATCH_RECOGNIZE-Klausel verwenden Sie einen ähnlichen Ausdruck, um das Muster der abzugleichenden Zeilen anzugeben. In diesem Fall beinhaltet das Suchen von Zeilen, die mit einem V-Muster übereinstimmen, das Suchen einer Sequenz von Zeilen, die Folgendes enthält:

• die Zeile, bevor der Aktienkurs sinkt, gefolgt von

• einer oder mehreren Zeilen, in denen der Aktienkurs sinkt, gefolgt von

• einer oder mehreren Zeilen, in denen der Aktienkurs steigt

Sie können dies als das folgende Zeilenmuster ausdrücken:

row_before_decrease row_with_price_decrease+ row_with_price_increase+

Zeilenmuster bestehen aus Mustervariablen, Quantifizierern (die ähnlich denen in regulären Ausdrücken verwendet werden) und Operatoren. Eine Mustervariable definiert einen Ausdruck, der auf einer Zeile ausgewertet wird.

Bei diesem Zeilenmuster:

• row_before_decrease, row_with_price_decrease, und row_with_price_increase sind Mustervariablen. Die Ausdrücke für diese Mustervariablen sollen Folgendes ergeben:

  • eine beliebige Zeile (die Zeile, bevor der Aktienkurs sinkt)

  • eine Zeile mit fallendem Aktienkurs

  • eine Zeile mit steigendem Aktienkurs

  row_before_decrease ist vergleichbar mit . in einem regulären Ausdruck. Im folgenden regulären Ausdruck stimmt . mit jedem einzelne Zeichen überein, das vor dem ersten Großbuchstaben im Muster auftritt.

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

  Copy

  In ähnlicher Weise stimmt row_before_decrease im Zeilenmuster mit jeder Zeile überein, die vor der ersten Zeile mit einem gesunkenen Kurs auftritt.

• Die +-Quantifizierer nach row_with_price_decrease und row_with_price_increase geben an, dass für jede dieser Zeilen eine oder mehrere Zeilen übereinstimmen müssen.

In einer MATCH_RECOGNIZE-Klausel verwenden Sie die Unterklausel PATTERN, um das Zeilenmuster anzugeben, das übereinstimmen soll:

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

Um die Ausdrücke für die Mustervariablen anzugeben, verwenden Sie die Unterklausel DEFINE:

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

Copy

Wobei:

• row_before_decrease muss hier nicht definiert werden, da es auf eine beliebige Zeile ergeben soll.

• row_with_price_decrease ist als Ausdruck für eine Zeile mit einem gesunkenen Kurs definiert.

• row_with_price_increase ist als Ausdruck für eine Zeile mit einem erhöhten Kurs definiert.

Um die Preise in unterschiedlichen Zeilen zu vergleichen und den Preis für die vorherige Zeile anzugeben, verwenden die Definitionen dieser Variablen die Navigationsfunktion LAG().

Das Zeilenmuster stimmt mit zwei Zeilensequenzen überein, wie unten dargestellt:

Bei der ersten übereinstimmende Zeilensequenz:

• row_before_decrease stimmt mit der Zeile mit dem Aktienkurs 50 überein.

• row_with_price_decrease stimmt mit der nächsten Zeile mit dem Aktienkurs 36 überein.

• row_with_price_increase stimmt mit den nächsten beiden Zeilen mit den Aktienkursen 39 und 42 überein.

Für die zweite übereinstimmende Zeilensequenz:

• row_before_decrease stimmt mit der Zeile mit dem Aktienkurs 42 überein. (Dies ist die gleiche Zeile, die am Ende der ersten übereinstimmenden Zeilensequenz steht).

• row_with_price_decrease stimmt mit der nächsten Zeile mit dem Aktienkurs 30 überein.

• row_with_price_increase stimmt mit den nächsten beiden Zeilen mit den Aktienkursen 47, 71 und 80 überein.

Schritt 3: Festlegen der zurückzugebenden Zeilen¶

MATCH_RECOGNIZE kann Folgendes zurückgeben:

• eine einzelne Zeile, die alle übereinstimmenden Sequenz zusammenfasst, oder

• jede Zeile in jeder übereinstimmenden Sequenz

In diesem Beispiel möchten Sie eine Zusammenfassung für jede übereinstimmende Sequenz zurückgeben. Verwenden Sie die Unterklausel ONE ROW PER MATCH, um anzugeben, dass für jede übereinstimmende Sequenz genau eine Zeile zurückgegeben werden soll.

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

Schritt 4: Festlegen der auszuwählenden Measures¶

Wenn Sie ONE ROW PER MATCH verwenden, gibt MATCH_RECOGNIZE keine der Spalten in der Tabelle zurück (bis auf die durch PARTITION BY angegebene Spalte), selbst wenn MATCH_RECOGNIZE in einer SELECT *-Anweisung enthalten ist. Um die Daten festzulegen, die von dieser Anweisung zurückgegeben werden sollen, müssen Sie Measures definieren. Measures sind zusätzliche Datenspalten, die für jede übereinstimmende Sequenz von Zeilen berechnet werden (z. B. das Startdatum der Sequenz, das Enddatum der Sequenz, die Anzahl der Tage in der Sequenz usw.).

Verwenden Sie die Unterklausel MEASURES, um diese zusätzlichen Spalten anzugeben, die in der Ausgabe zurückgegeben werden sollen. Das allgemeine Format für die Definition eines Measures ist wie folgt:

<expression> AS <column_name>

Wobei:

• expression gibt Informationen zu der Sequenz an, die Sie zurückgeben möchten. Für den Ausdruck können Sie Funktionen mit Spalten der Tabelle und Mustervariablen verwenden, die Sie zuvor definiert haben.

• column_name gibt den Namen der Spalte an, die in der Ausgabe zurückgegeben wird.

In diesem Beispiel können Sie die folgenden Measures definieren:

• Eine Nummer, die die Sequenz identifiziert (die erste übereinstimmende Sequenz, die zweite übereinstimmende Sequenz usw.).

  Verwenden Sie für diesen Measure die Funktion MATCH_NUMBER(), die die Nummer der Übereinstimmung zurückgibt. Die Nummern beginnen mit 1 für die erste Übereinstimmung in einer Partition von Zeilen. Wenn es mehrere Partitionen gibt, beginnt die Nummer in jeder Partition mit 1.

• Der Tag, bevor der Aktienkurs gesunken ist.

  Verwenden Sie für diesen Measure die Funktion FIRST(), die den Wert des Ausdrucks für die erste Zeile in der übereinstimmenden Sequenz zurückgibt. In diesem Beispiel gibt FIRST(price_date) den Wert der Spalte price_date in der ersten Zeile jeder übereinstimmenden Sequenz zurück, also das Datum, bevor der Aktienkurs gefallen ist.

• Der letzte Tag, an dem der Aktienkurs gestiegen ist.

  Verwenden Sie für diesen Measure die Funktion LAST(), die den Wert des Ausdrucks für die letzte Zeile in der übereinstimmenden Sequenz zurückgibt.

• Die Anzahl der Tage im V-Muster.

  Verwenden Sie für diesen Measure COUNT(*). Da Sie COUNT(*) in der Definition eines Measure angeben, gibt das Sternchen (*) an, dass Sie alle Zeilen in einer übereinstimmenden Sequenz zählen möchten (nicht alle Zeilen in der Tabelle).

• Die Anzahl der Tage, an denen der Kurs gefallen ist.

  Verwenden Sie für diesen Measure COUNT(row_with_price_decrease.*). Der Punkt gefolgt von einem Sternchen (.*) gibt an, dass Sie alle Zeilen in einer übereinstimmenden Sequenz zählen möchten, die mit der Mustervariablen row_with_price_decrease übereinstimmen.

• Die Anzahl der Tage, an denen der Kurs gestiegen ist.

  Verwenden Sie für diesen Measure COUNT(row_with_price_increase.*).

Es folgt die Unterklausel MEASURES, die den obigen Measure definiert:

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

Im Folgenden sehen Sie ein Beispiel für die Ausgabe mit den ausgewählten Measures:

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

Wie bereits erwähnt, enthält die Ausgabe die Spalte company, da die Klausel PARTITION BY diese Spalte angibt.

Schritt 5: Festlegen, wo die Suche nach der nächsten Übereinstimmung fortgesetzt werden soll¶

Nachdem eine übereinstimmende Zeilensequenz gefunden wurde, fährt MATCH_RECOGNIZE fort, die nächste übereinstimmende Sequenz zu finden. Sie können angeben, wo MATCH_RECOGNIZE mit der Suche nach der nächsten übereinstimmenden Sequenz beginnen soll.

Wie in der Darstellung von übereinstimmenden Sequenzen gezeigt, kann eine Zeile Teil von mehr als einer übereinstimmenden Sequenz sein. Im folgenden Beispiel ist die Zeile für 2020-10-04 Teil von zwei V-Mustern.

In diesem Beispiel können Sie, um die nächste übereinstimmende Sequenz zu finden, von einer Zeile ausgehen, in der der Kurs gestiegen ist. Um dies in der MATCH_RECOGNIZE-Klausel anzugeben, verwenden Sie AFTER MATCH SKIP:

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

wobei TO LAST row_with_price_increase angibt, dass Sie die Suche bei der letzten Zeile beginnen möchten, in der der Kurs gestiegen ist.

Verwenden von Quantifizierern mit Mustervariablen¶

In der Unterklausel PATTERN verwenden Sie einen regulären Ausdruck, um ein abzugleichendes Muster von Zeilen anzugeben. Sie verwenden Mustervariablen, um Zeilen in der Sequenz zu identifizieren, die bestimmte Bedingungen erfüllen.

Wenn Sie mehrere Zeilen abgleichen müssen, die eine bestimmte Bedingung erfüllen, können Sie einen Quantifizierer verwenden, wie in einem regulären Ausdruck.

Sie können z. B. den Quantifizierer + verwenden, um anzugeben, dass das Muster eine oder mehrere Zeilen enthalten muss, in denen der Aktienkurs um 10 % fällt, gefolgt von einer oder mehreren Zeilen, in denen der Aktienkurs um 5 % steigt:

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

Abgleichen von Mustern relativ zum Anfang oder Ende einer Partition¶

Um eine Sequenz von Zeilen relativ zum Anfang oder Ende einer Partition zu finden, können Sie die Metazeichen ^ und $ in der Unterklausel PATTERN verwenden. Diese Metazeichen in einem Zeilenmuster haben einen ähnlichen Zweck wie die gleichen Metazeichen in einem regulären Ausdruck:

• ^ stellt den Anfang einer Partition dar.

• $ stellt das Ende einer Partition dar.

Das folgende Muster stimmt mit einer Aktie überein, deren Preis zu Beginn der Partition größer als 75,00 ist:

PATTERN (^ GT75)
DEFINE
    GT75 AS price > 75.00

Beachten Sie, dass mit ^ und $ Positionen angegeben werden und nicht die Zeilen an diesen Positionen (ähnlich wie ^ und $ in einem regulären Ausdruck die Position angeben und nicht die Zeichen an diesen Positionen). In PATTERN (^ GT75) muss die erste Zeile (nicht die zweite Zeile) einen Preis haben, der größer ist als 75,00. In PATTERN (GT75 $) muss die letzte Zeile (nicht die vorletzte Zeile) größer als 75 sein.

Hier ist ein vollständiges Beispiel mit ^. Beachten Sie, dass, obwohl die Aktie XYZ in mehr als einer Zeile in dieser Partition einen höheren Preis als 60,00 hat, nur die Zeile am Anfang der Partition als Übereinstimmung betrachtet wird.

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

Hier ist ein vollständiges Beispiel mit $. Beachten Sie, dass, obwohl die Aktie ABCD in mehr als einer Zeile in dieser Partition einen höheren Preis als 50,00 hat, nur die Zeile am Ende der Partition als Übereinstimmung betrachtet wird.

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

Generieren einer Zeile für jede Übereinstimmung vs. Generieren aller Zeilen für jede Übereinstimmung¶

Wenn MATCH_RECOGNIZE eine Übereinstimmung findet, kann die Ausgabe entweder eine Zusammenfassungszeile für die gesamte Übereinstimmung oder eine Zeile für jeden Datenpunkt im Muster umfassen.

• ALL ROWS PER MATCH gibt an, dass die Ausgabe alle Zeilen in der Übereinstimmung enthält.

• ONE ROW PER MATCH gibt an, dass die Ausgabe nur eine Zeile für jede Übereinstimmung in jeder Partition enthält.

  Die Projektionsklausel der SELECT-Anweisung kann nur die Ausgabe von MATCH_RECOGNIZE verwenden. Das bedeutet im Endeffekt, dass die SELECT-Anweisung nur Spalten aus den folgenden Unterklauseln von MATCH_RECOGNIZE verwenden kann:

  • Die Unterklausel PARTITION BY.

    Alle Zeilen in einer Übereinstimmung stammen aus derselben Partition und haben daher denselben Wert für die Ausdrücke der PARTITION BY-Unterklausel.

  • Die MEASURES-Klausel.

    Wenn Sie MATCH_RECOGNIZE ... ONE ROW PER MATCH verwenden, generiert die Unterklausel MEASURES nicht nur Ausdrücke, die für alle Zeilen in der Übereinstimmung denselben Wert zurückgeben (z. B. MATCH_NUMBER()), sondern auch Ausdrücke, die für verschiedene Zeilen in der Übereinstimmung unterschiedliche Werte zurückgeben können (z. B. MATCH_SEQUENCE_NUMBER()). Wenn Sie Ausdrücke verwenden, die für verschiedene Zeilen in der Übereinstimmung unterschiedliche Werte zurückgeben können, ist die Ausgabe nicht deterministisch.

  Wenn Sie mit Aggregationsfunktionen und GROUP BY vertraut sind, könnte die folgende Analogie hilfreich sein, um ONE ROW PER MATCH zu verstehen:

  • Die PARTITION BY-Klausel in MATCH_RECOGNIZE gruppiert die Daten ähnlich wie GROUP BY die Daten in SELECT.

  • Die MEASURES-Klausel in einem MATCH_RECOGNIZE ... ONE ROW PER MATCH erlaubt Aggregationsfunktionen, wie z. B. COUNT(), die für jede Zeile in der Übereinstimmung denselben Wert zurückgeben, wie es MATCH_NUMBER() tut.

  Wenn Sie nur Aggregationsfunktionen und Ausdrücke verwenden, die für jede Zeile in der Übereinstimmung denselben Wert zurückgeben, dann verhält sich ... ONE ROW PER MATCH ähnlich wie GROUP BY und Aggregationsfunktionen.

Die Voreinstellung ist ONE ROW PER MATCH.

Die folgenden Beispiele zeigen den Unterschied in den Ausgaben von ONE ROW PER MATCH und ALL ROWS PER MATCH. Diese beiden Codebeispiele sind bis auf die ...ROW(S) PER MATCH-Klausel fast identisch. (Bei typischer Verwendung hat eine SQL-Anweisung mit ONE ROW PER MATCH andere MEASURES-Unterklauseln als eine SQL-Anweisung mit 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 |
+---------+---------+------------------+-------------------+

Ausschließen von Zeilen aus der Ausgabe¶

Bei einigen Abfragen möchten Sie vielleicht nur einen Teil des Musters in die Ausgabe aufnehmen. Sie möchten z. B. Muster finden, in denen Aktien viele Tage hintereinander gestiegen sind, aber nur die Spitzenwerte und einige zusammenfassende Informationen anzeigen (z. B. die Anzahl der Tage mit Kursanstiegen vor jedem Spitzenwert).

Sie können die Ausschlusssyntax im Muster verwenden, um MATCH_RECOGNIZE anzuweisen, nach einer bestimmten Mustervariablen zu suchen, sie aber nicht in die Ausgabe aufzunehmen. Um eine Mustervariable als Teil des zu suchenden Musters, aber nicht als Teil der Ausgabe aufzunehmen, verwenden Sie die Notation {- <Mustervariable> -}.

Hier ist ein einfaches Beispiel, das den Unterschied zwischen der Verwendung der Ausschlusssyntax und ohne deren Verwendung zeigt. Dieses Beispiel enthält zwei Abfragen, die jeweils nach einem Aktienkurs suchen, der bei unter 45 USD begann, dann fiel und wieder anstieg. Die erste Abfrage verwendet keine Ausschlusssyntax und zeigt daher alle Zeilen an. Die zweite Abfrage verwendet die Ausschlusssyntax und zeigt nicht den Tag an, an dem der Aktienkurs fiel.

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

Das nächste Beispiel ist realistischer. Es wird nach Mustern gesucht, in denen ein Aktienkurs einen oder mehrere Tage in Folge gestiegen und dann einen oder mehrere Tage in Folge gefallen ist. Da die Ausgabe recht groß sein kann, wird hier durch Ausschluss nur der erste Tag angezeigt, an dem die Aktie gestiegen ist (wenn sie mehr als einen Tag in Folge gestiegen ist), und nur der erste Tag, an dem sie gefallen ist (wenn sie mehr als einen Tag in Folge gefallen ist). Das Muster ist unten dargestellt:

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

Dieses Muster sucht nach Ereignissen in der folgenden Reihenfolge:

• Ein Startkurs kleiner als 45.

• Ein UP, möglicherweise unmittelbar gefolgt von anderen, die nicht in der Ausgabe enthalten sind.

• Ein DOWN, möglicherweise unmittelbar gefolgt von anderen, die nicht in der Ausgabe enthalten sind.

Hier sind der Code und die Ausgabe für Versionen des vorherigen Musters ohne Ausschluss und mit Ausschluss:

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

Grundlegende Informationen zur Übereinstimmung¶

In vielen Fällen möchten Sie, dass Ihre Abfrage nicht nur Informationen aus der Tabelle auflistet, die die Daten enthält, sondern auch Informationen zu den gefundenen Mustern. Wenn Sie Informationen zu den Übereinstimmungen selbst wünschen, geben Sie diese Informationen in der MEASURES-Klausel an.

Die MEASURES-Klausel kann die folgenden Funktionen enthalten, die spezifisch für MATCH_RECOGNIZE sind:

• MATCH_NUMBER(): Jedes Mal, wenn eine Übereinstimmung gefunden wird, wird ihr eine fortlaufende Übereinstimmungsnummer zugewiesen, beginnend bei 1. Diese Funktion gibt die Übereinstimmungsnummer zurück.

• MATCH_SEQUENCE_NUMBER(): Da ein Muster normalerweise mehr als einen Datenpunkt umfasst, möchten Sie vielleicht wissen, welcher Datenpunkt mit jedem Wert aus der Tabelle verbunden ist. Diese Funktion gibt die fortlaufende Nummer des Datenpunktes innerhalb der Übereinstimmung zurück.

• CLASSIFIER(): Der Klassifizierer ist der Name der Mustervariablen, mit der die Zeile übereinstimmt.

Die Abfrage unten enthält eine MEASURES-Klausel mit der Übereinstimmungsnummer, der Übereinstimmungssequenznummer und dem Klassifizierer.

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

Die Unterklausel MEASURES kann viel mehr Informationen als diese liefern. Weitere Informationen dazu finden Sie in der MATCH_RECOGNIZE-Referenzdokumentation.

Fenster, Fensterrahmen und Navigationsfunktionen¶

Die MATCH_RECOGNIZE-Klausel operiert auf einem „Fenster“ von Zeilen. Wenn MATCH_RECOGNIZE eine PARTITION-Unterklausel enthält, dann ist jede Partition ein eigenes Fenster. Wenn es keine PARTITION-Unterklausel gibt, dann ist die gesamte Eingabe ein Fenster.

Die PATTERN-Unterklausel von MATCH_RECOGNIZE gibt die Symbole in der Reihenfolge von links nach rechts an. Beispiel:

PATTERN (START DOWN UP)

Wenn Sie sich die Daten als eine Sequenz von Zeilen in aufsteigender Reihenfolge von links nach rechts vorstellen, können Sie sich MATCH_RECOGNIZE als eine Bewegung nach rechts vorstellen (z. B. vom frühesten zum spätesten Datum im Aktienkurs-Beispiel), wobei Sie in den Zeilen innerhalb jedes Fensters nach einem Muster suchen.

MATCH_RECOGNIZE beginnt mit der ersten Zeile im Fenster und prüft, ob diese Zeile und die nachfolgenden Zeilen mit dem Muster übereinstimmen.

Im einfachsten Fall wird nach der Feststellung, ob es eine Musterübereinstimmung ab der ersten Zeile im Fenster gibt, MATCH_RECOGNIZE eine Zeile nach rechts bewegt und dann der Vorgang wiederholt, wobei geprüft wird, ob die zweite Zeile der Beginn eines Auftretens des Musters ist. MATCH_RECOGNIZE bewegt sich weiter nach rechts, bis das Ende des Fensters erreicht ist.

(MATCH_RECOGNIZE kann sich um mehr als eine Zeile nach rechts bewegen. Sie können beispielsweise MATCH_RECOGNIZE so definieren, dass nach dem Ende des aktuellen Musters mit der Suche nach dem nächsten Muster begonnen wird).

Sie können sich das grob so vorstellen, als gäbe es einen „Rahmen“, der sich innerhalb des Fensters nach rechts bewegt. Der linke Rand dieses Rahmens befindet sich an der ersten Zeile in der Menge der Zeilen, die gerade auf eine Übereinstimmung geprüft werden. Der rechte Rand des Rahmens wird erst definiert, wenn eine Übereinstimmung gefunden wurde. Sobald eine Übereinstimmung gefunden wurde, ist der rechte Rand des Rahmens die letzte Zeile der Übereinstimmung. Wenn das Suchmuster z. B. pattern (start down up) wäre, dann ist die Zeile, die mit dem up übereinstimmt, die letzte Zeile vor dem rechten Rand des Rahmens.

(Wenn keine Übereinstimmung gefunden wird, dann ist der rechte Rand des Rahmens nie definiert und wird nie referenziert).

In einfachen Fällen können Sie sich einen gleitenden Fensterrahmen wie unten abgebildet vorstellen:

Sie haben bereits gesehen, dass Navigationsfunktionen wie LAG() in Ausdrücken der DEFINE-Unterklausel verwendet werden (z. B. DEFINE down_10_percent as LAG(price) * 0.9 >= price). Die folgende Abfrage zeigt, dass Navigationsfunktionen auch in der MEASURES-Unterklausel verwendet werden können. In diesem Beispiel zeigen die Navigationsfunktionen die Ränder (und damit die Größe) des Fensterrahmens an, der die aktuelle Übereinstimmung enthält.

Jede Ausgabezeile dieser Abfrage enthält die Werte der Navigationsfunktionen LAG(), LEAD(), FIRST() und LAST() für diese Zeile. Die Größe des Fensterrahmens ist die Anzahl der Zeilen zwischen FIRST() und LAST(), einschließlich der ersten und letzten Zeile selbst.

Die DEFINE- und PATTERN-Klauseln in der folgenden Abfrage wählen Gruppen von drei Zeilen aus (1.-3. Oktober, 2.-4. Oktober, 3.-5. Oktober usw.).

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

Die Ausgabe dieser Abfrage zeigt auch, dass die Funktionen LAG() und LEAD() NULL für Ausdrücke zurückgeben, die versuchen, Zeilen außerhalb der Übereinstimmungsgruppe zu referenzieren (d. h. außerhalb des Fensterrahmens).

Die Regeln für Navigationsfunktionen in DEFINE-Klauseln unterscheiden sich leicht von den Regeln für Navigationsfunktionen in MEASURES-Klauseln. So ist beispielsweise die Funktion PREV() derzeit in der MEASURES-Klausel verfügbar, aber nicht in der DEFINE-Klausel. Stattdessen können Sie LAG() in der DEFINE-Klausel verwenden. In der Referenzdokumentation für MATCH_RECOGNIZE sind die entsprechenden Regeln für jede Navigationsfunktion aufgeführt.

Die Unterklausel MEASURES kann auch Folgendes enthalten:

• Aggregationsfunktionen. Wenn das Muster z. B. mit einer unterschiedlichen Anzahl von Zeilen übereinstimmen kann (z. B. weil es mit einer oder mehr fallenden Aktienkursen übereinstimmt), dann möchten Sie vielleicht die Gesamtzahl der übereinstimmenden Zeilen wissen. Sie können dies durch Verwendung von COUNT(*) anzeigen.

• Allgemeine Ausdrücke, die auf Werte in jeder Zeile der Übereinstimmung angewendet werden. Dies können mathematische Ausdrücke, logische Ausdrücke usw. sein. Sie können z. B. die Werte in der Zeile betrachten und Textbeschreibungen wie „ABOVE AVERAGE“ ausgeben.

  Wenn Sie Zeilen gruppieren (ONE ROW PER MATCH) und eine Spalte unterschiedliche Werte für unterschiedliche Zeilen in der Gruppe hat, müssen Sie daran denken, dass der für diese Spalte ausgewählte Wert für diese Übereinstimmung nicht deterministisch ist und Ausdrücke, die auf diesem Wert basieren, wahrscheinlich ebenfalls nicht deterministisch sind.

Weitere Informationen zur Unterklausel MEASURES finden Sie in der Referenzdokumentation zu MATCH_RECOGNIZE.

Korrelation vs. Kausalität¶

Korrelation ist keine Garantie für Kausalität. MATCH_RECOGNIZE kann „falsch-positive“ Ergebnisse liefern (Fälle, in denen Sie ein Muster sehen, aber es ist nur ein Zufall).

Der Musterabgleich kann auch zu „falsch-negativen“ Ergebnissen führen (Fälle, in denen es ein Muster in der realen Welt gibt, das Muster aber nicht in der Datenprobe erscheint).

In den meisten Fällen ist das Finden einer Übereinstimmung (z. B. das Finden eines Musters, das auf Versicherungsbetrug hindeutet) nur der erste Schritt in einer Analyse.

Die folgenden Faktoren erhöhen typischerweise die Anzahl der falsch-positiven Ergebnisse:

• Große Datasets

• Suche nach einer großen Anzahl von Mustern

• Suche nach kurzen oder einfachen Mustern

Die folgenden Faktoren erhöhen typischerweise die Anzahl der falsch-negativen Ergebnisse:

• Kleine Datasets

• Keine Suche nach allen möglichen relevanten Mustern

• Suche nach Mustern, die komplexer als nötig sind

Reihenfolge-unabhängige Muster¶

Obwohl die meisten Musterabgleiche erfordern, dass die Daten in einer bestimmten Reihenfolge vorliegen (z. B. nach Zeit), gibt es Ausnahmen. Wenn zum Beispiel eine Person Versicherungsbetrug sowohl bei einem Autounfall als auch bei einem Wohnungseinbruch begeht, spielt es keine Rolle, in welcher Reihenfolge die Betrügereien geschehen.

Wenn das gesuchte Muster nicht Reihenfolge-abhängig ist, können Sie Operatoren wie „Alternative“ (|) und PERMUTE verwenden, um Ihre Suche weniger Reihenfolge-abhängig zu machen.

Mehrtägige Kurssteigerungen finden¶

Die folgende Abfrage findet alle Muster, in denen der Kurs der Firma ABCD an zwei aufeinanderfolgenden Tagen gestiegen ist:

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

Beispiel für SKIP TO NEXT ROW-Option¶

In diesem Beispiel wird die Option SKIP TO NEXT ROW veranschaulicht. Diese Abfrage sucht nach W-förmigen Kurven im Chart der jeweiligen Firma. Die Übereinstimmungen können sich überschneiden.

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

Suche nach Mustern in nicht benachbarten Zeilen¶

In manchen Situationen möchten Sie vielleicht nach Mustern in nicht zusammenhängenden Zeilen suchen. Wenn Sie z. B. Protokolldateien analysieren, möchten Sie vielleicht nach allen Mustern suchen, in denen einem schwerwiegenden Fehler eine bestimmte Sequenz von Warnungen vorausgegangen ist. Vielleicht gibt es keine natürliche Möglichkeit, die Zeilen so zu partitionieren und zu sortieren, dass alle relevanten Meldungen (Zeilen) in einem einzigen Fenster und nebeneinander liegen. In dieser Situation benötigen Sie möglicherweise ein Muster, das nach bestimmten Ereignissen sucht, aber nicht erfordert, dass die Ereignisse in den Daten zusammenhängend sind.

Unten sehen Sie ein Beispiel für DEFINE- und PATTERN-Klauseln, die entweder zusammenhängende oder nicht zusammenhängende Zeilen erkennen, die dem Muster entsprechen. Das Symbol ANY_ROW ist als TRUE definiert (es stimmt also mit jeder Zeile überein). Der * nach jedem Auftreten von ANY_ROW besagt, dass zwischen der ersten Warnung und der zweiten Warnung sowie zwischen der zweiten Warnung und der fatalen Fehlerprotokollmeldung ANY_ROW keinmal, einmal oder mehrmals auftreten darf. Das gesamte Muster sagt also, dass nach WARNING1 gesucht werden soll, gefolgt von einer beliebigen Anzahl von Zeilen, gefolgt von WARNING2, gefolgt von einer beliebigen Anzahl von Zeilen, gefolgt von FATAL_ERROR. Damit die irrelevanten Zeilen nicht mit in der Ausgabe erscheinen, verwendet die Abfrage die Ausschluss-Syntax ({- und -}).

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

Fehler bei Verwendung von ONE ROW PER MATCH und Angeben von Spalten in der Select-Klausel¶

Die ONE ROW PER MATCH-Klausel verhält sich ähnlich wie eine Aggregationsfunktion. Dies schränkt die möglichen Ausgabespalten ein, die Sie verwenden können. Wenn Sie z. B. ONE ROW PER MATCH verwenden und jede Übereinstimmung drei Zeilen mit unterschiedlichen Datumsangaben enthält, können Sie die Datumsspalte nicht als Ausgabespalte in der SELECT-Klausel angeben, da ein einzelnes Datum nicht für alle drei Zeilen korrekt sein kann.

Unerwartete Ergebnisse¶

• Prüfen Sie, ob die PATTERN- und DEFINE-Klauseln typografische Fehler aufweisen.

  Wenn ein in der PATTERN-Klausel verwendeter Mustervariablenname in der DEFINE-Klausel nicht definiert ist (z. B. weil der Name entweder in der PATTERN- oder in der DEFINE-Klausel falsch eingegeben wurde), wird kein Fehler gemeldet. Stattdessen wird einfach angenommen, dass der Name der Mustervariablen für jede Zeile wahr ist.

• Überprüfen Sie die SKIP-Klausel, um sicherzustellen, dass sie angemessen ist, z. B. um überlappende Muster ein- oder auszuschließen.