使用MATCH_RECOGNIZE查找连续10天以上购买客户的问题排查与修复

原因分析 #

客户1未出现在结果中的核心问题有两点：

1. 时间戳间隔不匹配：插入客户1的记录时，每条时间戳比前一条早1天1秒，按purchase_date升序排列后，下一条记录的时间戳比当前记录大1天1秒，而非原查询要求的精确1天。原定义next(purchase_date)=purchase_date + interval '1' day因时间差不满足，无法匹配任何连续序列。
2. 需求匹配偏差：通常“连续10天购买”指自然日连续（每天至少1次购买），而非严格的24小时时间间隔，原查询的精确时间条件不符合这类通用需求。

修复后的查询语句 #

方案1：匹配自然日连续购买（推荐） #

此方案忽略具体时间，仅判断日期是否连续，符合绝大多数业务场景需求：

select customer_id, first_date, last_date, match_length
from purchases
match_recognize(
    partition by customer_id
    order by trunc(purchase_date)
    measures
        first(purchase_date) as first_date,
        last(purchase_date) as last_date,
        count(*) as match_length
    one row per match
    pattern(P{10,})
    define P as next(trunc(purchase_date)) = trunc(purchase_date) + interval '1' day
);

方案2：匹配近似24小时间隔的时间戳（适配样本数据） #

如果确实需要基于时间戳的接近24小时间隔判断，可允许微小时间误差：

select customer_id, first_date, last_date, match_length
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,
        count(*) as match_length
    one row per match
    pattern(P{10,})
    define P as next(purchase_date) 
               between purchase_date + interval '23:59:59' hour to second 
               and purchase_date + interval '1 00:00:01' day to second
);

结果验证 #

方案1会返回客户1的连续15天购买记录，符合预期；方案2也能匹配客户1的样本数据，同时保留时间戳级别的判断逻辑。

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