MATCH_RECOGNIZE函数PATTERN使用+量词结果差异原因咨询

两个查询的差异本质是MATCH_RECOGNIZE中模式量词规则和MEASURES默认取值规则共同作用的结果： #

1. 模式PATTERN (same_dept)的执行逻辑 #

无任何量词的模式代表匹配长度为1的独立行序列，逐行判断规则：

• same_dept的定义为FIRST(dept_id) = dept_id，当序列长度为1时，FIRST(dept_id)就是当前行自身的部门ID，所有行都满足匹配条件，因此返回全部12条原始记录。

2. 模式PATTERN (same_dept+)的执行逻辑 #

+是正则匹配中的量词，代表匹配长度≥1的连续符合规则的行序列，且默认采用贪婪匹配策略，会尽可能把连续满足规则的行合并为一个匹配组：

• 你的原始数据按dept_id连续分组，刚好可以分为4个连续同部门的行组：
  • 组1：前3行，dept_id=5652
  • 组2：接下来2行，dept_id=5647
  • 组3：接下来4行，dept_id=5649
  • 组4：最后3行，dept_id=5652
• 你的MEASURES子句没有指定取序列内的哪一行字段值，默认会取每个匹配组的最后一行的对应字段，因此最终返回4条记录，刚好对应4个同部门组的最后一行数据。

扩展说明 #

如果你的需求是合并连续同部门的任职记录，得到每个任职阶段的完整起止时间，可以修改MEASURES子句明确取值位置：

SELECT *
FROM   empdepthist
MATCH_RECOGNIZE (
  PARTITION BY login_name
  ORDER BY startdate
  MEASURES
    FIRST(emp_name) AS emp_name,
    FIRST(dept_id) AS dept_id,
    FIRST(dept_name) AS dept_name,
    FIRST(startDate) AS tenure_start,
    LAST(endDate) AS tenure_end 
  PATTERN (same_dept+)
  DEFINE same_dept AS FIRST(dept_id) = dept_id
)

这样就可以得到4条完整的任职周期记录，每条对应一次完整的部门任职时间段。 #

内容的提问来源于stack exchange，提问作者Kate