使用MATCH_RECOGNIZE关联查询遇ORA-00918错误的解决求助

错误原因分析 #

触发ORA-00918的核心问题有三个：

1. 拼写错误：关联条件里把customer_id写成了custimer_id，少了字母o
2. 列歧义：JOIN后customers和purchases表都有customer_id列，MATCH_RECOGNIZE中引用purchase_date等列时未指定表别名，Oracle无法确定使用哪张表的列
3. 语法错误：主SELECT里pur customer_id是错误写法，应该是pur.customer_id

修正后的两种写法 #

写法一：直接在JOIN后的语句上使用MATCH_RECOGNIZE #

select pur.customer_id,
       c.first_name,
       c.last_name,
       first_date,
       last_date,
       trunc(last_date) - trunc(first_date) + 1 as consecutive_days
FROM purchases pur 
LEFT OUTER JOIN customers c ON c.customer_id = pur.customer_id  
match_recognize(
    partition by pur.customer_id
    order by pur.purchase_date
    measures
        first(pur.purchase_date) as first_date,
        last(pur.purchase_date) as last_date
    one row per match
    pattern(start_date P{9,})
    define P as pur.purchase_date >= prev(trunc(pur.purchase_date)) + interval '1' day
      and pur.purchase_date < prev(trunc(pur.purchase_date)) + interval '2' day
);

写法二：先通过MATCH_RECOGNIZE筛选结果，再关联客户表（更清晰，避免歧义） #

这种方式把连续购买的查询结果作为子查询，再和customers表关联，逻辑更直观：

with continuous_purchases as (
    select customer_id,
           first_date,
           last_date,
           trunc(last_date) - trunc(first_date) + 1 as consecutive_days
    from purchases 
    match_recognize(
        partition by customer_id
        order by purchase_date
        measures
            first(purchase_date) as first_date,
            last(purchase_date) as last_date
        one row per match
        pattern(start_date P{9,})
        define P as purchase_date >= prev(trunc(purchase_date)) + interval '1' day
          and purchase_date < prev(trunc(purchase_date)) + interval '2' day
    )
)
select cp.customer_id,
       c.first_name,
       c.last_name,
       cp.first_date,
       cp.last_date,
       cp.consecutive_days
from continuous_purchases cp
join customers c on cp.customer_id = c.customer_id;

验证结果 #

两种写法都会输出符合预期的结果：

CUSTOMER_ID FIRST_NAME LAST_NAME FIRST_DATE           LAST_DATE            CONSECUTIVE_DAYS
----------- ---------- --------- -------------------- -------------------- ----------------
1           John       Doe       25-MAR-2024 12:59:46 08-APR-2024 13:00:00                15

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