如何合并Oracle SQL分组与自定义排序查询得到目标结果？

源数据 #

Due Date    Status       Activity Name               Completed_date
            Completed    Maintenance (ADV)              23-Feb-23
9-Apr-23    Assessment   Maintenance (ADV)  
            Completed    Records Management Standards   10-Mar-23
16-Apr-23    Pending      Records Management Standards   
16-Apr-23    Assessment   Records Management Standards   
10-Mar-23    Scheduled    Records Management Standards   
            Completed    Monitor Compliance             14-Feb-23
9-Apr-23    Assessment   Monitor Compliance
            Completed    Monitor Customer               23-Feb-23
9-Apr-23    Pending      Monitor Customer   

期望目标结果 #

Due Date    Status          Activity Name             Completed_date
9-Apr-23    Completed   Maintenance (ADV)             23-Feb-23
16-Apr-23    Completed   Records Management Standards  10-Mar-23
9-Apr-23    Completed   Monitor Compliance            14-Feb-23
9-Apr-23    Completed   Monitor Customer              23-Feb-23

现有查询 #

查询1：按活动分组获取最大截止日期和完成日期 #

select MAX(DUE_DATE) as due_date, ACTIVITY_NAME, MAX(COMPLETED_DATE) as completed_date 
from activity_history 
group by ACTIVITY_NAME

查询2：按自定义状态优先级筛选每行记录 #

select t.*
from (select R.*,
             row_number() over (partition by ACTIVITY_NAME
                                order by (case when status = 'Completed' then 1
                                               when status = 'Scheduled' then 3
                                               else 2
                                          end)) as rank
      from activity_history  R
     ) t
where rank = 1;

合并后的查询 #

SELECT
    MAX(DUE_DATE) OVER (PARTITION BY ACTIVITY_NAME) AS due_date,
    STATUS,
    ACTIVITY_NAME,
    COMPLETED_DATE
FROM (
    SELECT
        R.*,
        ROW_NUMBER() OVER (
            PARTITION BY ACTIVITY_NAME
            ORDER BY (
                CASE 
                    WHEN STATUS = 'Completed' THEN 1
                    WHEN STATUS = 'Scheduled' THEN 3
                    ELSE 2
                END
            )
        ) AS rank
    FROM activity_history R
) t
WHERE t.rank = 1;

逻辑说明 #

1. 内层子查询通过ROW_NUMBER()按ACTIVITY_NAME分区，同时按自定义规则排序状态：优先保留Completed状态的记录，其次是Pending，最后是Scheduled，生成rank字段标记每行优先级。
2. 主查询使用MAX(DUE_DATE) OVER (PARTITION BY ACTIVITY_NAME)窗口函数，直接获取每个活动对应的最晚截止日期，无需单独分组查询。
3. 最后筛选rank=1的行，即可得到每个活动的Completed状态记录，以及对应的最大截止日期和完成日期，完全匹配目标结果。

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