如何判断指定分组的记录是否遵循CLICKED→CLAIMED→BOUGHT的正确状态顺序

需求说明 #

现有业务表存储用户行为状态，固定状态优先级顺序为 CLICKED → CLAIMED → BOUGHT，需要按number_id分组校验组内状态对应的日期是否符合优先级先后规则：

• 日期相等视为符合先后顺序
• 缺失高优先级状态不影响校验，现有状态顺序合规即整体合规
• 任意两个相邻优先级状态的日期顺序颠倒，整体判定为不合规

校验SQL #

行转列聚合校验方案（适配当前测试场景，每个number_id单状态最多一条记录） #

SELECT 
    number_id,
    CASE 
        WHEN COALESCE(MAX(CASE WHEN status = 'CLICKED' THEN datetime END), DATE '9999-12-31') 
            <= COALESCE(MAX(CASE WHEN status = 'CLAIMED' THEN datetime END), DATE '9999-12-31')
        AND COALESCE(MAX(CASE WHEN status = 'CLAIMED' THEN datetime END), DATE '9999-12-31') 
            <= COALESCE(MAX(CASE WHEN status = 'BOUGHT' THEN datetime END), DATE '9999-12-31')
        THEN '合规'
        ELSE '不合规'
    END AS check_result
FROM TBL_A
GROUP BY number_id
ORDER BY number_id;

校验结果 #

运行上述SQL后得到的校验结果如下：

逻辑说明 #

• 用CASE WHEN将不同状态的日期转成分组内的独立列，缺失状态的日期默认替换为最大日期9999-12-31，避免缺失状态影响低优先级状态的比较逻辑
• 仅需判断相邻优先级的日期是否满足非递减关系，即可覆盖所有校验规则
  如果业务场景中存在同一个number_id同一个状态有多条记录的情况，可以根据业务需求调整聚合逻辑，比如取对应状态的最早日期/最晚日期进行比较即可。

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