如何在JOIN两张表时仅按左表记录计算关联列的平均值？

问题出在你直接关联两张表后计算平均值，这会导致每个document_sentiment的记录被重复计算多次（对应关联的entity_sentiment条数），从而偏离了你想要的基于左表记录数的平均值。

修改后的查询语句 #

-- 先计算每个name对应的文档情感平均分（基于document_sentiment的记录数）
WITH document_average AS (
    SELECT name, AVG(score) AS average
    FROM document_sentiment
    GROUP BY name
),
-- 再聚合每个name对应的所有实体名称
entity_collection AS (
    SELECT doc.name, STRING_AGG(entity.ename, ', ') AS entities
    FROM document_sentiment doc
    JOIN entity_sentiment entity ON doc.dId = entity.dId
    GROUP BY doc.name
)
-- 关联两个子查询得到最终结果
SELECT da.name, da.average, ec.entities
FROM document_average da
JOIN entity_collection ec ON da.name = ec.name;

逻辑解释 #

1. 计算文档平均分：通过单独对document_sentiment分组聚合，直接得到基于左表记录数的平均值（也就是(100+70)/2=85），避免了JOIN后数据膨胀带来的重复计算。
2. 聚合实体列表：关联两张表后，按name聚合所有关联的实体名称，得到你需要的逗号分隔的实体字符串。
3. 关联结果：将两个子查询的结果通过name关联，得到最终的预期输出。

另外注意你原查询里的小错误：GROUP BY docT.cname应该改为GROUP BY docT.name，因为你的表结构里并没有cname字段。

内容的提问来源于stack exchange，提问作者user5155835