如何修改SQL查询仅保留无Keep_Flag='N'的合格账户数据

需求：修改现有SQL查询，仅展示无任何Keep_Flag='N'记录的账户全部字段——这类账户是过去12个月每个月都满足条件、符合额外费率折扣资格的账户，只要有一个月不满足（Keep_Flag='N'）就会被排除。

原生成月度合格标记的查询 #

SELECT id, account_id, reg_con, tagg_reading, read_date,
CASE
    WHEN reg_con * tagg_reading < 60 
         AND TO_CHAR(read_date, 'MM-DD') BETWEEN '06-15' AND '10-15' THEN 'Y'
    WHEN reg_con * tagg_reading < 90 
         AND (TO_CHAR(read_date, 'MM-DD') BETWEEN '10-16' AND '12-31' OR TO_CHAR(read_date, 'MM-DD') BETWEEN '01-01' AND '06-14') THEN 'Y'
    ELSE 'N'
END AS Keep_Flag
FROM test;

修改后的查询方案 #

方法一：子查询筛选合格账户后关联原表 #

先筛选出所有记录的Keep_Flag都不为'N'的账户ID，再关联原表获取全部字段：

SELECT t.id, t.account_id, t.reg_con, t.tagg_reading, t.read_date,
       k.Keep_Flag
FROM test t
JOIN (
    SELECT account_id
    FROM (
        SELECT account_id,
               CASE
                   WHEN reg_con * tagg_reading < 60 
                        AND TO_CHAR(read_date, 'MM-DD') BETWEEN '06-15' AND '10-15' THEN 'Y'
                   WHEN reg_con * tagg_reading < 90 
                        AND (TO_CHAR(read_date, 'MM-DD') BETWEEN '10-16' AND '12-31' OR TO_CHAR(read_date, 'MM-DD') BETWEEN '01-01' AND '06-14') THEN 'Y'
                   ELSE 'N'
               END AS Keep_Flag
        FROM test
        -- 可选：添加过去12个月的时间过滤，比如 WHERE read_date >= CURRENT_DATE - INTERVAL '12 months'
    ) sub
    GROUP BY account_id
    HAVING MAX(Keep_Flag) = 'Y' -- 所有记录的Keep_Flag都是Y，Y排序优先级高于N，MAX为Y则无N记录
) k ON t.account_id = k.account_id;

方法二：窗口函数标记账户合格状态 #

用窗口函数计算每个账户的Keep_Flag最大值，直接过滤掉存在'N'的账户：

SELECT id, account_id, reg_con, tagg_reading, read_date, Keep_Flag
FROM (
    SELECT id, account_id, reg_con, tagg_reading, read_date,
           CASE
               WHEN reg_con * tagg_reading < 60 
                    AND TO_CHAR(read_date, 'MM-DD') BETWEEN '06-15' AND '10-15' THEN 'Y'
               WHEN reg_con * tagg_reading < 90 
                    AND (TO_CHAR(read_date, 'MM-DD') BETWEEN '10-16' AND '12-31' OR TO_CHAR(read_date, 'MM-DD') BETWEEN '01-01' AND '06-14') THEN 'Y'
               ELSE 'N'
           END AS Keep_Flag,
           MAX(
               CASE
                   WHEN reg_con * tagg_reading < 60 
                        AND TO_CHAR(read_date, 'MM-DD') BETWEEN '06-15' AND '10-15' THEN 'Y'
                   WHEN reg_con * tagg_reading < 90 
                        AND (TO_CHAR(read_date, 'MM-DD') BETWEEN '10-16' AND '12-31' OR TO_CHAR(read_date, 'MM-DD') BETWEEN '01-01' AND '06-14') THEN 'Y'
                   ELSE 'N'
               END
           ) OVER (PARTITION BY account_id) AS account_keep_status
    FROM test
    -- 可选：添加过去12个月的时间过滤，比如 WHERE read_date >= CURRENT_DATE - INTERVAL '12 months'
) sub
WHERE account_keep_status = 'Y';

结果说明 #

修改后的查询会仅返回Account_ID 1001的所有记录，Account_ID 1002因存在Keep_Flag='N'的记录会被排除。

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