无发送量数据时通过打开、退信等行为计算邮件发送量的SQL查询问题

你现有查询存在两处核心错误，无法按主题行维度得到正确的发送量及相关指标：

• GROUP BY 子句中加入了Activity字段，会导致同一个主题行拆分出多条数据，每条对应一种活动类型，无法合并得到单主题行的全量统计结果
• CASE WHEN 放在聚合函数外层，仅能统计对应分组下单活动类型的计数，不符合多活动类型合并统计的需求

修正后的查询语句如下：

SELECT 
    SubjectLine
    -- 总点击相关量
    ,COUNT(CASE WHEN Activity IN ('Click','Opted In','Unsubscribe - All') THEN EmailAddress ELSE NULL END) AS TotalCT
    ,COUNT(DISTINCT CASE WHEN Activity IN ('Click','Opted In','Unsubscribe - All') THEN EmailAddress ELSE NULL END) AS UniqueCT
    -- 总打开相关量
    ,COUNT(CASE WHEN Activity = 'Open' THEN EmailAddress ELSE NULL END) AS TotalOpens
    ,COUNT(DISTINCT CASE WHEN Activity = 'Open' THEN EmailAddress ELSE NULL END) AS UniqueOpens
    -- 退信量
    ,COUNT(CASE WHEN Activity = 'Bounceback' THEN EmailAddress ELSE NULL END) AS Bounces
    -- 发送量：成功送达后有打开记录+退信记录的去重邮箱数，覆盖所有触达的邮箱
    ,COUNT(DISTINCT CASE WHEN Activity IN ('Open','Bounceback') THEN EmailAddress ELSE NULL END) AS Sends
FROM xyz
GROUP BY SubjectLine

逻辑说明：

• 仅保留SubjectLine作为唯一分组维度，确保一个主题行仅返回一行统计结果
• 将CASE WHEN判断放在聚合函数内部，符合多活动类型的计数逻辑：当满足对应活动类型时取邮箱地址，否则返回NULL，COUNT函数会自动忽略NULL值实现对应场景的计数
• 发送量统计改用DISTINCT去重邮箱，避免同一个邮箱多次打开/多次退信导致的计数重复，结果更准确

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