多表关联统计异常：Profiles/Questions/Votes关联结果不符合预期

数据表结构 #

1. Profiles表 #

2. Questions表 #

3. Votes表 #

需求 #

需要构建如下结构的汇总表：

错误的SQL语句 #

SELECT profiles.id,
    profiles.created_at,
    profiles.full_name,
    profiles.avatar_url,
    count(questions.owner_id) AS questions_published,
    count(votes.user_id) AS votes_cast
  FROM public.profiles
  LEFT JOIN public.questions
  ON profiles.id = questions.owner_id
  LEFT JOIN votes
  ON profiles.id = votes.user_id
  GROUP BY profiles.id;

错误执行结果 #

预期结果 #

问题分析 #

原SQL存在两个核心问题：

1. 笛卡尔积导致计数失真：直接关联三张表时，若一个用户有N个问题、同时存在M条投票记录，会生成N*M条组合记录，最终count统计的是组合行数而非实际数量。比如测试用户有2个问题+2条投票，关联后生成4条记录，导致计数翻倍。
2. 投票统计逻辑错误：原SQL用profiles.id = votes.user_id统计用户投出的票数，但需求是统计用户发布的问题收到的票数，正确关联条件应为questions.id = votes.question_id。

修正后的SQL语句 #

方式一：用DISTINCT避免笛卡尔积 #

SELECT 
    p.id,
    p.created_at,
    p.full_name,
    p.avatar_url,
    COUNT(DISTINCT q.id) AS questions_published,
    COUNT(v.question_id) AS votes_cast
FROM public.profiles p
LEFT JOIN public.questions q ON p.id = q.owner_id
LEFT JOIN public.votes v ON q.id = v.question_id
GROUP BY p.id, p.created_at, p.full_name, p.avatar_url;

方式二：子查询预统计（性能更优） #

SELECT 
    p.id,
    p.created_at,
    p.full_name,
    p.avatar_url,
    COALESCE(q.question_count, 0) AS questions_published,
    COALESCE(v.total_votes, 0) AS votes_cast
FROM public.profiles p
LEFT JOIN (
    -- 预统计每个用户发布的问题数
    SELECT owner_id, COUNT(*) AS question_count
    FROM public.questions
    GROUP BY owner_id
) q ON p.id = q.owner_id
LEFT JOIN (
    -- 预统计每个用户发布的问题收到的总票数
    SELECT q.owner_id, COUNT(v.question_id) AS total_votes
    FROM public.questions q
    LEFT JOIN public.votes v ON q.id = v.question_id
    GROUP BY q.owner_id
) v ON p.id = v.owner_id;

验证结果 #

执行上述任意SQL，均可得到符合预期的汇总结果。

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