MATCH_RECOGNIZE-Klausel

Gilt für: Databricks SQL Databricks Runtime 19.0 and above

Sucht und filtert Muster in den Zeilen der vorherigen table_reference. MATCH_RECOGNIZE Partitioniert die Eingabe, sortiert Zeilen innerhalb jeder Partition, entspricht einem Zeilenmuster mit dieser sortierten Sequenz und gibt zusammenfassungs- oder zeilenspezifische Ergebnisse abhängig vom Zeilen-pro-Match-Modus zurück.

Typische Verwendungen umfassen das Erkennen von Aufeinanderfolgenden Werten, V-förmigen oder W-förmigen Preisbewegungen und sitzungsbasierte Ereignisdatenströme.

Syntax

MATCH_RECOGNIZE (
  [ PARTITION BY partition [, ...] ]
  [ ORDER BY order_by ]
  [ MEASURES measures ]
  [ row_pattern_rows_per_match ]
  [ AFTER MATCH row_pattern_skip_to ]
  PATTERN ( row_pattern )
  DEFINE row_pattern_definition_list )

measures
  MEASURES { measureExpr AS measureName } [, ...]

row_pattern_rows_per_match
  { ONE ROW PER MATCH
  | ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ] }

row_pattern_skip_to
  SKIP PAST LAST ROW

Parameters

• PARTITION BY-Partition [, ...]

  Mindestens ein Ausdruck, der die Zeilengruppen definiert, in denen der Musterabgleich ausgeführt wird. Wenn Sie weglassen PARTITION BY, enthält die Partition alle Zeilen.

  PARTITION BY akzeptiert nur Spaltenverweise. Wenn Sie einen anderen Ausdruck angeben, löst Azure Databricks MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN aus.

• ORDER BY order_by

  Gibt die Reihenfolge der Zeilen innerhalb jeder Partition an. Musterabgleichs- und Navigationsfunktionen verwenden diese Reihenfolge.

• MAßNAHMEN

  Definiert optional die für jede Mustervergleichung zurückgegebenen Measurespalten.

• row_pattern_rows_per_match

  Steuert, wie viele Zeilen pro Übereinstimmung zurückgegeben werden. Der Standardwert lautet ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Gibt eine Zeile pro Übereinstimmung zurück. Das Ergebnis enthält nur Partitionsspalten und Measurespalten.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Gibt eine Zeile für jede Zeile zurück, die an einer Übereinstimmung teilnimmt. Jede Ausgabezeile enthält die entsprechenden Eingabespalten aus den Spalten und MEASURES Spalten, die table_referencePARTITION BY für diese Übereinstimmung berechnet werden.

    SHOW EMPTY MATCHES wird unter ALL ROWS PER MATCHund ist die Standardeinstellung, wenn Sie die Unterklausel für die Verarbeitung leerer Übereinstimmungen weglassen. Diese Version erzeugt keine leeren Übereinstimmungen, sodass das Schlüsselwort keine feststellbare Auswirkung auf das Ergebnis hat.

• AFTER MATCH row_pattern_skip_to

  Gibt an, von welcher Zeile nach dem Auffinden einer Übereinstimmung fortgesetzt werden soll. Diese Version unterstützt SKIP PAST LAST ROW nur. Fahren Sie mit der Zeile fort, die unmittelbar auf die letzte Zeile der aktuellen Übereinstimmung folgt. Dies ist die Standardeinstellung, wenn Sie die AFTER MATCH Klausel weglassen.

• PATTERN ( row_pattern )

  Gibt das muster an, das übereinstimmen soll.

• DEFINE row_pattern_definition_list

  Definiert die booleschen Variablen, auf die in den PATTERN Und-Klauseln MEASURES verwiesen wird.

Ergebnis

Das Ergebnis hängt vom Zeilen-pro-Match-Modus ab:

• ONE ROW PER MATCH

  Gibt PARTITION BY Spalten gefolgt von MEASURES Spalten zurück.

• ALL ROWS PER MATCH

  Gibt eine Zeile für jede Zeile zurück, die an einer Übereinstimmung teilnimmt. Jede Ausgabezeile enthält die entsprechenden Eingabespalten aus den Spalten und MEASURES Spalten, die table_referencePARTITION BY für diese Übereinstimmung berechnet werden.

Häufige Fehlerbedingungen

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITIONIEREN_NACH_MUSS_EINE_SPALTE_SEIN

Beispiele

Jede Abfrage verwendet eine stock_ticker(symbol, tstamp, price) Tabelle mit Ausnahme des letzten Beispiels, das page_views(user_id, event_time)verwendet.

Beispiel 1: Aufeinanderfolgende aufsteigende Ausführung

Finden Sie jede maximale Ausführung aufeinander folgender Preiserhöhungen pro Symbol. Die Variable strt hat keinen DEFINE Eintrag, sodass sie mit einer Zeile übereinstimmt und die Ausführung verankert. up+ erweitert die Übereinstimmung über eine oder mehrere aufeinander folgende Erhöhungen. PREV(price) liest den Preis der unmittelbar vorhergehenden Zeile in ORDER BY der Reihenfolge. ONE ROW PER MATCH gibt pro Ausführung eine einzelne Sammelzeile aus.

> CREATE OR REPLACE TEMP VIEW stock_ticker AS
  SELECT * FROM VALUES
    ('AAPL', TIMESTAMP '2024-01-01 09:30:00', 100.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:31:00', 102.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:32:00', 105.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:33:00', 104.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:34:00', 106.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:35:00', 108.0)
  AS t(symbol, tstamp, price);

> SELECT symbol, start_tstamp, end_tstamp, run_length
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp) AS start_tstamp,
             LAST(tstamp)  AS end_tstamp,
             COUNT(*)      AS run_length
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt up+ )
    DEFINE up AS price > PREV(price) ) AS T;
 symbol  start_tstamp           end_tstamp             run_length
 AAPL    2024-01-01 09:30:00    2024-01-01 09:32:00    3
 AAPL    2024-01-01 09:33:00    2024-01-01 09:35:00    3

Beispiel 2: V-Shape (Dip and recover)

Erkennen Sie einen Preis, der zuerst fällt, und steigt dann. down+ entspricht dem fallenden Bein und up+ der Wiederherstellung. LAST(down.tstamp) wählt die letzte Als klassifizierte downZeile aus, die der Trüge des V ist. Ein variabler qualifizierter Verweis, z down.tstamp . B. ermöglicht einem MEASURES Ausdruck das Lesen von Zeilen, die mit einer bestimmten Mustervariable übereinstimmen.

> SELECT symbol, start_tstamp, bottom_tstamp, end_tstamp
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp)     AS start_tstamp,
             LAST(down.tstamp) AS bottom_tstamp,
             LAST(tstamp)      AS end_tstamp
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt down+ up+ )
    DEFINE down AS price < PREV(price),
           up   AS price > PREV(price) ) AS T;
 symbol  start_tstamp           bottom_tstamp          end_tstamp
 AAPL    2024-01-01 09:32:00    2024-01-01 09:33:00    2024-01-01 09:35:00

Beispiel 3: Doppelboden (W-Shape)

Erkennen Sie zwei Dips, die durch eine teilweise Wiederherstellung getrennt sind. Das Muster schreibt vier Beine (down1+ up1+ down2+ up2+) aus, und mit unterschiedlichen Variablennamen können Sie jeden Trough unabhängig voneinander messen oder filtern. MATCH_NUMBER() nummerieren jedes W, das in einer Partition gefunden wurde.

> CREATE OR REPLACE TEMP VIEW stock_ticker AS
  SELECT * FROM VALUES
    ('AAPL', TIMESTAMP '2024-01-01 09:30:00', 100.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:31:00', 96.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:32:00', 92.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:33:00', 98.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:34:00', 101.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:35:00', 95.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:36:00', 90.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:37:00', 99.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:38:00', 104.0)
  AS t(symbol, tstamp, price);

> SELECT symbol, start_tstamp, end_tstamp, w_no
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp)  AS start_tstamp,
             LAST(tstamp)   AS end_tstamp,
             MATCH_NUMBER() AS w_no
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt down1+ up1+ down2+ up2+ )
    DEFINE down1 AS price < PREV(price),
           up1   AS price > PREV(price),
           down2 AS price < PREV(price),
           up2   AS price > PREV(price) ) AS T;
 symbol  start_tstamp           end_tstamp             w_no
 AAPL    2024-01-01 09:30:00    2024-01-01 09:38:00    1

Beispiel 4: Sessionization

Reduzieren Sie den Ereignisdatenstrom eines Benutzers in Sitzungen, in denen eine Lücke von mehr als 30 Minuten zwischen aufeinander folgenden Ereignissen beginnt, eine neue Sitzung. strt öffnet eine Sitzung in einer beliebigen Zeile. same_session* nimmt jedes folgende Ereignis auf, das innerhalb von 30 Minuten nach seinem Vorgänger auftritt. Wenn eine Lücke den Schwellenwert überschreitet, endet die Übereinstimmung, AFTER MATCH SKIP PAST LAST ROW wird beim nächsten Ereignis fortgesetzt, und eine neue Sitzung (eine neue MATCH_NUMBER()) beginnt. Der * Quantifizierer macht ein einzelnes Ereignis zu einer gültigen Einzeilensitzung.

> CREATE OR REPLACE TEMP VIEW page_views AS
  SELECT * FROM VALUES
    (1, TIMESTAMP '2024-01-01 09:00:00'),
    (1, TIMESTAMP '2024-01-01 09:15:00'),
    (1, TIMESTAMP '2024-01-01 10:00:00'),
    (1, TIMESTAMP '2024-01-01 10:10:00')
  AS t(user_id, event_time);

> SELECT user_id, session_no, session_start, session_end, event_count
  FROM page_views
  MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES MATCH_NUMBER()    AS session_no,
             FIRST(event_time) AS session_start,
             LAST(event_time)  AS session_end,
             COUNT(*)          AS event_count
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt same_session* )
    DEFINE same_session AS event_time <= PREV(event_time) + INTERVAL 30 MINUTE ) AS T;
 user_id  session_no  session_start          session_end            event_count
 1        1           2024-01-01 09:00:00    2024-01-01 09:15:00    2
 1        2           2024-01-01 10:00:00    2024-01-01 10:10:00    2

Verwandte Artikel

• DEFINE-Klausel für MATCH_RECOGNIZE
• MEASURES-Klausel für MATCH_RECOGNIZE
• PATTERN-Klausel für MATCH_RECOGNIZE
• MATCH_RECOGNIZE Funktionen
• Tabellenverweis