SQL新手求助：按subscriberKey条件保留/删除重复数据

需求回顾 #

处理重复emailAddress的规则：

• 若同一邮箱下的所有subscriberKey都以003、001或00Q开头 → 保留该邮箱的所有记录
• 若同一邮箱下存在任意一条subscriberKey不符合上述前缀规则 → 删除该邮箱的所有记录
• 非重复的邮箱（仅一条记录），无论subscriberKey是什么，都保留

原代码的问题 #

1. DELETE语法错误：SQL的DELETE语句不能在DELETE后指定字段，正确写法是DELETE FROM 表名（或带别名的DELETE t1 FROM 表名 t1）
2. 逻辑判断错误：原条件里的OR优先级导致逻辑混乱，且未按邮箱分组判断所有subscriberKey的合规性
3. HAVING使用不当：HAVING必须配合GROUP BY使用，否则无法实现分组统计判断

正确实现思路 #

先通过分组查询确定哪些邮箱是需要保留的，再基于这个结果筛选或删除记录：

1. 按emailAddress分组，统计每组是否存在不符合前缀规则的subscriberKey
2. 保留以下两类邮箱：
   • 非重复邮箱（组内仅一条记录）
   • 重复邮箱且组内所有subscriberKey都符合前缀规则

具体SQL实现 #

第一步：先查询符合预期结果的记录（推荐新手先验证结果再执行删除）

SELECT t.*
FROM [getting duplicates] t
INNER JOIN (
    SELECT 
        emailAddress,
        -- 判断该组是否存在不符合规则的subscriberKey
        MAX(CASE WHEN subscriberKey NOT LIKE '003%' AND subscriberKey NOT LIKE '001%' AND subscriberKey NOT LIKE '00Q%' THEN 1 ELSE 0 END) AS has_invalid_key
    FROM [getting duplicates]
    GROUP BY emailAddress
) AS group_check ON t.emailAddress = group_check.emailAddress
-- 保留条件：要么没有无效key，要么是唯一记录（唯一记录即使key无效也保留）
WHERE group_check.has_invalid_key = 0 
   OR (SELECT COUNT(*) FROM [getting duplicates] WHERE emailAddress = t.emailAddress) = 1;

第二步：删除不符合要求的记录

如果确认上述查询结果正确，可以执行删除：

DELETE t1
FROM [getting duplicates] t1
INNER JOIN (
    SELECT 
        emailAddress,
        MAX(CASE WHEN subscriberKey NOT LIKE '003%' AND subscriberKey NOT LIKE '001%' AND subscriberKey NOT LIKE '00Q%' THEN 1 ELSE 0 END) AS has_invalid_key,
        COUNT(*) AS record_count
    FROM [getting duplicates]
    GROUP BY emailAddress
) AS group_check ON t1.emailAddress = group_check.emailAddress
-- 删除条件：重复邮箱且存在无效key
WHERE group_check.record_count > 1 AND group_check.has_invalid_key = 1;

补充说明 #

• 用MAX(CASE...)来判断分组内是否存在无效记录：只要有一条不符合，MAX结果就是1，否则为0
• 拆分查询和删除步骤，避免误删数据，新手可以先通过SELECT验证结果
• 若要同时处理Status = 'unsubscribed'的记录，可以在子查询的CASE里加入该条件（比如WHEN (subscriberKey NOT LIKE ...) OR Status = 'unsubscribed' THEN 1）

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