sqlite:如何获得组计数 [英] sqlite: how to get a count of group counts

问题描述

我有一个网站上用户操作的 SQLite 表.每一行都是网站上的相同操作，只是不同的时间/日期，用用户 ID 标记.该表有超过 2000 万个条目.我了解如何通过用户 ID 使用 group by 功能按用户获取计数(即 A 执行了 3 次操作，B 4、C 2、D 4 等).换句话说，这很好用:

I have a SQLite table of user actions on a website. Each row is the same action on a web site, just different time/date, tagged with a user id. The table has more than 20Million entries. I understand how to get a count by user (i.e. A took the action 3 times, B 4, C 2, D 4, etc.) using the group by function by user id. In other words this works fine:

select count(uid) as event_count
from table
group by uid

我想要的是统计分布的数据，它是仅执行 1 个操作的用户数量、执行 2 个操作的用户数量等的统计数据.换种方式说:列表可能如下所示:

What I want is the data for a statistical distribution which is a count of the number of users who only took 1 action, a count of users that took 2 actions, etc. Said another way: The list might look something like:

1 | 339,440
2 | 452,555
3 | 99,239
5 | 20,209
etc. ...

我可以使用 have event_count = n 子句，然后对每个整数重新运行查询，直到所有整数都被计算在内，但这似乎很愚蠢.必须有一种方法可以让我得到一个包含两列的列表:组大小和执行完全相同操作数量的用户数.

I could use the having event_count = n clause and just rerun the query for every integer until all were accounted for but that seems silly. There must be a way that I can get a single list with two columns: the group size and the count of the users who all took the exact same number of actions.

推荐答案

就像在上面添加另一个分组一样简单:

As simply as adding another grouping above:

select event_count, count(*) as users_count
from
(select count(uid) as event_count
 from table
 group by uid) t
group by event_count
order by event_count

这篇关于sqlite:如何获得组计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！