一文带你了解不一样的SQL，惊喜多多( 三 ) _SQL

总之，关系数据库对于 JSON 数据类型的支持可以方便我们将 SQL 的强大功能与 NoSQL 的灵活性相结合；当我们需要为应用增加文档数据支持的时候，除了使用专门的 NoSQL 数据库之外，也可以考虑直接在现有的数据库中使用 JSON 数据类型。
2016 年 SQL 标准增加的另一个重要的功能就是行模式识别（Row Pattern Recognition）。
二.SQL 与复杂事件处理
SQL 行模式识别使用 MATCH_RECOGNIZE 子句表示，通过指定一个模式（正则表达式）查找多行数据之间的规律，并且可以对这些匹配的数据进行过滤、分组和聚合操作。行模式识别可以用于分析各种时间序列数据，例如股票行情数据分析、金融欺诈检测或者系统事件日志分析等。

文章插图

目前只有 Oracle 12c 实现了该功能，我们可以使用以下语句找出股票曲线中的所有 V 型曲线：

-- Oracle 12c 实现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 变量要求它的价格比上一行的价格更大。

该语句返回的结果如下：

SCODE|START_DATE         |BOTTOM_DATE        |END_DATE           |-----|-------------------|-------------------|-------------------|S001 |2019-01-01 00:00:00|2019-01-05 00:00:00|2019-01-06 00:00:00|S001 |2019-01-06 00:00:00|2019-01-07 00:00:00|2019-01-08 00:00:00|S001 |2019-01-08 00:00:00|2019-01-12 00:00:00|2019-01-13 00:00:00|S001 |2019-01-18 00:00:00|2019-01-20 00:00:00|2019-01-21 00:00:00|S001 |2019-01-21 00:00:00|2019-01-22 00:00:00|2019-01-27 00:00:00|S001 |2019-01-27 00:00:00|2019-01-28 00:00:00|2019-01-30 00:00:00|

查询返回了 6 条记录，分别对应了上图中的 6 个 V 型曲线。MATCH_RECOGNIZE 支持许多选项，尤其是通过 DEFINE 变量定义和 PATTERN 正则表达式模式可以实现各种复杂的趋势分析。