关于SQL中MATCH_RECOGNIZE的pattern与define子句作用及订单早/晚判定查询结果计算逻辑的问询

咱们一步步来拆解这个查询的核心逻辑，先从你最关心的MATCH_RECOGNIZE组件说起，再对应你的订单规则梳理完整流程。

一、MATCH_RECOGNIZE核心子句解析 #

这个查询用MATCH_RECOGNIZE来识别连续的提前订单链和中断的延迟订单，核心配置的作用如下：

1. 分组与排序基础 #

partition by id, disease, drug
order by ship_date

先把数据按患者ID、疾病、药物分组，每组内按发货日期排序，保证我们是按时间顺序处理每个患者的同一种药物订单，这是后续判断的基础。

2. pattern (a b*)：定义匹配模式 #

这个模式的含义很直白：

• a代表每组的第一条订单（也就是你说的first order）；
• b*代表紧跟在a后面的任意多条符合规则的订单（包括0条）。
  简单说就是：每组第一个订单标记为a，后续订单如果符合提前订单规则就归为b；如果不符合（延迟订单），就会开启新的a模式，相当于中断之前的提前订单链。

3. define b as ship_date <= a.ship_date + sum(supply) - supply：定义提前订单规则 #

这是判断订单是否为提前订单的核心条件，翻译成人话就是：当前订单的发货日期 ≤ 从第一个订单（a）到上一个订单的累计供应天数对应的到期日期。

• sum(supply)是从a到当前b订单的所有供应量总和；
• sum(supply) - supply减去当前订单的供应量，得到从a到上一个订单的累计供应量；
• a.ship_date + 这个累计值就是上一次预期的发货日期，当前订单如果早于等于这个日期，就属于提前订单（b类）。

4. measures：计算匹配过程的中间值 #

measures a.ship_date + sum(supply) - supply as e_date,
         classifier() as cls

• e_date：对于a类订单，它等于自身的发货日期（因为sum(supply)-supply=0）；对于b类订单，它就是上一次预期的发货日期，用来判断当前订单是否提前。
• classifier()：给每条记录打标签，标记它是a还是b，后续用来区分延迟/提前订单。

二、整个查询的完整计算流程 #

第一步：MATCH_RECOGNIZE生成中间结果 #

经过匹配后，每条订单会得到cls（a/b标签）和e_date（上一次预期日期）。比如你的第一条订单是a，第二条订单因为发货日期07-06-2020 ≤ 09-06-2020（10-05+30天），被标记为b，e_date就是09-06-2020。

第二步：prep CTE计算最终预期日期（exp_date） #

这一步严格遵循你的规则调整预期日期：

case cls when 'A' then lag(e_date + supply) over (partition by id, disease, drug order by ship_date) else e_date end as exp_date

• 如果是A类（延迟订单）：按照规则，下一次预期日期是上一次的预期日期 + 上一次的供应量，所以用lag(e_date + supply)取上一条记录的e_date（上一次预期日期）加上上一条的供应量，得到新的预期日期。比如你的第三条订单是延迟订单，它的exp_date就是第二条的e_date（09-06）+30=09-07-2020，完全符合你的规则。
• 如果是B类（提前订单）：直接用e_date作为当前订单的预期日期，用来标记它是提前的。

第三步：生成最终输出字段 #

最后把中间结果转换成你需要的格式：

• late_or_early：exp_date为空的是first order，A类标记为late，B类标记为early；
• gap：只有延迟订单（A类）计算gap，用实际发货日期减去预期日期，得到延迟的天数。

这样一步步下来，就完全贴合你设定的规则，生成了预期的输出表。

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