类别：: 查询语法

MATCH_RECOGNIZE¶

识别行集合中的模式匹配项。MATCH_RECOGNIZE 接受一个行集合（来自表、视图、子查询或其他来源）作为输入，然后返回此集合中给定行模式的所有匹配项。模式的定义与正则表达式类似。

此子句可以返回：

属于每个匹配项的所有行。
每个匹配项一个汇总行。

MATCH_RECOGNIZE 通常用于检测时间序列中的事件。例如， MATCH_RECOGNIZE 可以在股票价格历史记录表中搜索不同形状，例如 V （先向下再向上）或 W （向下、向上、向下、向上）。

MATCH_RECOGNIZE 是 FROM 子句的可选分子句。

备注

您不能在递归通用表表达式 (CTE) 中使用 MATCH_RECOGNIZE 子句。

另请参阅：: 识别与模式匹配的行序列

语法¶

MATCH_RECOGNIZE (
    [ PARTITION BY <expr> [, ... ] ]
    [ ORDER BY <expr> [, ... ] ]
    [ MEASURES <expr> [AS] <alias> [, ... ] ]
    [ ONE ROW PER MATCH |
      ALL ROWS PER MATCH [ { SHOW EMPTY MATCHES | OMIT EMPTY MATCHES | WITH UNMATCHED ROWS } ]
      ]
    [ AFTER MATCH SKIP
          {
          PAST LAST ROW   |
          TO NEXT ROW   |
          TO [ { FIRST | LAST} ] <symbol>
          }
      ]
    PATTERN ( <pattern> )
    DEFINE <symbol> AS <expr> [, ... ]
)

Copy

必需的分子句¶

DEFINE：定义符号¶

DEFINE <symbol1> AS <expr1> [ , <symbol2> AS <expr2> ]

Copy

符号（也称为“模式变量”）是模式的基本组成部分。

符号由表达式定义。如果某行的表达式求值结果为 true，则向该行分配符号。可以向一行分配多个符号。

未在 DEFINE 子句中定义但在模式中使用的符号将始终分配给所有行。它们隐式等同于以下示例：

...
define
    my_example_symbol as true
...

Copy

模式是根据符号和运算符定义的。

PATTERN：指定要匹配的模式¶

PATTERN ( <pattern> )

Copy

模式定义了表示匹配项的有效行序列。模式的定义类似于正则表达式 (regex)，由符号、运算符和量词构成。

例如，假设符号 S1 定义为 stock_price < 55, and symbol S2 is defined as 股票价格 > 55。以下模式指定了股票价格从低于 55 上涨到高于 55 的行序列：

PATTERN (S1 S2)

Copy

以下是有关模式定义的更复杂示例：

^ S1 S2*? ( {- S3 -} S4 )+ | PERMUTE(S1, S2){1,2} $

Copy

以下部分详细描述了此模式的各个组成部分。

备注

MATCH_RECOGNIZE 使用回溯 (link removed) 来与模式进行匹配。与其他使用回溯的正则表达式引擎 (link removed) 一样，要匹配的某些模式和数据组合可能需要很长时间才能执行，这可能导致高昂的计算成本。

要提高性能，请定义尽可能具体的模式：

确保每行仅匹配一个符号或少量符号
避免使用与每一行都匹配的符号（例如，不在 DEFINE 子句中的符号或定义为 True 的符号）
定义量词的上限（例如， {,10} 代替 *）。

例如，如果没有匹配的行，以下模式可能会导致成本增加：

symbol1+ any_symbol* symbol2

Copy

如果要匹配的行数有上限，您可以在量词中指定该限制以提高性能。此外，您可以查找不是 symbol1 的行（在本示例中为 not_symbol1），而不是指定要查找 symbol1 之后的 any_symbol ；

symbol1{1,limit} not_symbol1{,limit} symbol2

Copy

通常情况下，您应监控查询执行时间，验证查询花费的时间是否未超过预期。

符号:

符号与其分配到的行相匹配。以下符号可用：

symbol。例如， S1, ... , S4 这些符号在 DEFINE 分子句中定义，并且按行求值。（这些符号也可以包括未定义且自动分配给所有行的符号。）
^ （分区开始。）这是一个表示分区开始的虚拟符号，没有与之关联的行。可以用它来要求只在分区的开头开始匹配。

有关示例，请参阅相对于分区的开头或结尾的匹配模式。
$ （分区结束。）这是一个表示分区结束的虚拟符号，没有与之关联的行。可以用它来要求只在分区的末尾结束匹配。

有关示例，请参阅相对于分区的开头或结尾的匹配模式。

量器:

量词可以放在符号或运算之后。量词表示关联符号或运算出现的最小和最大次数。以下量词可用：

量词

含义

+

1 或更多。例如 ( {- S3 -} S4 )+。

*

0 或更多。例如 S2*?。

?

0 或 1。

{n}

正好为 n。

{n,}

n 或更多。

{,m}

0 到 m。

{n, m}

n 到 m。例如 PERMUTE(S1, S2){1,2}。

量词	含义
`+`	1 或更多。例如 `( {- S3 -} S4 )+`。
`*`	0 或更多。例如 `S2*?`。
`?`	0 或 1。
`{n}`	正好为 n。
`{n,}`	n 或更多。
`{,m}`	0 到 m。
`{n, m}`	n 到 m。例如 `PERMUTE(S1, S2){1,2}`。

默认情况下，量词处于“贪婪模式”，这意味着它们尽可能尝试匹配最大数量。要将量词置于“不情愿模式”（即量词尽可能尝试匹配最小数量），请在量词后面放置一个 ? （例如 S2*?）。

运算符:

运算符指定在行序列中应以何种顺序出现符号或其他运算，以形成有效的匹配。以下运算符可用：

运算符

含义

``... ...``（空间）

连接。指定一个符号或运算应紧随另一个符号或运算。例如， S1 S2 表示为 S2 定义的条件应出现在为 S1 定义的条件之后。

{- ... -}

排除。在输出中排除包含的符号或运算。例如， {- S3 -} 在输出中排除运算符 S3。排除的行不会出现在输出中，但会包含在 MEASURES 表达式的求值中。

( ... )

分组。用于覆盖运算符的优先级，或为组中的符号或运算应用相同的量词。在此示例中，量词 + 不仅应用于 S4，而且还应用于序列 {- S3 -} S4。

PERMUTE(..., ...)

排列。匹配指定模式的任何排列。例如， PERMUTE(S1, S2) 匹配 S1 S2 或 S2 S1。PERMUTE() 可接受无限数量的实参。

... | ...

替代。指定应出现第一个符号或运算或者另一个符号或运算。例如 ( S3 S4 ) | PERMUTE(S1, S2)。替代运算符优先于连接运算符。

运算符	含义
``... ...``（空间）	连接。指定一个符号或运算应紧随另一个符号或运算。例如， `S1 S2` 表示为 `S2` 定义的条件应出现在为 `S1` 定义的条件之后。
`{- ... -}`	排除。在输出中排除包含的符号或运算。例如， `{- S3 -}` 在输出中排除运算符 `S3`。排除的行不会出现在输出中，但会包含在 `MEASURES` 表达式的求值中。
`( ... )`	分组。用于覆盖运算符的优先级，或为组中的符号或运算应用相同的量词。在此示例中，量词 `+` 不仅应用于 `S4`，而且还应用于序列 `{- S3 -} S4`。
`PERMUTE(..., ...)`	排列。匹配指定模式的任何排列。例如， `PERMUTE(S1, S2)` 匹配 `S1 S2` 或 `S2 S1`。`PERMUTE()` 可接受无限数量的实参。
`... \| ...`	替代。指定应出现第一个符号或运算或者另一个符号或运算。例如 `( S3 S4 ) \| PERMUTE(S1, S2)`。替代运算符优先于连接运算符。

可选分子句¶

ORDER BY：匹配前对行进行排序¶

ORDER BY orderItem1 [ , orderItem2 ... ]

其中：

orderItem ::= { <column_alias> | <expr> } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Copy

定义行的顺序，就像对窗口函数做的一样。这是将每个分区的各行传给 MATCH_RECOGNIZE 运算符的顺序。

有关更多信息，请参阅对行进行分区和排序。

PARTITION BY：将行分区到窗口¶

PARTITION BY <expr1> [ , <expr2> ... ]

将输入行集进行分区，就像对窗口函数做的一样。MATCH_RECOGNIZE 为每个生成的分区分别执行匹配。

分区不仅将彼此相关的行进行分组，还利用了 Snowflake 的分布式数据处理能力，因为可以并行处理不同的分区。

有关分区的更多信息，请参阅对行进行分区和排序。

MEASURES：指定其他输出列¶

MEASURES <expr1> [AS] <alias1> [ ... , <exprN> [AS] <aliasN> ]

Copy

“度量”是添加到 MATCH_RECOGNIZE 运算符输出中的可选附加列。MEASURES 分子句中的表达式与 DEFINE 分子句中的表达式具有相同的功能。有关更多信息，请参阅符号。

在 MEASURES 分子句中，以下特定于 MATCH_RECOGNIZE 的函数可用：

MATCH_NUMBER() 返回匹配项的序列号。MATCH_NUMBER 从 1 开始，而且对于每个匹配项递增。
MATCH_SEQUENCE_NUMBER() 返回匹配项中的行号。MATCH_SEQUENCE_NUMBER 是连续的，而且从 1 开始。
CLASSIFIER() 返回一个 TEXT 值，其包含相应行匹配的符号。例如，如果某行匹配符号 GT75，则 CLASSIFIER 函数返回字符串“GT75”。

备注

在指定度量时，请注意在 DEFINE 和 MEASURES 中使用的窗口函数的相关限制部分中提到的限制。

ROW(S) PER MATCH：指定要返回的行¶

{
  ONE ROW PER MATCH  |
  ALL ROWS PER MATCH [ { SHOW EMPTY MATCHES | OMIT EMPTY MATCHES | WITH UNMATCHED ROWS } ]
}

Copy

指定要返回哪些行才能成功匹配。此分子句是可选的。

ALL ROWS PER MATCH：返回匹配项中的所有行。
ONE ROW PER MATCH：无论匹配项中有多少行，都为每个匹配项返回一个汇总行。这是默认设置。

请注意以下特殊情况：

空匹配项：如果模式能够与零行匹配，则会出现空匹配项。例如，如果将模式定义为 A*，并且将匹配尝试开头的第一行分配给符号 B，则会生成一个仅包含该行的空匹配项，因为 A* 模式中的 * 量词允许将出现 0 次的 A 视为匹配项。对于此行， MEASURES 表达式的求值方式有所不同：
- CLASSIFIER 函数返回 NULL。
- 窗口函数返回 NULL。
- COUNT 函数返回 0。
不匹配的行：如果某行与模式不匹配，则称为不匹配的行。MATCH_RECOGNIZE 也可以配置为返回不匹配的行。对于不匹配的行，MEASURES 分子句中的表达式返回 NULL。

排除

模式定义中的 ({- ... -}) 排除语法允许用户在输出中排除某些行。如果排除了模式中所有匹配的符号，则如果已指定 ALL ROWS PER MATCH，将不会为该匹配项生成任何行。请注意， MATCH_NUMBER 无论如何都是递增的。排除的行不属于结果的一部分，但对 MEASURES 表达式求值时会包括在内。

使用排除语法时，可以按以下方式指定 ROWS PER MATCH 分子句：
- ONE ROW PER MATCH（默认）
  
  每次成功的匹配都只返回一行。MEASURES 分子句中窗口函数的默认窗口函数语义为 FINAL。
  
  MATCH_RECOGNIZE 运算符的输出列是 PARTITION BY 分子句中提供的所有表达式和所有 MEASURES 表达式。匹配项的所有结果行按以下两者进行分组： PARTITION BY 分子句中提供的表达式，以及使用所有度量的 ANY_VALUE 聚合函数的 MATCH_NUMBER。因此，如果度量对于同一匹配的不同行的求值结果为不同值，则输出是不确定的。
  
  省略 PARTITION BY 和 MEASURES 分子句会导致出错（指示结果未包含任何列）。
  
  对于空匹配项，将生成一行。不匹配的行不在输出中。
- ALL ROWS PER MATCH
  
  为属于匹配项的每行返回一行，但与标记为排除的模式部分匹配的行除外。
  
  在 MEASURES 分子句的计算中，排除的行仍会被考虑在内。
  
  根据 AFTER MATCH SKIP TO 分子句，匹配项可能会重叠，因此同一行可能会在输出中多次出现。
  
  MEASURES 分子句中窗口函数的默认窗口函数语义为 RUNNING。
  
  MATCH_RECOGNIZE 运算符的输出列是所输入的行集的列和 MEASURES 分子句中定义的列。
  
  以下选项可用于 ALL ROWS PER MATCH：
  - SHOW EMPTY MATCHES (default) 在输出中添加空匹配项。不输出不匹配的行。
  - OMIT EMPTY MATCHES 既不会输出空匹配项，也不会输出不匹配的行。但是， MATCH_NUMBER 仍然按空匹配项递增。
  - WITH UNMATCHED ROWS 将空匹配项和不匹配的行添加到输出中。如果使用此子句，则模式不得包含排除项。
有关使用排除项来减少无关输出的示例，请参阅在不相邻行中搜索模式。

AFTER MATCH SKIP：指定匹配后在何处继续¶

AFTER MATCH SKIP
{
    PAST LAST ROW   |
    TO NEXT ROW   |
    TO [ { FIRST | LAST} ] <symbol>
}

Copy

此分子句指定找到正匹配项后在何处继续进行匹配。

PAST LAST ROW (default)

在当前匹配项的最后一行之后继续进行匹配。

这样可以防止包含重叠行的匹配项。例如，如果股票模式包含 3 个连续的 V 形，则 PAST LAST ROW 会找到一个而不是两个 W 模式。
TO NEXT ROW

在当前匹配项的第一行之后继续进行匹配。

这允许包含重叠行的匹配项。例如，如果股票模式包含 3 个连续的 V 形，则 TO NEXT ROW 会找到两个 W 模式（第一个模式基于前两个 V 形，第二个 W 形基于第二和第三个 V 形，因此两个模式包含相同的 V）。
TO [ { FIRST | LAST } ] <符号>

在已与给定符号匹配的第一行或最后一行（默认）处继续进行匹配。

至少需要将一行映射到给定符号，否则会引发错误。

如果这没有跳过当前匹配项的第一行，则会引发错误。

使用说明¶

DEFINE 和 MEASURES 子句中的表达式¶

DEFINE 和 MEASURES 子句允许表达式。这些表达式可能很复杂，并且可能包含窗口函数和特殊的导航函数（它们是一种窗口函数）。

在大多数方面， DEFINE 和 MEASURES 中的表达式都遵循 Snowflake SQL 语法中其他地方的表达式规则。但是，存在一些差异，如下所述：

窗口函数:

导航函数允许引用除当前行以外的其他行。例如，要创建定义价格下降的表达式，需要将一行中的价格与另一行中的价格进行比较。导航函数包括：

PREV( expr [ , offset [, default ] ] ) 导航到 MEASURES 分子句中当前匹配项内的前一行。

此函数目前在 DEFINE 分子句中不可用。您可以改为使用 LAG，它导航到当前窗口框架中的上一行。
NEXT( expr [ , offset [ , default ] ] ) 导航到当前窗口框架中的下一行。此函数等效于 LEAD。
FIRST( expr ) 导航到 MEASURES 分子句中当前匹配项的第一行。

此函数目前在 DEFINE 分子句中不可用。您可以改为使用 FIRST_VALUE，它导航到当前窗口框架的第一行。
LAST( expr ) 导航到当前窗口框架的最后一行。此函数类似于 LAST_VALUE，但对于 LAST，当 LAST 在 DEFINE 分子句中使用时，窗口框架被限制为当前匹配尝试的当前行。

有关使用导航函数的示例，请参阅返回有关匹配的信息。

通常，当在 MATCH_RECOGNIZE 子句中使用窗口函数时，窗口函数不需要有自己的 OVER (PARTITION BY ... ORDER BY ...) 子句。窗口由 MATCH_RECOGNIZE 子句的 PARTITION BY 和 ORDER BY 隐式确定。（但是，请参阅在 DEFINE 和 MEASURES 中使用的窗口函数的相关限制，以了解某些例外情况。）

通常，窗口框架也是从正使用窗口函数的当前上下文隐式派生的。框架的下限定义如下：

在 DEFINE 分子句中：

框架在当前匹配尝试的开头开始，但使用 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE 时除外。

在 MEASURES 分子句中：

框架在找到的匹配项的开头开始。

可以使用 RUNNING 或 FINAL 语义指定窗口框架的边缘。

expr ::= ... [ { RUNNING | FINAL } ] windowFunction ...
Copy
RUNNING：

通常，框架在当前行结束。但是，存在以下例外情况：

在 DEFINE 分子句中，对于 LAG、LEAD、FIRST_VALUE、LAST_VALUE 和 NEXT，框架在窗口的最后一行结束。

在 MEASURES 分子句中，对于 PREV、NEXT、LAG 和 LEAD，框架在窗口的最后一行结束。

在 DEFINE 分子句中， RUNNING 是默认（和唯一允许）的语义。

在 MEASURES 分子句中，当使用 ALL ROWS PER MATCH 分子句时， RUNNING 是默认语义。

FINAL：

框架在匹配项的最后一行结束。

FINAL 仅允许在 MEASURES 分子句中使用。应用 ONE ROW PER MATCH 时，它是默认语义。

符号谓词:

DEFINE 和 MEASURES 分子句中的表达式允许使用符号作为列引用的谓词。

predicatedColumnReference ::= <symbol>.<column>

Copy

<symbol> indicates a row that was matched, and the <column> 标识该行中的特定列。

带谓词的列引用意味着周围的窗口函数仅查看最终映射到指定符号的行。

带谓词的列引用可以在窗口函数的外部和内部使用。如果在窗口函数外部使用，则 <symbol>.<column> 与 LAST(<符号>.<column>) 相同。如果在窗口函数内部使用，则所有列引用都需要以同一个符号作为谓词，或者全部不带谓词。

以下内容说明了与导航相关的函数在带谓词的列引用下的行为方式：

PREV/LAG( ... <symbol>.<column> ... , <offset>) 从当前行（如果是 FINAL 语义，则为最后一行）开始（包括当前行），向后搜索窗口框架，以查找最终映射到指定 <symbol>, and then goes <offset> (default is 1) rows backwards, ignoring the symbol those rows were mapped to. If the searched part of the frame does not contain a row mapped to <symbol>，或者搜索将超出框架边缘，则返回 NULL。
NEXT/LEAD( ... <symbol>.<column> ... , <offset>) 从当前行（如果是 FINAL 语义，则为最后一行）开始（包括当前行），向后搜索窗口框架，以查找最终映射到指定 <symbol>, and then goes <offset> (default is 1) rows forward, ignoring the symbol those rows were mapped to. If the searched part of the frame does not contain a row mapped to <symbol>，或者搜索将超出框架边缘，则返回 NULL。
FIRST/FIRST_VALUE( ... <symbol>.<column> ... ) 从第一行开始（包括第一行）向前搜索窗口框架，直到当前行（包括当前行，而如果是 FINAL 语义，则为最后一行），以查找最终映射到指定 <symbol>. If the searched part of the frame does not contain a row mapped to <symbol>，则返回 NULL。
LAST/LAST_VALUE( ... <symbol>.<column> ... ) 从当前行（如果是 FINAL 语义，则为最后一行）开始（包括当前行），向后搜索窗口框架，以查找最终映射到指定 <symbol>. If the searched part of the frame does not contain a row mapped to <symbol>，则返回 NULL。

备注

在 DEFINE 和 MEASURES 中使用的窗口函数的相关限制部分中记录了窗口函数的相关限制。

在 DEFINE 和 MEASURES 中使用的窗口函数的相关限制¶

DEFINE 和 MEASURES 分子句中的表达式可以包含窗口函数。但是，在这些分子句中使用窗口函数存在一些限制。这些限制如下表所示：

函数

DEFINE (Running) [column/symbol.column]

MEASURES (Running) [column/symbol.column]

MEASURES (Final) [column/symbol.column]

列

✔ / ❌

✔ / ❌

✔ / ✔

PREV(...)

❌ / ❌

✔ / ❌

✔ / ❌

NEXT(...)

✔ / ❌

✔ / ❌

✔ / ❌

FIRST(...)

❌ / ❌

✔ / ❌

✔ / ✔

LAST(...)

✔ / ❌

✔ / ❌

✔ / ✔

LAG()

✔ / ❌

✔ / ❌

✔ / ❌

LEAD()

✔ / ❌

✔ / ❌

✔ / ❌

FIRST_VALUE()

✔ / ❌

✔ / ❌

✔ / ✔

LAST_VALUE()

✔ / ❌

✔ / ❌

✔ / ✔

聚合 [1]

✔ / ❌

✔ / ✔

✔ / ✔

其他窗口函数 [1]

✔ / ❌

✔ / ❌

✔ / ❌

函数	DEFINE (Running) [column/symbol.column]	MEASURES (Running) [column/symbol.column]	MEASURES (Final) [column/symbol.column]
列	✔ / ❌	✔ / ❌	✔ / ✔
PREV(...)	❌ / ❌	✔ / ❌	✔ / ❌
NEXT(...)	✔ / ❌	✔ / ❌	✔ / ❌
FIRST(...)	❌ / ❌	✔ / ❌	✔ / ✔
LAST(...)	✔ / ❌	✔ / ❌	✔ / ✔
LAG()	✔ / ❌	✔ / ❌	✔ / ❌
LEAD()	✔ / ❌	✔ / ❌	✔ / ❌
FIRST_VALUE()	✔ / ❌	✔ / ❌	✔ / ✔
LAST_VALUE()	✔ / ❌	✔ / ❌	✔ / ✔
聚合 [1]	✔ / ❌	✔ / ✔	✔ / ✔
其他窗口函数 [1]	✔ / ❌	✔ / ❌	✔ / ❌

特定于 MATCH_RECOGNIZE 的函数 MATCH_NUMBER()、MATCH_SEQUENCE_NUMBER() 和 CLASSIFIER() 目前在 DEFINE 分子句中不可用。

故障排除¶

使用 ONE ROW PER MATCH 时出现错误消息：“SELECT with no columns”¶

使用 ONE ROW PER MATCH 子句时，在 SELECT 的投影子句中只允许使用 PARTITION BY 和 MEASURES 分子句中的列和表达式。如果尝试使用没有 PARTITION BY 或 MEASURES 子句的 MATCH_RECOGNIZE，则会遇到类似 SELECT with no columns 的错误。

有关 ONE ROW PER MATCH 与 ALL ROWS PER MATCH 对比的更多信息，请参阅为每个匹配项生成一行与为每个匹配项生成所有行。

示例¶

识别与模式匹配的行序列主题包含许多示例，其中包括一些比此处大多数示例更简单的示例。如果还不熟悉 MATCH_RECOGNIZE，则可能需要先查看这些示例。

以下一些示例使用下面的表和数据：

create table stock_price_history (company TEXT, price_date DATE, price INT);

Copy

insert into stock_price_history values
    ('ABCD', '2020-10-01', 50),
    ('XYZ' , '2020-10-01', 89),
    ('ABCD', '2020-10-02', 36),
    ('XYZ' , '2020-10-02', 24),
    ('ABCD', '2020-10-03', 39),
    ('XYZ' , '2020-10-03', 37),
    ('ABCD', '2020-10-04', 42),
    ('XYZ' , '2020-10-04', 63),
    ('ABCD', '2020-10-05', 30),
    ('XYZ' , '2020-10-05', 65),
    ('ABCD', '2020-10-06', 47),
    ('XYZ' , '2020-10-06', 56),
    ('ABCD', '2020-10-07', 71),
    ('XYZ' , '2020-10-07', 50),
    ('ABCD', '2020-10-08', 80),
    ('XYZ' , '2020-10-08', 54),
    ('ABCD', '2020-10-09', 75),
    ('XYZ' , '2020-10-09', 30),
    ('ABCD', '2020-10-10', 63),
    ('XYZ' , '2020-10-10', 32);

Copy

下图显示了曲线的形状：

为每个 `V` 形报告一个汇总行¶

以下查询在先前出现的 stock_price_history 中搜索所有 V 形。在查询和输出之后，将对输出进行更详细的解释。

SELECT * FROM stock_price_history
  MATCH_RECOGNIZE(
    PARTITION BY company
    ORDER BY price_date
    MEASURES
      MATCH_NUMBER() AS match_number,
      FIRST(price_date) AS start_date,
      LAST(price_date) AS end_date,
      COUNT(*) AS rows_in_sequence,
      COUNT(row_with_price_decrease.*) AS num_decreases,
      COUNT(row_with_price_increase.*) AS num_increases
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO LAST row_with_price_increase
    PATTERN(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
    DEFINE
      row_with_price_decrease AS price < LAG(price),
      row_with_price_increase AS price > LAG(price)
  )
ORDER BY company, match_number;
+---------+--------------+------------+------------+------------------+---------------+---------------+
| COMPANY | MATCH_NUMBER | START_DATE | END_DATE   | ROWS_IN_SEQUENCE | NUM_DECREASES | NUM_INCREASES |
|---------+--------------+------------+------------+------------------+---------------+---------------|
| ABCD    |            1 | 2020-10-01 | 2020-10-04 |                4 |             1 |             2 |
| ABCD    |            2 | 2020-10-04 | 2020-10-08 |                5 |             1 |             3 |
| XYZ     |            1 | 2020-10-01 | 2020-10-05 |                5 |             1 |             3 |
| XYZ     |            2 | 2020-10-05 | 2020-10-08 |                4 |             2 |             1 |
| XYZ     |            3 | 2020-10-08 | 2020-10-10 |                3 |             1 |             1 |
+---------+--------------+------------+------------+------------------+---------------+---------------+

Copy

输出为每个匹配项显示一行（无论匹配项中有多少行）。

输出包含以下列：

COMPANY：公司的股票代码。
MATCH_NUMBER 是一个序列号，用于标识该数据集中的相应匹配项（例如，第一个匹配项的 MATCH_NUMBER 为 1，第二个匹配项的 MATCH_NUMBER 为 2，依此类推）。如果数据已分区，则 MATCH_NUMBER 是分区内的序列号（在本示例中，是每家公司/每只股票的序列号）。
START_DATE：模式的此次出现的开始日期。
END_DATE：模式的此次出现的结束日期。
ROWS_IN_SEQUENCE：这是匹配项中的行数。例如，第一个匹配项基于在 4 天（10 月 1 日至 10 月 4 日）内测得的价格，因此 ROWS_IN_SEQUENCE 是 4。
NUM_DECREASES：这是价格下跌的天数（在匹配项中）。例如，在第一个匹配项中，价格下跌了 1 天，然后上涨了 2 天，因此 NUM_DECREASES 是 1。
NUM_INCREASES：这是价格上涨的天数（在匹配项中）。例如，在第一个匹配项中，价格下跌了 1 天，然后上涨了 2 天，因此 NUM_INCREASES 是 2。

报告一家公司的所有匹配项的所有行¶

此示例返回每个匹配项中的所有行（而不仅仅是每个匹配项的一个汇总行）。此模式搜索“ABCD”公司的价格上涨：

select price_date, match_number, msq, price, cl from
  (select * from stock_price_history where company='ABCD') match_recognize(
    order by price_date
    measures
        match_number() as "MATCH_NUMBER",
        match_sequence_number() as msq,
        classifier() as cl
    all rows per match
    pattern(ANY_ROW UP+)
    define
        ANY_ROW AS TRUE,
        UP as price > lag(price)
)
order by match_number, msq;
+------------+--------------+-----+-------+---------+
| PRICE_DATE | MATCH_NUMBER | MSQ | PRICE | CL      |
|------------+--------------+-----+-------+---------|
| 2020-10-02 |            1 |   1 |    36 | ANY_ROW |
| 2020-10-03 |            1 |   2 |    39 | UP      |
| 2020-10-04 |            1 |   3 |    42 | UP      |
| 2020-10-05 |            2 |   1 |    30 | ANY_ROW |
| 2020-10-06 |            2 |   2 |    47 | UP      |
| 2020-10-07 |            2 |   3 |    71 | UP      |
| 2020-10-08 |            2 |   4 |    80 | UP      |
+------------+--------------+-----+-------+---------+

Copy

省略空匹配项¶

此示例搜索高于公司整个图表的平均价格的价格范围。此示例省略了空匹配项。但请注意，空匹配项仍然会让 MATCH_NUMBER 递增：

select * from stock_price_history match_recognize(
    partition by company
    order by price_date
    measures
        match_number() as "MATCH_NUMBER"
    all rows per match omit empty matches
    pattern(OVERAVG*)
    define
        OVERAVG as price > avg(price) over (rows between unbounded
                                  preceding and unbounded following)
)
order by company, price_date;
+---------+------------+-------+--------------+
| COMPANY | PRICE_DATE | PRICE | MATCH_NUMBER |
|---------+------------+-------+--------------|
| ABCD    | 2020-10-07 |    71 |            7 |
| ABCD    | 2020-10-08 |    80 |            7 |
| ABCD    | 2020-10-09 |    75 |            7 |
| ABCD    | 2020-10-10 |    63 |            7 |
| XYZ     | 2020-10-01 |    89 |            1 |
| XYZ     | 2020-10-04 |    63 |            4 |
| XYZ     | 2020-10-05 |    65 |            4 |
| XYZ     | 2020-10-06 |    56 |            4 |
| XYZ     | 2020-10-08 |    54 |            6 |
+---------+------------+-------+--------------+

Copy

演示 WITH UNMATCHED ROWS 选项¶

此示例演示 WITH UNMATCHED ROWS option。与上面的省略空匹配项示例一样，此示例搜索高于每家公司图表的平均价格的价格范围。请注意，此查询中的量词是 +，而前一个查询中的量词是 *：

select * from stock_price_history match_recognize(
    partition by company
    order by price_date
    measures
        match_number() as "MATCH_NUMBER",
        classifier() as cl
    all rows per match with unmatched rows
    pattern(OVERAVG+)
    define
        OVERAVG as price > avg(price) over (rows between unbounded
                                 preceding and unbounded following)
)
order by company, price_date;
+---------+------------+-------+--------------+---------+
| COMPANY | PRICE_DATE | PRICE | MATCH_NUMBER | CL      |
|---------+------------+-------+--------------+---------|
| ABCD    | 2020-10-01 |    50 |         NULL | NULL    |
| ABCD    | 2020-10-02 |    36 |         NULL | NULL    |
| ABCD    | 2020-10-03 |    39 |         NULL | NULL    |
| ABCD    | 2020-10-04 |    42 |         NULL | NULL    |
| ABCD    | 2020-10-05 |    30 |         NULL | NULL    |
| ABCD    | 2020-10-06 |    47 |         NULL | NULL    |
| ABCD    | 2020-10-07 |    71 |            1 | OVERAVG |
| ABCD    | 2020-10-08 |    80 |            1 | OVERAVG |
| ABCD    | 2020-10-09 |    75 |            1 | OVERAVG |
| ABCD    | 2020-10-10 |    63 |            1 | OVERAVG |
| XYZ     | 2020-10-01 |    89 |            1 | OVERAVG |
| XYZ     | 2020-10-02 |    24 |         NULL | NULL    |
| XYZ     | 2020-10-03 |    37 |         NULL | NULL    |
| XYZ     | 2020-10-04 |    63 |            2 | OVERAVG |
| XYZ     | 2020-10-05 |    65 |            2 | OVERAVG |
| XYZ     | 2020-10-06 |    56 |            2 | OVERAVG |
| XYZ     | 2020-10-07 |    50 |         NULL | NULL    |
| XYZ     | 2020-10-08 |    54 |            3 | OVERAVG |
| XYZ     | 2020-10-09 |    30 |         NULL | NULL    |
| XYZ     | 2020-10-10 |    32 |         NULL | NULL    |
+---------+------------+-------+--------------+---------+

Copy

在 MEASURES 子句中演示符号谓词¶

此示例演示了带符号谓词的 <symbol>.<column> 表示法的用法：

SELECT company, price_date, price, "FINAL FIRST(LT45.price)", "FINAL LAST(LT45.price)"
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               FINAL FIRST(LT45.price) AS "FINAL FIRST(LT45.price)",
               FINAL LAST(LT45.price)  AS "FINAL LAST(LT45.price)"
           ALL ROWS PER MATCH
           AFTER MATCH SKIP PAST LAST ROW
           PATTERN (LT45 LT45)
           DEFINE
               LT45 AS price < 45.00
           )
    WHERE company = 'ABCD'
    ORDER BY price_date;
+---------+------------+-------+-------------------------+------------------------+
| COMPANY | PRICE_DATE | PRICE | FINAL FIRST(LT45.price) | FINAL LAST(LT45.price) |
|---------+------------+-------+-------------------------+------------------------|
| ABCD    | 2020-10-02 |    36 |                      36 |                     39 |
| ABCD    | 2020-10-03 |    39 |                      36 |                     39 |
| ABCD    | 2020-10-04 |    42 |                      42 |                     30 |
| ABCD    | 2020-10-05 |    30 |                      42 |                     30 |
+---------+------------+-------+-------------------------+------------------------+

Copy

MATCH_RECOGNIZE¶

语法¶

必需的分子句¶

DEFINE：定义符号¶

PATTERN：指定要匹配的模式¶

可选分子句¶

ORDER BY：匹配前对行进行排序¶

PARTITION BY：将行分区到窗口¶

MEASURES：指定其他输出列¶

ROW(S) PER MATCH：指定要返回的行¶

AFTER MATCH SKIP：指定匹配后在何处继续¶

使用说明¶

DEFINE 和 MEASURES 子句中的表达式¶

在 DEFINE 和 MEASURES 中使用的窗口函数的相关限制¶

故障排除¶

使用 ONE ROW PER MATCH 时出现错误消息：“SELECT with no columns”¶

示例¶

为每个 V 形报告一个汇总行¶

报告一家公司的所有匹配项的所有行¶

省略空匹配项¶

演示 WITH UNMATCHED ROWS 选项¶

在 MEASURES 子句中演示符号谓词¶

为每个 `V` 形报告一个汇总行¶