我试图找到谁收到1/2/3邮箱从下表的独特客户的计数:
cust_id | month | email_cnt |
---|---|---|
1 | 202311 | 1 |
1 | 202311 | 1 |
1 | 202310 | 1 |
1 | 202310 | 0 |
1 | 202309 | 1 |
2 | 202311 | 0 |
2 | 202311 | 0 |
2 | 202310 | 0 |
2 | 202310 | 1 |
2 | 202309 | 1 |
3 | 202311 | 0 |
3 | 202311 | 1 |
3 | 202310 | 0 |
3 | 202310 | 0 |
3 | 202309 | 1 |
这是所需的输出,将每月的邮箱数相加后,计算收到x封邮箱的唯一客户的数量:
month | cust_with_1email | cust_with_2email | cust_with_3email |
---|---|---|---|
202311 | 2 | 1 | 0 |
202310 | 2 | 0 | 0 |
202309 | 3 | 0 | 0 |
我正在try 使用CASE函数,但没有成功:
SELECT distinct month,
SUM(case when email_cnt = 1 then email_cnt end) cust_with_1email,
SUM(case when email_cnt = 2 then email_cnt end) cust_with_2email,
SUM(case when email_cnt = 3 then email_cnt end) cust_with_3email
FROM mytable
GROUP BY month