sql - 如何在不使用子查询的情况下从连接表中获取计数的直方图？

Question

我有很多看起来像这样的表:(id，user_id，object_id)。我经常对“有多少用户拥有一个对象？有多少用户拥有两个对象？等等”这个问题感兴趣。并希望看到分布。

显而易见的答案如下:

select x.ucount, count(*) 
from (select count(*) as ucount from objects_users group by user_id) as x 
group by x.ucount 
order by x.ucount;

这会产生如下结果:

ucount | count
-------|-------
1      | 15
2      | 17
3      | 23
4      | 104
5      | 76
7      | 12

在这里使用子查询对我来说感觉不雅，我想弄清楚如何在没有的情况下产生相同的结果。此外，如果您要问的问题稍微复杂一些，则从子查询中传递更多信息会变得困惑。例如，如果您希望数据按用户的创建日期进一步分组:

select 
    x.ucount, 
    (select cdate from users where id = x.user_id) as cdate, 
    count(*) 
from (
    select user_id, count(*) as ucount 
    from objects_users group by user_id
) as x 
group by cdate, x.ucount,  
order by cdate, x.ucount;

有没有办法避免子查询的爆炸？我想最终我的反对意见是审美的，但它使查询难以阅读和编写。

Answer 1

我认为子查询正是执行此操作的合适方法，无论您的 RDBMS 是什么。为什么会不优雅？

对于第二个查询，只需加入 users像这样的表:

SELECT
 x.ucount,
 u.cdate,
 COUNT(*)
FROM (
 SELECT
  user_id,
  COUNT(*) AS ucount
 FROM objects_users
 GROUP BY user_id
) AS x
LEFT JOIN users AS u
 ON x.user_id = u.id
GROUP BY u.cdate, x.ucount
ORDER BY u.cdate, x.ucount