使用MATCH_RECOGNIZE查找无后续MODIFICATION的INIT-REVERSAL审计记录

现有交易审计表audit_table，存储全量交易操作审计日志：

• 所有审计流程均以INIT操作开头，后续可能出现REVERSAL（冲正）、MODIFICATION（修改）两类操作
• 表结构包含4个字段：TRANSACTION_ID（交易ID）、ACTION（操作类型）、AMOUNT（交易金额）、AUDIT_TS（审计时间戳）
  需要筛选符合以下规则的审计记录：
• 操作序列为INIT -> REVERSAL
• REVERSAL操作之后不存在任何MODIFICATION操作

样例数据判定规则：

• 符合要求：交易ID 555
• 不符合要求：
  • 交易111、222：存在INIT+REVERSAL序列，但后续存在MODIFICATION操作
  • 交易0、444：仅存在INIT操作，无后续REVERSAL
  • 交易333：无前置INIT操作，序列非法

原查询使用PATTERN (INIT REVERSAL$)写法存在两个核心问题：

1. 未添加序列起始锚点^，无法保证匹配到的INIT是交易流程的第一个操作，会误识别非起始位置的INIT操作
2. 仅在模式中声明了INIT、REVERSAL的匹配规则，未对REVERSAL之后的操作做约束，且定义的MODIFICATION规则未在模式中引用，导致会匹配到REVERSAL后带MODIFICATION的子序列，返回错误结果。
   另外原写法中iff(ACTION='xxx',TRUE,FALSE)属于冗余写法，布尔表达式本身即可返回TRUE/FALSE结果，无需嵌套函数。

场景1：严格匹配全流程仅为INIT->REVERSAL（匹配样例规则） #

如果业务要求交易全生命周期仅包含INIT、REVERSAL两个操作，REVERSAL为交易最后一个操作，直接使用首尾锚点严格匹配全序列即可：

SELECT *
FROM audit_table
MATCH_RECOGNIZE(
    PARTITION BY transaction_id
    ORDER BY audit_ts
    ALL ROWS PER MATCH
    PATTERN (^ INIT REVERSAL $)
    DEFINE
        INIT AS ACTION = 'INIT',
        REVERSAL AS ACTION = 'REVERSAL'
);

场景2：允许INIT与REVERSAL间存在其他操作，仅禁止REVERSAL后出现MODIFICATION #

如果业务允许INIT到REVERSAL之间存在其他非REVERSAL操作，仅要求REVERSAL之后无MODIFICATION，可使用如下模式：

SELECT *
FROM audit_table
MATCH_RECOGNIZE(
    PARTITION BY transaction_id
    ORDER BY audit_ts
    ALL ROWS PER MATCH
    PATTERN (^ INIT BEFORE_REV* REVERSAL AFTER_REV* $)
    DEFINE
        INIT AS ACTION = 'INIT',
        BEFORE_REV AS ACTION <> 'REVERSAL', -- 匹配INIT到REVERSAL之间的操作，不允许提前出现REVERSAL
        REVERSAL AS ACTION = 'REVERSAL',
        AFTER_REV AS ACTION <> 'MODIFICATION' -- 匹配REVERSAL之后的操作，禁止出现MODIFICATION
);

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