如何在Snowflake中实现Oracle关联模式MATCH_RECOGNIZE的订单客户回退检测

需求说明 #

从以下订单-客户数据中，识别出订单的客户被变更（可多次变更为不同客户）后又回退至原始客户的情况：

在Oracle中可通过带关联模式定义的MATCH_RECOGNIZE实现，但Snowflake暂不支持该特性，以下是Snowflake中的最优实现方案。

方案一：快速筛选符合条件的订单 #

该方案通过窗口函数标记订单的原始客户、是否发生过变更、是否回退至原始客户，最终筛选出符合要求的订单：

WITH order_customer_with_meta AS (
    SELECT 
        ORDER_NUM,
        CUSTOMER,
        LOAD_DATE,
        -- 获取当前订单的原始客户（最早加载的记录对应的客户）
        FIRST_VALUE(CUSTOMER) OVER (PARTITION BY ORDER_NUM ORDER BY LOAD_DATE) AS ORIGINAL_CUSTOMER,
        -- 标记当前记录是否为原始客户
        CASE WHEN CUSTOMER = ORIGINAL_CUSTOMER THEN 1 ELSE 0 END AS IS_ORIGINAL,
        -- 标记该订单是否存在非原始客户的变更记录
        MAX(CASE WHEN CUSTOMER != ORIGINAL_CUSTOMER THEN 1 ELSE 0 END) OVER (PARTITION BY ORDER_NUM) AS HAS_MODIFIED
    FROM order_customer
),
order_reversal_check AS (
    SELECT 
        ORDER_NUM,
        ORIGINAL_CUSTOMER,
        -- 判断是否存在变更后回退至原始客户的记录
        MAX(CASE 
            WHEN IS_ORIGINAL = 1 
                AND LOAD_DATE > (SELECT MIN(LOAD_DATE) FROM order_customer_with_meta ocwm WHERE ocwm.ORDER_NUM = ocwm_main.ORDER_NUM AND ocwm.IS_ORIGINAL = 0)
            THEN 1 ELSE 0 END) AS HAS_REVERSED
    FROM order_customer_with_meta ocwm_main
    GROUP BY ORDER_NUM, ORIGINAL_CUSTOMER
)
SELECT ORDER_NUM, ORIGINAL_CUSTOMER
FROM order_reversal_check
WHERE HAS_MODIFIED = 1 AND HAS_REVERSED = 1;

执行后会返回符合条件的订单（如示例中的ORDER_NUM=111）。

方案二：获取具体的变更-回退记录序列 #

如果需要查看订单对应的完整变更、回退记录，可通过阶段分组的方式实现：

WITH ordered_data AS (
    SELECT 
        ORDER_NUM,
        CUSTOMER,
        LOAD_DATE,
        FIRST_VALUE(CUSTOMER) OVER (PARTITION BY ORDER_NUM ORDER BY LOAD_DATE) AS ORIGINAL_CUSTOMER,
        CASE WHEN CUSTOMER = ORIGINAL_CUSTOMER THEN 'ORIGINAL' ELSE 'MODIFIED' END AS CUST_TYPE,
        ROW_NUMBER() OVER (PARTITION BY ORDER_NUM ORDER BY LOAD_DATE) AS RN
    FROM order_customer
),
change_points AS (
    SELECT 
        ORDER_NUM,
        RN,
        CUST_TYPE,
        -- 标记客户类型发生变化的记录点
        CASE WHEN CUST_TYPE != LAG(CUST_TYPE) OVER (PARTITION BY ORDER_NUM ORDER BY RN) THEN 1 ELSE 0 END AS CHANGE_FLAG
    FROM ordered_data
),
sequence_groups AS (
    SELECT 
        od.*,
        -- 累计变化标记，将连续相同客户类型的记录归为同一阶段
        SUM(cp.CHANGE_FLAG) OVER (PARTITION BY od.ORDER_NUM ORDER BY od.RN) AS SEQ_GROUP
    FROM ordered_data od
    JOIN change_points cp ON od.ORDER_NUM = cp.ORDER_NUM AND od.RN = cp.RN
)
SELECT 
    ORDER_NUM,
    CUSTOMER,
    LOAD_DATE,
    ORIGINAL_CUSTOMER
FROM sequence_groups
WHERE ORDER_NUM IN (
    SELECT ORDER_NUM
    FROM sequence_groups
    GROUP BY ORDER_NUM
    -- 筛选存在"原始→变更→原始"阶段序列的订单
    HAVING COUNT(DISTINCT SEQ_GROUP) >= 3 
        AND MAX(CASE WHEN CUST_TYPE = 'ORIGINAL' THEN SEQ_GROUP END) > MIN(CASE WHEN CUST_TYPE = 'MODIFIED' THEN SEQ_GROUP END)
);

执行后会返回ORDER_NUM=111的三条完整记录，清晰展示客户从aaa变更为bbb再回退到aaa的过程。

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