首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >按行计数并以coulmn格式显示结果

问按行计数并以coulmn格式显示结果
EN

Stack Overflow用户
提问于 2019-06-21 13:29:32
回答 2查看 35关注 0票数 0

我有一个数据,其中有EMAIL_ADDRESS和语言。我想按语言对电子邮件地址进行计数,然后在一行中显示结果

代码语言:javascript
复制
CHINESE     PORTUGUESE    KOREAN SPANISH FRENCH  
  2             1          1      1      1  

当我尝试使用case语句时

代码语言:javascript
复制
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;   

这是我不想要的

代码语言:javascript
复制
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列中,不能有零

EN

回答 2

Stack Overflow用户

发布于 2019-06-21 13:34:17

您需要对这些CASE表达式求和,而且如果您只想要一个表级聚合,似乎也不需要使用GROUP BY:

代码语言:javascript
复制
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列并将其选中:

代码语言:javascript
复制
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;
票数 2
EN

Stack Overflow用户

发布于 2019-06-21 19:43:50

我想我会推荐先做distinct:

代码语言:javascript
复制
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会比五个快。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56697354

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档