从相关表中计算MySQL平均计数

Question

我有两个表格：

用户表

用户列表

故事表

故事列表 - 每个用户有多个故事

我想知道每个用户平均有多少个故事。（不是特定用户，而是所有用户）

期望结果：每个用户平均 2.3 个故事

尝试过：

select avg(w) from (select count(distinct user_id) as w from stories group by user_id) a; 以上代码可以运行但似乎不正确。

还有：

SELECT user_id, ( SELECT COUNT(*) FROM stories w WHERE w.user_id = u.user_id ) as TotalStories FROM user u;

返回了每个用户的平均故事数，而不是整体平均。

- jhanifen

---

从故事中选择 (select count(distinct user_id)作为 w,按 user_id 分组) a; 选取平均值(avg) w。 - jhanifen

以上运行了，但似乎不正确。 - jhanifen

SELECT user_id, ( SELECT COUNT(*) FROM stories w WHERE w.user_id = u.user_id ) as TotalStories FROM user u; 选择 user_id， （ 选择计数（*） 从故事w 其中w.user_id = u.user_id ）作为总偏爱 从用户u; - jhanifen

请将此内容添加到您的问题中，通过编辑您的问题以便每个成员都可以查看此内容。 - rahularyansharma

以上代码不会计算平均值，它只是返回整个用户列表的平均值。 - jhanifen

Answer 1

首先你需要知道每个用户的故事数量：

select count(s.id) as n
from users u left outer join stories s on u.id = s.user_id
group by u.id

那么只需将 avg 应用于该值：

select avg(n)
from (
    select count(s.id) as n
    from users u left outer join stories s on u.id = s.user_id
    group by u.id
) as dt

- mu is too short

---

这个错误提示是“每个派生表必须有自己的别名”。 - jhanifen

@jhanifen：抱歉，忘记了“as dt”这一部分。 - mu is too short

这也可以工作，并且有两个单独的查询返回相同的结果作为测试是非常好的。 - jhanifen

有没有关于哪种方法更好或更快的想法？ - jhanifen

@jhanifen：你需要对它们进行分析。使用“avg”告诉数据库您的确切意图，这通常是最好的选择；一般来说，您能够告诉数据库您正在尝试做什么，它就能够尽可能快地给您提供您想要的结果。如果您想要一个平均值，请向数据库请求；如果您想要一个标准差，请向数据库请求（stddev_pop），... - mu is too short

Answer 2

SELECT COUNT(userid) AS totalusers, SUM(storycount) AS totalstories, SUM(storycount) / COUNT(userid) AS average_stories
FROM (
    SELECT users.id AS userid, COUNT(stories.id) AS storycount
    FROM users
    LEFT JOIN stories ON (users.id = stories.user_id)
    GROUP BY users.id
) AS child

内部查询执行每个用户的故事计数。外部查询计算总用户数、总故事数和每个用户平均故事数。

- Marc B