MATCH_RECOGNIZE是SQL标准中定义的一种用于识别数据序列中特定模式的能力，Oracle从12c版本开始支持该特性，能够帮助开发者在查询中直接完成连续数据的规则匹配，不需要依赖复杂的PL/SQL逻辑或者多次自连接查询。
MATCH_RECOGNIZE核心语法结构
完整的MATCH_RECOGNIZE子句通常包含以下几个部分，每个部分都有明确的职责：
- PARTITION BY：指定分组的字段，类似于窗口函数的分区逻辑，模式匹配会在每个分区内独立进行
- ORDER BY：指定分区内数据的排序规则，模式匹配需要明确的顺序来识别连续的数据
- MEASURES：定义匹配完成后输出的字段，比如匹配的起始行ID、匹配到的行数等
- ALL ROWS PER MATCH/PATTERN (ROW NUMBER)：指定匹配结果的输出方式，前者输出所有匹配到的行，后者只输出匹配摘要
- PATTERN：定义要匹配的模式规则，使用正则表达式类似的语法描述序列规则
- DEFINE：定义模式中每个变量的判断条件，也就是每个步骤需要满足的数据规则
基础使用示例：识别连续上涨的股价
假设我们有一张股价表stock_price，包含股票代码symbol、交易日期trade_date、当日收盘价price三个字段，现在需要找出连续3天及以上上涨的股票走势片段。
首先创建测试表并插入测试数据：
-- 创建测试表
CREATE TABLE stock_price (
symbol VARCHAR2(10),
trade_date DATE,
price NUMBER(10,2)
);
-- 插入测试数据
INSERT INTO stock_price VALUES ('AAPL', TO_DATE('2024-01-01','YYYY-MM-DD'), 100.00);
INSERT INTO stock_price VALUES ('AAPL', TO_DATE('2024-01-02','YYYY-MM-DD'), 102.50);
INSERT INTO stock_price VALUES ('AAPL', TO_DATE('2024-01-03','YYYY-MM-DD'), 105.00);
INSERT INTO stock_price VALUES ('AAPL', TO_DATE('2024-01-04','YYYY-MM-DD'), 103.00);
INSERT INTO stock_price VALUES ('AAPL', TO_DATE('2024-01-05','YYYY-MM-DD'), 106.00);
INSERT INTO stock_price VALUES ('AAPL', TO_DATE('2024-01-06','YYYY-MM-DD'), 108.00);
INSERT INTO stock_price VALUES ('MSFT', TO_DATE('2024-01-01','YYYY-MM-DD'), 200.00);
INSERT INTO stock_price VALUES ('MSFT', TO_DATE('2024-01-02','YYYY-MM-DD'), 198.00);
INSERT INTO stock_price VALUES ('MSFT', TO_DATE('2024-01-03','YYYY-MM-DD'), 201.00);
INSERT INTO stock_price VALUES ('MSFT', TO_DATE('2024-01-04','YYYY-MM-DD'), 204.00);
COMMIT;
接下来使用MATCH_RECOGNIZE编写查询，识别连续上涨的片段：
SELECT *
FROM stock_price
MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY trade_date
MEASURES
FIRST(trade_date) AS start_date,
LAST(trade_date) AS end_date,
COUNT(*) AS consecutive_days
ALL ROWS PER MATCH
PATTERN (START UP+)
DEFINE
UP AS price > LAG(price) OVER (ORDER BY trade_date)
) mr;
上述查询的逻辑说明：
- PARTITION BY symbol：按股票代码分区，不同股票的模式匹配互不影响
- ORDER BY trade_date：按交易日期排序，确保上涨判断的顺序正确
- MEASURES部分定义了输出字段，包括上涨片段的起始日期、结束日期、连续上涨的天数
- ALL ROWS PER MATCH：输出所有匹配到的连续上涨的交易日记录
- PATTERN (START UP+)：模式规则是首先有一个起始行，后面跟着1个或多个UP状态的行，+表示前面的元素出现至少一次
- DEFINE部分定义UP的判断条件：当前行的price大于同一分区内按trade_date排序的前一行的price，也就是当日价格比前一日高
模式规则语法说明
PATTERN子句使用的语法和正则表达式类似，常用的规则符号包括：
| 符号 | 含义 | 示例 |
|---|---|---|
| * | 前面的元素出现0次或多次 | A* 表示A出现0次或多次 |
| + | 前面的元素出现1次或多次 | A+ 表示A出现1次或多次 |
| ? | 前面的元素出现0次或1次 | A? 表示A出现0次或1次 |
| {n} | 前面的元素出现n次 | A{3} 表示A出现3次 |
| {n,} | 前面的元素出现至少n次 | A{2,} 表示A出现至少2次 |
| {n,m} | 前面的元素出现n到m次 | A{1,3} 表示A出现1到3次 |
| | | 或逻辑 | A|B 表示匹配A或者B |
| () | 分组 | (A B) 表示A和B作为一个整体 |
复杂场景示例：识别股价先涨后跌的片段
如果需要识别连续上涨2天及以上，之后连续下跌1天及以上的片段，可以调整模式和定义：
SELECT *
FROM stock_price
MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY trade_date
MEASURES
FIRST(trade_date) AS up_start,
LAST(trade_date) AS down_end,
COUNT(UP_ROW) AS up_days,
COUNT(DOWN_ROW) AS down_days
ALL ROWS PER MATCH
PATTERN (UP{2,} DOWN{1,})
DEFINE
UP AS price > LAG(price) OVER (ORDER BY trade_date),
DOWN AS price < LAG(price) OVER (ORDER BY trade_date)
) mr;
这里模式定义为UP{2,} DOWN{1,}，表示先有至少2个连续上涨的行，之后有至少1个连续下跌的行，DEFINE部分分别定义了UP和DOWN的判断条件，MEASURES中还可以对不同状态的行进行计数，区分上涨和下跌的天数。
SQL标准与Oracle实现的差异
SQL标准中的MATCH_RECOGNIZE定义和Oracle的实现基本兼容，主要差异点包括：
- SQL标准支持
ONE ROW PER MATCH和ALL ROWS PER MATCH两种输出模式，Oracle两者都支持 - SQL标准允许在DEFINE子句中使用更多窗口函数，Oracle目前支持LAG、FIRST_VALUE、LAST_VALUE等常用窗口函数
- 部分数据库对PATTERN子句的正则语法支持略有不同，Oracle的实现符合SQL标准的大部分规范
如果需要在其他支持SQL标准的数据库中使用该特性，只需要调整数据库特定的语法细节，核心的模式定义和逻辑可以复用。
MATCH_RECOGNIZEOracleSQL_standardpattern_matching修改时间：2026-06-17 19:30:33