PostgreSQL中string_agg与COUNT联用导致计数异常问题

嘿，这个问题我之前也碰到过！核心原因是多表连接产生了笛卡尔积，导致你的COUNT统计的是连接后的重复行数，而不是真实的点赞次数。

为什么会出现这种情况？ #

让我给你拆解下逻辑：

• 假设某个concept（知识点）在concept_skills里有4条记录（对应4个技能标签），同时在likes里有5条点赞记录
• 当你直接把concepts、concept_skills、likes三个表做INNER JOIN时，每条技能记录都会和每条点赞记录配对，最终会生成 4×5=20 行数据
• 这时候COUNT(likes.concept_id)统计的是这20行的数量，自然就变成了真实点赞数的4倍

而你单独用COUNT时结果正常，是因为那时候只关联了concepts和likes，没有引入concept_skills，不会产生这种行的重复，统计的就是真实的点赞次数。

两种可行的修复方案 #

方案1：用子查询分别聚合，避免笛卡尔积

这种方式是最稳妥的，分别对技能表和点赞表做分组统计，再关联到主表：

SELECT 
    c.*,
    cs.skillsSortedById,
    l.counted
FROM concepts c
INNER JOIN (
    -- 先聚合每个知识点的技能列表
    SELECT 
        concept_id,
        string_agg(DISTINCT concept_skill, ', ') AS skillsSortedById
    FROM concept_skills
    GROUP BY concept_id
) cs ON c.concept_id = cs.concept_id
INNER JOIN (
    -- 再单独统计每个知识点的点赞数
    SELECT 
        concept_id,
        COUNT(*) AS counted
    FROM likes
    GROUP BY concept_id
) l ON c.concept_id = l.concept_id
ORDER BY l.counted DESC;

这样两个子查询都是基于concept_id独立聚合，不会产生行的重复，统计结果自然准确。

方案2：用COUNT(DISTINCT)去重（如果点赞表有唯一主键）

如果你的likes表有唯一主键（比如like_id），可以直接在COUNT里加DISTINCT，让数据库只统计唯一的点赞记录：

SELECT 
    concepts.*,
    string_agg(DISTINCT concept_skill, ', ') AS skillsSortedById,
    COUNT(DISTINCT likes.like_id) AS counted 
FROM concepts 
INNER JOIN concept_skills ON concepts.concept_id = concept_skills.concept_id 
INNER JOIN likes ON concepts.concept_id = likes.concept_id 
GROUP BY concepts.concept_id, concept_skills.concept_id 
ORDER BY counted desc;

⚠️ 注意：这种方式虽然代码改动小，但如果点赞表数据量很大，DISTINCT会带来额外的性能开销，优先推荐方案1。

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