我有一个数据,其中有EMAIL_ADDRESS和语言。我想按语言对电子邮件地址进行计数,然后在一行中显示结果
CHINESE PORTUGUESE KOREAN SPANISH FRENCH
2 1 1 1 1 当我尝试使用case语句时
select
(case when (LANGUAGE = 'CHINESE') THEN count(distinct email_address) END) as CHINESE,
(case when (LANGUAGE = 'FRENCH') THEN count(distinct email_address) END) as FRENCH,
(case when (LANGUAGE = 'PORTUGUESE') THEN count(distinct email_address) END) as PORTUGUESE,
(case when (LANGUAGE = 'SPANISH') THEN count(distinct email_address) END) as SPANISH,
(case when (LANGUAGE = 'KOREAN') THEN count(distinct email_address) END) as KOREAN
from
table1
group by language; 这是我不想要的
CHINESE PORTUGUESE KOREAN SPANISH FRENCH
2 0 0 0 0
0 1 0 0 0
0 0 1 0 0
0 0 0 1 0
0 0 0 0 1 它应该在一行5列中,不能有零
发布于 2019-06-21 13:34:17
您需要对这些CASE表达式求和,而且如果您只想要一个表级聚合,似乎也不需要使用GROUP BY:
SELECT
COUNT(DISTINCT CASE WHEN LANGUAGE = 'CHINESE' THEN email_address END) AS CHINESE,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'FRENCH' THEN email_address END) AS FRENCH,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'PORTUGUESE' THEN email_address END) AS PORTUGUESE,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'SPANISH' THEN email_address END) AS SPANISH,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'KOREAN' THEN email_address END) AS KOREAN
FROM table1;如果您希望按更细粒度的对象进行这些聚合,例如用户,那么GROUP BY user列并将其选中:
SELECT
userId,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'CHINESE' THEN email_address END) AS CHINESE,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'FRENCH' THEN email_address END) AS FRENCH,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'PORTUGUESE' THEN email_address END) AS PORTUGUESE,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'SPANISH' THEN email_address END) AS SPANISH,
COUNT(DISTINCT CASE WHEN LANGUAGE = 'KOREAN' THEN email_address END) AS KOREAN
FROM table1
GROUP BY
userId;发布于 2019-06-21 19:43:50
我想我会推荐先做distinct:
SELECT SUM(CASE WHEN LANGUAGE = 'CHINESE' THEN 1 ELSE 0 END) AS CHINESE,
SUM(CASE WHEN LANGUAGE = 'FRENCH' THEN 1 ELSE 0 END) AS FRENCH,
SUM(CASE WHEN LANGUAGE = 'PORTUGUESE' THEN 1 ELSE 0 END) AS PORTUGUESE,
SUM(CASE WHEN LANGUAGE = 'SPANISH' THEN 1 ELSE 0 END) AS SPANISH,
SUM(CASE WHEN LANGUAGE = 'KOREAN' THEN 1 ELSE 0 END) AS KOREAN
FROM (SELECT DISTINCT email_address, language
FROM table1
) t1;在更大的表上,比较这两种方法的性能将是很有趣的。但我预计一个DISTINCT会比五个快。
https://stackoverflow.com/questions/56697354
复制相似问题