行模式识别的功能分析查找多行数据的拐点和规律
本篇我们介绍 SQL:2016 标准新增的一个功能:行模式识别(Row Pattern Recognition)。
- 行模式识别
行模式识别用于查找多行数据之间的规律。行模式识别通过指定一个模式(正则表达式),找到匹配该模式的一组数据行;并且可以对这些匹配的一组数据进行过滤、分组和聚合操作。
行模式识别可以用于分析各种时间序列数据,例如股票行情数据分析、金融欺诈检测或者系统事件日志分析等。
行模式识别用于查找多行数据之间的规律,与查询条件中的 LIKE 模式匹配是不同的概念。
目前只有 Oracle 12c 实现了该功能,因此本篇的示例仅支持 Oracle 数据库。本篇使用的示例表和数据(stockandbank.sql)可以从 GitHub 下载。
分析股票走势图
以下是一个纯属虚构的股票曲线图:
其中的数据来源于示例脚本中的股票价格表(stock),表中的字段包括股票代码(scode)、交易日期(tradedate)和收盘价格(price)。以下是该表中的部分数据:
SQL 标准使用 MATCH_RECOGNIZE 子句表示行模式识别。利用该功能,我们可以找出股票曲线中的各种变化模式,例如 V 型曲线或 W 型曲线。以下语句用于查找所有的 V 型曲线:
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