SQL 行模式匹配：用 MATCH_RECOGNIZE 实现"行级正则"

当行与行之间也需要"正则"

设想你在一家安全团队工作，手里有一张记录登录尝试的数据表：每一行是一次登录事件，包含用户 ID、时间戳和结果（成功或失败）。现在有个典型需求——找出"连续多次失败后紧跟一次成功"的行为序列，这往往是暴力破解得手的信号。

用传统 SQL 处理这类问题会让人头疼。你需要窗口函数、自连接、子查询层层嵌套，才能勉强描述出"若干个失败事件之后跟着一个成功事件"这种跨行的顺序关系。代码越写越长，逻辑越来越难读，维护起来更是灾难。

问题的本质在于：SQL 天生擅长处理集合，却不擅长表达"行与行之间的顺序模式"。而这恰恰是正则表达式最拿手的领域——只不过正则匹配的是字符序列，我们真正想要的，是能匹配"行序列"的工具。

MATCH_RECOGNIZE：把正则思维带进行序列

MATCH_RECOGNIZE 正是为解决这一痛点而生。它是 SQL 标准中定义的行模式识别子句，思路可以概括为一句话：像写正则表达式一样，去匹配数据表中连续的行。

它的核心组成部分包括几个关键子句：

PARTITION BY 与 ORDER BY

PARTITION BY 决定按什么维度分组——比如按用户 ID 分区，这样每个用户的登录事件序列会被独立分析，不会互相干扰。ORDER BY 则定义行的排列顺序，模式匹配严重依赖顺序，通常按时间戳排序，确保事件按发生的先后被逐行检查。

DEFINE 与 PATTERN

DEFINE 用来给"行的状态"命名并定义判定条件。例如你可以定义 FAIL AS result = 'failure'、SUCCESS AS result = 'success'，把每一行归类为某种模式变量。

PATTERN 则是整个功能的灵魂所在——它用类似正则的语法描述这些状态变量应当以怎样的顺序出现。想表达"一次或多次失败后跟一次成功"，写成 PATTERN (FAIL+ SUCCESS) 即可。这里的 + 号与正则中的含义完全一致，代表"一次或多次"。

MEASURES 与 输出模式

MEASURES 子句负责从匹配到的行序列中提取你关心的信息，比如失败次数、首次失败时间、最终成功时间等。而 ONE ROW PER MATCH 和 ALL ROWS PER MATCH 则控制输出粒度：前者每个匹配返回一行汇总，后者返回匹配区间内的每一行明细。

MEASURES 中可以使用 CLASSIFIER() 函数获取每一行被归入的模式变量名称，以及 MATCH_NUMBER() 函数为同一分区内的多个匹配编号，这在分析同一用户是否多次触发同一行为模式时非常有用。FIRST() 和 LAST() 是 MEASURES 中常用的辅助函数，分别取某个模式变量首次和末次匹配行的字段值。此外，PATTERN 支持的量词与正则基本对齐：*（零次或多次）、+（一次或多次）、?（零次或一次），以及 {n}、{n,m} 等精确次数控制，使得对"恰好失败5次"或"失败2到10次"这类精确频次的模式描述同样简洁。

一个可读性的飞跃

把上述思路串起来，检测"暴力破解得手"的查询大致长这样：

SELECT *
FROM login_attempts
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    FIRST(FAIL.event_time) AS first_fail,
    SUCCESS.event_time AS breach_time,
    COUNT(FAIL.*) AS fail_count
  ONE ROW PER MATCH
  PATTERN (FAIL+ SUCCESS)
  DEFINE
    FAIL AS result = 'failure',
    SUCCESS AS result = 'success'
) AS mr;

对比传统写法里满屏的自连接和窗口函数，这段查询几乎可以"读作自然语言"：按用户分区、按时间排序，找出"若干次失败后接一次成功"的模式，并报告首次失败时间、得手时间和失败次数。意图一目了然，这正是声明式 SQL 应有的样子。

它适合哪些场景

行模式识别的价值远不止于安全审计。任何涉及"事件顺序"的分析都能从中受益：

• 金融风控：识别账户的异常交易序列，例如短时间内多笔小额试探后紧跟一笔大额转账。
• 用户行为分析：追踪转化漏斗中"浏览→加购→下单"的完整路径，或识别流失前的行为特征。
• IoT 与监控：从传感器时序数据中检测"读数持续上升后突然骤降"这类异常波形。
• 股价分析：识别经典的技术形态，比如 V 型反转、连续上涨等价格走势。

这些场景的共性在于：关注点不是单行数据，而是多行之间随时间展开的模式。这恰恰是普通聚合与窗口函数难以优雅表达的盲区。

现实中的支持情况

需要留意的是，MATCH_RECOGNIZE 虽然是 SQL 标准的一部分，但各数据库和数据处理引擎的支持程度参差不齐。Oracle、Snowflake、Trino/Presto、Apache Flink 等已提供支持，而 PostgreSQL、MySQL 等常见开源数据库尚未原生支持这一语法。在动手前，务必确认你所使用的引擎是否兼容。

对于数据工程师和分析师而言，掌握 MATCH_RECOGNIZE 意味着多了一件应对"顺序模式"问题的利器。它把原本需要大量过程式代码才能表达的逻辑，压缩成一段清晰的声明式描述——正如把繁琐的字符串遍历交给正则表达式一样，让复杂的行序列匹配变得直观可维护。

MATCH_RECOGNIZE 最早于 2016 年被纳入 SQL:2016 标准（ISO/IEC 9075-2:2016），是该版本标准中最重要的新增特性之一。Oracle Database 12c Release 2 是最早落地实现的主流数据库，Snowflake 在 2021 年前后跟进支持，流处理引擎 Apache Flink 因天然面向事件流场景，也在较早阶段提供了支持。值得注意的是，Flink 中的实现与批处理数据库存在一些差异——例如对无界流的处理机制、超时语义的定义等，在跨引擎迁移查询时需要特别核对文档。对于暂不支持该语法的 PostgreSQL 用户，社区中存在一些基于递归 CTE 或窗口函数的变通方案，但代码复杂度会显著上升，且性能表现不一。