mysql 复杂 SUM 除以 COUNT

Question

我对 mysql 不是很有经验，所以我希望我的问题不是太愚蠢，并且代码行或多或少具有可读性。以下 mysql 查询提供了一组学生在小组作业中制作的特定内容的数量。

SELECT
access_collections.id AS "Group ID", 
access_collections.name AS "Group",
SUM(CASE WHEN system_log.object_subtype="blog" AND system_log.event="create" THEN 1 ELSE 0 END) AS "Number of blog entries",
SUM(CASE WHEN system_log.object_subtype="comment" AND system_log.event="create" THEN 1 ELSE 0 END) AS "Number of comments",
SUM(CASE WHEN system_log.object_subtype="discussion_reply" AND system_log.event="create" THEN 1 ELSE 0 END) AS "Number of discussion replies",
SUM(CASE WHEN system_log.object_subtype="chat_message" AND system_log.event="create" THEN 1 ELSE 0 END) AS "Number of chat messages",
SUM(CASE WHEN system_log.object_subtype="file" AND system_log.event="create" THEN 1 ELSE 0 END) AS "Number of uploaded files",
SUM(CASE WHEN system_log.object_subtype="messages" AND system_log.event="create" THEN 1 ELSE 0 END) AS "Number of messages",

FROM system_log

INNER JOIN access_collection_membership ON access_collection_membership.user_guid=system_log.performed_by_guid
INNER JOIN access_collections ON access_collections.id=access_collection_membership.access_collection_id

GROUP BY access_collections.id

现在我想获取所有这些类型内容的平均数量。它对我来说不适用于 AVG() 所以我想我可以根据组成员的数量来划分内容总和。获取每个组成员的查询是这样的

SELECT access_collections.id, COUNT(access_collection_membership.user_guid)
FROM access_collection_membership
INNER JOIN access_collections ON access_collections.id=access_collection_membership.access_collection_id
GROUP BY access_collections.id

我知道我的 SUM() 函数应该替换为 COUNT() 函数，但经过几个小时的尝试后它对我不起作用。如果有人能帮助我，我会很高兴:)如果您需要更多信息，请告诉我。

Answer 1

我自己找到了答案。

正如预期的那样，我必须(或者至少我做到了)将 SUM() 函数更改为 COUNT 函数。对于一个示例列，正确的 COUNT() 函数是

SELECT COUNT(CASE WHEN system_log.object_subtype="blog"AND system_log.event="create"THEN system_log.object_subtype END) AS“博客条目数”...

在该查询之上，我构建了平均值(也适用于该示例列)，如下所示。

SELECT 
access_collections.id AS "Group ID", 
access_collections.name AS "Group",
TRUNCATE(AVG(blog),2) AS "Average amount of blog entries"
FROM (SELECT system_log.performed_by_guid,
      COUNT(CASE WHEN system_log.object_subtype="blog" AND system_log.event="create" THEN system_log.object_subtype END) AS blog
      FROM system_log GROUP BY system_log.performed_by_guid) ttemp

INNER JOIN users_entity ON users_entity.guid=ttemp.performed_by_guid
INNER JOIN access_collection_membership ON access_collection_membership.user_guid=users_entity.guid
INNER JOIN access_collections ON access_collections.id=access_collection_membership.access_collection_id

GROUP BY access_collections.id
ORDER BY access_collections.name