Oracle 12c 的新功能：模式匹配查询
原文：
https://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8956
模式匹配SQL
在一系列的数据行中辨识出某种模式是一种广泛需求的功能，但在以前的SQL中是不可能的。有许多解决方法，但这些都很难写，很难理解，而且执行效率低下。在Oracle数据库中，从12c开始，你可以使用原生SQL中执行效率很高的 MATCH_RECOGNIZE 子句来实现这种功能。本章讨论如何做到这一点，并包括以下几个部分：
．模式匹配的概述
．模式匹配中的基本主题
．模式匹配详细信息
．模式匹配中的高级主题
．模式匹配中的规则与限制
．模式匹配中的例子
模式匹配的概述
SQL中的模式匹配是用MATCH_RECOGNIZE子句执行的。MATCH_RECOGNIZE子句使得你能够执行如下任务：
．使用PARTITION BY 和 ORDER BY 子句对MATCH_RECOGNIZE子句中的数据进行逻辑分区和排序。
．使用MATCH_RECOGNIZE子句中的PATTERN子句来定义要查找的数据行的模式。这些模式使用规则表达式语法，这是一种应用于你所定义的模式变量的强劲而高表现力的功能。
．在DEFINE子句中指定将一行数据映射到一个行模式变量所需的逻辑条件。
．在MEASURES子句中定义度量，这是一些可在SQL查询的其他部分所使用的表达式。
模式匹配可以让你识别价格模式，例如图 20-1 中所示的V-形和W-形，还可以进行多种类型的计算。例如，你的计算可能包括观测的次数或者上行或下行的平均值。
这一节包括以下主题：
．为什么使用模式匹配？
．在模式匹配中数据是如何被处理的？
．模式匹配的特殊功能
为什么使用模式匹配？
对于许多类别的工作而言，从不同的数据行中辨识查找模式的能力是很重要的。例子包括受一系列事件驱动的各种业务流程，例如安全应用必须侦测出异常的行为，在金融应用中你会查找价格、交易量和其他行为的模式。其他常见的用途是欺诈检测应用和传感器数据的分析。用来描述这个一般领域的术语是复杂事件处理，而模式匹配是这种活动的一个强大的援助。
现在来考虑例子20-1的查询。它使用在图20-1所示的股票价格，你可以用下面的CREATE和INSERT语句加载到你的数据库。该查询找出所有股票价格跌至底部然后再上涨的情形。这通常被称为V形。在研究这个查询之前，先看看输出。只有三行数据，因为代码被写成仅为每个匹配报告一行，而总共有三次匹配被发现。该MATCH_RECOGNIZE子句允许你在每次匹配展示一行，或者每次匹配展示所有行之间进行选择。在这个例子中，我们使用的是更为简短的每次匹配输出一行。
例子 20-1 模式匹配: 简单的V形，每次匹配输出一行
CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-01', 12);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-02', 17);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-03', 19);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-04', 21);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-05', 25);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-06', 12);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-07', 15);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-08', 20);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-09', 24);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-10', 25);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-11', 19);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-12', 15);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-13', 25);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-14', 25);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-15', 14);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-16', 12);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-17', 14);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-18', 24);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-19', 23);
INSERT INTO Ticker VALUES('ACME', DATE '2011-04-20', 22);
SELECT *
FROM Ticker MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
LAST(DOWN.tstamp) AS bottom_tstamp,
LAST(UP.tstamp) AS end_tstamp
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.symbol, MR.start_tstamp;
SYMBOL START_TST BOTTOM_TS END_TSTAM
---------- --------- --------- ---------
ACME 05-APR-11 06-APR-11 10-APR-11
ACME 10-APR-11 12-APR-11 13-APR-11
ACME 14-APR-11 16-APR-11 18-APR-11
这个查询做了什么？下面解释了 MATCH_RECOGNIZE子句中的每一行：
．PARTITION BY 将Ticker表数据划分成逻辑分组，每组包含一种股票代号。
．ORDER BY 将每个逻辑分组内的数据按照tstamp排序。
．MEASURES 定义了三个度量：V形开始的时间戳(start_tstamp)，V形底部的时间戳(bottom_tstamp)，以及V形结束的时间戳(end_tstamp)。bottom_tstamp 和 end_tstamp 度量使用了LAST()函数来确保读取到的值是每个匹配模式中的最后一个时间戳的值。
．ONE ROW PER MATCH 的意思是对于每个找到的模式匹配，只会有一行输出。
．AFTER MATCH SKIP TO LAST UP 的意思是每当你找到一个匹配，你就在UP模式变量的最后一行重新开始你的搜索。一个模式变量是一个在MATCH_RECOGNIZE中使用的变量，在DEFINE子句定义。
．PATTERN (STRT DOWN+ UP+) 说的是你在搜索的模式有三个模式变量：STRT, DOWN, 以及 UP。DOWN 和 UP之后的加号(+)意思是它们中的每一个都至少有一行被映射。这个模式定义一个规则表达式，这是一种表现力很强的搜索方式。
．DEFINE 给了我们当一个行被映射到你的行模式变量STRT, DOWN, 和 UP时应该满足的条件。因为没有为STRT指定条件，任何一行都可以被映射为STRT。为什么一个模式变量会没有条件？你可以用它来作为测试匹配的起点。DOWN和UP都利用了PREV()函数，这使得它们能够把当前行的价格和前一行的价格进行比较。当价格比前一行更低时DOWN被匹配，所以它定义了V形的下行侧（左腿）。如果价格比前一行更高则被映射到UP。
下面两个图可以帮助你更好地理解例子20-1返回的结果。图20-2显示了映射到特定模式变量（在PATTERN子句中指定）的日期。在模式变量到日期的映射可见之后，MEASURES子句就用该信息来计算度量值。度量值的结果被显示在图20-3中。
图 20-2 股票图演示了哪些日期被映射到哪些模式变量
图20-2标记出映射到模式变量的日期。映射是基于PATTERN子句中指定的模式和DEFINE子句中指定的逻辑条件。垂直的细线显示了被找到的三个匹配模式的边界。在每个匹配中，第一个日期映射到STRT模式变量（标记为Start），紧跟着是一个或多个被映射到DOWN模式变量的日期，最后是一个或多个被映射到UP模式变量的日期。
因为你在查询中指定了AFTER MATCH SKIP TO LAST UP，两个相邻的匹配可以共享一行。这意味着一个单独的日期可以被映射到两个变量。例如，四月十号同时被映射到UP和STRT两个变量：四月十号是第一个匹配的终点，也是第二个匹配的起点。
图20-3 股票图展示了度量值相应的日期
在图20-3中，标记仅仅是查询的MEASURES子句中所定义的度量：START (查询中的start_tstamp), BOTTOM (查询中的bottom_tstamp), 以及 END (查询中的end_tstamp)。如同图20-2，垂直的细线显示了被找到的三个匹配模式的边界。每个匹配有一个起始日期，一个底部日期，以及一个终止日期。正如图20-2，日期四月十号可以在两个匹配中找到：它是第一个匹配的终止度量和第二个匹配的起始度量。图20-3 的标记日期显示了哪些日期对应着度量的定义，这些度量是基于图20-2中所显示的模式变量。
注意，图20-3中标记的日期对应着早些时候例子中输出的九个日期。输出第一行的日期显示在第一次匹配中，输出的第二行的日期显示在第二次匹配中，输出的第三行的日期显示在第三次匹配中。
模式匹配中的数据是如何被处理的
MATCH_RECOGNIZE子句执行了这些步骤：
1.行模式的输入表根据PARTITION BY子句被分区。每个分区包含输入表的数据集，它们在分区列上有相同的值。
2.每个行模式分区根据ORDER BY子句被排序。
3.每个排序后的分区被搜索PATTERN的匹配。
4.模式匹配通过在最早的行查找匹配来操作，在行模式匹配区间中的考虑顺序是按照ORDER BY子句所指定的方式来排序的。
在一系列行中的模式匹配是一种渐进的处理，一行接一行地检查是否能和模式相符。在这种渐进处理的模型中，在整个模式被识别出来之前的任何步骤，你都只有部分匹配，而且你不知道未来加入的是什么行，也不知道那些未来的行会被映射到什么变量。
如果在最早的行没有找到匹配，搜索就会转移到分区中的下一行，查看是否从那行开始可以找到一个匹配。
5.在匹配找到之后，行模式匹配就会计算行模式中的度量列，这些就是MEASURES子句中定义的表达式。
6.如果使用的是第一个例子中所示的ONE ROW PER MATCH, 模式匹配就会为每个找到的匹配生成一行。如果你用的是ALL ROWS PER MATCH，每一个匹配到的行都会包括在模式匹配的输出中。
7.AFTER MATCH SKIP子句决定了在一个非空匹配被找到之后，行模式匹配要在区间中的何处重新开始。在前一例子中，行模式匹配重启于找到的匹配的最后一行(AFTER MATCH SKIP TO LAST UP)
模式匹配的特殊功能
这些功能是：
．规则表达式是系统用来搜索数据模式的一种稳健和由来已久的方式。Perl语言的规则达式功能被采纳为模式匹配规则的设计目标，而Oracle数据库12c的第1版，为模式匹配实现了这些规则的一个子集。
．Oracle的规则和典型的规则表达式之间的不同之处在于，行模式变量是按布尔条件来定义，而不是字符或字符的集合。
．虽然模式匹配使用规则表达式的记法来表达模式，实际上它的功能更加丰富，因为模式变量可以被定义为依赖于前面的行被映射到行模式变量的方式。DEFINE子句使得模式变量可以建立在其他模式变量之上。
．在行模式和度量的定义中可以使用子查询。
模式匹配中的基本主题
本章节将会讨论：
．模式匹配的基本例子
．模式匹配中的任务以及关键字
．模式匹配的语法
模式匹配的基本例子
本章节包含了一些模式匹配的基本例子。
例子20-2的第一行是为了改善输出格式，如果你用的是SQL*Plus的话。
例子20-2 简单的V形模式匹配，输出所有匹配行
column var_match format a4
SELECT *
FROM Ticker MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
FINAL LAST(DOWN.tstamp) AS bottom_tstamp,
FINAL LAST(UP.tstamp) AS end_tstamp,
MATCH_NUMBER() AS match_num,
CLASSIFIER() AS var_match
ALL ROWS 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.symbol, MR.match_num, MR.tstamp;
SYMBOL TSTAMP START_TST BOTTOM_TS END_TSTAM MATCH_NUM VAR_ PRICE
---------- --------- --------- --------- --------- ---------- ---- ----------
ACME 05-APR-11 05-APR-11 06-APR-11 10-APR-11 1 STRT 25
ACME 06-APR-11 05-APR-11 06-APR-11 10-APR-11 1 DOWN 12
ACME 07-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 15
ACME 08-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 20
ACME 09-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 24
ACME 10-APR-11 05-APR-11 06-APR-11 10-APR-11 1 UP 25
ACME 10-APR-11 10-APR-11 12-APR-11 13-APR-11 2 STRT 25
ACME 11-APR-11 10-APR-11 12-APR-11 13-APR-11 2 DOWN 19
ACME 12-APR-11 10-APR-11 12-APR-11 13-APR-11 2 DOWN 15
ACME 13-APR-11 10-APR-11 12-APR-11 13-APR-11 2 UP 25
ACME 14-APR-11 14-APR-11 16-APR-11 18-APR-11 3 STRT 25
ACME 15-APR-11 14-APR-11 16-APR-11 18-APR-11 3 DOWN 14
ACME 16-APR-11 14-APR-11 16-APR-11 18-APR-11 3 DOWN 12
ACME 17-APR-11 14-APR-11 16-APR-11 18-APR-11 3 UP 14
ACME 18-APR-11 14-APR-11 16-APR-11 18-APR-11 3 UP 24
15 rows selected.
这个查询做了什么？它类似于例子 20-1 中的查询，只是MEASURES子句中的项目不同，以及修改为ALL ROWS PER MATCH, 以及查询结尾的ORDER BY的修改。在MEASURES子句中新增了这些：
．MATCH_NUMBER() AS match_num
因为这个例子为每次匹配给出了多行，你必须知道哪些行是哪次匹配的成员。MATCH_NUMBER为一个特定的匹配中的每一行赋予相同的数字。例如，在一个行模式匹配的第一次匹配中的所有行，其match_num的值都被赋值为1。注意在每一个行模式的分区中，这个匹配号又会从1开始。
．CLASSIFIER() AS var_match
为了得知哪一行映射到哪个变量，请使用CLASSIFIER函数。在这个例子中，有些行会映射到STRT变量，一些行映射到DOWN变量，其他的映射到UP变量。
．FINAL LAST()
通过在bottom_tstamp指定FINAL并使用LAST()函数，每次匹配中的每一行都显示同样的日期，即它的V形的底部。类似地，在end_tstamp度量应用FINAL LAST()使得每次匹配中的每一行显示相同的日期，即V形的结束。如果没加这个语法，显示的日期就是每行的滚动值。
其他两行中的修改：
．ALL ROWS PER MATCH —— 例子20-1 用ONE ROW PER MATCH为每次匹配给出了仅仅一行的汇总，这个例子则要求显示每次匹配中的每一行。
．最后一行的 ORDER BY —— 这是为了利用MATCH_NUM，因此每次匹配中的所有行都会在一起，并且按先后排序。
注意四月十号的行出现了两次，因为它在两个模式匹配中：它是第一次匹配的最后一天，也是第二次匹配的第一天。
例子 20-3 在一个变量上聚合的模式匹配
例子 20-3 突出了聚合函数在模式匹配查询中的使用。
SELECT *
FROM Ticker MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES
MATCH_NUMBER() AS match_num,
CLASSIFIER() AS var_match,
FINAL COUNT(UP.tstamp) AS up_days,
FINAL COUNT(tstamp) AS total_days,
RUNNING COUNT(tstamp) AS cnt_days,
price - STRT.price AS price_dif
ALL ROWS 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.symbol, MR.match_num, MR.tstamp;
SYMBOL TSTAMP MATCH_NUM VAR_ UP_DAYS TOTAL_DAYS CNT_DAYS PRICE_DIF PRICE
------ --------- --------- ---- ------- ---------- -------- --------- -----
ACME 05-APR-11 1 STRT 4 6 1 0 25
ACME 06-APR-11 1 DOWN 4 6 2 -13 12
ACME 07-APR-11 1 UP 4 6 3 -10 15
ACME 08-APR-11 1 UP 4 6 4 -5 20
ACME 09-APR-11 1 UP 4 6 5 -1 24
ACME 10-APR-11 1 UP 4 6 6 0 25
ACME 10-APR-11 2 STRT 1 4 1 0 25
ACME 11-APR-11 2 DOWN 1 4 2 -6 19
ACME 12-APR-11 2 DOWN 1 4 3 -10 15
ACME 13-APR-11 2 UP 1 4 4 0 25
ACME 14-APR-11 3 STRT 2 5 1 0 25
ACME 15-APR-11 3 DOWN 2 5 2 -11 14
ACME 16-APR-11 3 DOWN 2 5 3 -13 12
ACME 17-APR-11 3 UP 2 5 4 -11 14
ACME 18-APR-11 3 UP 2 5 5 -1 24
15 rows selected.
这个查询做了什么？它构建于例子20-2，增加了三个使用聚合函数COUNT()的度量。它还增加了一个度量以显示一个表达式中如何使用一个修饰列与未修饰列。
up_days度量（带有FINAL COUNT）显示了每个匹配中映射到UP模式变量的天数。你可以通过对图20-2中每次匹配的UP标签进行计数来检验。
total_days度量（同样带有FINAL COUNT）引入了未修饰列的使用。因为这个度量指定了FINAL count(tstamp)，没有模式变量用于修饰tstamp列，它返回的是一个匹配中包含的所有行的计数。
cnt_days度量引入了RUNNING关键字。这个度量给出了一个滚动计数，它有助于区分一个匹配中的数据行。注意它同样没有用模式变量来修饰tstamp列，所以它适用于匹配中的所有行。在这个例子中你不需要明确使用RUNNING关键字，因为它是缺省的。更多细节请参见“Running 与 Final的语义和关键字对照”小节。
price_dif度量向我们显示了每天的股票价格与匹配的第一天价格相比的变动。在表达式"price - STRT.price),"中你看到了未修饰列"price,"和修饰列"STRT.price"被一起使用的例子。
例子20-4 演示了一个W形
例子20-4 W形匹配
SELECT *
FROM Ticker MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES
MATCH_NUMBER() AS match_num,
CLASSIFIER() AS var_match,
STRT.tstamp AS start_tstamp,
FINAL LAST(UP.tstamp) AS end_tstamp
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST UP
PATTERN (STRT DOWN+ UP+ DOWN+ UP+)
DEFINE
DOWN AS DOWN.price < PREV(DOWN.price),
UP AS UP.price > PREV(UP.price)
) MR
ORDER BY MR.symbol, MR.match_num, MR.tstamp;
SYMBOL TSTAMP MATCH_NUM VAR_ START_TST END_TSTAM PRICE
---------- --------- ---------- ---- --------- --------- ----------
ACME 05-APR-11 1 STRT 05-APR-11 13-APR-11 25
ACME 06-APR-11 1 DOWN 05-APR-11 13-APR-11 12
ACME 07-APR-11 1 UP 05-APR-11 13-APR-11 15
ACME 08-APR-11 1 UP 05-APR-11 13-APR-11 20
ACME 09-APR-11 1 UP 05-APR-11 13-APR-11 24
ACME 10-APR-11 1 UP 05-APR-11 13-APR-11 25
ACME 11-APR-11 1 DOWN 05-APR-11 13-APR-11 19
ACME 12-APR-11 1 DOWN 05-APR-11 13-APR-11 15
ACME 13-APR-11 1 UP 05-APR-11 13-APR-11 25
这个查询做了什么？它构建于例子20-1中所介绍的概念，并且在数据中寻找W形而不是V形。这个查询结果显示了一个W形。为了查找W形，定义PATTERN的规则表达式被修改为两次查找DOWN紧跟着UP的模式: PATTERN (STRT DOWN+ UP+ DOWN+ UP+)这个模式定义意味着仅当两个V形之间没有间隔时才匹配一个W形。例如，假设有一个平直间隔，价格不变，而这个间隔发生在两个V形之间，则此模式不会与数据匹配。为了演示返回数据，输出被设定为ALL ROWS PER MATCH。注意在MEASURES子句中的FINAL LAST(UP.tstamp)返回的是被映射到UP的最后一行的时间戳。
本主题由 newkid 于 2016-4-21 04:47 设置高亮
本版精华
我也玩个游戏，我申请加精，求批准
[2010-4-16日更新207页]贡献一本自己写的plsql应用程序开发指南
【好东西还是留在本版】我多年收藏的各种语言编码规范
Oracle PLSQL读取(解析)Excel文档
使用Partitioned Outer Join实现稠化报表
【重磅干货】兔子的SQL优化方法论
[三思笔记]-oracle函数介绍
如何sql查询出连续号码段
Jonathan Lewis关于CBO的系列文章
练练手，一句SQL实现输出九九乘法表
模式匹配中的任务以及关键字
本章节讨论下列的任务以及关键字：
．PARTITION BY: 将数据行进行逻辑上的分组
．ORDER BY: 在分区中对数据行进行逻辑排序
．[ONE ROW | ALL ROWS] PER MATCH: 为每个匹配选择汇总或者是明细
．MEASURES: 定义从模式匹配导出的计算
．PATTERN: 定义要匹配的行模式
．DEFINE: 定义模式主变量
．AFTER MATCH SKIP: 在一个匹配找到之后重启匹配的过程
．MATCH_NUMBER: 找出哪些行是哪次匹配的成员
．CLASSIFIER: 找出哪个模式变量作用于哪些行
PARTITION BY: 将数据行进行逻辑上的分组
通常情况下你会把你的输入数据划分成逻辑上的组进行分析。在股票的例子中，你对模式匹配进行划分，使得它每次只适用于一支股票。你用PARTITION BY关键字来做到这一点。PARTITION BY用来指定行模式输入表的数据行按照一个或多个列进行分区。匹配在分区之内被查找，不会跨越分区边界。
如果没有PARTITION BY，那么行模式输入表的所有行构成了一个单独的行模式分区。
在分区中对数据行进行逻辑排序
在你将输入数据划分为逻辑分区之后，你需要将每个分区内的数据进行排序。
如果没有对行的排序，你就没有一个可靠的顺序来检查模式匹配。ORDER BY 关键字被用来指定一个行模式分区内的行的顺序。
[ONE ROW | ALL ROWS] PER MATCH: 为每个匹配选择汇总或者是明细
有时候你需要关于匹配的汇总数据，另一些时候你需要明细。你可以用下面的SQL关键字来做到这一点：
ONE ROW PER MATCH
每次匹配产生一个汇总的行。这是缺省的。
ALL ROWS PER MATCH
一个跨越了多行的匹配将会为匹配中的每一行产生一行输出。
这个输出会在“行模式输出”一节中进行解释。
MEASURES: 定义从模式匹配导出的计算
模式匹配子句允许你创建在广泛的分析范围内有用的表达式。这些表达式通过使用MEASURES子句表现为在输出中的列。MEASURES子句定义了行模式的度量列，它们的值是通过对特定匹配相关的表达式进行求值计算出来的。
PATTERN: 定义要匹配的行模式
PATTERN子句允许你定义哪些模式变量必须被匹配，它们被匹配的顺序，以及必须匹配的行的数量。PATTERN子句为搜索匹配指定了一个规则表达式。
一个行模式匹配包括了一个行模式分区中一系列连续的数据行。匹配中的每一行被映射到一个模式变量。从行到模式变量的映射必须遵从PATTERN子句中的规则表达式，并且DEFINE子句中所有的条件都必须为真。
DEFINE: 定义主要的模式变量
因为 PATTERN 子句依赖于模式变量，你必须有一个子句来定义这些变量。它们是在DEFINE子句中指定的。
DEFINE 是一个必需的子句，用于指定一行数据被映射到一个特定的模式变量时必须满足的条件。
一个模式变量并不一定需要一个定义。任何一行都可以被映射到一个未定义的模式变量。
AFTER MATCH SKIP: 在一个匹配找到之后重启匹配的过程
在查询找到一个匹配之后，它必须在确切的起点查找下一个匹配。你是否想查找这样的匹配，使得更早的匹配终点和下一个匹配的起点重叠？或者你想要另外的不同做法？在指定重启点这方面，模式匹配提供了高度的灵活性。AFTER MATCH SKIP子句决定了在一个非空匹配被找到之后重启行模式匹配的起点。子句的缺省是AFTER MATCH SKIP PAST LAST ROW: 在当前匹配的最后一行之后的下一行重启模式匹配。
MATCH_NUMBER: 找出哪些行是哪次匹配的成员
在一个给定的分区内你可能会有大量的模式匹配。你要如何区分这些匹配？这是通过MATCH_NUMBER函数实现的。在一个行模式分区内的匹配以它们被找到的顺序的先后进行编号，从1开始。注意匹配编号在每个行模式分区内都再次从1开始，因为在行模式分区之间并没有固定顺序。
CLASSIFIER: 找出哪个模式变量作用于哪些行
随着你知道看见的是哪个MATCH_NUMBER，你可能想知道哪个模式的组件适用于哪个特定的行。这是通过CLASSIFIER函数做到的。一个数据行的CLASSIFIER就是这一行被模式匹配映射的模式变量。CLASSIFIER函数返回一个字符串，它的值是该行被映射到的模式变量名。
模式匹配的语法
模式匹配的语法如下：
table_reference ::=
{only (query_table_expression) | query_table_expression }[flashback_query_clause]
[pivot_clause|unpivot_clause|row_pattern_recognition_clause] [t_alias]
row_pattern_recognition_clause ::=
MATCH_RECOGNIZE (
[row_pattern_partition_by ]
[row_pattern_order_by ]
[row_pattern_measures ]
[row_pattern_rows_per_match ]
[row_pattern_skip_to ]
PATTERN (row_pattern)
[ row_pattern_subset_clause]
DEFINE row_pattern_definition_list
)
row_pattern_partition_by ::=
PARTITION BY column[, column]...
row_pattern_order_by ::=
ORDER BY column[, column]...
row_pattern_measures ::=
MEASURES row_pattern_measure_column[, row_pattern_measure_column]...
row_pattern_measure_column ::=
expression AS c_alias
row_pattern_rows_per_match ::=
ONE ROW PER MATCH
| ALL ROWS PER MATCH
row_pattern_skip_to ::=
AFTER MATCH {
SKIP TO NEXT ROW
| SKIP PAST LAST ROW
| SKIP TO FIRST variable_name
| SKIP TO LAST variable_name
| SKIP TO variable_name}
row_pattern ::=
row_pattern_term
| row_pattern "|" row_pattern_term
row_pattern_term ::=
row_pattern_factor
| row_pattern_term row_pattern_factor
row_pattern_factor ::=
row_pattern_primary [row_pattern_quantifier]
row_pattern_quantifier ::=
*[?]
|+[?]
|?[?]
|"{"[unsigned_integer ],[unsigned_integer]"}"[?]
|"{"unsigned_integer "}"
row_pattern_primary ::=
variable_name
|$
|^
|([row_pattern])
|"{-" row_pattern"-}"
| row_pattern_permute
row_pattern_permute ::=
PERMUTE (row_pattern [, row_pattern] ...)
row_pattern_subset_clause ::=
SUBSET row_pattern_subset_item [, row_pattern_subset_item] ...
row_pattern_subset_item ::=
variable_name = (variable_name[ , variable_name]...)
row_pattern_definition_list ::=
row_pattern_definition[, row_pattern_definition]...
row_pattern_definition ::=
variable_name AS condition
模式匹配之内的行模式操作的语法如下：
function ::=
single_row_function
| aggregate_function
| analytic_function
| object_reference_function
| model_function
| user_defined_function
| OLAP_function
| data_cartridge_function
| row_pattern_recognition_function
row_pattern_recognition_function ::=
row_pattern_classifier_function
| row_pattern_match_number_function
| row_pattern_navigation_function
| row_pattern_aggregate_function
row_pattern_classifier_function ::=
CLASSIFIER( )
row_pattern_match_number_function ::=
MATCH_NUMBER( )
row_pattern_navigation_function ::=
row_pattern_navigation_logical
| row_pattern_navigation_physical
| row_pattern_navigation_compound
row_pattern_navigation_logical ::=
[RUNNING|FINAL] {FIRST|LAST} (expression[,offset])
row_pattern_navigation_physical ::=
{PREV|NEXT}(expression[, offset])
row_pattern_navigation_compound ::=
{PREV | NEXT} (
[RUNNING| FINAL] {FIRST|LAST} (expression[, offset]) [,offset])
在模式匹配之内指定的集合函数语法如下：
row_pattern_aggregate_function ::=
[RUNNING | FINAL] aggregate_function
模式匹配的细节
本章节呈现的是前一章节讨论的项目的细节，加上一些额外的主题。注意有些资料比较复杂，这是不可避免的。模式匹配的某些方面需要特别注意微妙的细节。
．PARTITION BY: 将数据行进行逻辑上的分组
．ORDER BY: 在分区中对数据行进行逻辑排序
．[ONE ROW | ALL ROWS] PER MATCH: 为每个匹配选择汇总或者是明细
．MEASURES: 定义查询中用到的计算
．PATTERN: 定义要匹配的行模式
．SUBSET: 定义联合行模式变量
．DEFINE: 定义主要的模式变量
．AFTER MATCH SKIP: 定义在一个匹配找到之后从哪里重启
．MEASURES 和 DEFINE中的表达式
．行模式输出
PARTITION BY: 将数据行进行逻辑上的分组
通常情况下你会把你的输入数据划分成逻辑上的组进行分析。在股票的例子中，你对模式匹配进行划分，使得它每次只适用于一支股票。你用PARTITION BY关键字来做到这一点。PARTITION BY用来指定行模式输入表的数据行按照一个或多个列进行分区。匹配在分区之内被查找，不会跨越分区边界。
如果没有PARTITION BY，那么行模式输入表的所有行构成了一个单独的行模式分区。
在分区中对数据行进行逻辑排序
在你将输入数据划分为逻辑分区之后，你需要将每个分区内的数据进行排序。
如果没有对行的排序，你就没有一个可靠的顺序来检查模式匹配。ORDER BY 关键字被用来指定一个行模式分区内的行的顺序。
ORDER BY子句被用于指定一个行模式分区内的行的顺序。如果一个行模式分区内的两行的顺序无法通过ORDER BY来确定，那么MATCH_RECOGNIZE子句的结果就是不确定的：它可能无法在每次查询执行的时候给出一致的结果。
[ONE ROW | ALL ROWS] PER MATCH: 为每个匹配选择汇总或者是明细
有时候你需要关于匹配的汇总数据，另一些时候你需要明细。你可以用下面的SQL关键字来做到这一点：
ONE ROW PER MATCH
每次匹配产生一个汇总的行。这是缺省的。
ALL ROWS PER MATCH
一个跨越了多行的匹配将会为匹配中的每一行产生一行输出。
这个输出会在“行模式输出”一节中进行解释。
MATCH_RECOGNIZE子句可能找到一个零行的匹配。对于一个空的匹配，ONE ROW PER MATCH返回一个汇总行：PARTITION BY列从空匹配发生的行得到值，而度量列则被从一个空集合求值。
ALL ROWS PER MATCH 有三个子选项:
ALL ROWS PER MATCH SHOW EMPTY MATCHES
ALL ROWS PER MATCH OMIT EMPTY MATCHES
ALL ROWS PER MATCH WITH UNMATCHED ROWS
这些选项会在 "模式匹配的高级主题"中被解释。
MEASURES: 定义查询中用到的计算
MEASURES子句为模式输出表定义了列清单。每个模式度量列被用一个列名定义，其值是通过相应的模式度量表达式指定的。
一个值表达式是根据模式变量定义的。值表达式可以包含集合函数，模式导航操作，CLASSIFIER(), MATCH_NUMBER(), 以及对输入表的列的引用。更多信息请参见"MEASURES 和 DEFINE中的表达式"
PATTERN: 定义要匹配的行模式
PATTERN关键字指定了要从分区中的一系列排序过的数据行中辨认出的模式。模式中的每个变量明对应一个布尔型条件，这在随后的DEFINE语法组件中被指定。
PATTERN子句被用于指定一个规则表达式。对规则表达式的概念及细节的解释不在这份资料的范围之内。如果你对规则表达式不熟悉，我们建议你利用其他资源让自己对这个话题更加了解。
一个PATTERN子句中的规则表达式被包含在括号中。PATTERN可能使用如下操作符：
．拼接
拼接被用于按匹配顺序列出模式中的两个或更多项目。当两个连续的项目之间没有操作符号，它们就被拼接。例如：PATTERN (A B C)
．修饰符
修饰符定义了匹配所接受的迭代次数。修饰符是后缀操作符，有如下选项：
* — 0次或多次迭代
+ — 1次或多次迭代
? — 0次或1次迭代
{n} — n 次迭代 (n > 0)
{n,} — n 次或更多次迭代 (n >= 0)
{n,m} — n次至m次 (包括n和m) 迭代 (0 <= n <= m, 0 < m)
{,m} — 0次至m次 (包括0和m) 迭代 (m > 0)
惰性修饰符 — 这是通过在一个修饰符后面紧跟一个额外的问号来指定的(*?, +?, ??, {n,}?, { n, m }?, {,m}?)。惰性修饰符与贪婪修饰符的区别请参见"惰性修饰符与贪婪修饰符的对照"一节。
下面是使用修饰符的例子：
A* 匹配0次或多次A的迭代
A{3,6} 匹配3至6次A的迭代
A{,4} 匹配0至4次A的迭代
．任选项
任选项从一个包含多种可能的规则表达式清单中匹配一个单独的规则表达式。任选项清单是通过在每个规则表达式之间放置一个竖线 (|)来创建的。任选项是通过指定的顺序来优先选择的。例如，PATTERN (A | B | C) 试图先匹配A。如果A不能匹配，它试图匹配B。如果B不能匹配，它试图匹配C。
．分组
分组会将一个表达式的一部分当作一个单独的单元来处理，使得你能够将表达式操作符，例如修饰符应用于那个组。例如，PATTERN ((A B){3} C)会试图匹配组(A B)三次，然后查找C的一次出现。
．排列
更多信息请参见“如何表示排列”一节。
．排除
要从ALL ROWS PER MATCH的输出中排除的部分模式是用{- 和 -}括起来的。参见“如何从输出中排除部分模式”
．界定符
界定符是以位置而不是行来定位的。它们匹配一个分区的开始或者末尾。
^ 匹配分区中的第一行之前的位置。
$ 匹配分区中的最后一行之后的位置。
例如，PATTERN (^A+$)仅仅在一个分区中所有的行都满足A的条件才进行匹配。匹配结果扩展到整个分区。
．空模式 (), 匹配一个空集。
本节包含如下两个主题：
．惰性修饰符与贪婪修饰符的对照
．操作符的优先级
惰性修饰符与贪婪修饰符的对照
模式修饰符被称作是贪婪的; 它们会试图在被应用之处匹配尽可能多的规则表达式的实例。例外是那些后缀带有一个问号"?"的模式修饰符，这些被称作是惰性的。它们会试图在被应用之处匹配尽可能少的规则表达式的实例。
追加到一个模式变量后面的贪婪和惰性修饰符之间的区别可以演示如下：
A* 试图将尽可能多的行映射到A, 而A*?则会映射将尽可能少的行映射到A。例如：
PATTERN (X Y* Z)
这个模式包含三个变量名，X,Y 和Z，Y被*所修饰。这意味着当连续的输入行满足如下条件时，一个模式匹配将被识别并报告：
有一行满足了变量X所定义的条件，随后有0行或者多行满足变量Y所定义的条件，随后有一行满足变量Z所定义的条件。
在模式匹配的处理过程中，当一行被映射到X，并且0行或多行被映射到Y, 如果下一行同时可能被映射到Y和Z(也即同时满足了Y和Z定义的条件)，那么，因为Y的修饰符 * 是贪婪的，这一行被优先映射到Y而不是Z。由于这种贪婪的属性，Y比Z更优先，被映射到Y的行数更多。如果模式表达式是PATTERN (X Y*? Z), 它在Y使用了惰性修饰符 "*?"，那么Z就比Y更优先。
操作符的优先级
一个规则表达式中的元素的优先级从高到低如下：
．row_pattern_primary
这些元素包括行模式主变量（指那些没有用SUBSET(参见"SUBSET: 定义联合行模式变量"一节)，界定符，PERMUTE, 括号表达式，排除语法和空模式进行创建的模式变量）
．修饰符
一个row_pattern_primary可能有零个或者一个修饰符。
．拼接
．任选项
任选项的优先级可演示为PATTERN(A B | C D), 它等价于 PATTERN ((A B) | (C D))。然而，它不等价于PATTERN (A (B | C) D)。
修饰符的优先级可演示为PATTERN (A B *), 它等价于PATTERN (A (B*))。然而，它不等价于PATTERN ((A B)*)。
一个修饰符不能立即跟在另一个修饰符后面。例如，PATTERN(A**)是被禁止的。
一个行模式主变量可以在一个模式中出现多次，例如PATTERN (X Y X)
SUBSET: 定义联合行模式变量
有时候，创建一个群组的多个模式变量并且使用它自己的一个变量名来进行引用是很有帮助的。这些群组被称为联合行模式变量，你用SUBSET子句创建它们。用SUBSET创建的联合行模式变量可以在MEASURES 和 DEFINE子句中使用。SUBSET子句是可选的。它被用来声明联合行模式变量。例如，下面的查询用SUBSET来计算被映射到STRT和DOWN变量的联合之上的所有行的平均值，此处STRT是模式的起点，DOWN是V形的下行（左侧）腿。
例子 20-5 演示创建一个联合行模式变量
例子 20-5 定义联合行模式变量
SELECT *
FROM Ticker MATCH_RECOGNIZE(
PARTITION BY symbol
ORDER BY tstamp
MEASURES FIRST(STRT.tstamp) AS strt_time,
LAST(DOWN.tstamp) AS bottom,
AVG(STDN.Price) AS stdn_avgprice
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST UP
PATTERN (STRT DOWN+ UP+)
SUBSET STDN= (STRT, DOWN)
DEFINE
UP AS UP.Price > PREV(UP.Price),
DOWN AS DOWN.Price < PREV (DOWN.Price)
);
SYMBOL STRT_TIME BOTTOM STDN_AVGPRICE
------ --------- --------- -------------
ACME 05-APR-11 06-APR-11 18.5
ACME 10-APR-11 12-APR-11 19.6666667
ACME 14-APR-11 16-APR-11 17
这个例子声明了一个单独的联合行模式变量STDN, 并且定义它为映射到STRT和DOWN的联合。在一个查询中可能有多个联合行模式变量，例如：
PATTERN (W+ X+ Y+ Z+)
SUBSET XY = (X, Y),
WZ = (W, Z)
一个SUBSET项目的右侧是一个在括号之内的用逗号隔开的不重复的行模式主变量清单。这将联合行模式变量（在左侧）定义成行模式主变量（在右侧）的联合。
注意，右侧的模式变量清单不能包含任何联合行模式变量（不能有联合的联合）。
对于每个匹配，存在着一个隐式的联合行模式变量，称作通用行模式变量。这个通用行模式变量是所有行模式主变量的联合。例如，如果你的模式有模式主变量A,B,和C，那么通用行模式变量就等价于一个SUBSET子句，参数为(A,B,C)。因此，匹配的每一行都被映射到这个通用行模式变量。任何一个在MEASURES 或 DEFINE子句中被引用的未修饰列都被隐式地用这个通用行模式变量进行修饰。注意，不存在一个关键字来明确指明这个通用行模式变量。
DEFINE: 定义模式主变量
DEFINE 是一个必需的子句，用来指定那些定义模式主变量的条件。在例子中：
DEFINE UP AS UP.Price > PREV(UP.Price),
DOWN AS DOWN.Price < PREV(DOWN.Price)
UP被定义为条件UP.Price > PREV (UP.Price), 而DOWN被定义为条件DOWN.Price < PREV (DOWN.Price) (PREV是一个行模式导航操作，它被求值为一个表达式在前一行中的值; 关于完整的行模式导航操作，请参见"行模式导航操作"一节)
一个模式变量不一定需要定义；如果没有定义，任何一行都可以被映射到模式变量。
一个联合行模式变量（参见"SUBSET: 定义联合行模式变量"一节对SUBSET的讨论）不能够在DEFINE中被定义，但可以在模式变量的定义中被引用。
一个模式变量的定义可以引用另外一个模式变量，这在例子20-6中被演示。
例子 20-6 定义模式变量
SELECT *
FROM Ticker MATCH_RECOGNIZE (
PARTITION BY Symbol
FROM Ticker
MATCH_RECOGNIZE (
PARTITION BY Symbol
ORDER BY tstamp
MEASURES FIRST (A.tstamp) AS A_Firstday,
LAST (D.tstamp) AS D_Lastday,
AVG (B.Price) AS B_Avgprice,
AVG (D.Price) AS D_Avgprice
PATTERN (A B+ C+ D)
SUBSET BC = (B,C)
DEFINE A AS Price > 100,
B AS B.Price > A.Price,
C AS C.Price < AVG (B.Price),
D AS D.Price > MAX (BC.Price)
) M
在这个例子中:
A的定义隐式地引用了通用行模式变量（因为对未修饰的price列的引用）。
B的定义引用了模式变量A。
C的定义引用了模式变量B。
D的定义引用了联合行模式变量BC。
在一个匹配尝试中，条件在分区中的连续的数据行上进行求值，当前行被初步映射到一个模式变量，只要是该模式所允许的。为了能够成功映射，条件必须为真。
在前一个例子中：
A AS Price > 100
Price指的是当前行的price, 因为最后一个被映射到任何一个行模式主变量的行是当前行，它初步被映射到A。在这个例子中，如果用的是A.Price也会导致同样的结果。
B AS B.Price > A.Price
B.Price 指的是当前行的Price（因为B正在被定义），而此时A.Price 指的是最后一个映射到A的行。从这个模式来看，映射到A的唯一行也是映射的第一行。
C AS C.Price < AVG(B.Price)
此处C.Price指的是当前行的Price，因为C正在被定义。聚合函数AVG()被计算为所有已经映射到B的行的平均值。
D AS D.Price > MAX(BC.Price)
模式变量D类似于模式变量C, 虽然它演示的是布尔条件中的联合行模式变量的使用。在这个例子中，MAX(BC.Price)返回的是被匹配到变量B或者变量C的那些行的价格的最大值。布尔条件的语义在"MEASURES 和 DEFINE中的表达式"一节中被更详细地讨论。
AFTER MATCH SKIP: 定义在一个匹配找到之后从哪里重启
AFTER MATCH SKIP 子句决定了在一个非空匹配被找到之后从哪里重启行模式匹配。子句的缺省是AFTER MATCH SKIP PAST LAST ROW。可选项如下：
．AFTER MATCH SKIP TO NEXT ROW
在当前匹配第一行之后的下一行重启模式匹配。
．AFTER MATCH SKIP PAST LAST ROW
在当前匹配的最后一行之后的下一行重启模式匹配。
．AFTER MATCH SKIP TO FIRST pattern_variable
在被映射到模式变量的第一行重启模式匹配。
．AFTER MATCH SKIP TO LAST pattern_variable
在被映射到模式变量的最后一行重启模式匹配。
．AFTER MATCH SKIP TO pattern_variable
和AFTER MATCH SKIP TO LAST pattern_variable相同。
在使用 AFTER MATCH SKIP TO FIRST 或者 AFTER MATCH SKIP TO [LAST]的时候, 有可能不存在被映射到模式变量pattern_variable的行。例如：
AFTER MATCH SKIP TO A
PATTERN (X A* X)
例子中的模式变量A可能没有被映射的行。如果没有数据行被映射到A，也就没有数据行可以skip to, 因此会产生运行异常。另一种有问题的条件是AFTER MATCH SKIP可能试图在上一次匹配开始的同一行重启模式匹配。例如：
AFTER MATCH SKIP TO X
PATTERN (X Y+ Z)
在这个例子中，AFTER MATCH SKIP TO X试图在前一匹配被找到的第一行重启模式匹配。这会导致死循环，所以这类情景会产生运行异常。
注意AFTER MATCH SKIP 语法只是决定在一个非空匹配之后重启匹配扫描的起点。如果找到了空匹配，有一行将会被跳过（如同指定了SKIP TO NEXT ROW）。于是一个空匹配永远不会引发这些异常。得到这种异常的查询必须被重写，例如下面这样：
AFTER MATCH SKIP TO A
PATTERN (X (A | B) Y)
如果有一行被映射到B，这会引发运行错误，因为没有数据行被映射到A。如果目的是要跳转到A或者B，则下面方法可行：
AFTER MATCH SKIP TO C
PATTERN (X (A | B) Y)
SUBSET C = (A, B)
在改过的例子中，不可能发生运行错误，不管是A或者B被匹配。
另一个例子：
AFTER MATCH SKIP TO FIRST A
PATTERN (A* X)
这个例子在第一个匹配之后就得到了异常，不管是因为跳转至匹配的同一行（如果A*匹配到了）或者因为跳转到一个不存在的行（如果A*没有匹配到）。在这个例子中，SKIP TO NEXT ROW 是更好的选择。
当你把AFTER MATCH SKIP PAST LAST ROW之外的跳转选项和ALL ROWS PER MATCH一起使用，两个连续的匹配就有可能发生重叠，此时行模式输入表的一行R有可能出现在多于一个匹配中。在这种情况下，行模式输出表在这行参与的每个匹配都会有一行输出。如果行模式输入表的一行参与了多个匹配，则MATCH_NUMBER函数可用来区分这些匹配。当一行数据参与了多个匹配，它在每个匹配中的类别符classifier可能是不同的。
MEASURES 和 DEFINE中的表达式
模式匹配提供了如下的标量表达式，这是行模式匹配独有的：
．行模式导航操作，使用的是函数 PREV, NEXT, FIRST 和 LAST。行模式导航操作在“行模式导航操作”一节讨论。
．MATCH_NUMBER函数，它返回一个行模式匹配在它的模式区间中的序号，这在“MATCH_NUMBER：查找哪些行在哪些匹配中”一节讨论。
．CLASSIFIER函数，它返回数据行被映射到的行模式主变量的名称，这在“CLASSIFIER: 查找哪些模式变量应用于哪些行”一节讨论。
MEASURES 和 DEFINE子句中的表达式具有相同的语法和语义，只有下列例外：
．DEFINE 子句仅支持running语义。
．MEASURES 子句缺省为 running 语义，但也支持 final 语义。其区别在 "RUNNING 与 FINAL 语义对照"一节讨论。
表达式的使用
本章节讨论在模式匹配中使用表达式需要考虑的一些东西，包括：
．MATCH_NUMBER: 找出哪些行在哪个匹配中
．CLASSIFIER: 找出哪个模式变量应用于哪些行
．行模式列引用
．聚合
．行模式导航
MATCH_NUMBER: 找出哪些行在那个匹配中
在一个行模式分区中的匹配是根据它们被找到的先后从1开始按顺序编号的。注意这个匹配号在每个行模式分区中都是从1重新开始，因为在行模式分区之间并没有内在的顺序。MATCH_NUMBER() 是一个函数，它返回一个数字，其小数精度为零，其值是行模式分区内的匹配顺序号。
前面使用MATCH_NUMBER() 的例子显示出它被用于MEASURES子句。MATCH_NUMBER()也可能被用在DEFINE子句，在那里它可以被用来定义那些对匹配号有依赖的条件。
CLASSIFIER: 找出哪个模式变量应用于哪些行
CLASSIFIER函数返回一个字符串，它的值是数据行被映射到的模式变量名。CLASSIFIER可以用在MEASURES和DEFINE子句中。
在DEFINE子句中，CLASSIFIER函数返回的是当前行被映射到的模式主变量名。
在MEASURES变量中：
．如果ONE ROW PER MATCH被指定，查询在处理MEASURES子句的时候使用的是匹配的最后一行，所以CLASSIFIER函数返回的是匹配的最后一行所映射到的模式变量名。
．如果ALL ROWS PER MATCH被指定，对于匹配中找到的每一行，CLASSIFIER函数返回的是该行被映射到的模式变量名。
一个空匹配的第一行的classifier的值是NULL。
行模式列引用
一个行模式列引用是一个列名被隐式或者显式的模式变量所修饰，例如：
A.Price
A是模式变量，Price是列名。一个不带修饰符的列名，例如Price，被隐性修饰为通用行模式变量，它引用的是匹配中所有行的集合。列引用可以嵌套在其他语法元素中，尤其是聚集和导航操作符。（然而，行模式匹配的嵌套有一些限制，参见“MATCH_RECOGNIZE中禁止的嵌套”对FROM子句的描述）。
模式列引用被分为如下几类：
．嵌套在聚合中，例如SUM: 这是一种聚合行模式的列引用。
．嵌套在行模式导航操作中(PREV, NEXT, FIRST, and LAST)：这是一种导航行模式的列引用。
．其他：普通的行模式列引用。
所有嵌套在聚合或者导航操作中的模式列引用都必须被同一个模式变量所修饰。例如：
PATTERN (A+ B+)
DEFINE B AS AVG(A.Price + B.Tax) > 100
上述例子是一个语法错误，因为A和B是两个不同的模式变量。聚合语义需要一个单独的行集合；没有任何办法可以形成一个单独的行集合使得A.Price + B.Tax可以在它之上求值。
然而，下列情况是可接受的：
DEFINE B AS AVG (B.Price + B.Tax) > 100
在上述例子中，所有聚合中的模式列引用都被B所修饰。
一个未修饰的列引用被隐性修饰为通用行模式变量，它引用的是一个匹配中所有的行的集合。例如：
DEFINE B AS AVG(Price + B.Tax) > 1000
上述例子是一个语法错误，因为未修饰列引用Price被通用行模式变量隐性修饰，而B.Tax则被B显式修饰。然而，下列情况是可接受的：
DEFINE B AS AVG (Price + Tax) > 1000
在上述例子中，Price和Tax都被通用行模式变量隐性修饰。
聚合
聚合函数(COUNT, SUM, AVG, MAX, 和 MIN)在MEASURES 和 DEFINE 子句中都可以使用。注意DISTINCT关键字不被支持。在行模式匹配中使用时，聚合函数操作于映射到一个特定模式变量的行的集合，使用的是running 或 final 语义中的一种。例如：
MEASURES SUM (A.Price) AS RunningSumOverA,
FINAL SUM(A.Price) AS FinalSumOverA
ALL ROWS PER MATCH
在这个例子中，A是一个模式变量。第一个模式度量RunningSumOverA没有指定是RUNNING还是FINAL，因此缺省为RUNNING。这意味着它被计算为：那些被当前匹配映射到A的，截至并且包括当前行的数据行中的Price的和。第二个模式度量，FinalSumOverA计算的是所有被当前匹配映射到A的行的Price之和，包括那些比当前行更迟匹配的行。FINAL聚合只能在MEASURES子句中使用，不能用在DEFINE子句中。
一个包含在聚合函数中的未修饰列引用会被隐式地使用通用行模式变量进行修饰，它引用的是当前模式匹配的所有行。例如：
SUM (Price)
计算的是当前行模式匹配的所有行的Price的滚动之和。
包含在聚合函数中的所有列引用都必须被相同的模式变量所修饰。例如：
SUM (Price + A.Tax)
因为Price被通用行模式变量隐式修饰，而A.Tax被A显式修饰，你就会得到一个语法错误。
COUNT聚合函数针对模式匹配有特殊的语法，使得COUNT(A.*)可以被指定。COUNT(A.*)是被当前模式匹配映射到变量A的行数。至于COUNT(*)，*隐式地覆盖了通用行模式变量的行，所以COUNT(*)是当前模式匹配的行数。
行模式导航操作
总共有四个函数：PREV, NEXT, FIRST, and LAST——它们可以根据物理偏移或者逻辑偏移量启动行模式的导航。
PREV 和 NEXT
PREV函数可以用来对一个分区内的更早的一行的一个表达式进行求值。它根据物理行数来操作，并不局限于映射到某个特定变量的行。如果更早的一行不存在，NULL被返回。例如：
DEFINE A AS PREV (A.Price) > 100
上述例子说的是，如果当前行的前一行的price大于100, 则当前行可以被映射到A。如果前一行不存在（也即，当前行是行模式匹配分区中的第一行），则PREV(A.Price)为NULL，因此条件不为真，所以第一行不能被映射到A。
注意，你可以在定义模式变量A的时候使用另一种模式变量（如B），并对那个另外的模式变量应用一个PREV()函数作为条件。这类似于：
DEFINE A AS PREV (B.PRICE) > 100
在这个例子中，PREV()使用的起始行是被映射到模式变量B的最后一行。
PREV函数可以接受一个可选的非负数整数参数，指示到前面行的物理偏移量。因此：
PREV (A.Price, 0) 等同于 A.Price.
PREV (A.price, 1) 等同于 PREV (A.Price). 注意：1 是缺省的偏移量。
PREV (A.Price, 2) 是比A的滚动语义所表示的行更早两行的Price的值。（如果没有行被映射到A, 或者不存在更早的两行，则PREV (A.Price, 2)为NULL）
偏移量必须是一个运行时的常量（字面量，绑定变量，或者包含这两者的表达式），但不能是一个列或者是一个子查询。
NEXT函数是PREV函数的向后查找的版本。它可以被用于根据一个物理偏移引用行模式分区中的向后方向的行。除了函数名之外的语法和PREV相同，例如：
DEFINE A AS NEXT (A.Price) > 100
上述例子在行模式分区中向后查找一行。注意在DEFINE子句中，模式匹配不支持越过当前行的聚合函数，因为要预测未来什么行会被映射到哪个模式变量是很困难的。NEXT函数并没有违反这个定律，因为它是根据一个物理偏移导航到“未来的”行，这不需要知道未来的行映射。
例如，为了找出一个被隔离的行，它比前两行和后两行的平均值的两倍更大：这可以利用NEXT表达为：
PATTERN ( X )
DEFINE X AS X.Price > 2 * ( PREV (X.Price, 2)
+ PREV (X.Price, 1)
+ NEXT (X.Price, 1)
+ NEXT (X.Price, 2) ) / 4
注意，在PREV或NEXT中被求值的行不一定要被映射到参数中的模式变量。例如，在本例中，PREV (X.Price, 2)所求值的行并不在匹配之中。这个模式变量的目的是为了识别出从哪一行开始算偏移量，而不是最终到达的行。（如果在一个PREV()或者NEXT()中，一个模式变量的定义引用了它自身，那么它所指的开始算偏移量的那一行就是当前行。）这一点在"在PREV和NEXT中嵌套FIRST和LAST"章节中被更进一步讨论。
PREV 和 NEXT 可以伴随多个列的引用一起使用，例如:
DEFINE A AS PREV (A.Price + A.Tax) < 100
当你在PREV或者NEXT的第一个参数使用一个复杂表达式，所有的修饰符必须为同一个模式变量（在此例中是A）。
PREV和NEXT总是有着滚动的语义；关键字RUNNING 和 FINAL 不能用在 PREV 或者 NEXT。（参见相关章节“RUNNING和FINAL语义以及关键字”）。为了得到FINAL语义，可以使用诸如PREV (FINAL LAST (A.Price))，其解释参见章节"在PREV和NEXT中嵌套FIRST和LAST"。
FIRST 和 LAST
与 PREV 和 NEXT 函数相反，FIRST 和 LAST函数仅仅在映射到模式变量的行中导航：它们用的是逻辑偏移，而非物理偏移。FIRST返回的是在映射到一个模式变量的一组数据行中的第一行所求出的表达式的值。例如：
FIRST (A.Price)
如果没有数据行被映射到A, 那么其值为NULL。
类似地，LAST返回的是在映射到一个模式变量的一组数据行中的最后一行所求出的表达式的值。例如：
LAST (A.Price)
前述例子求出了被映射到A的最后一行的 A.price的值（如果这样的行不存在则返回NULL）。
FIRST 和 LAST 操作符能够接受一个可选的非负数整数参数，指示着被映射到这个模式变量的所有行集合中的一个逻辑偏移量。例如：
FIRST (A.Price, 1)
上述例子对映射到A的第二行的Price进行求值。考虑下面表20-1中的数据集和映射：
表 20-1 模式及其数据行
--------------------------
Row Price Mapping
R1 10 A
R2 20 B
R3 30 A
R4 40 C
R5 50 A
那么：
．FIRST (A.Price) = FIRST (A.Price, 0) = LAST (A.Price, 2) = 10
．FIRST (A.Price, 1) = LAST (A.Price, 1) = 30
．FIRST (A.Price, 2) = LAST (A.Price, 0) = LAST (A.Price) = 50
．FIRST (A.Price, 3) 为 null, LAST (A.Price, 3)也是null
注意，这个偏移量是逻辑偏移量，是在映射到模式变量A的行{R1, R3, R5}之内移动。它不像PREV 或者 NEXT是物理偏移量。
可选的整数参数必需是一个运行时的常量（字面量，绑定变量，或者包含这两者的表达式），不能是一个列或者子查询。
FIRST 或 LAST的第一个参数必需至少含有一个行模式的列引用。因此，FIRST(1) 是一个语法错误。
FIRST 或 LAST的第一个参数可能包含多于一个行模式的列引用，此时所有的修饰符必须为同一个模式变量。例如，FIRST (A.Price + B.Tax)是一个语法错误，但FIRST (A.Price + A.Tax)则可接受。
FIRST 和 LAST同时支持RUNNING和FINAL语义。RUNNING关键字是缺省的，也是DEFINE子句中支持的唯一关键字。FINAL语义可以在MEASURES中通过关键字FINAL进行访问，例如：
MEASURES FINAL LAST (A.Price) AS FinalPrice
ALL ROWS PER MATCH
RUNNING和FINAL语义和关键字对照
本章节讨论的是在使用RUNNING和FINAL时要注意考虑的一些东西。
RUNNING和FINAL语义
在一系列的数据行中的模式匹配通常被认为是一种渐进的过程，数据被一行接一行地检查看看它是否符合模式。在这种渐进处理模型中，在整个模式被识别出来之前的任何步骤，只有一部分的匹配，并且它不知道未来会有什么数据行会被加入，也不知道这些未来的行会被映射到哪些变量。因此，在模式匹配中，在DEFINE子句中的布尔条件中的行模式列引用具有RUNNING语义。这意味着一个模式变量代表着那些已经被映射到这个模式变量的数据行的集合，截至并包括当前行，但不包括任何未来的行。
在完整的匹配建立之后，FINAL语义成为可能。在一个成功匹配的最后一行，FINAL语义和RUNNING语义是一样的。FINAL语义只在MEASURES中可用，因为在DEFINE中，是否能得到完整匹配还是不确定的。
RUNNING 和 FINAL关键字被用来指定相应的RUNNING 或FINAL语义；这些关键字的规则在“RUNNING和FINAL关键字”中被讨论。
MEASURES 和 DEFINE中的表达式求值的基本规则如下：
．当一个包含模式变量的表达式在一组的行上被求值，则被映射到该模式变量的数据行的集合就被使用。如果该集合为空，则COUNT为0, 任何其他引用到模式变量的表达式为NULL。
．当一个表达式需要在一个单独的行上求值，那么该集合中的最后一行被使用。如果集合为空，则表达式为null。
例如，考虑下列的表和例子20-7中的查询：
例子 20-7 RUNNING 和 FINAL 语义
SELECT M.Symbol, M.Tstamp, M.Price, M.RunningAvg, M.FinalAvg
FROM TICKER MATCH_RECOGNIZE (
PARTITION BY Symbol
ORDER BY tstamp
MEASURES RUNNING AVG (A.Price) AS RunningAvg,
FINAL AVG (A.Price) AS FinalAvg
ALL ROWS PER MATCH
PATTERN (A+)
DEFINE A AS A.Price >= AVG (A.Price)
) M
;
考虑下列表20-2中显示的排序过的行模式分区数据。
表 20-2 模式和分区数据
Row Symbol Timestamp Price
--------------------------------------
R1 XYZ 09-Jun-09 10
R2 XYZ 10-Jun-09 16
R3 XYZ 11-Jun-09 13
R4 XYZ 12-Jun-09 9
下列逻辑可被用来查找匹配：
．在行模式分区中的第一行，暂且把R1行映射到变量A。在这个时候，映射到变量A的行的集合是{R1}。为了验证这个映射是否成功，可以对谓词求值：
A.Price >= AVG (A.Price)
在左侧，A.Price必须被单独一行求值，也即RUNNING语义中的集合的最后一行。集合中的最后一行是R1; 因此A.Price 是 10。
在右侧，AVG (A.Price)是一个聚合函数，这是用行的集合来计算的。这个平均值是10/1 = 10。
因此谓词相当于检测10 >= 10，答案是肯定的，所以映射成功。然而，模式A+是贪婪的，所以查询必须匹配尽可能多的行。
．在行模式分区的第二行，暂且把R2行映射到变量A。此时有两行被映射到A，因此集合为{R1, R2}。通过对下列谓词求值验证映射是否成功：
A.Price >= AVG (A.Price)
在左侧，A.Price必须被单独一行求值，也即RUNNING语义中的集合的最后一行。集合中的最后一行是R2; 因此A.Price 是 16。
在右侧，AVG (A.Price)是一个聚合函数，这是用行的集合来计算的。这个平均值是(10+16)/2 = 13。
因此谓词相当于检测16 >= 13，答案是肯定的，所以映射成功。
．在行模式分区的第三行，暂且把R3行映射到变量A。现在有三行被映射到A，因此集合为{R1, R2, R3}。通过对下列谓词求值验证映射是否成功：
A.Price >= AVG (A.Price)
在左侧，A.Price在R3上被求值; 因此A.Price 是 13。
在右侧，AVG (A.Price)是一个聚合函数，这是用行的集合来计算的。这个平均值是(10+16+13)/3 = 13。
因此谓词相当于检测13 >= 13，答案是肯定的，所以映射成功。
．在行模式分区的第四行，暂且把R3行映射到变量A。此时集合为{R1, R2, R3, R4}。通过对下列谓词求值验证映射是否成功：
A.Price >= AVG (A.Price)
在左侧，A.Price在R4上被求值; 因此A.Price 是 9。
在右侧，AVG (A.Price)是一个聚合函数，这是用行的集合来计算的。这个平均值是(10+16+13+9)/4 = 12。
因此谓词相当于检测9 >= 12，答案是否定的，所以映射不成功。
R4不满足A的定义，所以A+的最长匹配是{R1, R2, R3}。因A+有一个贪婪修饰符，这是首选的匹配。
DEFINE中计算的平均值是滚动(RUNNING)平均值。在MEASURES中，特别是在ALL ROWS PER MATCH中，区分最终(FINAL)和滚动(RUNNING)聚合函数是有可能的。注意在MEASURES子句中关键字RUNNING 和 FINAL 的使用。其区别可以在表20-3中的例子的结果观察得到。
表 20-3 行模式导航
Symbol Timestamp Price Running Average Final Average
-------------------------------------------------------------
XYZ 2009-06-09 10 10 13
XYZ 2009-06-10 16 13 13
XYZ 2009-06-11 13 13 13
有可能映射到一个模式变量的集合是空的。当对一个空集合求值的时候：
．COUNT 是 0。
．任何其他的聚合函数，行模式导航操作，或者普通的模式列引用都是NULL。
例如：
PATTERN ( A? B+ )
DEFINE A AS A.Price > 100,
B AS B.Price > COUNT (A.*) * 50
在前述例子中，考虑下列表20-4中的排序过的行模式分区数据：
表 20-4 模式和数据行
Row Price
--------------
R1 60
R2 70
R3 40
在此数据中查找匹配如下：
．暂且把R1行映射到变量A。（修饰符 "?" 意味着先尝试一个单独到A的匹配; 如果不成功，那么一个空匹配就被采用作为A?的匹配）。为了查看映射是否成功，谓词 A.Price > 100 被求值。A.Price是60; 因此谓词为假，映射到A不成功。
．因为映射到A失败了，一个空匹配被采用作为A?的匹配。
．暂且把R1行映射到变量B。对应此映射所检查的谓词为B.Price > COUNT (A.*) * 50
没有任何数据行被映射到A, 因此COUNT (A.*) 为0。因为 B.Price = 60 大于0, 映射是成功的。
．类似地，R2 和 R能够被成功映射到B。因为没有更多的行，这就是完整的匹配：没有任何行被映射到A, 而行{R1, R2, R3}被映射到B。
一个映射变量可以向前引用，也即，引用一个尚未被匹配的模式变量。例如:
PATTERN (X+ Y+)
DEFINE X AS COUNT (Y.*) > 3,
Y AS Y.Price > 10
上述例子是有效的语法。然而，这个例子永远不会被匹配，因为当一个行被映射到X的时候，没有任何行被映射到Y。因此COUNT(Y.*) 为0而且永远不可能大于3。哪怕未来有四个可能成功被映射到Y的行，也是如此。考虑表20-5中的数据集：
表 20-5 模式与数据行
Row Price
--------------
R1 2
R2 11
R3 12
R4 13
R5 14
把{R2, R3, R4, R5}映射到Y可能是成功的，因为所有这些行都满足定义Y的布尔条件。在这种情况下，你可能会认为你可以把R1映射到X, 并且得到一个成功的完整匹配。然而，模式匹配的规则不会找到这个匹配，因为根据模式X+ Y+, 在任何的行被映射到Y之前至少必须有一行被映射到X。
RUNNING 与 FINAL 关键字对照
RUNNING 和 FINAL 关键字被用于指定所需的语义是滚动的(running)或者是最终的(final)。RUNNING 和 FINAL可以被用于聚合函数以及行模式导航操作FIRST 和 LAST。
在一个行模式匹配查询中，聚合函数以及FIRST 和 LAST可能出现在下列几个地方：
．在DEFINE子句中。在处理DEFINE子句的时候，查询仍然是在辨识出一个匹配的中间，因此唯一支持的语义是RUNNING。
．在MEASURES子句中。在处理MEASURES子句的时候，查询已经完成了匹配的识别；因此，它就有可能考虑final语义。有两种子类别：
如果 ONE ROW PER MATCH 被指定，则从概念上查询被放置在匹配的最后一行，running 和 final语义没有真正的差别。
如果 ALL ROWS PER MATCH 被指定，那么行模式输出表就会为每个匹配保留一行。在这种情况下，用户可能希望看到running 和 final的两种值，所以模式匹配提供了RUNNING 和 FINAL关键字来支持这种区别。
基于这种分析，模式匹配指定如下几点:
．在MEASURES中，RUNNING 和 FINAL 关键字能够被用来指定一个聚合函数、FIRST或者LAST中的语义。关键字写在操作符之前，例如：RUNNING COUNT (A.*) 或者 FINAL SUM (B.Price)。
．在MEASURES 和 DEFINE中, 缺省的都是RUNNING。
．在DEFINE中，FINAL不被允许；如果必要的话，RUNNING可以被用来增加明确性。
在带有ONE ROW PER MATCH的MEASURES中，所有的聚合函数，FIRST和LAST是在匹配的最后一行被识别之后才计算的，所以缺省的RUNNING语义实际上和FINAL语义没有区别。此时用户可能更倾向于把表达式的缺省语义认为是FINAL，或者可以选择把FINAL写出来以增加明确性。
普通的列引用具有滚动(RUNNING)语义。(对于 ALL ROWS PER MATCH，为了在MEASURES中得到最终语义，请使用FINAL LAST 行模式导航操作符，而不是普通的列引用。)
普通的行模式列引用
一个普通的行模式列引用既没有聚合也没有导航，例如：
A.Price
"RUNNING 和 FINAL 关键字对照" 说过，普通的行模式列引用总是具有滚动(RUNNING)语义。这意味着：
在DEFINE中，一个普通的列引用所引用的是截至并包括当前为止，映射到模式变量的最后一行。如果没有这样的行，则其值为NULL。
在MEASURES中，有两种子类别：
如果 ALL ROWS PER MATCH 被指定，那么就有一个当前行的概念，语义和DEFINE中的相同。
如果 ONE ROW PER MATCH 被指定，则从概念上查询被放置在匹配的最后一行。一个普通的列引用所引用的是映射到模式变量的最后一行。如果这个变量没有被映射到任何的行，则其值为NULL。
这些语义和LAST操作符是一样的，缺省都是隐式的RUNNING。所以，一个诸如X.Price的普通的列引用等同于RUNNING LAST (X.Price)。
行模式输出
MATCH_RECOGNIZE的结果被称为行模式输出表。行模式输出表的形态(行的类型)取决于对ONE ROW PER MATCH 或者 ALL ROWS PER MATCH的选择。
如果ONE ROW PER MATCH被指定或者隐含，则行模式输出表的列就是行模式的分区列，顺序就是声明的顺序，后面以声明的顺序跟着行模式度量(MEASURE)列。因为一个表至少必须有一列，这意味着至少必须有一个行模式分区列，或者一个行模式度量列。
如果ALL ROWS PER MATCH 被指定，那么行模式输出表的列就是行模式的分区列(按声明的顺序)，然后是排序列(按声明的顺序)，然后是行模式度量列(按声明的顺序)，最后是行模式输入表所剩下的列，按它们在行模式输入表中出现的顺序。
模式度量列的名称和声明类型取决于MEASURES子句。非度量列的名称和声明类型是从模式输入表中相应的列继承来的。
相关名称和行模式输出
可以为行模式输出表指定相关名称，类似如下：
SELECT M.Matchno
FROM Ticker MATCH_RECOGNIZE (...
MEASURE MATCH_NUMBER() AS Matchno
...
) M
在前述例子中，M就是被赋予行模式输出表的相关名称。指定一个相关名称的好处是，它可以被用来修饰行模式输出表的列名，例如前述例子中的M.Matchno。这对于解析同名的列尤其重要，如果FROM子句中还有其他表的话。
模式匹配的高级主题
本章节讨论下列高级主题：
．在PREV和NEXT中嵌套FIRST和LAST
．处理空匹配或未匹配行
．如果从输出中排除部分模式
．如何表达全排列
在PREV和NEXT中嵌套FIRST和LAST
FIRST 和 LAST 提供了在已经被映射到一个特定模式变量的行的集合之内的导航；PREV和NEXT提供了从一个特定行开始的一个物理偏移量的导航。这些导航可以被结合起来，方式是在PREV或者NEXT之内嵌套FIRST或者LAST。这就允许如下的表达式：
PREV (LAST (A.Price + A.Tax, 1), 3)
在此例中，A必须是一个模式变量。这是行模式列引用所必需的，并且所有的符合操作符中所有的模式变量都必须是同一个（本例中是A）。
这个复合操作符是按照如下来求值的：
1. 内部操作符LAST在映射到模式变量A的行集合上单独操作。在这个集合中，找到倒数第二行（如果这样的行不存在，结果是NULL）。
2. 外部操作符PREV从步骤1中找到的行开始，在行模式分区中再往前三行（如果这样的行不存在，结果是NULL）。
3. 令R为独立于实现方式的范围变量，引用的是步骤2所找到的行。在表达式A.Price + A.Tax中, 将所有出现的模式变量A替换为R。结果表达式R.Price + R.Tax 被求值并且确定了复合导航操作的值。
例如，考虑表20-6中的数据集及映射：
表 20-6 数据集及映射
Row Price Tax Mapping
--------------------------------
R1 10 1
R2 20 2 A
R3 30 3 B
R4 40 4 A
R5 50 5 C
R6 60 6 A
为了对PREV (LAST (A.Price + A.Tax, 1), 3)求值，可以使用下列步骤：
．被映射到A的行集合是{R2, R4, R6}。LAST在这个集合上操作，从末端开始偏移，到达于R4行。
．PREV执行了一个物理偏移，R4之前的3行，到达于R1。
．令R为指向R1的范围变量。R.Price + R.Tax被求值，得到 10+1 = 11。
注意，这个嵌套并不是被定义为典型的嵌套函数的求值方式。内部操作符LAST实际上并不会对表达式A.Price + A.Tax求值; 它用这个表达式来指定一个模式变量(A)并且在映射到该变量的行之内导航。外部操作符PREV在这些行上执行了进一步的物理导航。表达式A.Price + A.Tax 实际上并不是这样被求值的，因为最终到达的行并不一定映射到模式变量A。在这个例子中，R1没有并映射到任何模式变量。
处理空匹配或未匹配行
ALL ROWS PER MATCH 有三个子选项:
．ALL ROWS PER MATCH SHOW EMPTY MATCHES
．ALL ROWS PER MATCH OMIT EMPTY MATCHES
．ALL ROWS PER MATCH WITH UNMATCHED ROWS
这些选项在下列主题中被解释：
．处理空匹配
．处理未匹配行
处理空匹配
有些模式允许空匹配。例如，模式(A*)可以和被映射到A的零行或者多行所匹配。
一个空匹配不会把任何的数据行映射到模式变量；尽管如此，一个空匹配还是有一个起始行。例如，在一个分区的第一行可能有一个空匹配，分区的第二行也有一个空匹配，等等。一个空匹配被赋予一个匹配顺序号，基于其起始行的序号，这和其他的匹配是一样的。
当使用的是ONE ROW PER MATCH, 一个空匹配会导致输出表中产生一行。一个空匹配中的行模式度量被计算如下：
．MATCH_NUMBER()的值是空匹配的匹配顺序号。
．任何的COUNT都是0。
．任何其他的聚合函数，行模式导航操作，或者普通的行模式列引用都是NULL。
至于ALL ROWS PER MATCH，问题就产生了，因为在空匹配中没有数据行，是否应该产生一行输出？为了处理这种情况，有两种选项：
．ALL ROWS PER MATCH SHOW EMPTY MATCHES: 用了此选项，一个空匹配会在行模式输出表中产生单独的一行。
．ALL ROWS PER MATCH OMIT EMPTY MATCHES: 用了此选项，一个空匹配在行模式输出表中会被忽略。（这可能会导致匹配顺序号出现空隙）
ALL ROWS PER MATCH 的缺省是 SHOW EMPTY MATCHES。使用此选项，一个空匹配会在行模式输出表中产生一行。在这个行中：
．CLASSIFIER()函数的值为 null。
．MATCH_NUMBER ()函数的值是空匹配的匹配顺序号。
．任何普通行模式列引用的值都是null。
．任何聚合函数或者行模式导航操作的值都被用一个空集合进行计算（因此任何COUNT都是0, 所有其他的聚合函数和行模式导航操作都是null）。
．与行模式输入表中的任何列相对应的列的值，和空匹配起始行的相应列的值相同。
处理未匹配行
行模式输入表中的某些行，可能既不是一个空匹配的起始行，也没有被映射到一个非空匹配。这样的行被称为未匹配行。
选项ALL ROWS PER MATCH WITH UNMATCHED ROWS会显示空匹配以及未匹配行。空匹配行的处理方式和SHOW EMPTY MATCHES相同。当显示一个未匹配行的时候，所有行模式度量都为null，有点类似于一个外连接的NULL扩展侧。于是，COUNT和MATCH_NUMBER可能被用来区分一个未匹配行和空匹配的起始行。排除语法{- -}被禁止，因为它违背了WITH UNMATCHED ROWS的精神。更多信息请参见“如果从输出中排除部分模式”章节。
一个模式是不可能同时允许空匹配并且又有未匹配行的。原因是，如果行模式输入表中的一个行不能够被映射到一个行模式主变量，那么这一行仍然可以是一个空匹配的起始行，不会被当作未匹配行，假设这个模式允许空匹配的话。因此，如果一个模式允许空匹配，那么ALL ROWS PER MATCH SHOW EMPTY MATCHES的输出就和ALL ROWS PER MATCH WITH UNMATCHED ROWS相同。于是，WITH UNMATCHED ROWS 主要的目的就是为了用于那些不允许空匹配的模式。然而，用户可以选择指定WITH UNMATCHED ROWS，如果用户不确定一个模式是有空匹配还是未匹配行。
注意，如果ALL ROWS PER MATCH WITH UNMATCHED ROWS和缺省的跳转选项一起使用（AFTER MATCH SKIP PAST LAST ROW），那么对于输入的每一行，在输出中就恰好会有一行与之对应。
其它的跳转选项允许和WITH UNMATCHED ROWS一起使用，此时有可能一行被映射到多于一个匹配，并且在行模式输出表中出现多次。未匹配行在输出中只会出现一次。
如何从输出中排除部分模式
当ALL ROWS PER MATCH和OMIT EMPTY MATCHES 或者 SHOW EMPTY MATCHES 子选项一起使用时，模式中部分匹配的行可以从行模式输出表中排除。排除的部分在PATTERN子句中被包围在 {- 和 -} 之间。
例如，下列例子找出起始价格不低于10的最长价格上升期。
例子 20-8 价格上升期
SELECT M.Symbol, M.Tstamp, M.Matchno, M.Classfr, M.Price, M.Avgp
FROM Ticker MATCH_RECOGNIZE (
PARTITION BY Symbol
ORDER BY tstamp
MEASURES FINAL AVG(S.Price) AS Avgp,
CLASSIFIER() AS Classfr,
MATCH_NUMBER() AS Matchno
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST B
PATTERN ( {- A -} B+ {- C+ -} )
SUBSET S = (A,B)
DEFINE
A AS A.Price >= 10,
B AS B.Price > PREV(B.Price),
C AS C.Price <= PREV(C.Price)
) M
ORDER BY symbol, tstamp;
SYMBOL TSTAMP MATCHNO CLAS PRICE AVGP
---------- --------- ---------- ---- ---------- ----------
ACME 02-APR-11 1 B 17 18.8
ACME 03-APR-11 1 B 19 18.8
ACME 04-APR-11 1 B 21 18.8
ACME 05-APR-11 1 B 25 18.8
ACME 07-APR-11 2 B 15 19.2
ACME 08-APR-11 2 B 20 19.2
ACME 09-APR-11 2 B 24 19.2
ACME 10-APR-11 2 B 25 19.2
ACME 13-APR-11 3 B 25 20
ACME 17-APR-11 4 B 14 16.6666667
ACME 18-APR-11 4 B 24 16.6666667
行模式输出表只会显示映射到B的行，映射到A和C的行从输出中被排除了。虽然被排除的行不会出现在行模式输出表中，它们并没有从联合模式变量的定义中被排除，或者从DEFINE或者MEASURES中的标量表达式的计算中被排除。例如，参见前述例子中的模式主变量A和C 的定义，联合模式变量S的定义，或者AVGP行模式度量的定义。
排除语法不允许用于ALL ROWS PER MATCH WITH UNMATCHED ROWS。
排除语法允许用于 ONE ROW PER MATCH, 虽然它没有任何效果，因为此时每个匹配只有一个汇总的行。
如何表达全排列
PERMUTE语法可以用来表达一个由更简单的模式的全排列所构成的模式。例如，PATTERN (PERMUTE (A, B, C))等同于三个模式变量A,B和C的全排列，类似下面的：
PATTERN (A B C | A C B | B A C | B C A | C A B | C B A)
注意，PERMUTE是按次序展开的，并且要排列的每个元素必须和其它元素用逗号隔开。（在这个例子中，因为三个模式变量A,B和C是以字母顺序列出的，跟随着的各种展开的可能性也是以字母顺序列出的。）这是很重要的，因为各种排列是以展开的顺序进行尝试的。因此对(A B C)的匹配尝试发生在对(A C B)的尝试之前，以此类推; 第一个成功的尝试可以被称为“胜者”。
另一个例子：
PATTERN (PERMUTE (X{3}, B C?, D))
这和下面的等价：
PATTERN ((X{3} B C? D)
| (X{3} D B C?)
| (B C? X{3} D)
| (B C? D X{3})
| (D X{3} B C?)
| (D B C? X{3}))
注意模式元素"B C?"没有用逗号隔开，所以它们被看作是一个单独的单元。
模式匹配的规则和限制
本章节讨论如下的规则和限制：
．输入表的要求
．MATCH_RECOGNIZE子句中禁止的嵌套
．拼接的MATCH_RECOGNIZE子句
．聚合函数的限制
输入表的要求
行模式输入表是MATCH_RECOGNIZE的输入参数。你可以用一个表或者一个视图，或者一个带名字的查询（用WITH子句定义）。行模式输入表也可以是一个派生表（亦称内联视图）。例如：
FROM (SELECT S.Name, T.Tstamp, T.Price
FROM Ticker T, SymbolNames S
WHERE T.Symbol = S.Symbol)
MATCH_RECOGNIZE (...) M
行模式输入表不能是一个表的连接。解决方法是使用一个派生表，如下：
FROM (SELECT * FROM A LEFT OUTER JOIN B ON (A.X = B.Y))
MATCH_RECOGNIZE (...) M
模式输入表的列名不能有二义性。如果行模式输入表是一个基表或者视图，这是没问题的，因为SQL不允许在基表或者视图中有重名的列。只有当模式输入表是派生表时才会有问题。例如，考虑一个两表的连接，Emp 和 Dept,它们各自都有一个列叫做Name。下面是一个语法错误：
FROM (SELECT D.Name, E.Name, E.Empno, E.Salary
FROM Dept D, Emp E
WHERE D.Deptno = E.Deptno)
MATCH_RECOGNIZE (
PARTITION BY D.Name
...)
前述例子是一个错误，因为变量D在MATCH_RECOGNIZE中是不可见的（D的范围只是在派生表之内）。
改写成类似如下的也不行：
FROM (SELECT D.Name, E.Name, E.Empno, E.Salary
FROM Dept D, Emp E
WHERE D.Deptno = E.Deptno)
MATCH_RECOGNIZE (
PARTITION BY Name
...)
这个改写去掉了MATCH_RECOGNIZE子句中的变量D，然而，现在的错误是Name不确定，因为在派生表中有两个列名字为Name。处理这种情况的方法是在派生表本身区分列名，类似如下：
FROM (SELECT D.Name AS Dname, E.Name AS Ename,
E.Empno, E.Salary
FROM Dept D, Emp E
WHERE D.Deptno = E.Deptno)
MATCH_RECOGNIZE (
PARTITION BY Dname
...)
参见：
Oracle数据库SQL语言参考
MATCH_RECOGNIZE子句中禁止的嵌套
下列各种嵌套在MATCH_RECOGNIZE子句中是禁止的：
．在一个MATCH_RECOGNIZE子句中嵌套另外一个
．在MEASURES子句或者DEFINE子句中向外引用。这意味着除了行模式输入表之外，一个MATCH_RECOGNIZE不能够引用外层查询的任何表。
．在MEASURES 或者 DEFINE中不能使用相关子查询。并且，MEASURES 或 DEFINE之中的子查询不能引用模式变量。
．MATCH_RECOGNIZE子句不能被用于递归查询。
．SELECT FOR UPDATE 语句不能使用 MATCH_RECOGNIZE子句。
拼接的MATCH_RECOGNIZE子句
注意，把一个MATCH_RECOGNIZE的输出作为另一个的输入是允许的，如下例：
SELECT ...
FROM ( SELECT *
FROM Ticker
MATCH_RECOGNIZE (...) )
MATCH_RECOGNIZE (...)
在这个例子中，第一个MATCH_RECOGNIZE是在一个派生表中，随后为第二个MATCH_RECOGNIZE提供输入。
．聚合函数的限制．
聚合函数COUNT, SUM, AVG, MAX, 和 MIN 可以被用于MEASURES 和 DEFINE子句。DISTINCT关键字不被支持。
模式匹配的例子
本章节包含下列类型的高级模式匹配的例子：
．模式匹配例子：股票市场
．模式匹配例子：安全日志分析
．模式匹配例子：时域化(Sessionization)
．模式匹配例子：金融跟踪
模式匹配例子：股票市场
本章节包含了基于涉及股票价格和模式的常见任务的模式匹配例子。
例子 20-9 指定幅度的下跌
例子20-9中的查询显示了当前价格比前一天的收市价下跌多于指定百分比（在本例中是8%）的股票。
CREATE TABLE Ticker3Wave (SYMBOL VARCHAR2(10), tstamp DATE, PRICE NUMBER);
INSERT INTO Ticker3Wave VALUES('ACME', '01-Apr-11', 1000);
INSERT INTO Ticker3Wave VALUES('ACME', '02-Apr-11', 775);
INSERT INTO Ticker3Wave VALUES('ACME', '03-Apr-11', 900);
INSERT INTO Ticker3Wave VALUES('ACME', '04-Apr-11', 775);
INSERT INTO Ticker3Wave VALUES('ACME', '05-Apr-11', 900);
INSERT INTO Ticker3Wave VALUES('ACME', '06-Apr-11', 775);
INSERT INTO Ticker3Wave VALUES('ACME', '07-Apr-11', 900);
INSERT INTO Ticker3Wave VALUES('ACME', '08-Apr-11', 775);
INSERT INTO Ticker3Wave VALUES('ACME', '09-Apr-11', 800);
INSERT INTO Ticker3Wave VALUES('ACME', '10-Apr-11', 550);
INSERT INTO Ticker3Wave VALUES('ACME', '11-Apr-11', 900);
INSERT INTO Ticker3Wave VALUES('ACME', '12-Apr-11', 800);
INSERT INTO Ticker3Wave VALUES('ACME', '13-Apr-11', 1100);
INSERT INTO Ticker3Wave VALUES('ACME', '14-Apr-11', 800);
INSERT INTO Ticker3Wave VALUES('ACME', '15-Apr-11', 550);
INSERT INTO Ticker3Wave VALUES('ACME', '16-Apr-11', 800);
INSERT INTO Ticker3Wave VALUES('ACME', '17-Apr-11', 875);
INSERT INTO Ticker3Wave VALUES('ACME', '18-Apr-11', 950);
INSERT INTO Ticker3Wave VALUES('ACME', '19-Apr-11', 600);
INSERT INTO Ticker3Wave VALUES('ACME', '20-Apr-11', 300);
SELECT *
FROM Ticker3Wave MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES B.tstamp AS timestamp,
A.price AS Aprice,
B.price AS Bprice,
((B.price - A.price)*100) / A.price AS PctDrop
ONE ROW PER MATCH
AFTER MATCH SKIP TO B
PATTERN (A B)
DEFINE
B AS (B.price - A.price) / A.price < -0.08
);
SYMBOL TIMESTAMP APRICE BPRICE PCTDROP
------ --------- ---------- ------- ----------
ACME 02-APR-11 1000 775 -22.5
ACME 04-APR-11 900 775 -13.888889
ACME 06-APR-11 900 775 -13.888889
ACME 08-APR-11 900 775 -13.888889
ACME 10-APR-11 800 550 -31.25
ACME 12-APR-11 900 800 -11.111111
ACME 14-APR-11 1100 800 -27.272727
ACME 15-APR-11 800 550 -31.25
ACME 19-APR-11 950 600 -36.842105
ACME 20-APR-11 600 300 -50.0
10 rows selected.
例子 20-10 指定幅度的下跌然后回复到原来的价格
例子20-10中的查询扩展了例子20-9中定义的模式。它找到一支下跌超过8%的股票。它也查找当股票价格在原价格之下持续了零天或者更多天。然后它识别出那些涨回原价或者超过初始值的股票。因为知道这种模式出现的天数可能有用，这个天数也被包含了。start_price是一个匹配的起始价格，end_price是匹配的终止价格，它终止于价格等于或者高于起始价的那一天。
SELECT *
FROM Ticker3Wave MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES
A.tstamp as start_timestamp,
A.price as start_price,
B.price as drop_price,
COUNT(C.*)+1 as cnt_days,
D.tstamp as end_timestamp,
D.price as end_price
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B C* D)
DEFINE
B as (B.price - A.price)/A.price < -0.08,
C as C.price < A.price,
D as D.price >= A.price
);
SYMBOL START_TIM START_PRICE DROP_PRICE CNT_DAYS END_TIMES END_PRICE
---------- --------- ----------- ---------- -------- --------- ----------
ACME 01-APR-11 1000 775 11 13-APR-11 1100
ACME 14-APR-11 800 550 1 16-APR-11 800
例子 20-11 在交易历史中找出V形和U形
例子 20-11 显示出在定义一个模式的时候，把数据的所有表现形式都考虑进去是多么的重要。TickerVU表和第一个例子中的Ticker表相似，只是它在第三个谷底连续有两天价格一样，即四月16和17号。这种带平底的下跌称为U形。原来的例子20-1是否能够辨认出这个修改过的数据，它非常像一个V形，并且把U形也包含到输出里去？不行，查询必须修改成下面的样子。
CREATE TABLE TickerVU (SYMBOL VARCHAR2(10), tstamp DATE, PRICE NUMBER);
INSERT INTO TickerVU values('ACME', '01-Apr-11', 12);
INSERT INTO TickerVU values('ACME', '02-Apr-11', 17);
INSERT INTO TickerVU values('ACME', '03-Apr-11', 19);
INSERT INTO TickerVU values('ACME', '04-Apr-11', 21);
INSERT INTO TickerVU values('ACME', '05-Apr-11', 25);
INSERT INTO TickerVU values('ACME', '06-Apr-11', 12);
INSERT INTO TickerVU values('ACME', '07-Apr-11', 15);
INSERT INTO TickerVU values('ACME', '08-Apr-11', 20);
INSERT INTO TickerVU values('ACME', '09-Apr-11', 24);
INSERT INTO TickerVU values('ACME', '10-Apr-11', 25);
INSERT INTO TickerVU values('ACME', '11-Apr-11', 19);
INSERT INTO TickerVU values('ACME', '12-Apr-11', 15);
INSERT INTO TickerVU values('ACME', '13-Apr-11', 25);
INSERT INTO TickerVU values('ACME', '14-Apr-11', 25);
INSERT INTO TickerVU values('ACME', '15-Apr-11', 14);
INSERT INTO TickerVU values('ACME', '16-Apr-11', 12);
INSERT INTO TickerVU values('ACME', '17-Apr-11', 12);
INSERT INTO TickerVU values('ACME', '18-Apr-11', 24);
INSERT INTO TickerVU values('ACME', '19-Apr-11', 23);
INSERT INTO TickerVU values('ACME', '20-Apr-11', 22);
如果你执行例子20-1原来的查询，只是改用这张表名， 会发生什么？
SELECT *
FROM TickerVU MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
DOWN.tstamp AS bottom_tstamp,
UP.tstamp AS end_tstamp
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.symbol, MR.start_tstamp;
SYMBOL START_TST BOTTOM_TS END_TSTAM
---------- --------- --------- ---------
ACME 05-APR-11 06-APR-11 10-APR-11
ACME 10-APR-11 12-APR-11 13-APR-11
它没有显示三行输出（每次下跌一行），只是显示出两行。这是因为没有变量被定义来处理价格下跌的平底。现在，用这个查询修改过的版本，在DEFINE子句中加上了一个变量来处理平底数据，并且在PATTERN子句中使用这个变量。
SELECT *
FROM TickerVU MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
DOWN.tstamp AS bottom_tstamp,
UP.tstamp AS end_tstamp
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST UP
PATTERN (STRT DOWN+ FLAT* UP+)
DEFINE
DOWN AS DOWN.price < PREV(DOWN.price),
FLAT AS FLAT.price = PREV(FLAT.price),
UP AS UP.price > PREV(UP.price)
) MR
ORDER BY MR.symbol, MR.start_tstamp;
SYMBOL START_TST BOTTOM_TS END_TSTAM
---------- --------- --------- ---------
ACME 05-APR-11 06-APR-11 10-APR-11
ACME 10-APR-11 12-APR-11 13-APR-11
ACME 14-APR-11 16-APR-11 18-APR-11
现在，你得到的输出包含数据中所有的三次价格下跌。这里得到的教训是必须考虑你数据序列中所有可能的变化，并且在你的PATTERN, DEFINE, 和 MEASURES子句中把这些可能性按需求包括进去。
例子 20-12 查找艾略特波浪模式：倒V形的多个实例
例子 20-12 显示了一种股票价格模式类型的简化版本，该模式被称为艾略特波浪，它有多个连续的倒V形模式。在这个特定的例子中，模式表达式搜寻的是一天（或多天）上升紧接着一天（或多天）的下降，并且这个序列必须连续出现五次，没有间隔。也即，模式看起来类似于： /\/\/\/\/\
SELECT MR_ELLIOTT.*
FROM Ticker3Wave MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES
COUNT(*) as CNT,
COUNT(P.*) AS CNT_P,
COUNT(Q.*) AS CNT_Q,
COUNT(R.*) AS CNT_R,
COUNT(S.*) AS CNT_S,
COUNT(T.*) AS CNT_T,
COUNT(U.*) AS CNT_U,
COUNT(V.*) AS CNT_V,
COUNT(W.*) AS CNT_W,
COUNT(X.*) AS CNT_X,
COUNT(Y.*) AS CNT_Y,
COUNT(Z.*) AS CNT_Z,
CLASSIFIER() AS CLS,
MATCH_NUMBER() AS MNO
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST Z
PATTERN (P Q+ R+ S+ T+ U+ V+ W+ X+ Y+ Z+)
DEFINE
Q AS Q.price > PREV(Q.price),
R AS R.price < PREV(R.price),
S AS S.price > PREV(S.price),
T AS T.price < PREV(T.price),
U AS U.price > PREV(U.price),
V AS V.price < PREV(V.price),
W AS W.price > PREV(W.price),
X AS X.price < PREV(X.price),
Y AS Y.price > PREV(Y.price),
Z AS Z.price < PREV(Z.price)
) MR_ELLIOTT
ORDER BY symbol, tstamp;
SYMB TSTAMP CNT CNT_P CNT_Q CNT_R CNT_S CNT_T CNT_U CNT_V CNT_W CNT_X CNT_Y CNT_Z CLS MNO PRICE
---- --------- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- --- -----
ACME 02-APR-11 1 1 0 0 0 0 0 0 0 0 0 0 P 1 775
ACME 03-APR-11 2 1 1 0 0 0 0 0 0 0 0 0 Q 1 900
ACME 04-APR-11 3 1 1 1 0 0 0 0 0 0 0 0 R 1 775
ACME 05-APR-11 4 1 1 1 1 0 0 0 0 0 0 0 S 1 900
ACME 06-APR-11 5 1 1 1 1 1 0 0 0 0 0 0 T 1 775
ACME 07-APR-11 6 1 1 1 1 1 1 0 0 0 0 0 U 1 900
ACME 08-APR-11 7 1 1 1 1 1 1 1 0 0 0 0 V 1 775
ACME 09-APR-11 8 1 1 1 1 1 1 1 1 0 0 0 W 1 800
ACME 10-APR-11 9 1 1 1 1 1 1 1 1 1 0 0 X 1 550
ACME 11-APR-11 10 1 1 1 1 1 1 1 1 1 1 0 Y 1 900
ACME 12-APR-11 11 1 1 1 1 1 1 1 1 1 1 1 Z 1 800
11 rows selected.
例子 20-13 查找艾略特波浪并且指定可接受的行数范围
类似于例子20-12, 例子 20-13指定了一个倒V形的艾略特波浪。然而，在这个例子中，每个模式变量使用了规则表达式来指定需要匹配的连续行数，并且这个行数被指定为一个范围。为每个模式变量查找三个或四个连续匹配，使用的是语法"{3,4}"。输出显示的是一个完整匹配的所有的行，并且让你看到每个模式变量的确切起始和终止分别是什么时候。注意，变量W和X各自有四行匹配，而变量Y和在分别只有三行匹配。
CREATE TABLE tickerwavemulti (symbol VARCHAR2(10), tstamp DATE, price NUMBER);
INSERT INTO tickerwavemulti VALUES('ACME', '01-May-10', 36.25 );
INSERT INTO tickerwavemulti VALUES('BLUE', '01-May-10', 177.85);
INSERT INTO tickerwavemulti VALUES('EDGY', '01-May-10', 27.18);
INSERT INTO tickerwavemulti VALUES('ACME', '02-May-10', 36.47);
INSERT INTO tickerwavemulti VALUES('BLUE', '02-May-10', 177.25);
INSERT INTO tickerwavemulti VALUES('EDGY', '02-May-10', 27.41);
INSERT INTO tickerwavemulti VALUES('ACME', '03-May-10', 36.36);
INSERT INTO tickerwavemulti VALUES('BLUE', '03-May-10', 176.16);
INSERT INTO tickerwavemulti VALUES('EDGY', '03-May-10', 27.43);
INSERT INTO tickerwavemulti VALUES('ACME', '04-May-10', 36.25);
INSERT INTO tickerwavemulti VALUES('BLUE', '04-May-10', 176.28);
INSERT INTO tickerwavemulti VALUES('EDGY', '04-May-10', 27.56);
INSERT INTO tickerwavemulti VALUES('ACME', '05-May-10', 36.36);
INSERT INTO tickerwavemulti VALUES('BLUE', '05-May-10', 177.72);
INSERT INTO tickerwavemulti VALUES('EDGY', '05-May-10', 27.31);
INSERT INTO tickerwavemulti VALUES('ACME', '06-May-10', 36.70);
INSERT INTO tickerwavemulti VALUES('BLUE', '06-May-10', 178.36);
INSERT INTO tickerwavemulti VALUES('EDGY', '06-May-10', 27.23);
INSERT INTO tickerwavemulti VALUES('ACME', '07-May-10', 36.50);
INSERT INTO tickerwavemulti VALUES('BLUE', '07-May-10', 178.93);
INSERT INTO tickerwavemulti VALUES('EDGY', '07-May-10', 27.08);
INSERT INTO tickerwavemulti VALUES('ACME', '08-May-10', 36.66);
INSERT INTO tickerwavemulti VALUES('BLUE', '08-May-10', 178.18);
INSERT INTO tickerwavemulti VALUES('EDGY', '08-May-10', 26.90);
INSERT INTO tickerwavemulti VALUES('ACME', '09-May-10', 36.98);
INSERT INTO tickerwavemulti VALUES('BLUE', '09-May-10', 179.15);
INSERT INTO tickerwavemulti VALUES('EDGY', '09-May-10', 26.73);
INSERT INTO tickerwavemulti VALUES('ACME', '10-May-10', 37.08);
INSERT INTO tickerwavemulti VALUES('BLUE', '10-May-10', 180.39);
INSERT INTO tickerwavemulti VALUES('EDGY', '10-May-10', 26.86);
INSERT INTO tickerwavemulti VALUES('ACME', '11-May-10', 37.43);
INSERT INTO tickerwavemulti VALUES('BLUE', '11-May-10', 181.44);
INSERT INTO tickerwavemulti VALUES('EDGY', '11-May-10', 26.78);
INSERT INTO tickerwavemulti VALUES('ACME', '12-May-10', 37.68);
INSERT INTO tickerwavemulti VALUES('BLUE', '12-May-10', 183.11);
INSERT INTO tickerwavemulti VALUES('EDGY', '12-May-10', 26.59);
INSERT INTO tickerwavemulti VALUES('ACME', '13-May-10', 37.66);
INSERT INTO tickerwavemulti VALUES('BLUE', '13-May-10', 181.50);
INSERT INTO tickerwavemulti VALUES('EDGY', '13-May-10', 26.39);
INSERT INTO tickerwavemulti VALUES('ACME', '14-May-10', 37.32);
INSERT INTO tickerwavemulti VALUES('BLUE', '14-May-10', 180.65);
INSERT INTO tickerwavemulti VALUES('EDGY', '14-May-10', 26.31);
INSERT INTO tickerwavemulti VALUES('ACME', '15-May-10', 37.16);
INSERT INTO tickerwavemulti VALUES('BLUE', '15-May-10', 179.51);
INSERT INTO tickerwavemulti VALUES('EDGY', '15-May-10', 26.53);
INSERT INTO tickerwavemulti VALUES('ACME', '16-May-10', 36.98);
INSERT INTO tickerwavemulti VALUES('BLUE', '16-May-10', 180.00);
INSERT INTO tickerwavemulti VALUES('EDGY', '16-May-10', 26.76);
INSERT INTO tickerwavemulti VALUES('ACME', '17-May-10', 37.19);
INSERT INTO tickerwavemulti VALUES('BLUE', '17-May-10', 179.24);
INSERT INTO tickerwavemulti VALUES('EDGY', '17-May-10', 26.63);
INSERT INTO tickerwavemulti VALUES('ACME', '18-May-10', 37.45);
INSERT INTO tickerwavemulti VALUES('BLUE', '18-May-10', 180.48);
INSERT INTO tickerwavemulti VALUES('EDGY', '18-May-10', 26.84);
INSERT INTO tickerwavemulti VALUES('ACME', '19-May-10', 37.79);
INSERT INTO tickerwavemulti VALUES('BLUE', '19-May-10', 181.21);
INSERT INTO tickerwavemulti VALUES('EDGY', '19-May-10', 26.90);
INSERT INTO tickerwavemulti VALUES('ACME', '20-May-10', 37.49);
INSERT INTO tickerwavemulti VALUES('BLUE', '20-May-10', 179.79);
INSERT INTO tickerwavemulti VALUES('EDGY', '20-May-10', 27.06);
INSERT INTO tickerwavemulti VALUES('ACME', '21-May-10', 37.30);
INSERT INTO tickerwavemulti VALUES('BLUE', '21-May-10', 181.19);
INSERT INTO tickerwavemulti VALUES('EDGY', '21-May-10', 27.17);
INSERT INTO tickerwavemulti VALUES('ACME', '22-May-10', 37.08);
INSERT INTO tickerwavemulti VALUES('BLUE', '22-May-10', 179.88);
INSERT INTO tickerwavemulti VALUES('EDGY', '22-May-10', 26.95);
INSERT INTO tickerwavemulti VALUES('ACME', '23-May-10', 37.34);
INSERT INTO tickerwavemulti VALUES('BLUE', '23-May-10', 181.21);
INSERT INTO tickerwavemulti VALUES('EDGY', '23-May-10', 26.71);
INSERT INTO tickerwavemulti VALUES('ACME', '24-May-10', 37.54);
INSERT INTO tickerwavemulti VALUES('BLUE', '24-May-10', 181.94);
INSERT INTO tickerwavemulti VALUES('EDGY', '24-May-10', 26.96);
INSERT INTO tickerwavemulti VALUES('ACME', '25-May-10', 37.69);
INSERT INTO tickerwavemulti VALUES('BLUE', '25-May-10', 180.88);
INSERT INTO tickerwavemulti VALUES('EDGY', '25-May-10', 26.72);
INSERT INTO tickerwavemulti VALUES('ACME', '26-May-10', 37.60);
INSERT INTO tickerwavemulti VALUES('BLUE', '26-May-10', 180.72);
INSERT INTO tickerwavemulti VALUES('EDGY', '26-May-10', 26.47);
INSERT INTO tickerwavemulti VALUES('ACME', '27-May-10', 37.93);
INSERT INTO tickerwavemulti VALUES('BLUE', '27-May-10', 181.54);
INSERT INTO tickerwavemulti VALUES('EDGY', '27-May-10', 26.73);
INSERT INTO tickerwavemulti VALUES('ACME', '28-May-10', 38.17);
INSERT INTO tickerwavemulti VALUES('BLUE', '28-May-10', 182.93);
INSERT INTO tickerwavemulti VALUES('EDGY', '28-May-10', 26.89);
SELECT MR_EW.*
FROM tickerwavemulti MATCH_RECOGNIZE (
PARTITION by symbol
ORDER by tstamp
MEASURES V.tstamp AS START_T,
Z.tstamp AS END_T,
COUNT(V.price) AS CNT_V,
COUNT(W.price) AS UP__W,
COUNT(X.price) AS DWN_X,
COUNT(Y.price) AS UP__Y,
COUNT(Z.price) AS DWN_Z,
MATCH_NUMBER() AS MNO
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST Z
PATTERN (V W{3,4} X{3,4} Y{3,4} Z{3,4})
DEFINE
W AS W.price > PREV(W.price),
X AS X.price < PREV(X.price),
Y AS Y.price > PREV(Y.price),
Z AS Z.price < PREV(Z.price)
) MR_EW
ORDER BY symbol, tstamp;
SYMB TSTAMP START_T END_T CNT_V UP__W DWN_X UP__Y DWN_Z MNO PRICE
---- --------- --------- --------- ----- ----- ----- ----- ----- ----- -------
ACME 08-MAY-10 08-MAY-10 1 0 0 0 0 1 36.66
ACME 09-MAY-10 08-MAY-10 1 1 0 0 0 1 36.98
ACME 10-MAY-10 08-MAY-10 1 2 0 0 0 1 37.08
ACME 11-MAY-10 08-MAY-10 1 3 0 0 0 1 37.43
ACME 12-MAY-10 08-MAY-10 1 4 0 0 0 1 37.68
ACME 13-MAY-10 08-MAY-10 1 4 1 0 0 1 37.66
ACME 14-MAY-10 08-MAY-10 1 4 2 0 0 1 37.32
ACME 15-MAY-10 08-MAY-10 1 4 3 0 0 1 37.16
ACME 16-MAY-10 08-MAY-10 1 4 4 0 0 1 36.98
ACME 17-MAY-10 08-MAY-10 1 4 4 1 0 1 37.19
ACME 18-MAY-10 08-MAY-10 1 4 4 2 0 1 37.45
ACME 19-MAY-10 08-MAY-10 1 4 4 3 0 1 37.79
ACME 20-MAY-10 08-MAY-10 20-MAY-10 1 4 4 3 1 1 37.49
ACME 21-MAY-10 08-MAY-10 21-MAY-10 1 4 4 3 2 1 37.30
ACME 22-MAY-10 08-MAY-10 22-MAY-10 1 4 4 3 3 1 37.08
15 rows selected.
例子 20-14 进入匹配的中间来查看匹配的重叠
例子 20-14 突出了AFTER MATCH SKIP子句在查找重叠匹配方面的威力。它有一个查找W形的简单模式，由模式变量Q,R,S,和T构成。对于W的每条腿（每个侧边），行数可以是一行或多行。匹配还利用了MATCH SKIP TO 子句：当一个匹配被发现，它只会往前跳转到R的最后一个值，这是W形的中点。这使得查询能够在W形中查找这样的匹配：一个W形的后半截是另一个紧跟着的重叠的W形的前半截。在下列的输出中，你可以看到第一个匹配终止于四月五号，但是第二个匹配重叠并且开始于四月三号。
SELECT MR_W.*
FROM Ticker3Wave MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES
MATCH_NUMBER() AS MNO,
P.tstamp AS START_T,
T.tstamp AS END_T,
MAX(P.price) AS TOP_L,
MIN(Q.price) AS BOTT1,
MAX(R.price) AS TOP_M,
MIN(S.price) AS BOTT2,
MAX(T.price) AS TOP_R
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST R
PATTERN ( P Q+ R+ S+ T+ )
DEFINE
Q AS Q.price < PREV(Q.price),
R AS R.price > PREV(R.price),
S AS S.price < PREV(S.price),
T AS T.price > PREV(T.price)
) MR_W
ORDER BY symbol, mno, tstamp;
SYMB TSTAMP MNO START_T END_T TOP_L BOTT1 TOP_M BOTT2 TOP_R PRICE
---- --------- ----- --------- --------- ----- ----- ----- ----- ----- -----
ACME 01-APR-11 1 01-APR-11 1000 1000
ACME 02-APR-11 1 01-APR-11 1000 775 775
ACME 03-APR-11 1 01-APR-11 1000 775 900 900
ACME 04-APR-11 1 01-APR-11 1000 775 900 775 775
ACME 05-APR-11 1 01-APR-11 05-APR-11 1000 775 900 775 900 900
ACME 03-APR-11 2 03-APR-11 900 900
ACME 04-APR-11 2 03-APR-11 900 775 775
ACME 05-APR-11 2 03-APR-11 900 775 900 900
ACME 06-APR-11 2 03-APR-11 900 775 900 775 775
ACME 07-APR-11 2 03-APR-11 07-APR-11 900 775 900 775 900 900
ACME 05-APR-11 3 05-APR-11 900 900
ACME 06-APR-11 3 05-APR-11 900 775 775
ACME 07-APR-11 3 05-APR-11 900 775 900 900
ACME 08-APR-11 3 05-APR-11 900 775 900 775 775
ACME 09-APR-11 3 05-APR-11 09-APR-11 900 775 900 775 800 800
ACME 07-APR-11 4 07-APR-11 900 900
ACME 08-APR-11 4 07-APR-11 900 775 775
ACME 09-APR-11 4 07-APR-11 900 775 800 800
ACME 10-APR-11 4 07-APR-11 900 775 800 550 550
ACME 11-APR-11 4 07-APR-11 11-APR-11 900 775 800 550 900 900
ACME 09-APR-11 5 09-APR-11 800 800
ACME 10-APR-11 5 09-APR-11 800 550 550
ACME 11-APR-11 5 09-APR-11 800 550 900 900
ACME 12-APR-11 5 09-APR-11 800 550 900 800 800
ACME 13-APR-11 5 09-APR-11 13-APR-11 800 550 900 800 1100 1100
ACME 11-APR-11 6 11-APR-11 900 900
ACME 12-APR-11 6 11-APR-11 900 800 800
ACME 13-APR-11 6 11-APR-11 900 800 1100 1100
ACME 14-APR-11 6 11-APR-11 900 800 1100 800 800
ACME 15-APR-11 6 11-APR-11 900 800 1100 550 550
ACME 16-APR-11 6 11-APR-11 16-APR-11 900 800 1100 550 800 800
ACME 17-APR-11 6 11-APR-11 17-APR-11 900 800 1100 550 875 875
ACME 18-APR-11 6 11-APR-11 18-APR-11 900 800 1100 550 950 950
33 rows selected.
例子 20-15 找出指定时间间隔内发生的大宗交易
在例子 20-15中，你要找出大宗交易，即在集中的区间内有大量交易的股票。在这个例子中，大宗交易被定义为一个小时之内出现三次交易并且每次交易超过30,000 股。注意，包含一个诸如B这样的模式变量是很重要的，这样模式就能够接受不符合条件的交易。如果没有B变量，模式只能够匹配连续三个符合条件的交易。
这个例子中的查询使用了stockT04表。
CREATE TABLE STOCKT04 (symbol varchar2(10), tstamp TIMESTAMP,
price NUMBER, volume NUMBER);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.00.00.000000 PM', 35, 35000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.05.00.000000 PM', 35, 15000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.10.00.000000 PM', 35, 5000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.11.00.000000 PM', 35, 42000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.16.00.000000 PM', 35, 7000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.19.00.000000 PM', 35, 5000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.20.00.000000 PM', 35, 5000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.33.00.000000 PM', 35, 55000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.36.00.000000 PM', 35, 15000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.48.00.000000 PM', 35, 15000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 12.59.00.000000 PM', 35, 15000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 01.09.00.000000 PM', 35, 55000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 01.19.00.000000 PM', 35, 55000);
INSERT INTO STOCKT04 VALUES('ACME', '01-Jan-10 01.29.00.000000 PM', 35, 15000);
SELECT *
FROM stockT04 MATCH_RECOGNIZE (
PARTITION BY symbol
ORDER BY tstamp
MEASURES FIRST (A.tstamp) AS in_hour_of_trade,
SUM (A.volume) AS sum_of_large_volumes
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B* A B* A)
DEFINE
A AS ((A.volume > 30000) AND
((A.tstamp - FIRST (A.tstamp)) < '0 01:00:00.00' )),
B AS ((B.volume <= 30000) AND ((B.tstamp - FIRST (A.tstamp)) < '0
01:00:00.00'))
);
SYMBOL IN_HOUR_OF_TRADE SUM_OF_LARGE_VOLUMES
------ ----------------------------- --------------------
ACME 01-JAN-10 12.00.00.000000 PM 132000
1 row selected.
模式匹配例子：安全日志分析
本章节的例子处理一个计算机系统，该系统发出错误信息和认证检查，并把事件存储在一个系统文件中。为了确定是否有安全问题以及其它问题，你需要分析系统文件。这个行为也称作日志梳理，因为软件会梳理文件并查找有问题的项目。注意这些例子中的源数据没有被显示，因为它会用到太多空间。在这些例子中，AUTHENLOG表来自日志文件。
例子 20-16 连续四个或者更多的相同信息
例子20-16中的查询会在三种可能的错误类型(errtype): error, notice, 和 warn的集合之中查找连续四个或者更多的相同信息。
SELECT MR_SEC.ERRTYPE,
MR_SEC.MNO AS Pattern,
MR_SEC.CNT AS Count,
SUBSTR(MR_SEC.MSG_W, 1, 30) AS Message,
MR_SEC.START_T AS Starting_on,
MR_SEC.END_T AS Ending_on
FROM AUTHENLOG
MATCH_RECOGNIZE(
PARTITION BY errtype
ORDER BY tstamp
MEASURES
S.tstamp AS START_T,
W.tstamp AS END_T,
W.message AS MSG_W,
COUNT(*) AS CNT,
MATCH_NUMBER() AS MNO
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ( S W{3,} )
DEFINE W AS W.message = PREV (W.message)
) MR_SEC
ORDER BY ErrType, Pattern;
ERRTYP PATTERN COUNT MESSAGE STARTING_ON ENDING_ON
------ ------- ----- ------------------- ---------------------------- ----------------------------
error 1 4 script not found or 09-JAN-10 12.00.06.000006 PM 09-JAN-10 12.00.15.000015 PM
error 2 4 File does not exist 04-FEB-10 12.00.18.000018 PM 04-FEB-10 12.00.23.000023 PM
error 3 4 File does not exist 06-FEB-10 12.00.25.000025 PM 06-FEB-10 12.00.33.000033 PM
error 4 4 File does not exist 13-FEB-10 12.00.19.000019 PM 14-FEB-10 12.00.07.000007 PM
error 5 5 File does not exist 28-FEB-10 12.00.27.000027 PM 28-FEB-10 12.00.34.000034 PM
error 6 4 script not found or 05-APR-10 12.00.19.000019 PM 05-MAR-10 12.00.23.000023 PM
error 7 4 File does not exist 07-MAR-10 12.00.31.000031 PM 08-MAR-10 12.00.02.000002 PM
error 8 4 File does not exist 14-MAR-10 12.00.19.000019 PM 15-MAR-10 12.00.00.000000 PM
error 9 4 File does not exist 20-MAR-10 12.00.02.000002 PM 20-MAR-10 12.00.06.000006 PM
error 10 5 File does not exist 28-APR-10 12.00.24.000024 PM 28-APR-10 12.00.31.000031 PM
error 11 5 script not found or 01-MAY-10 12.00.15.000015 PM 02-MAY-10 12.00.11.000011 PM
error 12 5 user jsmith: authen 02-MAY-10 12.00.54.000054 PM 03-MAY-10 12.00.11.000011 PM
error 13 4 File does not exist 09-MAY-10 12.00.46.000046 PM 10-MAY-10 12.00.01.000001 PM
error 14 4 File does not exist 20-MAY-10 12.00.42.000042 PM 20-MAY-10 12.00.47.000047 PM
error 15 4 user jsmith: authen 21-MAY-10 12.00.08.000008 PM 21-MAY-10 12.00.18.000018 PM
error 16 4 File does not exist 24-MAY-10 12.00.07.000007 PM 25-MAY-10 12.00.01.000001 PM
error 17 4 user jsmith: authen 12-JUN-10 12.00.00.000000 PM 12-JUN-10 12.00.07.000007 PM
error 18 4 script not found or 12-JUN-10 12.00.18.000018 PM 13-JUN-10 12.00.01.000001 PM
error 19 4 File does not exist 17-JUN-10 12.00.23.000023 PM 17-JUN-10 12.00.30.000030 PM
error 20 5 File does not exist 21-JUN-10 12.00.31.000031 PM 22-JUN-10 12.00.01.000001 PM
error 21 4 user jsmith: authen 22-JUN-10 12.00.36.000036 PM 22-JUN-10 12.00.56.000056 PM
error 22 4 File does not exist 08-JUL-10 12.00.29.000029 PM 08-JUL-10 12.00.32.000032 PM
error 23 6 user jsmith: authen 10-JUL-10 12.00.43.000043 PM 11-JUL-10 12.00.06.000006 PM
error 24 4 File does not exist 12-JUL-10 12.00.09.000009 PM 12-JUL-10 12.00.22.000022 PM
error 25 4 File does not exist 26-JUL-10 12.00.18.000018 PM 27-JUL-10 12.00.04.000004 PM
error 26 4 File does not exist 03-AUG-10 12.00.02.000002 PM 03-AUG-10 12.00.11.000011 PM
error 27 4 File does not exist 23-AUG-10 12.00.04.000004 PM 23-AUG-10 12.00.18.000018 PM
error 28 5 File does not exist 24-AUG-10 12.00.09.000009 PM 26-AUG-10 12.00.00.000000 PM
error 29 4 script not found or 09-SEP-10 12.00.03.000003 PM 09-SEP-10 12.00.09.000009 PM
error 30 4 script not found or 11-SEP-10 12.00.22.000022 PM 11-SEP-10 12.00.31.000031 PM
error 31 4 script not found or 23-SEP-10 12.00.09.000009 PM 23-SEP-10 12.00.16.000016 PM
error 32 5 script not found or 17-OCT-10 12.00.02.000002 PM 18-OCT-10 12.00.09.000009 PM
error 33 4 File does not exist 20-OCT-10 12.00.35.000035 PM 21-OCT-10 12.00.00.000000 PM
error 34 5 File does not exist 21-OCT-10 12.00.16.000016 PM 21-OCT-10 12.00.35.000035 PM
error 35 4 File does not exist 26-OCT-10 12.00.25.000025 PM 26-OCT-10 12.00.35.000035 PM
error 36 4 user jsmith: authen 26-OCT-10 12.00.43.000043 PM 26-OCT-10 12.00.49.000049 PM
error 37 4 user jsmith: authen 01-NOV-10 12.00.35.000035 PM 01-NOV-10 12.00.39.000039 PM
error 38 4 File does not exist 09-NOV-10 12.00.46.000046 PM 10-NOV-10 12.00.09.000009 PM
error 39 4 user jsmith: authen 11-NOV-10 12.00.14.000014 PM 11-NOV-10 12.00.30.000030 PM
error 40 4 user jsmith: authen 22-NOV-10 12.00.46.000046 PM 23-NOV-10 12.00.07.000007 PM
error 41 4 script not found or 03-DEC-10 12.00.14.000014 PM 03-DEC-10 12.00.27.000027 PM
error 42 5 File does not exist 07-DEC-10 12.00.02.000002 PM 07-DEC-10 12.00.37.000037 PM
error 43 4 user jsmith: authen 11-DEC-10 12.00.06.000006 PM 11-DEC-10 12.00.11.000011 PM
error 44 4 user jsmith: authen 19-DEC-10 12.00.26.000026 PM 20-DEC-10 12.00.04.000004 PM
error 45 4 user jsmith: authen 25-DEC-10 12.00.11.000011 PM 25-DEC-10 12.00.17.000017 PM
error 46 4 File does not exist 04-JAN-11 12.00.09.000009 PM 04-JAN-11 12.00.19.000019 PM
error 47 4 user jsmith: authen 10-JAN-11 12.00.23.000023 PM 11-JAN-11 12.00.03.000003 PM
error 48 4 File does not exist 11-JAN-11 12.00.14.000014 PM 11-JAN-11 12.00.24.000024 PM
notice 1 4 Child 3228: Release 08-JAN-10 12.00.38.000038 PM 09-JAN-10 12.00.02.000002 PM
notice 2 4 Child 3228: Release 16-JAN-10 12.00.10.000010 PM 17-JAN-10 12.00.13.000013 PM
notice 3 4 Child 1740: Startin 28-JAN-10 12.00.17.000017 PM 28-JAN-10 12.00.22.000022 PM
notice 4 4 Child 1740: Child p 08-MAR-10 12.00.37.000037 PM 08-MAR-10 12.00.40.000040 PM
notice 5 4 Child 3228: All wor 19-APR-10 12.00.10.000010 PM 19-APR-10 12.00.15.000015 PM
notice 6 4 Child 1740: Acquire 02-MAY-10 12.00.38.000038 PM 02-MAY-10 12.00.46.000046 PM
notice 7 4 Child 1740: Starting 09-MAY-10 12.00.03.000003 PM 09-MAY-10 12.00.08.000008 PM
notice 8 4 Child 3228: Child pr 18-MAY-10 12.00.38.000038 PM 18-MAY-10 12.00.45.000045 PM
notice 9 4 Child 3228: All work 25-JUL-10 12.00.04.000004 PM 25-JUL-10 12.00.09.000009 PM
notice 10 4 Child 3228: All work 24-AUG-10 12.00.11.000011 PM 24-AUG-10 12.00.18.000018 PM
notice 11 4 Child 1740: Starting 19-SEP-10 12.00.05.000005 PM 19-SEP-10 12.00.15.000015 PM
notice 12 4 Child 1740: Acquired 06-OCT-10 12.00.07.000007 PM 06-OCT-10 12.00.13.000013 PM
notice 13 4 Child 1740: Starting 09-JAN-11 12.00.12.000012 PM 09-JAN-11 12.00.18.000018 PM
warn 1 3448 The ScriptAlias dire 01-JAN-10 12.00.00.000000 PM 17-JAN-11 12.00.18.000018 PM
62 rows selected.
例子 20-17 连续四个或更多的认证失败记录
在例子 20-17中, 你要找的是连续四个或更多的认证失败记录，不管来自哪个IP源地址。输出显示了两个匹配，第一个有五行，最后一个有四行。
SELECT MR_SEC2.ERRTYPE AS Authen,
MR_SEC2.MNO AS Pattern,
MR_SEC2.CNT AS Count,
MR_SEC2.IPADDR AS On_IP,
MR_SEC2.TSTAMP AS Occurring_on
FROM AUTHENLOG
MATCH_RECOGNIZE(
PARTITION BY errtype
ORDER BY tstamp
MEASURES
COUNT(*) AS CNT,
MATCH_NUMBER() AS MNO
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST W
PATTERN ( S W{3,} )
DEFINE S AS S.message LIKE '%authenticat%',
W AS W.message = PREV (W.message)
) MR_SEC2
ORDER BY Authen, Pattern, Count;
AUTHEN PATTERN COUNT ON_IP OCCURRING_ON
------ ------- --------- ------------ ----------------------------
error 1 1 10.111.112.3 02-MAY-10 12.00.54.000054 PM
error 1 2 10.111.112.6 03-MAY-10 12.00.07.000007 PM
error 1 3 10.111.112.6 03-MAY-10 12.00.08.000008 PM
error 1 4 10.111.112.6 03-MAY-10 12.00.09.000009 PM
error 1 5 10.111.112.6 03-MAY-10 12.00.11.000011 PM
error 2 1 10.111.112.5 21-MAY-10 12.00.08.000008 PM
error 2 2 10.111.112.6 21-MAY-10 12.00.16.000016 PM
error 2 3 10.111.112.4 21-MAY-10 12.00.17.000017 PM
error 2 4 10.111.112.6 21-MAY-10 12.00.18.000018 PM
error 3 1 10.111.112.5 12-JUN-10 12.00.00.000000 PM
error 3 2 10.111.112.4 12-JUN-10 12.00.04.000004 PM
error 3 3 10.111.112.3 12-JUN-10 12.00.06.000006 PM
error 3 4 10.111.112.3 12-JUN-10 12.00.07.000007 PM
error 4 1 10.111.112.5 22-JUN-10 12.00.36.000036 PM
error 4 2 10.111.112.5 22-JUN-10 12.00.50.000050 PM
error 4 3 10.111.112.5 22-JUN-10 12.00.53.000053 PM
error 4 4 10.111.112.6 22-JUN-10 12.00.56.000056 PM
error 5 1 10.111.112.4 10-JUL-10 12.00.43.000043 PM
error 5 2 10.111.112.6 10-JUL-10 12.00.48.000048 PM
error 5 3 10.111.112.6 10-JUL-10 12.00.51.000051 PM
error 5 4 10.111.112.3 11-JUL-10 12.00.00.000000 PM
error 5 5 10.111.112.5 11-JUL-10 12.00.04.000004 PM
error 5 6 10.111.112.3 11-JUL-10 12.00.06.000006 PM
error 6 1 10.111.112.4 26-OCT-10 12.00.43.000043 PM
error 6 2 10.111.112.4 26-OCT-10 12.00.47.000047 PM
error 6 3 10.111.112.4 26-OCT-10 12.00.48.000048 PM
error 6 4 10.111.112.5 26-OCT-10 12.00.49.000049 PM
error 7 1 10.111.112.3 01-NOV-10 12.00.35.000035 PM
error 7 2 10.111.112.5 01-NOV-10 12.00.37.000037 PM
error 7 3 10.111.112.5 01-NOV-10 12.00.38.000038 PM
error 7 4 10.111.112.3 01-NOV-10 12.00.39.000039 PM
error 8 1 10.111.112.6 11-NOV-10 12.00.14.000014 PM
error 8 2 10.111.112.5 11-NOV-10 12.00.20.000020 PM
error 8 3 10.111.112.6 11-NOV-10 12.00.24.000024 PM
error 8 4 10.111.112.3 11-NOV-10 12.00.30.000030 PM
error 9 1 10.111.112.5 22-NOV-10 12.00.46.000046 PM
error 9 2 10.111.112.5 22-NOV-10 12.00.51.000051 PM
error 9 3 10.111.112.3 23-NOV-10 12.00.06.000006 PM
error 9 4 10.111.112.3 23-NOV-10 12.00.07.000007 PM
error 10 1 10.111.112.5 11-DEC-10 12.00.06.000006 PM
error 10 2 10.111.112.4 11-DEC-10 12.00.07.000007 PM
error 10 3 10.111.112.5 11-DEC-10 12.00.08.000008 PM
error 10 4 10.111.112.6 11-DEC-10 12.00.11.000011 PM
error 11 1 10.111.112.5 19-DEC-10 12.00.26.000026 PM
error 11 2 10.111.112.5 20-DEC-10 12.00.01.000001 PM
error 11 3 10.111.112.4 20-DEC-10 12.00.03.000003 PM
error 11 4 10.111.112.3 20-DEC-10 12.00.04.000004 PM
error 12 1 10.111.112.4 25-DEC-10 12.00.11.000011 PM
error 12 2 10.111.112.4 25-DEC-10 12.00.12.000012 PM
error 12 3 10.111.112.4 25-DEC-10 12.00.16.000016 PM
error 12 4 10.111.112.3 25-DEC-10 12.00.17.000017 PM
error 13 1 10.111.112.6 10-JAN-11 12.00.23.000023 PM
error 13 2 10.111.112.6 11-JAN-11 12.00.00.000000 PM
error 13 3 10.111.112.3 11-JAN-11 12.00.02.000002 PM
error 13 4 10.111.112.4 11-JAN-11 12.00.03.000003 PM
55 rows selected.
例子 20-18 来自同一个IP地址的认证失败记录
例子20-18中的查询和例子20-17类似，但是它查找的是来自同一个IP源地址的认证失败记录，连续出现三次或更多。
SELECT MR_S3.MNO AS Pattern, MR_S3.CNT AS Count,
MR_S3.ERRTYPE AS Type, MR_S3.IPADDR AS On_IP_addr,
MR_S3.START_T AS Starting_on, MR_S3.END_T AS Ending_on
FROM AUTHENLOG
MATCH_RECOGNIZE(
PARTITION BY errtype
ORDER BY tstamp
MEASURES
S.tstamp AS START_T,
W.tstamp AS END_T,
W.ipaddr AS IPADDR,
COUNT(*) AS CNT,
MATCH_NUMBER() AS MNO
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST W
PATTERN ( S W{2,} )
DEFINE S AS S.message LIKE '%authenticat%',
W AS W.message = PREV (W.message)
AND W.ipaddr = PREV (W.ipaddr)
) MR_S3
ORDER BY Type, Pattern;
PATTERN COUNT TYPE ON_IP_ADDR STARTING_ON ENDING_ON
------- ----- ----- ------------ ---------------------------- ----------------------------
1 4 error 10.111.112.6 03-MAY-10 12.00.07.000007 PM 03-MAY-10 12.00.11.000011 PM
2 3 error 10.111.112.5 22-JUN-10 12.00.36.000036 PM 22-JUN-10 12.00.53.000053 PM
3 3 error 10.111.112.4 27-JUN-10 12.00.03.000003 PM 27-JUN-10 12.00.08.000008 PM
4 3 error 10.111.112.6 19-JUL-10 12.00.15.000015 PM 19-JUL-10 12.00.17.000017 PM
5 3 error 10.111.112.4 26-OCT-10 12.00.43.000043 PM 26-OCT-10 12.00.48.000048 PM
6 3 error 10.111.112.4 25-DEC-10 12.00.11.000011 PM 25-DEC-10 12.00.16.000016 PM
7 3 error 10.111.112.5 12-JAN-11 12.00.01.000001 PM 12-JAN-11 12.00.08.000008 PM
7 rows selected.
模式匹配例子：时域化(Sessionization)
时域化(Sessionization)是定义用户活动的会话的过程，通常涉及一个会话中的多个事件。模式匹配使得时域化的查询更容易表达。例如，你可能想知道你的网站访问者在一个典型的会话中会浏览多少个页面。如果你是通讯服务供应商，你可能想知道两个用户之间发生掉线和重拨的电话通话的特征。企业可以从了解他们的用户会话行为得到显著的价值，因为它可以帮助企业定义服务产品和增强功能，定价，营销等等。下列例子包含了两个和网站点击流相关的行业时域化案例，随后是一个涉及电话通话的例子。
例子 20-19 点击流数据的简单时域化
例子 20-19 是一个点击流数据分析的时域化的简单演示。对于一系列的行，目标是要把会话侦测出来，为每个会话赋予一个会话ID，然后显示每个输入行及其会话ID。下面的数据来自一个网页服务器的系统日志，它保存着所有的页面请求。你从一系列的数据行开始，每一行是一个用户的页面请求事件。在这个简单的例子中，数据包含了一个分区键，它就是用户ID，一个时间戳，它指示着用户请求一个页面的时间。网页服务器的日志显示了一个用户请求一个指定页面的时间，但是没有说明用户何时停止查看该页面。
在例子20-19中，一个会话被定义为一个或者多个按时间排序的、分区键（USER_ID）相同的行，其时间戳的时间间隔小于一个指定的阈值。在这个例子中，阈值是10个时间单位。如果不同行之间的时间戳间隔大于10个时间单位，它们就被认为是不同的会话。注意这里使用的10单元阈值是一个假想的值: 每个现实世界的用例都要求分析员的判断来确定最合适的时间间隔阈值。在历史上，30分钟的间隔是一个普遍被用来区分网站访问的会话的阈值。
从创建点击流事件的表开始。
CREATE TABLE Events(
Time_Stamp NUMBER,
User_ID VARCHAR2(10)
);
然后插入数据。下面的插入语句已经被排序并且隔开，然你阅读更方便，使得你可以看到分区以及里面的会话。在现实生活中，事件是以时间顺序到达的，不同会话的数据行是相互间杂的。
INSERT INTO Events(Time_Stamp, User_ID) VALUES ( 1, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (11, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (23, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (34, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (44, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (53, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (63, 'Mary');
INSERT INTO Events(Time_Stamp, User_ID) VALUES ( 3, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (13, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (23, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (33, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (43, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (54, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (63, 'Richard');
INSERT INTO Events(Time_Stamp, User_ID) VALUES ( 2, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (12, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (22, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (32, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (43, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (47, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (48, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (59, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (60, 'Sam');
INSERT INTO Events(Time_Stamp, User_ID) VALUES (68, 'Sam');
下面的行模式匹配查询这现实每个输入行的SESSION_ID。如上面所述，如果事件之间的间隔是10个时间单位或者更少，则被认为是同一个会话。会话的阈值被表达在DEFINE子句的模式变量中。
SELECT time_stamp, user_id, session_id
FROM Events MATCH_RECOGNIZE
(PARTITION BY User_ID ORDER BY Time_Stamp
MEASURES match_number() AS session_id
ALL ROWS PER MATCH
PATTERN (b s*)
DEFINE
s AS (s.Time_Stamp - prev(Time_Stamp) <= 10)
)
ORDER BY user_id, time_stamp;
The output will be:
TIME_STAMP USER_ID SESSION_ID
---------- ---------- ----------
1 Mary 1
11 Mary 1
23 Mary 2
34 Mary 3
44 Mary 3
53 Mary 3
63 Mary 3
3 Richard 1
13 Richard 1
23 Richard 1
33 Richard 1
43 Richard 1
54 Richard 2
63 Richard 2
2 Sam 1
12 Sam 1
22 Sam 1
32 Sam 1
43 Sam 2
47 Sam 2
48 Sam 2
59 Sam 3
60 Sam 3
68 Sam 3
24 rows selected.
时域化数据的聚合
像上述例子中把会话序号赋予明细级别的数据行，仅仅是分析过程的开始。时域化数据的商业价值仅仅在按会话聚合之后才呈现出来。
例子 20-20 带有聚合的简单时域化
例子20-20把数据进行聚合，每个会话给出一行，带有这些列： Session_ID, User_ID, 每个会话总有几个被聚合的事件，会话持续的总时间。这个输出使得我们很容易看到每个会话有多少用户点击，以及每个会话持续多长时间。反过来，这个查询的数据可以被用来驱动许多其他的分析，如最大，最小和平均会话持续时间。
SELECT session_id, user_id, start_time, no_of_events, duration
FROM Events MATCH_RECOGNIZE
(PARTITION BY User_ID
ORDER BY Time_Stamp
MEASURES MATCH_NUMBER() session_id,
COUNT(*) AS no_of_events,
FIRST(time_stamp) start_time,
LAST(time_stamp) - FIRST(time_stamp) duration
PATTERN (b s*)
DEFINE
s AS (s.Time_Stamp - PREV(Time_Stamp) <= 10)
)
ORDER BY user_id, session_id;
The output will be:
SESSION_ID USER_ID START_TIME NO_OF_EVENTS DURATION
---------- ---------- ---------- ------------ ----------
1 Mary 1 2 10
2 Mary 23 1 0
3 Mary 34 4 29
1 Richard 3 5 40
2 Richard 54 2 9
1 Sam 2 4 30
2 Sam 43 3 5
3 Sam 59 3 9
8 rows selected.
掉线通话的时域化
在点击流数据的例子中，源数据中并没有明确的终止点来指明查看一个页面的终止时间。即使有用户活动的明确终止点，一个终止点也未必意味着用户想要终止会话。考虑一下有一个人正在用移动电话服务，他的电话掉线了：通常情况下，用户会重拨并且继续通话。在这种情况下，涉及了同一对电话号码的多个通话应该被认为是同一个电话通话。
例子 20-21 掉线通话的时域化
例子20-21演示了电话通话记录的时域化。它使用了电话通话的明细记录数据作为时域化的基础，通话记录包含了起始时间(Start_Time), 终止时间(End_Time), 主叫ID(Caller_ID), 被叫ID(Callee_ID)。查询做了如下的事：
．根据主叫和被叫将数据分区。
．找出那些从主叫到被叫的多个通话，如果相继通话之间的间隔少于60秒的阈值就可以划分为同一个会话。这个阈值是在DEFINE子句中的模式变量B中指定的。
．为每个会话返回（见MEASURES子句）：
．．session_id, 主叫和被叫
．．在一个会话中开启了几次通话
．．有效通话的总时长(在会话中电话连通的总时长)
．．中断的总时长(在会话中电话掉线的总时长)
SELECT Caller, Callee, Start_Time, Effective_Call_Duration,
(End_Time - Start_Time) - Effective_Call_Duration
AS Total_Interruption_Duration, No_Of_Restarts, Session_ID
FROM my_cdr MATCH_RECOGNIZE
( PARTITION BY Caller, Callee ORDER BY Start_Time
MEASURES
A.Start_Time AS Start_Time,
End_Time AS End_Time,
SUM(End_Time - Start_Time) AS Effective_Call_Duration,
COUNT(B.*) AS No_Of_Restarts,
MATCH_NUMBER() AS Session_ID
PATTERN (A B*)
DEFINE B AS B.Start_Time - PREV(B.end_Time) < 60
);
因为前述查询需要大量数据才有意义，而这会消耗大量空间，所以这里没有包含INSERT语句。然而，下面是输出的样本。
SQL> desc my_cdr
Name Null? Type
-------------- ---------- ----------
CALLER NOT NULL NUMBER(38)
CALLEE NOT NULL NUMBER(38)
START_TIME NOT NULL NUMBER(38)
END_TIME NOT NULL NUMBER(38)
SELECT * FROM my_cdr ORDER BY 1, 2, 3, 4;
CALLER CALLEE START_TIME END_TIME
------ ------ ---------- ---------
1 7 1354 1575
1 7 1603 1829
1 7 1857 2301
1 7 2320 2819
1 7 2840 2964
1 7 64342 64457
1 7 85753 85790
1 7 85808 85985
1 7 86011 86412
1 7 86437 86546
1 7 163436 163505
1 7 163534 163967
1 7 163982 164454
1 7 214677 214764
1 7 214782 215248
1 7 216056 216271
1 7 216297 216728
1 7 216747 216853
1 7 261138 261463
1 7 261493 261864
1 7 261890 262098
1 7 262115 262655
1 7 301931 302226
1 7 302248 302779
1 7 302804 302992
1 7 303015 303258
1 7 303283 303337
1 7 383019 383378
1 7 383407 383534
1 7 424800 425096
30 rows selected.
CALLER CALLEE START_TIME EFFECTIVE_CALL TOTAL_INTERUPTION NO_OF_RE SESSION_ID
------ ------- --------- -------------- ----------------- -------- ----------
1 7 1354 1514 96 4 1
1 7 64342 115 0 0 2
1 7 85753 724 69 3 3
1 7 163436 974 44 2 4
1 7 214677 553 18 1 5
1 7 216056 752 45 2 6
1 7 261138 1444 73 3 7
1 7 301931 1311 95 4 8
1 7 383019 486 29 1 9
1 7 424800 296 0 0 10
10 rows selected.
模式匹配例子：金融跟踪
一个普遍的金融应用是搜寻可疑的金融模式。例子20-22演示了如何侦测可疑的转账，因为它们满足了你所定义的特定的异常标准。
例子 20-22 可疑的转账
在例子20-22中，我们搜寻一种看起来可疑的转移资金的模式。在这个例子中，它被定义为30天内的三个或者更多的小额（低于2000元）转账，在最后一个小额转账之后的10天内紧跟着一个大额转账（高于一百万）。为了简化，表和数据都被处理成基础的样子。
首先，我们创建一张表，包含必要的数据：
CREATE TABLE event_log
( time DATE,
userid VARCHAR2(30),
amount NUMBER(10),
event VARCHAR2(10),
transfer_to VARCHAR2(10));
然后我们插入数据到event_log:
INSERT INTO event_log VALUES
(TO_DATE('01-JAN-2012', 'DD-MON-YYYY'), 'john', 1000000, 'deposit', NULL);
INSERT INTO event_log VALUES
(TO_DATE('05-JAN-2012', 'DD-MON-YYYY'), 'john', 1200000, 'deposit', NULL);
INSERT INTO event_log VALUES
(TO_DATE('06-JAN-2012', 'DD-MON-YYYY'), 'john', 1000, 'transfer', 'bob');
INSERT INTO event_log VALUES
(TO_DATE('15-JAN-2012', 'DD-MON-YYYY'), 'john', 1500, 'transfer', 'bob');
INSERT INTO event_log VALUES
(TO_DATE('20-JAN-2012', 'DD-MON-YYYY'), 'john', 1500, 'transfer', 'allen');
INSERT INTO event_log VALUES
(TO_DATE('23-JAN-2012', 'DD-MON-YYYY'), 'john', 1000, 'transfer', 'tim');
INSERT INTO event_log VALUES
(TO_DATE('26-JAN-2012', 'DD-MON-YYYY'), 'john', 1000000, 'transfer', 'tim');
INSERT INTO event_log VALUES
(TO_DATE('27-JAN-2012', 'DD-MON-YYYY'), 'john', 500000, 'deposit', NULL);
然后我们可以查询这张表：
SELECT userid, first_t, last_t, amount
FROM (SELECT * FROM event_log WHERE event = 'transfer')
MATCH_RECOGNIZE
(PARTITION BY userid ORDER BY time
MEASURES FIRST(x.time) first_t, y.time last_t, y.amount amount
PATTERN ( x{3,} y )
DEFINE x AS (event='transfer' AND amount < 2000),
y AS (event='transfer' AND amount >= 1000000 AND
LAST(x.time) - FIRST(x.time) < 30 AND
y.time - LAST(x.time) < 10));
USERID FIRST_T LAST_T AMOUNT
---------- --------- --------- -------
john 06-JAN-12 26-JAN-12 1000000
在这个语句中，第一个加粗的文本表示小额转账，第二个表示大额转账，第三个表示小儿转账发生在30天内，第四个表示大额转账发生在最后一个小额转账的10天内。
这个语句可以被进一步细化以包含可疑转账的收款人，如下所示：
SELECT userid, first_t, last_t, amount, transfer_to
FROM (SELECT * FROM event_log WHERE event = 'transfer')
MATCH_RECOGNIZE
(PARTITION BY userid ORDER BY time
MEASURES z.time first_t, y.time last_t, y.amount amount,
y.transfer_to transfer_to
PATTERN ( z x{2,} y )
DEFINE z AS (event='transfer' AND amount < 2000),
x AS (event='transfer' AND amount <= 2000 AND
PREV(x.transfer_to) <> x.transfer_to),
y AS (event='transfer' AND amount >= 1000000 AND
LAST(x.time) - z.time < 30 AND
y.time - LAST(x.time) < 10 AND
SUM(x.amount) + z.amount < 20000);
USERID FIRST_T LAST_T AMOUNT TRANSFER_TO
---------- --------- --------- ------- -----------
john 15-JAN-12 26-JAN-12 1000000 tim
在这个语句中，第一个加粗文本表示第一笔小额转账，第二个表示两次或更多到不同账户的小额转账，第三个表示小额转账的总和小于20000元。
