mysql - sql时间实例频率

Question

我正在尝试输出工作人员登录的频率，因此我有两个表。

表 1: worker

+----------+
| workerid |
+----------+
| w1       |
| w2       |
| w3       |
| w4       |
+----------+

表2:信息

+----------+-----------+
| workerid | timestamp |
+----------+-----------+
| w1       |  23494944 |
| w1       |  24444444 |
| w3       |  26773735 |
| w4       |  86433333 |
+----------+-----------+

查询的预期输出:

+------------------+----------------+
| count of workers | number_entries |
+------------------+----------------+
|                1 |              2 |
|                2 |              1 |
+------------------+----------------+

我写了这个，但我不知道如何定义时间的频率。

SELECT COUNT(DISTINCT(Worker_id)) as  count_of_workers, COUNT(FREQUENCY) as number_entries
GROUP BY user id
ORDER BY COUNT desc;

Answer 1

如果我理解正确的话，你需要两个group by:

select num_entries, count(*) as num_workers
from (select worker_id, count(*) as num_entries
      from information
      group by worker_id
     ) i
group by num_entries
order by num_entries;