PostgreSQL中帖子点赞/评论/阅读量及得分统计的SQL问题

我有以下4张表：

• posts
• likes
• comments
• post_read_count
  每张表都包含post_id字段。我需要编写一条PostgreSQL查询语句，获取每个未删除帖子的点赞数、评论数、阅读量，以及这三项数值求和得到的score字段。

我当前写的SQL语句如下：

SELECT posts.post_id,
       posts.title,
       count(likes.like_id) as likes,
       count(comments.comment_id) as comments,
       post_read_count.count as reads,
       (count(likes.like_id) + count(comments.comment_id) + post_read_count.count) as score
FROM community.posts
LEFT JOIN community.likes
    ON posts.post_id = likes.post_id
LEFT JOIN community.comments
    ON posts.post_id = comments.post_id
LEFT JOIN community.post_read_count
    ON posts.post_id = post_read_count.post_id
WHERE posts.is_deleted = false
GROUP BY posts.post_id, post_read_count.count
ORDER BY posts.post_id DESC
LIMIT 100

但这条查询无法得到正确的评论数，score字段的求和计算也存在异常。

示例数据 #

posts表：

likes表：

comments表：

post_read_count表：

期望输出 #

你的查询错误在于直接对likes和comments做左连接后再计数，这会产生笛卡尔积：比如一个帖子有4个点赞和1条评论，连接后会生成4×1=4行数据，此时count(likes.like_id)会得到正确的4，但count(comments.comment_id)也会被算成4（因为每一行的comment_id都存在），导致评论数被错误放大。

推荐先对likes和comments按post_id做聚合统计，再与posts和post_read_count做左连接，彻底避免笛卡尔积问题：

SELECT 
    p.post_id,
    p.title,
    COALESCE(l.like_count, 0) AS likes,
    COALESCE(c.comment_count, 0) AS comments,
    COALESCE(prc.count, 0) AS reads,
    COALESCE(l.like_count, 0) + COALESCE(c.comment_count, 0) + COALESCE(prc.count, 0) AS score
FROM community.posts p
LEFT JOIN (
    SELECT post_id, COUNT(like_id) AS like_count
    FROM community.likes
    GROUP BY post_id
) l ON p.post_id = l.post_id
LEFT JOIN (
    SELECT post_id, COUNT(comment_id) AS comment_count
    FROM community.comments
    GROUP BY post_id
) c ON p.post_id = c.post_id
LEFT JOIN community.post_read_count prc ON p.post_id = prc.post_id
WHERE p.is_deleted = false
ORDER BY p.post_id DESC
LIMIT 100;

说明 #

1. 子查询聚合：先分别统计每个帖子的点赞数和评论数，确保每个post_id只返回一行统计结果，避免连接后的数据膨胀。
2. COALESCE函数：处理没有点赞、评论或阅读记录的帖子，将NULL值替换为0，保证数值计算的正确性。
3. 别名简化：给表和子查询设置别名，让SQL更简洁易读。

备选方案（使用COUNT(DISTINCT)） #

如果不想改写子查询，也可以用COUNT(DISTINCT)去重修正原查询，但数据量大时性能不如先聚合再连接：

SELECT 
    posts.post_id,
    posts.title,
    COUNT(DISTINCT likes.like_id) AS likes,
    COUNT(DISTINCT comments.comment_id) AS comments,
    COALESCE(post_read_count.count, 0) AS reads,
    COUNT(DISTINCT likes.like_id) + COUNT(DISTINCT comments.comment_id) + COALESCE(post_read_count.count, 0) AS score
FROM community.posts
LEFT JOIN community.likes ON posts.post_id = likes.post_id
LEFT JOIN community.comments ON posts.post_id = comments.post_id
LEFT JOIN community.post_read_count ON posts.post_id = post_read_count.post_id
WHERE posts.is_deleted = false
GROUP BY posts.post_id, posts.title, post_read_count.count
ORDER BY posts.post_id DESC
LIMIT 100;

注意：这里需要在GROUP BY中显式加入posts.title（若post_id是主键，PostgreSQL允许只按post_id分组，但显式加入更稳妥），同时用COUNT(DISTINCT)去除重复计数的问题。

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