Oracle SQL查询优化：过滤已关闭超365天案件的实现方案咨询

修改思路 #

我们只需要在原有SQL基础上做两处调整即可实现需求：

1. 在case_status的子查询中新增分析函数，计算每个案件的最新关闭日期（仅统计状态ID为2的关闭记录）
2. 新增过滤条件，排除存在关闭记录且最新关闭日期距今超过365天的案件

调整后完整SQL #

select c.case_id caseId, c.memberId memberId, c.last_name lastName, c.first_name firstName, lkp_alt.descr altShowCauseAuthority, 
 'Active' as caseType,
 listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
 from cases c join lkP_alt_show_cause_authority lkp_alt
 on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
 and c.case_type = 'P'
 JOIN (SELECT case_id, 
               case_status_id,
               COUNT(DECODE(case_status_id, 2, 1, NULL)) OVER (PARTITION BY case_id) AS IS_CLOSED,
               -- 新增字段：按案件分组取最新的关闭日期，无关闭记录则为NULL
               MAX(CASE WHEN case_status_id = 2 THEN case_status_date END) OVER (PARTITION BY case_id) AS latest_closed_date
        FROM case_status) cs ON cs.case_id = c.case_id AND cs.is_closed = 0
 join lkp_case_status lkp_cs
 on lkp_cs.id = cs.case_status_id 
 where (c.created_by = 1 and c.assigned_to is null)
 or (c.assigned_to = 1) and c.delete_date is null
 -- 新增过滤规则：保留无关闭记录、或关闭记录距今不超过1年的案件
 AND (cs.latest_closed_date IS NULL OR cs.latest_closed_date >= SYSDATE - 365)
 group by c.case_id, c.memberId, c.last_name, c.first_name, lkp_alt.descr, c.case_type;

补充说明 #

如果需要更精准的按自然年计算（自动处理闰年情况），可以将过滤条件中的SYSDATE - 365替换为ADD_MONTHS(SYSDATE, -12)。

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