题目:
用CASE语句 计算唯一用户数:
补充:一个“users” table里面有user_guid 和 country 还有state 等column信息参考答案:
%%sql
SELECT COUNT(DISTINCT user_guid),
CASE
WHEN (state="NY" OR state="NJ") THEN "Group 1-NY/NJ"
WHEN (state="NC" OR state="SC") THEN "Group 2-NC/SC"
WHEN state="CA" THEN "Group 3-CA"
ELSE "Group 4-Other"
END AS state_group
FROM users
WHERE country="US" AND state IS NOT NULL
GROUP BY state_group
返回了4栏
898Group 1-NY/NJ
653Group 2-NC/SC
1417Group 3-CA
6388Group 4-Other
=============================
我自己写得
%%sql
SELECT CASE newuser.state
WHEN ('NY'or 'NJ') THEN 'Group 1'
WHEN ('NC'or 'SC') THEN 'Group 2'
WHEN 'CA' THEN 'Group 3'
ELSE 'Group 4'
END AS stategroup,
COUNT(distinct newuser.user_guid)
FROM
(SELECT user_guid,country,state
FROM users u
WHERE country='US' and state IS NOT NULL ) AS newuser
GROUP BY stategroup
只返回了一栏 Group 1: 9356
?为什么我写得返回了4栏相加的总和 但是只有一个GROUP 1???
求大神解答!!!
用CASE语句 计算唯一用户数:
补充:一个“users” table里面有user_guid 和 country 还有state 等column信息参考答案:
%%sql
SELECT COUNT(DISTINCT user_guid),
CASE
WHEN (state="NY" OR state="NJ") THEN "Group 1-NY/NJ"
WHEN (state="NC" OR state="SC") THEN "Group 2-NC/SC"
WHEN state="CA" THEN "Group 3-CA"
ELSE "Group 4-Other"
END AS state_group
FROM users
WHERE country="US" AND state IS NOT NULL
GROUP BY state_group
返回了4栏
898Group 1-NY/NJ
653Group 2-NC/SC
1417Group 3-CA
6388Group 4-Other
=============================
我自己写得
%%sql
SELECT CASE newuser.state
WHEN ('NY'or 'NJ') THEN 'Group 1'
WHEN ('NC'or 'SC') THEN 'Group 2'
WHEN 'CA' THEN 'Group 3'
ELSE 'Group 4'
END AS stategroup,
COUNT(distinct newuser.user_guid)
FROM
(SELECT user_guid,country,state
FROM users u
WHERE country='US' and state IS NOT NULL ) AS newuser
GROUP BY stategroup
只返回了一栏 Group 1: 9356
?为什么我写得返回了4栏相加的总和 但是只有一个GROUP 1???
求大神解答!!!
解决方案 »
- mysql的select效率太慢怎么优化?
- 如何提高数据库存在更新、不存在插入的效率??
- 这两句话有什么区别?
- 还是一道数据库面试题
- 急!急!急! update userinfo set Prestige_Value=sun((select Prestige_Value from userinfo where UserId=1)+1
- mysql administrator tool 在设置指定时间自动备份后不运行?
- php5使用pconnect连接mysql5,mysql中的连接进程一直保持在11个,是正常的吗?
- 把.dbf文件转化成为mysql的数据库表的文件的软件在哪找啊?
- mysql修改密码的问题
- mysql查询指定结点的所有子结点
- 咨询一个sql查询的连接优化问题,谢谢!
- mysql运行一段时间后悔莫名的挂掉 错误日志如下所示 求大神解答!
WHEN newuser.state IN ('NY', 'NJ') THEN 'Group 1' ...
WHEN ('NY'or 'NJ') T 不支持这种语法。可以 WHEN NY' then ... WHEN NJ then ..
话说mysql的语法,真是不一般呐。case when 还可以 写or。你先写出标准的语法试试:CASE
WHEN (state in ("NY" ,"NJ") THEN "Group 1-NY/NJ"
WHEN (state in "NC" ,"SC") THEN "Group 2-NC/SC"
WHEN state="CA" THEN "Group 3-CA"
ELSE "Group 4-Other"
END AS state_group
按你的语句改为
---
SELECT CASE newuser.stateWHEN newuser.state IN ('NY','NJ') THEN 'Group 1'
WHEN newuser.state IN ("NC","SC") THEN "Group 2"
WHEN newuser.state="CA" THEN "Group 3-CA"
ELSE "Group 4-Other"
END AS state_group,COUNT(distinct newuser.user_guid)
------
返回了2组...没有了3,4组呢?
COUNT(distinct newuser.user_guid) state_group
8458 Group 1
898 Group 2
写作一个TABLE就不会....郁闷了几天