klausa MATCH_RECOGNIZE

Berlaku untuk: pemeriksaan Databricks SQL Databricks Runtime 19.0 ke atas

Important

Fitur ini ada di Beta. Admin ruang kerja dapat mengontrol akses ke fitur ini dari halaman Pratinjau . Lihat Kelola Pratinjau Azure Databricks.

Menemukan dan memfilter pola di baris table_reference sebelumnya. MATCH_RECOGNIZE mempartisi input, mengurutkan baris dalam setiap partisi, mencocokkan pola baris dengan urutan yang diurutkan, dan mengembalikan ringkasan atau hasil per baris tergantung pada mode baris-per-pertandingan.

Penggunaan umum termasuk mendeteksi eksekusi nilai berturut-turut, pergerakan harga berbentuk V atau berbentuk W, dan sesi aliran peristiwa.

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 OLEH partisi [, ...]

  Satu atau beberapa ekspresi yang menentukan grup baris tempat pencocokan pola berjalan. Jika Anda menghilangkan PARTITION BY, partisi berisi semua baris.

  PARTITION BY hanya menerima referensi kolom. Jika Anda menentukan ekspresi lain, Azure Databricks akan menaikkan MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Menentukan urutan baris dalam setiap partisi. Fungsi pencocokan pola dan navigasi menggunakan urutan ini.

• LANGKAH

  Secara opsional menentukan kolom pengukuran yang dikembalikan untuk setiap kecocokan pola.

• row_pattern_rows_per_match

  Mengontrol berapa banyak baris yang dikembalikan per kecocokan. Defaultnya adalah ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Mengembalikan satu baris per kecocokan. Hasilnya berisi kolom partisi dan hanya mengukur kolom.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Mengembalikan satu baris untuk setiap baris yang berpartisipasi dalam kecocokan. Setiap baris output menyertakan kolom input yang sesuai dari kolom , PARTITION BY dan MEASURES kolom yang dihitung untuk kecocokan tersebuttable_reference.

    SHOW EMPTY MATCHES diterima di bawah ALL ROWS PER MATCH, dan merupakan default saat Anda menghilangkan sub-klausa penanganan kecocokan kosong. Rilis ini tidak menghasilkan kecocokan kosong, sehingga kata kunci tidak memiliki efek yang dapat diamati pada hasilnya.

• SETELAH MATCH row_pattern_skip_to

  Menentukan baris mana yang akan dilanjutkan setelah kecocokan ditemukan. Rilis SKIP PAST LAST ROW ini hanya mendukung. Lanjutkan dengan baris segera setelah baris terakhir dari kecocokan saat ini. Ini adalah default ketika Anda menghilangkan AFTER MATCH klausa.

• POLA ( row_pattern )

  Menentukan pola yang akan dicocokkan.

• TENTUKAN row_pattern_definition_list

  Menentukan variabel boolean yang direferensikan dalam PATTERN klausa dan MEASURES .

Result

Hasilnya tergantung pada mode rows-per-match:

• ONE ROW PER MATCH

  Mengembalikan PARTITION BY kolom diikuti oleh MEASURES kolom.

• ALL ROWS PER MATCH

  Mengembalikan satu baris untuk setiap baris yang berpartisipasi dalam kecocokan. Setiap baris output menyertakan kolom input yang sesuai dari kolom , PARTITION BY dan MEASURES kolom yang dihitung untuk kecocokan tersebuttable_reference.

Kondisi kesalahan umum

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

Examples

Setiap kueri menggunakan stock_ticker(symbol, tstamp, price) tabel, kecuali contoh terakhir yang menggunakan page_views(user_id, event_time).

Contoh 1: Eksekusi naik berturut-turut

Temukan setiap eksekusi maksimum kenaikan harga berturut-turut per simbol. Variabel strt tidak DEFINE memiliki entri, sehingga cocok dengan baris apa pun dan jangkar eksekusi. up+ memperluas kecocokan di satu atau beberapa peningkatan berturut-turut. PREV(price) membaca harga baris ORDER BY sebelumnya secara berurutan. ONE ROW PER MATCH memancarkan satu baris ringkasan per eksekusi.

> 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

Contoh 2: Bentuk V (dip dan pulihkan)

Deteksi harga yang pertama kali turun, lalu naik. down+ cocok dengan kaki jatuh dan up+ pemulihan. LAST(down.tstamp) memilih baris terakhir yang diklasifikasikan sebagai down, yang merupakan palung V. Referensi yang memenuhi syarat variabel seperti down.tstamp memungkinkan ekspresi membaca baris yang MEASURES cocok dengan variabel pola tertentu.

> 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

Contoh 3: Dua-bawah (W-shape)

Deteksi dua turunan yang dipisahkan oleh pemulihan parsial. Pola ini mengeja empat kaki (down1+ up1+ down2+ up2+), dan nama variabel yang berbeda memungkinkan Anda mengukur atau memfilter setiap palung secara independen. MATCH_NUMBER() angka setiap W yang ditemukan dalam partisi.

> 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

Contoh 4: Sesiisasi

Ciutkan aliran peristiwa pengguna ke dalam sesi, di mana kesenjangan lebih dari 30 menit antara peristiwa berturut-turut memulai sesi baru. strt membuka sesi pada baris mana pun. same_session* menyerap setiap peristiwa berikut yang terjadi dalam waktu 30 menit dari pendahulunya. Ketika celah melebihi ambang batas, pertandingan berakhir, AFTER MATCH SKIP PAST LAST ROW dilanjutkan pada peristiwa berikutnya, dan sesi baru (baru MATCH_NUMBER()) dimulai. Kuantifier * menjadikan peristiwa kesepian sebagai sesi satu baris yang valid.

> 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

Artikel terkait

• Klausa DEFINE untuk MATCH_RECOGNIZE
• Klausa MEASURES untuk MATCH_RECOGNIZE
• Klausa POLA untuk MATCH_RECOGNIZE
• fungsi MATCH_RECOGNIZE
• referensi tabel