测试DuckDB 2.1的match_recognize模式匹配语句

C:\\d>wget https://artifacts.duckdb.org/latest/duckdb-cli-windows-amd64.tar.gz -O duckdb-cli-windows-amd64-260918a.tar.gz

duckdb-cli-windows-amd64-2609 100%[=================================================>] 18.39M 33.3KB/s in 7m 57s

2026-09-18 17:29:40 (39.5 KB/s) – 'duckdb-cli-windows-amd64-260918a.tar.gz' saved [19288695/19288695]

C:\\d>duckdb0918a
DuckDB v2.1.0-alpha42457 (Unknown Version)
Enter ".help" for usage hints.

with d (type,dt,status)as
( select 'X' ,date '2021-7-1','1' from dual
union all select 'X' ,date '2021-7-2','1' from dual
union all select 'X' ,date '2021-7-3','0' from dual
union all select 'X' ,date '2021-7-4','0' from dual
union all select 'X' ,date '2021-7-5','1' from dual
union all select 'X' ,date '2021-7-6','0' from dual
union all select 'X' ,date '2021-7-7','0' from dual
union all select 'X' ,date '2021-7-8','0' from dual
union all select 'X' ,date '2021-7-9','1' from dual
union all select 'X' ,date '2021-7-10','1' from dual
union all select 'X' ,date '2021-7-11','1' from dual
)
SELECT *
FROM d
MATCH_RECOGNIZE (
PARTITION BY type
ORDER BY dt
measures
dt as dt,
status as status
one ROW PER MATCH
PATTERN ( A )
DEFINE
A as status<>prev(status) or prev(status) is null
);

输出

memory D .read match-rec3.txt
┌─────────┬────────────┬─────────┐
│ type │ dt │ status │
│ varchar │ date │ varchar │
├─────────┼────────────┼─────────┤
│ X │ 2021-07-01 │ 1 │
│ X │ 2021-07-03 │ 0 │
│ X │ 2021-07-05 │ 1 │
│ X │ 2021-07-06 │ 0 │
│ X │ 2021-07-09 │ 1 │
└─────────┴────────────┴─────────┘

with d (type,dt,status)as
( select 'X' ,date '2021-7-1','1' from dual
union all select 'X' ,date '2021-7-2','1' from dual
union all select 'X' ,date '2021-7-3','0' from dual
union all select 'X' ,date '2021-7-4','0' from dual
union all select 'X' ,date '2021-7-5','1' from dual
union all select 'X' ,date '2021-7-6','0' from dual
union all select 'X' ,date '2021-7-7','0' from dual
union all select 'X' ,date '2021-7-8','0' from dual
union all select 'X' ,date '2021-7-9','1' from dual
union all select 'X' ,date '2021-7-10','1' from dual
union all select 'X' ,date '2021-7-11','1' from dual
)
SELECT *
FROM d
MATCH_RECOGNIZE (
PARTITION BY type
ORDER BY dt
ALL ROWS PER MATCH
PATTERN ( (A|{–B–})+ )
DEFINE
A as status<>last(status,1) or prev(status) is null
);

输出

memory D .read match-rec4.txt
┌─────────┬────────────┬─────────┐
│ type │ dt │ status │
│ varchar │ date │ varchar │
├─────────┼────────────┼─────────┤
│ X │ 2021-07-01 │ 1 │
│ X │ 2021-07-03 │ 0 │
│ X │ 2021-07-05 │ 1 │
│ X │ 2021-07-06 │ 0 │
│ X │ 2021-07-09 │ 1 │
└─────────┴────────────┴─────────┘

with tmp(id ,page) as
(select 1,3 from dual union all select 2,4 from dual union all
select 4,8 from dual union all select 3,5 from dual union all
select 5,9 from dual union all select 6,16 from dual union all
select 7,15 from dual union all select 8,18 from dual
)
SELECT *
FROM tmp
MATCH_RECOGNIZE
(
ORDER BY page
MEASURES
A.page as firstpage,
LAST(page) as lastpage,
COUNT(*) as cnt
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B*)
DEFINE B AS page = PREV(page)+1
);

输出，注意别名前的as不可省略。

memory D .read match-rec5.txt
Parser Error:
syntax error at or near "cnt"

LINE 29: COUNT(*) cnt
^^^
memory D .read match-rec5.txt
┌───────────┬──────────┬───────┐
│ firstpage │ lastpage │ cnt │
│ int32 │ int32 │ int64 │
├───────────┼──────────┼───────┤
│ 15 │ 16 │ 2 │
│ 18 │ 18 │ 1 │
│ 8 │ 9 │ 2 │
│ 3 │ 5 │ 3 │
└───────────┴──────────┴───────┘

with gen (id, val) as
(select 1, 3 from dual union all select 2, 2 from dual union all select 3,5 from dual union all
select 5, 3 from dual union all select 8, 2 from dual union all select 9,5 from dual union all
select 10, 3 from dual union all select 12, 2 from dual union all select 13,5 from dual union all
select 14, 3 from dual union all select 15, 2 from dual union all select 16,5 from dual union all
select 20, 3 from dual union all select 21, 2 from dual union all select 23,5 from dual
)
select bid,bid+2 as eid,sum3 from gen
match_recognize(
order by id
measures
first (a.id) as bid,
sum(val) as sum3
one row per match
after match skip to next row
pattern (A B*)
define
B as b.id<=a.id+2
);

输出

memory D .read match-rec6.txt
┌───────┬───────┬────────┐
│ bid │ eid │ sum3 │
│ int32 │ int32 │ int128 │
├───────┼───────┼────────┤
│ 1 │ 3 │ 10 │
│ 10 │ 12 │ 5 │
│ 15 │ 17 │ 7 │
│ 5 │ 7 │ 3 │
│ 14 │ 16 │ 10 │
│ 12 │ 14 │ 10 │
│ 20 │ 22 │ 5 │
│ 16 │ 18 │ 5 │
│ 23 │ 25 │ 5 │
│ 2 │ 4 │ 7 │
│ 3 │ 5 │ 8 │
│ 9 │ 11 │ 8 │
│ 8 │ 10 │ 10 │
│ 13 │ 15 │ 10 │
│ 21 │ 23 │ 7 │
└───────┴───────┴────────┘
15 rows 3 columns
memory D