问题的回答。
问题的历史。
在 SQL:2016 标准之前，识别有序数据集中的多行顺序模式需要复杂的自连接、基于游标的过程逻辑或模拟有限状态机的递归 CTE。这些方法会遭遇组合爆炸、性能差和维护噩梦的问题。MATCH_RECOGNIZE 子句的引入提供了一种基于正则表达式的声明性、数学严谨的语法，用于行模式识别，使复杂事件处理可以直接在关系引擎中进行。
问题。
检测特定的可变长度序列——例如 W 形价格形态——需要将每一行与多个前驱和后继进行比较，同时在整个序列中保持上下文状态。标准窗口函数只能引用固定的偏移量（例如 LAG 1、LEAD 1），使其无法处理腿的持续时间变化的模式。理论上，递归 CTE 可以跟踪状态转换，但在处理多步具有严格顺序约束的模式时变得计算成本高且语法冗长。
解决方案。
MATCH_RECOGNIZE 允许使用布尔条件定义模式变量，通过正则表达式语法（例如 A B+ C+ D+ E+）指定目标模式，并计算匹配行的聚合度量。它原生支持分区、排序和导航功能（PREV、NEXT、FIRST、LAST）。
SELECT * FROM stock_ticks MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tick_time MEASURES STRT.price AS start_price, FINAL LAST(DOWN1.price) AS first_trough, FINAL LAST(UP1.price) AS middle_peak, FINAL LAST(DOWN2.price) AS second_trough, FINAL LAST(UP2.price) AS end_price, MATCH_NUMBER() AS pattern_id ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP2 PATTERN (STRT DOWN1+ UP1+ DOWN2+ UP2+) DEFINE DOWN1 AS DOWN1.price < PREV(DOWN1.price), UP1 AS UP1.price > PREV(UP1.price), DOWN2 AS DOWN2.price < PREV(DOWN2.price) AND DOWN2.price < FIRST(UP1.price), -- 必须低于中间峰值 UP2 AS UP2.price > PREV(UP2.price) ) AS pattern_matches;
生活中的情况
背景。
一家量化交易公司需要在高频外汇数据（逐笔数据）中检测 W 形双底模式，以自动化长期头寸的入场点。该模式需要两个不同的低谷，由一个峰值分开，每条腿代表至少 0.5% 的价格变动。
问题。
数据集中包含 1000 万行，每天跨 50 种货币对。基于 Python 的检测在每小时传输数千兆字节数据时引入了网络延迟和内存限制。使用多个 LAG()/LEAD() 自连接的标准 SQL 方法在尝试关联 W 模式的四条腿时造成了笛卡尔积，导致查询在 10 分钟后超时。
解决方案 1：客户端 Python 处理。
团队最初使用 pandas 和自定义循环逻辑检测峰值和低谷。优点： 丰富的分析库，易于单元测试。缺点： 数据传输瓶颈（数小时的延迟），处理完整市场历史时应用服务器内存耗尽，以及无法实时反应。
解决方案 2：递归 CTE 状态机。
他们尝试了一个递归 CTE 跟踪五个状态（0=寻求开始，1=第一次下降，2=第一次上升，3=第二次下降，4=第二次上升）。优点： 纯 SQL，逻辑严密。缺点： 数据库引擎中的单线程执行，深度递归时的指数放慢，以及 300 多行不易理解的 SQL，在波动序列上容易出现栈溢出错误。
解决方案 3：MATCH_RECOGNIZE 实现。
团队实现了上述 SQL:2016 模式匹配查询。优点： 原生引擎优化（矢量化执行），精简的 25 行查询完美匹配数学模式定义，自动处理通过量词（+）实现的变长腿，以及高效跳过以防止冗余的重叠匹配。缺点： 需要将数据库迁移到支持 SQL:2016 特性的 Oracle 19c，并为不熟悉 SQL 中正则表达式语法的开发人员提供初步培训。
选择的解决方案和结果。
由于在历史回测中的亚秒性能，因此选择了解决方案 3。AFTER MATCH SKIP TO LAST UP2 子句确保一旦 W 模式完成，扫描将重新开始于模式的末尾，以避免重叠检测。系统成功识别了 99.8% 的人工验证 W 模式，将检测延迟从 45 分钟（Python）减少到 800 毫秒，从而实现实时算法交易。
候选者常常遗漏的内容
AFTER MATCH SKIP 子句如何确定匹配后的恢复点，以及 SKIP TO NEXT ROW 与 SKIP PAST LAST ROW 对重叠模式的重要性？
AFTER MATCH SKIP 决定了模式匹配器继续扫描的地点。SKIP PAST LAST ROW（默认）在当前匹配的最终行之后恢复，防止任何行参与多个匹配——适合于独特事件检测。相反，SKIP TO NEXT ROW 在匹配的起始行的下一行立即恢复，允许重叠匹配。这在金融时间序列中至关重要，因为单一的低谷可能合法地形成两个连续 W 模式的底部（重叠窗口）。候选人常常默认使用标准跳过，无法避免错误筛选掉有效的重叠信号，降低检测灵敏度。
MEASURES 子句中 RUNNING 和 FINAL 语义之间的区别是什么，这如何影响可变长度模式中的聚合计算？
RUNNING 在构建匹配时对每一行评估表达式（例如，在下降腿期间计算移动平均值）。FINAL 仅在完整匹配的最后一行上评估表达式，使用所有模式变量的最终绑定值（例如，计算从模式开始到结束的总百分比变化）。候选人在计算模式范围内的指标（如 MAX(leg_price) - MIN(leg_price)）时常常遗漏 FINAL 关键字，导致中间值从不完整的匹配返回，这导致错误的交易信号计算。
您如何处理空匹配并确保未匹配的行出现在输出中以便于调试？
默认情况下，MATCH_RECOGNIZE 过滤掉未参与匹配的行。为了包括未匹配的行（对于审计为什么某些序列未满足模式标准至关重要），必须指定 ALL ROWS PER MATCH 结合 SHOW EMPTY MATCHES。在这种模式下，每一输入行都会生成输出，模式度量对未匹配行返回 NULL。此外，MATCH_NUMBER() 对未匹配的行返回 NULL。候选人常常在“缺失数据”调试方面挣扎，不知道严格的 DEFINE 条件过滤了有效行，并且他们未能利用 SHOW EMPTY MATCHES 来诊断导致模式拒绝的特定布尔条件（例如，第二个低谷未低于第一个）。