Oracle按Rel_ID和SITE_ID分组筛选无Ack='Y'的指定行

原始数据表 #

Rel_ID SITE_ID   Ack   Added_date
ABC    123        Y    08/09/2023
ABC    123     (null)  08/06/2023
ABC    124     (null)  08/07/2023   
ABC    124     (null)  08/06/2023
ABC    124       N     08/05/2023
ABC    125       Y     07/06/2023
ABC    125       Y     07/07/2023
ABC    126      (null)  09/08/2023
ABC    126      (null)  09/09/2023
ABC    127      (null)  05/08/2023
ABC    127       N      05/09/2023

需求规则 #

• 若某SITE_ID存在Ack='Y'的记录，该SITE_ID的所有记录均不显示
• 若SITE_ID的Ack值全为null或'N'，则取该SITE_ID下Added_date最早的一条记录

期望结果 #

Rel_ID SITE_ID   Ack   Added_date
ABC    124       N     08/05/2023
ABC    126      (null)  09/08/2023
ABC    127      (null)  05/08/2023

用户尝试的SQL语句 #

SELECT * FROM (SELECT RR.*, RANK() OVER (PARTITION BY RR.Rel_ID ,RR.SITE_ID 
ORDER BY (CASE WHEN decode(ack, null, 'N', ack) = 'Y' then 1 else 2 end) asc, 
RR.CREATE_DTE asc) AS RANK FROM  Address_data  RR WHERE (ack is null or ack= 'N')) RS 
WHERE RANK = 1

原SQL存在的问题 #

1. 过滤逻辑错误：提前用WHERE (ack is null or ack= 'N')过滤记录，但没有先排除那些存在Ack='Y'的SITE_ID（比如123、125），导致这些SITE_ID的非Y记录仍会进入后续计算，不符合需求。
2. 字段名错误：排序时使用了RR.CREATE_DTE，但原始表的日期字段是Added_date，字段不匹配。
3. 冗余判断：外层已过滤掉Ack='Y'的记录，窗口函数中关于Ack='Y'的CASE判断无意义。

修正后的SQL方案 #

方案一：使用CTE+窗口函数

WITH valid_sites AS (
    -- 筛选出不存在Ack='Y'的站点
    SELECT Rel_ID, SITE_ID
    FROM Address_data
    GROUP BY Rel_ID, SITE_ID
    HAVING MAX(CASE WHEN Ack = 'Y' THEN 1 ELSE 0 END) = 0
)
SELECT Rel_ID, SITE_ID, Ack, Added_date
FROM (
    SELECT 
        ad.*,
        -- 按站点分组，取最早的一条记录
        ROW_NUMBER() OVER (PARTITION BY ad.Rel_ID, ad.SITE_ID ORDER BY ad.Added_date ASC) AS rn
    FROM Address_data ad
    JOIN valid_sites vs ON ad.Rel_ID = vs.Rel_ID AND ad.SITE_ID = vs.SITE_ID
) t
WHERE rn = 1;

方案二：使用关联子查询

SELECT ad.Rel_ID, ad.SITE_ID, ad.Ack, ad.Added_date
FROM Address_data ad
-- 取当前站点最早的日期记录
WHERE ad.Added_date = (
    SELECT MIN(Added_date)
    FROM Address_data
    WHERE Rel_ID = ad.Rel_ID AND SITE_ID = ad.SITE_ID
)
-- 排除存在Ack='Y'的站点
AND NOT EXISTS (
    SELECT 1
    FROM Address_data
    WHERE Rel_ID = ad.Rel_ID AND SITE_ID = ad.SITE_ID AND Ack = 'Y'
);

修正说明 #

• 先通过CTE或NOT EXISTS排除所有存在Ack='Y'的SITE_ID，确保这些站点的记录完全不参与后续计算。
• 使用ROW_NUMBER()（比RANK()更适合此场景，因为每个站点仅需一条最早记录）按Added_date升序排序，取序号为1的记录。
• 修正了字段名错误，替换为原始表的Added_date字段。
• 移除了冗余的decode和CASE判断，简化逻辑。

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