上一篇我们学习了如何利用排名窗口函数实现产品的分类排名，以及利用取值窗口函数进行销量的环比/同比分析。
本篇我们介绍 SQL:2016 标准新增的一个功能：行模式识别（Row Pattern Recognition）。
行模式识别
行模式识别用于查找多行数据之间的规律。行模式识别通过指定一个模式（正则表达式），找到匹配该模式的一组数据行；并且可以对这些匹配的一组数据进行过滤、分组和聚合操作。行模式识别可以用于分析各种时间序列数据，例如股票行情数据分析、金融欺诈检测或者系统事件日志分析等。
行模式识别用于查找多行数据之间的规律，与查询条件中的 LIKE 模式匹配是不同的概念。
目前只有 Oracle 12c 实现了该功能，因此本篇的示例仅支持 Oracle 数据库。本篇使用的示例表和数据（stock_and_bank.sql）可以从 GitHub 下载。
分析股票走势图
以下是一个纯属虚构的股票曲线图：
其中的数据来源于示例脚本中的股票价格表（stock），表中的字段包括股票代码（scode）、交易日期（tradedate）和收盘价格（price）。以下是该表中的部分数据：
SQL 标准使用 MATCHRECOGNIZE 子句表示行模式识别。利用该功能，我们可以找出股票曲线中的各种变化模式，例如 V 型曲线或 W 型曲线。以下语句用于查找所有的 V 型曲线：
— Oracle 实现_ SELECT FROM stock MATCH_RECOGNIZE ( PARTITION BY scode ORDER BY tradedate MEASURES STRT.tradedate AS start_date, LAST(DOWN.tradedate) AS bottom_date, LAST(UP.tradedate) AS end_date ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER *BY MR.scode, MR.start_date; 复制
其中，MATCH_RECOGNIZE 子句比较复杂，它的执行过程如下：
- PARTITION BY scode 按照股票代码进行分区，可以同时分析多只股票的数据；如果省略，所有的数据作为一个整体进行分析，这一点与窗口函数类似；
- ORDER BY tradedate 按照交易日期进行排序，用于分析股票价格随着时间变化的规律；
- MEASURES 定义了三个输出值，分别代表 V 型曲线的起始日期、最低点日期以及结束日期；其中的 STRT、DOWN 和 UP 都是 DEFINE 选项中定义的变量；LAST(DOWN.tradedate) 表示下降曲线中的最后一个日期，也就是最低点日期；LAST(UP.tradedate) 表示上升曲线中的最后一个日期，也就是结束日期；
- ONE ROW PER MATCH 表示每次匹配只输出一个汇总结果；每个 V 型曲线输出一条记录；如果使用 ALL ROWS PER MATCH 选项，每个 V 型曲线都会输出构成曲线的所有节点，下文给出了相应的示例；
- AFTER MATCH SKIP TO LAST UP 表示找到匹配的数据后，从当前 V 型曲线的最后一个上升点（UP）重新开始下一次查找；
- PATTERN (STRT DOWN+ UP+) 定义了需要查找的模式，使用正则表达式语法表示。从起点（STRT）开始，先下降一次或多次（DOWN+），再上升一次或多次（UP+），也就是 V 型曲线；
- DEFINE 用于定义模式变量需要满足的条件。STRT 变量没有指定任何条件，意味着所有行都可以作为 V 型曲线的开始；DOWN 变量要求它的价格比上一行的价格更小，PREV 函数表示上一行；UP 变量要求它的价格比上一行的价格更大。
该语句执行的结果如下：
该查询返回了 6 条记录，分别对应了下图中的 6 个 V 型曲线：
从以上的分析可以看出，MATCHRECOGNIZE 支持许多选项，尤其是通过 DEFINE 变量定义和 PATTERN 正则表达式模式可以实现各种复杂的趋势分析。接下来我们修改一下以上示例中的某些选项：
— Oracle 实现_ SELECT FROM stock MATCH_RECOGNIZE ( PARTITION BY scode ORDER BY tradedate MEASURES STRT.tradedate AS start_date, FINAL LAST(DOWN.tradedate) AS bottom_date, FINAL LAST(UP.tradedate) AS end_date, MATCH_NUMBER() AS match_num, CLASSIFIER() AS var_match ALL ROWS PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP{2,}) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) 1.1 ) MR ORDER BY MR.scode, MR.tradedate; 复制
其中，FINAL LAST 函数输出每个 V 型曲线中最后一个下降的日期和上升日期；MATCH_NUMBER 表示第几次匹配；CLASSIFIER 表示匹配哪个变量；ALL ROWS PER MATCH 表示输出构成 V 型曲线的所有数据行；PATTERN 中指定曲线下降后必须出现两次以上（UP{2,}）的连续上涨；DEFINE 中要求每次上涨幅度必须大于 10%。该语句执行的结果如下：
该查询总共找到 2 个符合条件的结果（MATCH_NUMBER），并且输出了构成 V 型曲线的所有数据。其中第一次匹配有 3 次连续 UP；第二次匹配有 2 次连续 UP；每次上涨都超过了 10%。下图显示了这两次匹配的情况和对应的数据点：
只要能通过变量定义和正则表达式模式进行描述，行模式识别可以实现任意复杂的数据流处理。
检测可疑的银行转账
对于银行等金融机构而言，需要根据国家监管要求监控异常的货币支付交易，防范利用银行支付结算进行洗钱等违法犯罪活动。其中关键的问题是如何识别可疑的交易行为，以下是一些简单的判断条件：
- 短期内资金分散转入、集中转出或集中转入、分散转出；
- 短期内累计 100 万元以上现金收付；
- 相同收付款人之间短期内频繁发生资金收付。
其中，短期通常是指 10 个营业日。
作为演示，我们将会采用简化的监控模式。在本篇的示例脚本中，还有一个银行交易日志表（banklog），包括日志编号（log_id）、交易时间戳（ts）、银行账户（from_user）、交易金额（amount）、交易类型（type）以及目标账户（to_user）。该表中的数据如下：
以下语句用于检测 5 天之内累积转账超过一百万的账号：
— Oracle 实现_ SELECT from_user, first_t, last_t, total_amount FROM (SELECT FROM bank_log WHERE type = ‘转账’) MATCH_RECOGNIZE (PARTITION BY from_user ORDER BY ts MEASURES FIRST(x.ts) first_t, y.ts last_t, SUM(x.amount) + y.amount total_amount PATTERN ( x{1,}? y) DEFINE x AS (type = ‘转账’), y AS (type = ‘转账’ AND y.ts - FIRST(x.ts) <= 5 AND *SUM(x.amount) + y.amount >= 1000000) ); 复制
其中：
- FROM 中的子查询用于获取转账交易；
- PARTITION BY 用于分别分析多个不同的账号；
- ORDER BY 按照交易时间进行排序；
- MEASURES 输出了匹配模式的数据中的第一次交易时间（FIRST(x.ts)）、最后一次交易时间（y.ts）以及总共交易的金额；
- PATTERN 表示 x 变量出现一次以上并且出现 y 变量，？表示懒惰匹配（只要累积到达一百万立即停止本次匹配，开始下一次匹配）；
- DEFINE 定义 x 变量为转账操作，y 变量为转账并且与最早的 x 相差不超过 5天，同时 x 的累积金额加上 y 的金额大于等于 1000000。
该查询的结果如下：
结果表明，该账号从 2019 年 01 月 05 日 13 点到 2019 年 01 月 10 日 7 点累积转账 1050000，是一个可疑的频繁大额转账。
除了 SUM 函数之外，COUNT、AVG、MAX 以及 MIN 函数也可出现在 DEFINE 和 MEASURES 选项中。
我们通过两种常见的应用场景介绍了 SQL 行模式识别（MATCH_RECOGNIZE）的简单用法。作为最新的 SQL 功能，虽然暂时只有 Oracle 12c 以上版本提供了实现，相信其他数据库产品很快就会跟进。如果想要了解更多的语法细节和使用案例，可以参考 Oracle 官方文档。
小结
SQL 行模式识别（MATCH_RECOGNIZE）综合了 WHERE、GROUP BY、HAVING 以及窗口函数的 OVER 子句的功能，能够用于检测数据流中的复杂模式，具有处理复杂事件（CEP）的强大功能。常见的应用包括侦测异常的安全行为、发现金融交易行为模式、欺诈检测和传感器数据分析等。
思考题 1：如何查找股票曲线图中的 W 型曲线呢？
思考题 2：如何监控频繁转账的账号，例如 10 分钟之内转账 5 次以上？
若有收获，就点个赞吧
0 人点赞
