如何统计每月接收1、2、3封邮件的唯一客户数量？

原SQL的问题 #

你的SQL存在两个关键问题：

1. SELECT distinct month多余：GROUP BY month已经会按月份分组，每个月份只会返回一行记录，无需额外去重。
2. 统计逻辑错误：SUM(case when email_cnt = 1 then email_cnt end)是对符合条件的email_cnt值进行累加，而非统计唯一客户数。如果同一客户当月有多条email_cnt=1的记录，会被重复计算，无法得到正确的唯一客户数量。

针对不同场景的正确实现 #

场景1：每个客户每月仅一条记录（cust_id + month为唯一键）

如果原表中每个客户每个月只有一条记录（即email_cnt直接代表该客户当月接收的总邮件数），可以使用以下SQL：

SELECT 
    month,
    COUNT(CASE WHEN email_cnt = 1 THEN cust_id END) AS cust_with_1email,
    COUNT(CASE WHEN email_cnt = 2 THEN cust_id END) AS cust_with_2email,
    COUNT(CASE WHEN email_cnt = 3 THEN cust_id END) AS cust_with_3email
FROM mytable
GROUP BY month

或者用SUM替代COUNT，效果完全一致：

SELECT 
    month,
    SUM(CASE WHEN email_cnt = 1 THEN 1 ELSE 0 END) AS cust_with_1email,
    SUM(CASE WHEN email_cnt = 2 THEN 1 ELSE 0 END) AS cust_with_2email,
    SUM(CASE WHEN email_cnt = 3 THEN 1 ELSE 0 END) AS cust_with_3email
FROM mytable
GROUP BY month

场景2：同一客户每月存在多条记录（每条记录对应单封邮件）

如果原表中同一客户同一月有多条记录（比如email_cnt恒为1，代表单封邮件），需要先统计每个客户每月的总邮件数，再统计各分组的客户数：

WITH monthly_email_summary AS (
    SELECT 
        cust_id,
        month,
        SUM(email_cnt) AS total_emails
    FROM mytable
    GROUP BY cust_id, month
)
SELECT 
    month,
    COUNT(CASE WHEN total_emails = 1 THEN cust_id END) AS cust_with_1email,
    COUNT(CASE WHEN total_emails = 2 THEN cust_id END) AS cust_with_2email,
    COUNT(CASE WHEN total_emails = 3 THEN cust_id END) AS cust_with_3email
FROM monthly_email_summary
GROUP BY month

也可以直接用COUNT(DISTINCT)简化逻辑（但性能可能不如先聚合）：

SELECT 
    month,
    COUNT(DISTINCT CASE WHEN email_cnt = 1 THEN cust_id END) AS cust_with_1email,
    COUNT(DISTINCT CASE WHEN email_cnt = 2 THEN cust_id END) AS cust_with_2email,
    COUNT(DISTINCT CASE WHEN email_cnt = 3 THEN cust_id END) AS cust_with_3email
FROM mytable
GROUP BY month

逻辑说明 #

• COUNT(CASE ...)：当email_cnt（或total_emails）符合条件时，返回cust_id（非空值），COUNT会统计这些非空值的数量，即符合条件的唯一客户数。
• SUM(CASE ...)：符合条件时返回1，否则返回0，累加后即为符合条件的客户数量。
• 先聚合的方式（WITH子句）能避免重复计算同一客户，确保统计的是唯一客户数。

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