行模式识别

1. 概述

IoTDB 支持行模式识别，该功能支持通过定义模式变量的识别逻辑以及正则表达式来捕获一段连续的数据，并对每一段捕获的数据进行分析计算，适用于识别时序数据中的特定模式、检测特定事件等业务场景。如果将行模式识别看作对数据进行分组处理，则核心流程大致如下：

  • 通过 PATTERN、DEFINE、SUBSET 子句进行分组捕获
  • 通过 MEASURES 子句对捕获的分组进行计算处理
  • 通过 ROWS PER MATCH 子句设定分组的输出形式
  • 通过 AFTER MATCH SKIP 子句设定如何定位下一个分组的开始位置

注意：该功能从 V 2.0.5 版本开始提供。

2. 功能介绍

2.1 语法格式

MATCH_RECOGNIZE (
  [ PARTITION BY column [, ...] ]
  [ ORDER BY column [, ...] ]
  [ MEASURES measure_definition [, ...] ]
  [ ROWS PER MATCH ]
  [ AFTER MATCH skip_to ]
  PATTERN ( row_pattern )
  [ SUBSET subset_definition [, ...] ]
  DEFINE variable_definition [, ...]
)

说明：

  • PARTITION BY ： 可选，用于对输入表进行分组，每个分组能独立进行模式匹配。如果未声明该子句，则整个输入表将作为一个整体进行处理。
  • ORDER BY ：可选，用于确保输入数据按某种顺序进行匹配处理。
  • MEASURES ：可选，用于指定从匹配到的一段数据中提取哪些信息。
  • ROWS PER MATCH ：可选，用于指定模式匹配成功后结果集的输出方式。
  • AFTER MATCH SKIP ：可选，用于指定在识别到一个非空匹配后，下一次模式匹配应从哪一行继续进行。
  • PATTERN ：用于定义需要匹配的行模式。
  • SUBSET ：可选，用于将多个基本模式变量所匹配的行合并为一个逻辑集合。
  • DEFINE ：用于定义行模式的基本模式变量。

2.2 DEFINE 子句

用于为模式识别中的每个基本模式变量指定其判断条件。这些变量通常由标识符（如 A, B）代表，并通过该子句中的布尔表达式精确定义哪些行符合该变量的要求。

  • 在模式匹配执行过程中，仅当布尔表达式返回 TRUE 时，才会将当前行标记为该变量，从而将其纳入到当前匹配分组中。
-- 只有在当前行的 totalprice 值小于前一行 totalprice 值的情况下，当前行才可以被识别为 B。
DEFINE B AS totalprice < PREV(totalprice)
  • 未在子句中显式定义的变量，其匹配条件隐含为恒真（TRUE），即可在任何输入行上成功匹配。

2.3 SUBSET 子句

用于将多个基本模式变量（如 A、B）匹配到的行合并成一个联合模式变量（如 U），使这些行可以被视为同一个逻辑集合进行操作。可用于MEASURES、DEFINE 和AFTER MATCH SKIP子句。

SUBSET U = (A, B)

例如，对于模式 PATTERN ((A | B){5} C+) ，在匹配过程中无法确定第五次重复时具体匹配的是基本模式变量 A 还是 B，因此

  1. 在 MEASURES 子句中，若需要引用该阶段最后一次匹配到的行，则可通过定义联合模式变量 SUBSET U = (A, B)实现。此时表达式 RPR_LAST(U.totalprice) 将直接返回该目标行的 totalprice 值。
  2. 在 AFTER MATCH SKIP 子句中，若匹配结果中未包含基本模式变量 A 或 B 时，执行 AFTER MATCH SKIP TO LAST B 或 AFTER MATCH SKIP TO LAST A 会因锚点缺失跳转失败；而通过引入联合模式变量 SUBSET U = (A, B)，使用 AFTER MATCH SKIP TO LAST U 则始终有效。

2.4 PATTERN 子句

用于定义需要匹配的行模式，其基本构成单元是基本模式变量。

PATTERN ( row_pattern )

2.4.1 模式种类

行模式语法格式描述
模式连接（Pattern Concatenation）A B+ C+ D+由不带任何运算符的子模式组成，按声明顺序依次匹配所有子模式。
模式选择（Pattern Alternation）A | B | C由以|分隔的多个子模式组成，仅匹配其中一个。当多个子模式均可匹配时，选择最左侧的子模式匹配。
模式排列（Pattern Permutation）PERMUTE(A, B, C)该模式等价于对所有子模式元素的不同顺序进行选择匹配，即要求 A、B、C 三者均须匹配，但其出现顺序不固定。当多种匹配顺序均可成功时，依据 PERMUTE 列表中元素的定义先后顺序，按字典序原则确定优先级。例如，A B C 为最高优先，C B A 则为最低优先。
模式分组（Pattern Grouping）(A B C)用圆括号将子模式括起，视作一个整体对待，可与其他运算符配合使用。如(A B C)+表示连续出现一组(A B C)的模式。
空模式（Empty Pattern）()表示一个不包含任何行的空匹配
模式排除（Pattern Exclusion）{- row_pattern -}用于指定在输出中需要排除的匹配部分。通常与ALL ROWS PER MATCH选项结合使用，用于输出感兴趣的行。如PATTERN (A {- B+ C+ -} D+)，并使用ALL ROWS PER MATCH时，输出将仅包含匹配的首行（A对应行）与尾部行（D+对应行）。

2.4.2 分区起始/结束锚点（Partition Start/End Anchor）

  • ^A 表示匹配以 A 为分区开始的模式
    • 当 PATTERN 子句的取值为 ^A 时，要求匹配必须从分区的首行开始，且这一行要满足 A 的定义
    • 当 PATTERN 子句的取值为 ^A^ 或 A^ 时，输出结果为空
  • A$ 表示匹配以 A 为分区结束的模式
    • 当 PATTERN 子句的取值为 A$ 时，要求必须在分区的结束位置匹配，并且这一行要满足 A的定义
    • 当 PATTERN 子句的取值为 $A 或 $A$ 时，输出结果为空

示例介绍可见 3.1 小节

2.4.3 量词（Quantifiers）

量词用于指定子模式重复出现的次数，置于相应子模式之后，如 (A | B)*。

常用量词如下：

量词描述
*零次或多次重复
+一次或多次重复
?零次或一次重复
{n}恰好重复 n 次
{m, n}重复次数在 m 到 n 之间（m、n 为非负整数）。* 若省略左界，则默认从 0 开始；* 若省略右界，则重复次数不设上限（如 {5,} 等同于“至少重复五次”）；* 若同时省略左右界，即 {,}，则与 * 等价。
  • 可通过在量词后加 ? 改变匹配偏好。
    • {3,5}：偏好 5 次，最不偏好 3 次；{3,5}?：偏好 3 次，最不偏好 5 次
    • ?：偏好 1 次；??：偏好 0 次

2.5 AFTER MATCH SKIP 子句

用于指定在识别到一个非空匹配后，下一次模式匹配应从哪一行继续进行。

跳转策略描述是否允许识别重叠匹配项
AFTER MATCH SKIP PAST LAST ROW默认行为。在当前匹配的最后一行之后的下一行开始。否
AFTER MATCH SKIP TO NEXT ROW在当前匹配中的第二行开始。是
AFTER MATCH SKIP TO [ FIRST | LAST ] pattern_variable跳转到某个模式变量的 [ 第一行最后一行 ] 开始。
  • 在所有可能的配置中，仅当 ALL ROWS PER MATCH WITH UNMATCHED ROWS 与 AFTER MATCH SKIP PAST LAST ROW 联合使用时，系统才能确保对每个输入行恰好生成一条输出记录。

示例介绍可见 3.2 小节

2.6 ROWS PER MATCH 子句

用于指定模式匹配成功后结果集的输出方式，主要包括以下两种选项：

输出方式规则描述输出结果空匹配/未匹配行处理逻辑
ONE ROW PER MATCH每一次成功匹配，产生一行输出结果。* PARTITION BY 子句中的列* MEASURES 子句中定义的表达式。输出空匹配；跳过未匹配行。
ALL ROWS PER MATCH每一次匹配中的每一行都将产生一条输出记录，除非该行通过 exclusion 语法排除。* PARTITION BY 子句中的列* ORDER BY 子句中的列* MEASURES 子句中定义的表达式* 输入表中的其余列* 默认：输出空匹配；跳过未匹配行。* ALL ROWS PER MATCH​SHOW EMPTY MATCHES​：默认输出空匹配，跳过未匹配行* ALL ROWS PER MATCH​OMIT EMPTY MATCHES​：不输出空匹配，跳过未匹配行* ALL ROWS PER MATCH​WITH UNMATCHED ROWS​：输出空匹配，并为每一条未匹配行额外生成一条输出记录

2.7 MEASURES 子句

用于指定从匹配到的一段数据中提取哪些信息。该子句为可选项，如果未显式指定，则根据 ROWS PER MATCH 子句的设置，部分输入列会成为模式识别的输出结果。

MEASURES measure_expression AS measure_name [, ...]
  • measure_expression 是根据匹配的一段数据计算出的标量值。
用法示例说明
A.totalprice AS starting_price返回匹配分组中第一行（即与变量 A 关联的唯一一行）中的价格，作为起始价格。
RPR_LAST(B.totalprice) AS bottom_price返回与变量 B 关联的最后一行中的价格，代表“V”形模式中最低点的价格，对应下降区段的末尾。
RPR_LAST(U.totalprice) AS top_price返回匹配分组中的最高价格，对应变量 C 或 D 所关联的最后一行，即整个匹配分组的末尾。【假设 SUBSET U = (C, D)】
  • 每个 measure_expression 都会定义一个输出列，该列可通过其指定的 measure_name 进行引用。

2.8 行模式识别表达式

在 MEASURES 与 DEFINE 子句中使用的表达式为​标量表达式​，用于在输入表的行级上下文中求值。标量表达式除了支持标准 SQL 语法外，还支持针对行模式识别的特殊扩展函数。

2.8.1 模式变量引用

A.totalprice  
U.orderdate  
orderstatus
  • 当列名前缀为某基本模式变量或联合模式变量时，表示引用该变量所匹配的所有行的对应列值。
  • 若列名不带前缀，则等同于使用“​全局联合模式变量​”（即所有基本模式变量的并集）作前缀，表示引用当前匹配中所有行的该列值。

不允许在模式识别表达式中使用表名作列名前缀。

2.8.2 扩展函数

函数名函数式描述
MATCH_NUMBER函数MATCH_NUMBER()返回当前匹配在分区内的序号，从 1 开始计数。空匹配与非空匹配一致，也占用匹配序号。
CLASSIFIER 函数CLASSIFIER(option)1. 返回当前行所映射的基本模式变量名称。1. option是一个可选参数：可以传入基本模式变量CLASSIFIER(A）或联合模式变量CLASSIFIER(U），用于限定函数作用范围，对于不在范围内的行，直接返回 NULL。在对联合模式变量使用时，可用于辨别该行究竟映射至并集中哪一个基本模式变量。
逻辑导航函数RPR_FIRST(expr, k)1. 表示从当前匹配分组中，定位至第一个满足 expr 的行，在此基础上再向分组尾部方向搜索到第 k 次出现的同一模式变量对应行，返回该行的指定列值。如果在指定方向上未能找到第 k 次匹配行，则函数返回 NULL。1. 其中 k 是可选参数，默认为 0，表示仅定位至首个满足条件的行；若显式指定，必须为非负整数。
逻辑导航函数RPR_LAST(expr, k)1. 表示从当前匹配分组中，定位至最后一个满足 expr 的行，在此基础上再向分组开头方向搜索到第 k 次出现的同一模式变量对应行，返回该行的指定列值。如果在指定方向上未能找到第 k 次匹配行，则函数返回 NULL。1. 其中 k 是可选参数，默认为 0，表示仅定位至末个满足条件的行；若显式指定，必须为非负整数。
物理导航函数PREV(expr, k)1. 表示从最后一次匹配至给定模式变量的行开始，向开头方向偏移 k 行，返回对应列值。若导航超出​分区边界​，则函数返回 NULL。1. 其中 k 是可选参数，默认为 1；若显式指定，必须为非负整数。
物理导航函数NEXT(expr, k)1. 表示从最后一次匹配至给定模式变量的行开始，向尾部方向偏移 k 行，返回对应列值。若导航超出​分区边界​，则函数返回 NULL。1. 其中 k 是可选参数，默认为 1；若显式指定，必须为非负整数。
聚合函数COUNT、SUM、AVG、MAX、MIN 函数可用于对当前匹配中的数据进行计算。聚合函数与导航函数不允许互相嵌套。（V 2.0.6 版本起支持）
嵌套函数PREV/NEXT(CLASSIFIER())物理导航函数与 CLASSIFIER 函数嵌套。用于获取当前行的前一个和后一个匹配行所对应的模式变量
嵌套函数PREV/NEXT(RPR_FIRST/RPR_LAST(expr, k)）物理函数内部允许嵌套逻辑函数，逻辑函数内部不允许嵌套物理函数。用于先进行逻辑偏移，再进行物理偏移。

示例介绍可见 3.3 小节

2.8.3 RUNNING 和 FINAL 语义

  1. 定义
  • RUNNING: 表示计算范围为当前匹配分组内，从分组的起始行到当前正在处理的行（即到当前行为止）。
  • FINAL: 表示计算范围为当前匹配分组内，从分组的起始行到分组的最终行（即整个匹配分组）。
  1. 作用范围
  • DEFINE 子句默认采用 RUNNING 语义。
  • MEASURES 子句默认采用 RUNNING 语义，支持指定 FINAL 语义。当采用 ONE ROW PER MATCH 输出模式时，所有表达式都从匹配分组的末行位置进行计算，此时 RUNNING 语义与 FINAL 语义等价。
  1. 语法约束
  • RUNNING 和 FINAL 需要写在逻辑导航函数或聚合函数之前，不能直接作用于列引用。
    • 合法：RUNNING RPP_LAST(A.totalprice)、FINAL RPP_LAST(A.totalprice)
    • 非法：RUNNING A.totalprice、FINAL A.totalprice、 RUNNING PREV(A.totalprice)

3. 语法示例

原始数据

IoTDB:database3> select * from t
+-----------------------------+------+----------+
|                         time|device|totalprice|
+-----------------------------+------+----------+
|2025-01-01T00:01:00.000+08:00|    d1|        90|
|2025-01-01T00:02:00.000+08:00|    d1|        80|
|2025-01-01T00:03:00.000+08:00|    d1|        70|
|2025-01-01T00:04:00.000+08:00|    d1|        80|
|2025-01-01T00:05:00.000+08:00|    d1|        70|
|2025-01-01T00:06:00.000+08:00|    d1|        80|
+-----------------------------+------+----------+

-- 创建语句
create table t(device tag, totalprice int32 field)

insert into t(time,device,totalprice) values(2025-01-01T00:01:00, 'd1', 90),(2025-01-01T00:02:00, 'd1', 80),(2025-01-01T00:03:00, 'd1', 70),(2025-01-01T00:04:00, 'd1', 80),(2025-01-01T00:05:00, 'd1', 70),(2025-01-01T00:06:00, 'd1', 80)

3.1 Patter 子句分区锚点

  • 查询 sql
SELECT m.time, m.match, m.price, m.label
FROM t
MATCH_RECOGNIZE ( 
    ORDER BY time 
    MEASURES 
         MATCH_NUMBER() AS match, 
         RUNNING RPR_LAST(totalprice) AS price, 
         CLASSIFIER() AS label 
    ALL ROWS PER MATCH 
    AFTER MATCH SKIP PAST LAST ROW 
    PATTERN %s -- PATTERN 子句 
    DEFINE A AS true 
) AS m;

  • 查询结果

    • 当 PATTERN 子句为 PATTERN (^A) 时
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|    A|
+-----------------------------+-----+-----+-----+
Total line number = 1
    • 当 PATTERN 子句为 PATTERN (^A^) 时
    +----+-----+-----+-----+
|time|match|price|label|
+----+-----+-----+-----+
+----+-----+-----+-----+
Empty set.
    • 当 PATTERN 子句为 PATTERN (A$) 时
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:06:00.000+08:00|    1|   80|    A|
+-----------------------------+-----+-----+-----+
Total line number = 1
    • 当 PATTERN 子句为 PATTERN ($A$) 时
    +----+-----+-----+-----+
|time|match|price|label|
+----+-----+-----+-----+
+----+-----+-----+-----+
Empty set.

3.2 AFTER MATCH SKIP 子句

  • 查询 sql
SELECT m.time, m.match, m.price, m.label
FROM t
MATCH_RECOGNIZE (
    ORDER BY time
    MEASURES 
        MATCH_NUMBER() AS match,
        RUNNING RPR_LAST(totalprice) AS price,
        CLASSIFIER() AS label
    ALL ROWS PER MATCH
    %s -- AFTER MATCH SKIP 子句
    PATTERN (A B+ C+ D?)
    SUBSET U = (C, D)
    DEFINE 
        B AS B.totalprice < PREV (B.totalprice),
        C AS C.totalprice > PREV (C.totalprice),
        D AS false -- 永远不会匹配成功
) AS m;

  • 查询结果

    • 当 AFTER MATCH SKIP PAST LAST ROW 时
      • 第一次匹配：第 1、2、3、4 行
      • 第二次匹配：根据 AFTER MATCH SKIP PAST LAST ROW 语义，从第 5 行开始，无法再找寻到一个合法匹配
      • 此模式一定不会出现重叠匹配
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|    A|
|2025-01-01T00:02:00.000+08:00|    1|   80|    B|
|2025-01-01T00:03:00.000+08:00|    1|   70|    B|
|2025-01-01T00:04:00.000+08:00|    1|   80|    C|
+-----------------------------+-----+-----+-----+
Total line number = 4
    • 当 AFTER MATCH SKIP TO NEXT ROW 时
      • 第一次匹配：第 1、2、3、4 行
      • 第二次匹配：根据 AFTER MATCH SKIP TO NEXT ROW 语义，从第 2 行开始，匹配：第 2、3、4 行
      • 第三次匹配：尝试从第 3 行开始，失败
      • 第三次匹配：尝试从第 4 行开始，成功，匹配第 4、5、6行
      • 此模式允许出现重叠匹配
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|    A|
|2025-01-01T00:02:00.000+08:00|    1|   80|    B|
|2025-01-01T00:03:00.000+08:00|    1|   70|    B|
|2025-01-01T00:04:00.000+08:00|    1|   80|    C|
|2025-01-01T00:02:00.000+08:00|    2|   80|    A|
|2025-01-01T00:03:00.000+08:00|    2|   70|    B|
|2025-01-01T00:04:00.000+08:00|    2|   80|    C|
|2025-01-01T00:04:00.000+08:00|    3|   80|    A|
|2025-01-01T00:05:00.000+08:00|    3|   70|    B|
|2025-01-01T00:06:00.000+08:00|    3|   80|    C|
+-----------------------------+-----+-----+-----+
Total line number = 10
    • 当 AFTER MATCH SKIP TO FIRST C 时
      • 第一次匹配：第 1、2、3、4 行
      • 第二次匹配：从第一个 C （也就是第 4 行）处开始，匹配第4、5、6行
      • 此模式允许出现重叠匹配
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|    A|
|2025-01-01T00:02:00.000+08:00|    1|   80|    B|
|2025-01-01T00:03:00.000+08:00|    1|   70|    B|
|2025-01-01T00:04:00.000+08:00|    1|   80|    C|
|2025-01-01T00:04:00.000+08:00|    2|   80|    A|
|2025-01-01T00:05:00.000+08:00|    2|   70|    B|
|2025-01-01T00:06:00.000+08:00|    2|   80|    C|
+-----------------------------+-----+-----+-----+
Total line number = 7
    • 当 AFTER MATCH SKIP TO LAST B 或 AFTER MATCH SKIP TO B 时
      • 第一次匹配：第 1、2、3、4 行
      • 第二次匹配：尝试从最后一个 B （也就是第 3 行）处开始，失败
      • 第二次匹配：尝试从第 4 行开始，成功匹配第4、5、6行
      • 此模式允许出现重叠匹配
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|    A|
|2025-01-01T00:02:00.000+08:00|    1|   80|    B|
|2025-01-01T00:03:00.000+08:00|    1|   70|    B|
|2025-01-01T00:04:00.000+08:00|    1|   80|    C|
|2025-01-01T00:04:00.000+08:00|    2|   80|    A|
|2025-01-01T00:05:00.000+08:00|    2|   70|    B|
|2025-01-01T00:06:00.000+08:00|    2|   80|    C|
+-----------------------------+-----+-----+-----+
Total line number = 7
    • 当 AFTER MATCH SKIP TO U 时
      • 第一次匹配：第 1、2、3、4 行
      • 第二次匹配：SKIP TO U 表示跳转到最后一个 C 或 D，D 永远不可能匹配成功，所以就是跳转到最后一个 C（也就是第 4 行），成功匹配第4、5、6行
      • 此模式允许出现重叠匹配
    +-----------------------------+-----+-----+-----+
|                         time|match|price|label|
+-----------------------------+-----+-----+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|    A|
|2025-01-01T00:02:00.000+08:00|    1|   80|    B|
|2025-01-01T00:03:00.000+08:00|    1|   70|    B|
|2025-01-01T00:04:00.000+08:00|    1|   80|    C|
|2025-01-01T00:04:00.000+08:00|    2|   80|    A|
|2025-01-01T00:05:00.000+08:00|    2|   70|    B|
|2025-01-01T00:06:00.000+08:00|    2|   80|    C|
+-----------------------------+-----+-----+-----+
Total line number = 7
    • 当 AFTER MATCH SKIP TO A 时，不能跳转到匹配的第一行， 否则会造成死循环
    Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: AFTER MATCH SKIP TO failed: cannot skip to first row of match
    • 当 AFTER MATCH SKIP TO B 时，不能跳转到匹配分组中不存在的模式变量
    Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: AFTER MATCH SKIP TO failed: pattern variable is not present in match

3.3 行模式表达式-扩展函数

3.3.1 CLASSIFIER()函数

  • 查询 sql
SELECT m.time, m.match, m.price, m.lower_or_higher, m.label
FROM t
MATCH_RECOGNIZE (
    ORDER BY time
    MEASURES 
        MATCH_NUMBER() AS match,
        RUNNING RPR_LAST(totalprice) AS price,
        CLASSIFIER(U) AS lower_or_higher,
        CLASSIFIER(W) AS label
    ALL ROWS PER MATCH
    PATTERN ((L | H) A)
    SUBSET 
        U = (L, H),
        W = (A, L, H)
    DEFINE 
        A AS A.totalprice = 80,
        L AS L.totalprice < 80,
        H AS H.totalprice > 80
) AS m;
  • 查询结果
+-----------------------------+-----+-----+---------------+-----+
|                         time|match|price|lower_or_higher|label|
+-----------------------------+-----+-----+---------------+-----+
|2025-01-01T00:01:00.000+08:00|    1|   90|              H|    H|
|2025-01-01T00:02:00.000+08:00|    1|   80|              H|    A|
|2025-01-01T00:03:00.000+08:00|    2|   70|              L|    L|
|2025-01-01T00:04:00.000+08:00|    2|   80|              L|    A|
|2025-01-01T00:05:00.000+08:00|    3|   70|              L|    L|
|2025-01-01T00:06:00.000+08:00|    3|   80|              L|    A|
+-----------------------------+-----+-----+---------------+-----+
Total line number = 6

3.3.2 逻辑导航函数

  • 查询 sql
SELECT m.time, m.measure
FROM t
MATCH_RECOGNIZE ( 
    ORDER BY time 
    MEASURES 
         %s AS measure -- MEASURES 子句
    ALL ROWS PER MATCH 
    PATTERN (A+)
    DEFINE A AS true 
) AS m;

  • 查询结果

    • 当取值为 totalprice、RPR_LAST(totalprice)、RUNNING RPR_LAST(totalprice) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:01:00.000+08:00|     90|
|2025-01-01T00:02:00.000+08:00|     80|
|2025-01-01T00:03:00.000+08:00|     70|
|2025-01-01T00:04:00.000+08:00|     80|
|2025-01-01T00:05:00.000+08:00|     70|
|2025-01-01T00:06:00.000+08:00|     80|
+-----------------------------+-------+
Total line number = 6
    • 当取值为 FINAL RPR_LAST(totalprice) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:01:00.000+08:00|     80|
|2025-01-01T00:02:00.000+08:00|     80|
|2025-01-01T00:03:00.000+08:00|     80|
|2025-01-01T00:04:00.000+08:00|     80|
|2025-01-01T00:05:00.000+08:00|     80|
|2025-01-01T00:06:00.000+08:00|     80|
+-----------------------------+-------+
Total line number = 6
    • 当取值为 RPR_FIRST(totalprice)、 RUNNING RPR_FIRST(totalprice)、FINAL RPR_FIRST(totalprice)时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:01:00.000+08:00|     90|
|2025-01-01T00:02:00.000+08:00|     90|
|2025-01-01T00:03:00.000+08:00|     90|
|2025-01-01T00:04:00.000+08:00|     90|
|2025-01-01T00:05:00.000+08:00|     90|
|2025-01-01T00:06:00.000+08:00|     90|
+-----------------------------+-------+
Total line number = 6
    • 当取值为 RPR_LAST(totalprice, 2) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:01:00.000+08:00|   null|
|2025-01-01T00:02:00.000+08:00|   null|
|2025-01-01T00:03:00.000+08:00|     90|
|2025-01-01T00:04:00.000+08:00|     80|
|2025-01-01T00:05:00.000+08:00|     70|
|2025-01-01T00:06:00.000+08:00|     80|
+-----------------------------+-------+
Total line number = 6
    • 当取值为 FINAL RPP_LAST(totalprice, 2) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:01:00.000+08:00|     80|
|2025-01-01T00:02:00.000+08:00|     80|
|2025-01-01T00:03:00.000+08:00|     80|
|2025-01-01T00:04:00.000+08:00|     80|
|2025-01-01T00:05:00.000+08:00|     80|
|2025-01-01T00:06:00.000+08:00|     80|
+-----------------------------+-------+
Total line number = 6
    • 当取值为 RPR_FIRST(totalprice, 2) 和 FINAL RPR_FIRST(totalprice, 2) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:01:00.000+08:00|     70|
|2025-01-01T00:02:00.000+08:00|     70|
|2025-01-01T00:03:00.000+08:00|     70|
|2025-01-01T00:04:00.000+08:00|     70|
|2025-01-01T00:05:00.000+08:00|     70|
|2025-01-01T00:06:00.000+08:00|     70|
+-----------------------------+-------+
Total line number = 6

3.3.3 物理导航函数

  • 查询 sql
SELECT m.time, m.measure
FROM t
MATCH_RECOGNIZE ( 
    ORDER BY time 
    MEASURES 
         %s AS measure -- MEASURES 子句
    ALL ROWS PER MATCH 
    PATTERN (B)
    DEFINE B AS B.totalprice >= PREV(B.totalprice)
) AS m;

  • 查询结果

    • 当取值为 PREV(totalprice) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:04:00.000+08:00|     70|
|2025-01-01T00:06:00.000+08:00|     70|
+-----------------------------+-------+
Total line number = 2
    • 当取值为 PREV(B.totalprice, 2) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:04:00.000+08:00|     80|
|2025-01-01T00:06:00.000+08:00|     80|
+-----------------------------+-------+
Total line number = 2
    • 当取值为 PREV(B.totalprice, 4) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:04:00.000+08:00|   null|
|2025-01-01T00:06:00.000+08:00|     80|
+-----------------------------+-------+
Total line number = 2
    • 当取值为 NEXT(totalprice) 或 NEXT(B.totalprice, 1) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:04:00.000+08:00|     70|
|2025-01-01T00:06:00.000+08:00|   null|
+-----------------------------+-------+
Total line number = 2
    • 当取值为 NEXT(B.totalprice, 2) 时
    +-----------------------------+-------+
|                         time|measure|
+-----------------------------+-------+
|2025-01-01T00:04:00.000+08:00|     80|
|2025-01-01T00:06:00.000+08:00|   null|
+-----------------------------+-------+
Total line number = 2

3.3.4 聚合函数

  • 查询 sql
SELECT m.time, m.count, m.avg, m.sum, m.min, m.max
FROM t
MATCH_RECOGNIZE ( 
    ORDER BY time 
    MEASURES 
         COUNT(*) AS count,
         AVG(totalprice) AS avg,
         SUM(totalprice) AS sum,
         MIN(totalprice) AS min,
         MAX(totalprice) AS max
    ALL ROWS PER MATCH 
    PATTERN (A+)
    DEFINE A AS true 
) AS m;
  • 查询结果
+-----------------------------+-----+-----------------+-----+---+---+
|                         time|count|              avg|  sum|min|max|
+-----------------------------+-----+-----------------+-----+---+---+
|2025-01-01T00:01:00.000+08:00|    1|             90.0| 90.0| 90| 90|
|2025-01-01T00:02:00.000+08:00|    2|             85.0|170.0| 80| 90|
|2025-01-01T00:03:00.000+08:00|    3|             80.0|240.0| 70| 90|
|2025-01-01T00:04:00.000+08:00|    4|             80.0|320.0| 70| 90|
|2025-01-01T00:05:00.000+08:00|    5|             78.0|390.0| 70| 90|
|2025-01-01T00:06:00.000+08:00|    6|78.33333333333333|470.0| 70| 90|
+-----------------------------+-----+-----------------+-----+---+---+
Total line number = 6

3.3.5 嵌套函数

  1. 示例一
  • 查询 sql
SELECT m.time, m.match, m.price, m.lower_or_higher, m.label, m.prev_label, m.next_label
FROM t
MATCH_RECOGNIZE (
    ORDER BY time
    MEASURES 
        MATCH_NUMBER() AS match,
        RUNNING RPR_LAST(totalprice) AS price,
        CLASSIFIER(U) AS lower_or_higher,
        CLASSIFIER(W) AS label,
        PREV(CLASSIFIER(W)) AS prev_label,
        NEXT(CLASSIFIER(W)) AS next_label
    ALL ROWS PER MATCH
    PATTERN ((L | H) A)
    SUBSET 
        U = (L, H),
        W = (A, L, H)
    DEFINE 
        A AS A.totalprice = 80,
        L AS L.totalprice < 80,
        H AS H.totalprice > 80
) AS m;
  • 查询结果
+-----------------------------+-----+-----+---------------+-----+----------+----------+
|                         time|match|price|lower_or_higher|label|prev_label|next_label|
+-----------------------------+-----+-----+---------------+-----+----------+----------+
|2025-01-01T00:01:00.000+08:00|    1|   90|              H|    H|      null|         A|
|2025-01-01T00:02:00.000+08:00|    1|   80|              H|    A|         H|      null|
|2025-01-01T00:03:00.000+08:00|    2|   70|              L|    L|      null|         A|
|2025-01-01T00:04:00.000+08:00|    2|   80|              L|    A|         L|      null|
|2025-01-01T00:05:00.000+08:00|    3|   70|              L|    L|      null|         A|
|2025-01-01T00:06:00.000+08:00|    3|   80|              L|    A|         L|      null|
+-----------------------------+-----+-----+---------------+-----+----------+----------+
Total line number = 6
  1. 示例二
  • 查询 sql
SELECT m.time, m.prev_last_price, m.next_first_price 
FROM t 
MATCH_RECOGNIZE ( 
    ORDER BY time  
    MEASURES 
        PREV(RPR_LAST(totalprice), 2) AS prev_last_price, 
        NEXT(RPR_FIRST(totalprice), 2) as next_first_price  
    ALL ROWS PER MATCH 
    PATTERN (A+)    
    DEFINE A AS true 
) AS m;
  • 查询结果
+-----------------------------+---------------+----------------+
|                         time|prev_last_price|next_first_price|
+-----------------------------+---------------+----------------+
|2025-01-01T00:01:00.000+08:00|           null|              70|
|2025-01-01T00:02:00.000+08:00|           null|              70|
|2025-01-01T00:03:00.000+08:00|             90|              70|
|2025-01-01T00:04:00.000+08:00|             80|              70|
|2025-01-01T00:05:00.000+08:00|             70|              70|
|2025-01-01T00:06:00.000+08:00|             80|              70|
+-----------------------------+---------------+----------------+
Total line number = 6

4. 场景示例

以示例数据为源数据

4.1 时间分段查询

将 table1 中的数据按照时间间隔小于等于 24 小时分段，查询每段中的数据总条数，以及开始、结束时间。

查询SQL

SELECT start_time, end_time, cnt 
FROM table1 
MATCH_RECOGNIZE (
    ORDER BY time 
    MEASURES 
        RPR_FIRST(A.time) AS start_time, 
        RPR_LAST(time) AS end_time, 
        COUNT() AS cnt 
    PATTERN (A B*)  
    DEFINE  B AS (cast(B.time as INT64) - cast(PREV(B.time) as INT64)) <= 86400000
) AS m

查询结果

+-----------------------------+-----------------------------+---+
|                   start_time|                     end_time|cnt|
+-----------------------------+-----------------------------+---+
|2024-11-26T13:37:00.000+08:00|2024-11-26T13:38:00.000+08:00|  2|
|2024-11-27T16:38:00.000+08:00|2024-11-30T14:30:00.000+08:00| 16|
+-----------------------------+-----------------------------+---+
Total line number = 2

4.2 差值分段查询

将 table2 中的数据按照 humidity 湿度值差值小于 0.1 分段，查询每段中的数据总条数，以及开始、结束时间。

  • 查询sql
SELECT start_time, end_time, cnt 
FROM table2 
MATCH_RECOGNIZE (
    ORDER BY time 
    MEASURES 
        RPR_FIRST(A.time) AS start_time, 
        RPR_LAST(time) AS end_time, 
        COUNT() AS cnt 
    PATTERN (A B*)  
    DEFINE  B AS (B.humidity - PREV(B.humidity )) <=0.1
) AS m;
  • 查询结果
+-----------------------------+-----------------------------+---+
|                   start_time|                     end_time|cnt|
+-----------------------------+-----------------------------+---+
|2024-11-26T13:37:00.000+08:00|2024-11-27T00:00:00.000+08:00|  2|
|2024-11-28T08:00:00.000+08:00|2024-11-29T00:00:00.000+08:00|  2|
|2024-11-29T11:00:00.000+08:00|2024-11-30T00:00:00.000+08:00|  2|
+-----------------------------+-----------------------------+---+
Total line number = 3

4.3 事件统计查询

将 table1 中数据按照设备号分组，统计上海地区湿度大于 35 的开始、结束时间及最大湿度值。

  • 查询sql
SELECT m.device_id, m.match, m.event_start, m.event_end, m.max_humidity 
FROM table1
MATCH_RECOGNIZE (
  PARTITION BY device_id
  ORDER BY time 
  MEASURES
    MATCH_NUMBER() AS match,
    RPR_FIRST(A.time) AS event_start,
    RPR_LAST(A.time) AS event_end,
    MAX(A.humidity) AS max_humidity 
  ONE ROW PER MATCH
  PATTERN (A+)
  DEFINE
    A AS A.region= '上海' AND A.humidity> 35
) AS m
  • 查询结果
+---------+-----+-----------------------------+-----------------------------+------------+
|device_id|match|                  event_start|                    event_end|max_humidity|
+---------+-----+-----------------------------+-----------------------------+------------+
|      100|    1|2024-11-28T09:00:00.000+08:00|2024-11-29T18:30:00.000+08:00|        45.1|
|      101|    1|2024-11-30T09:30:00.000+08:00|2024-11-30T09:30:00.000+08:00|        35.2|
+---------+-----+-----------------------------+-----------------------------+------------+
Total line number = 2****