MySQL -按唯一登录日范围内的用户数和部门分组
MySQL -按唯一登录日范围内的用户数和部门分组
提问于 2016-01-18 19:26:13
我现在正在进行一个查询，该查询将计算每个用户的唯一登录天数，将其放在一个桶中(1-2，3-4登录天数等)，并按部门返回每个桶中的每个用户的数量。如果这一点不太清楚，我很抱歉，希望下面的例子能帮助说明我的问题。
我有一张这样的桌子：
+-------------+-----------+------------+-----------------+
| time | user_name | dept | event |
+-------------+-----------+------------+-----------------+
| 2016-01-04 | Joe | finance | logged in |
| 2016-01-04 | Jeff | marketing | logged in |
| 2016-01-04 | Jeff | marketing | project created |
| 2016-01-04 | Bob | finance | logged in |
| 2016-01-04 | Mark | accounting | logged in |
| 2016-01-05 | Bob | finance | logged in |
| 2016-01-08 | Bob | finance | logged in |
| 2016-01-09 | Jeff | marketing | logged in |
| 2016-01-10 | Jeff | marketing | logged in |
| 2016-01-11 | Nate | accounting | logged in |
| 2016-01-11 | Nate | accounting | project created |
+-------------+-----------+------------+-----------------+我想退一张这样的桌子：
+------------------+-----------------+------------+
| number of logins | number of users | dept |
+------------------+-----------------+------------+
| 1-2 | 1 | finance |
| 3-4 | 1 | finance |
| 5+ | 0 | finance |
| 1-2 | 0 | marketing |
| 3-4 | 1 | marketing |
| 5+ | 0 | marketing |
| 1-2 | 2 | accounting |
| 3-4 | 0 | accounting |
| 5+ | 0 | accounting |
+------------------+-----------------+------------+ 到目前为止，我的查询如下：
select
(case when count(distinct(`time`)) between 1 and 2 then '1-2'
when count(distinct(`time`)) between 3 and 4 then '3-4'
else '5+'
end) as buckets, dept, user_name
from change_log where event in ('logged in')
group by dept, user_name然而，这是返回如下所示的一个表，这是我能得到的最接近我想要的，但我不知道如何将它卷到仅仅通过桶和部门。
+---------+------------+-----------+
| buckets | dept | user_name |
+---------+------------+-----------+
| 1-2 | accounting | Mark |
| 1-2 | accounting | Nate |
| 3-4 | finance | Bob |
| 1-2 | finance | Joe |
| 3-4 | marketing | Jeff |
+---------+------------+-----------+回答 3
Stack Overflow用户
回答已采纳
发布于 2016-01-18 19:58:43
像这样吗？
select buckets, dept, count(user_name) no_of_u from
(select
(case when count(distinct(`time`)) between 1 and 2 then '1-2'
when count(distinct(`time`)) between 3 and 4 then '3-4'
else '5+'
end) as buckets, dept, user_name
from change_log where event in ('logged in')
group by dept, user_name)
group by buckets, deptStack Overflow用户
发布于 2016-01-18 20:04:11
但你可以这样做：
select
ELT(LEAST(count(*),5), '1-2', '1-2', '3-4', '3-4','5+') as buckets,
dept,
count(*) as `number of users`
FROM change_log where event in ('logged in')
WHERE event in ('logged in')
GROUP BY
dept,
ELT(LEAST(count((*),5), '1-2', '1-2', '3-4', '3-4','5+');Stack Overflow用户
发布于 2016-01-18 19:54:48
为了确定我明白，你基本上是在尝试这样做：
select
(case when count(distinct(`time`)) between 1 and 2 then '1-2'
when count(distinct(`time`)) between 3 and 4 then '3-4'
else '5+'
end) as buckets, dept, count(*) as `number of users`
from change_log where event in ('logged in')
group by dept, buckets现在您不能在MySQL中这样做，因为您不能对case语句的输出进行分组，但从功能上说，这就是所希望的结果？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34862507
复制相关文章
相似问题
腾讯云开发者
