Oracle SQL中按规则为用户最新符合条件状态标记Yes的需求

表结构与测试数据 #

CREATE TABLE person_status (
  user_id number,
  status_1 varchar(50),
  status_1_date date,
  status_2 varchar(50),
  status_2_date date,
  status_3 varchar(50),
  status_3_date date,
  status_4 varchar(50),
  status_4_date date,
  status_5 varchar(50),
  status_5_date date
);

INSERT INTO person_status VALUES (1,'Active',TO_DATE('5/7/2024 12:15:20 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('5/6/2024 05:20:09 PM','MM/DD/YYYY HH:MI:SS AM'),'Active',TO_DATE('11/28/2023 01:47:02 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('5/5/2024 12:10:08 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('3/27/2024 01:28:10 PM','MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO person_status VALUES (2,'Active',TO_DATE('5/7/2024 12:15:20 PM','MM/DD/YYYY HH:MI:SS AM'),'Active',TO_DATE('5/6/2024 05:20:09 PM','MM/DD/YYYY HH:MI:SS AM'),'Active',TO_DATE('11/28/2023 01:47:02 PM','MM/DD/YYYY HH:MI:SS AM'),'Active',TO_DATE('5/5/2024 12:10:08 PM','MM/DD/YYYY HH:MI:SS AM'),'Active',TO_DATE('3/27/2024 01:28:10 PM','MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO person_status VALUES (3,'Suspend',TO_DATE('5/7/2024 12:15:20 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('5/6/2024 05:20:09 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('11/28/2023 01:47:02 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('5/5/2024 12:10:08 PM','MM/DD/YYYY HH:MI:SS AM'),'Suspend',TO_DATE('3/27/2024 01:28:10 PM','MM/DD/YYYY HH:MI:SS AM'));

查询需求 #

• 若用户所有状态均为Suspend，为最新设置该状态的字段标记Yes；
• 若用户所有状态均为Active，为最新设置该状态的字段标记Yes；
• 若用户状态既有Active又有Suspend，为最新设置Active状态的字段标记Yes。

用户1的预期输出 #

解决方案SQL #

WITH user_status_summary AS (
    SELECT 
        user_id,
        -- 判断用户状态类型
        CASE 
            WHEN COUNT(CASE WHEN status != 'Suspend' THEN 1 END) = 0 THEN 'ALL_SUSPEND'
            WHEN COUNT(CASE WHEN status != 'Active' THEN 1 END) = 0 THEN 'ALL_ACTIVE'
            ELSE 'MIXED' 
        END AS status_type,
        -- 混合状态下最新的Active日期
        MAX(CASE WHEN status = 'Active' THEN status_date END) AS latest_active_date,
        -- 全同状态下最新的状态日期
        MAX(status_date) AS latest_status_date
    FROM (
        -- 将列转行，统一处理每组状态与日期
        SELECT user_id, status_1 AS status, status_1_date AS status_date FROM person_status
        UNION ALL
        SELECT user_id, status_2 AS status, status_2_date AS status_date FROM person_status
        UNION ALL
        SELECT user_id, status_3 AS status, status_3_date AS status_date FROM person_status
        UNION ALL
        SELECT user_id, status_4 AS status, status_4_date AS status_date FROM person_status
        UNION ALL
        SELECT user_id, status_5 AS status, status_5_date AS status_date FROM person_status
    ) unpivoted
    GROUP BY user_id
)
SELECT 
    ps.user_id,
    ps.status_1,
    CASE 
        WHEN uss.status_type = 'MIXED' AND ps.status_1 = 'Active' AND ps.status_1_date = uss.latest_active_date THEN 'Yes'
        WHEN uss.status_type IN ('ALL_ACTIVE','ALL_SUSPEND') AND ps.status_1_date = uss.latest_status_date THEN 'Yes'
        ELSE 'No' 
    END AS type_1,
    ps.status_2,
    CASE 
        WHEN uss.status_type = 'MIXED' AND ps.status_2 = 'Active' AND ps.status_2_date = uss.latest_active_date THEN 'Yes'
        WHEN uss.status_type IN ('ALL_ACTIVE','ALL_SUSPEND') AND ps.status_2_date = uss.latest_status_date THEN 'Yes'
        ELSE 'No' 
    END AS type_2,
    ps.status_3,
    CASE 
        WHEN uss.status_type = 'MIXED' AND ps.status_3 = 'Active' AND ps.status_3_date = uss.latest_active_date THEN 'Yes'
        WHEN uss.status_type IN ('ALL_ACTIVE','ALL_SUSPEND') AND ps.status_3_date = uss.latest_status_date THEN 'Yes'
        ELSE 'No' 
    END AS type_3,
    ps.status_4,
    CASE 
        WHEN uss.status_type = 'MIXED' AND ps.status_4 = 'Active' AND ps.status_4_date = uss.latest_active_date THEN 'Yes'
        WHEN uss.status_type IN ('ALL_ACTIVE','ALL_SUSPEND') AND ps.status_4_date = uss.latest_status_date THEN 'Yes'
        ELSE 'No' 
    END AS type_4,
    ps.status_5,
    CASE 
        WHEN uss.status_type = 'MIXED' AND ps.status_5 = 'Active' AND ps.status_5_date = uss.latest_active_date THEN 'Yes'
        WHEN uss.status_type IN ('ALL_ACTIVE','ALL_SUSPEND') AND ps.status_5_date = uss.latest_status_date THEN 'Yes'
        ELSE 'No' 
    END AS type_5
FROM person_status ps
JOIN user_status_summary uss ON ps.user_id = uss.user_id;

思路说明 #

1. 列转行处理：把每个用户的5组状态和日期转为行记录，便于统一统计用户的整体状态分布。
2. 状态类型汇总：通过分组计算，判断用户属于全Suspend、全Active还是混合状态，同时算出对应场景下需要匹配的最新日期。
3. 标记匹配：关联原表和汇总结果，根据用户的状态类型，对比字段的状态和日期，为符合条件的字段标记Yes，否则标记No。

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