postgreSQL查询，以获取每条帖子的点赞、评论、阅读次数和得分

Question

所以我有4个表：

1. 帖子
2. 点赞
3. 评论
4. 读取

每个表都有列post_id

我想创建一个查询，对于每个帖子，我想知道点赞数量、评论数量和阅读次数
最后，我想要另一个列的评分，在这里我总结了点赞、评论和阅读中的值

以下是我必须执行的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

但是我无法获得正确的评论数量，并且项目的总和不能正常工作

我在postgres 上

这是一个样本数据：

posts:
post_id | title
101       some title
102       hello there
103       good day sir
104       good bye         
105       whats up 

likes:
like_id | post_id
1         101  
2         101
3         101
4         102
5         102
6         104
7         104
8         105
9         105
10        101
comments:
comment_id | post_id
1            103  
2            103
3            103
4            101
5            102
6            104
7            105
8            105
9            105
10           103
post_read_count:
post_id | count
101       12
102       54
103       76
104       23         
105       87 

期望输出：

output:
post_id | title         | likes | comments | reads | score
101       some title      4       1          12      17
102       hello there     2       1          54      57
103       good day sir    0       4          76      80
104       good bye        2       1          23      26
105       whats up        2       3          87      92

Answer 1

因为您有一对多，所以我会使用两个CTE来获得您的合计计数。您需要合并，因为有些计数可能为零，并且不能将NULL与整数相加。

Schema(PostgreSQL v13(

create table posts (
post_id integer, 
title varchar(20)
);
insert into posts values 
(101, 'some title'),
(102, 'hello there'),
(103, 'good day sir'),
(104, 'good bye'),    
(105, 'whats up');
create table likes (
like_id integer, 
post_id integer
);

insert into likes values 
(1, 101),
(2, 101),
(3, 101),
(4, 102),
(5, 102),
(6, 104),
(7, 104),
(8, 105),
(9, 105),
(10, 101);
create table comments (
comment_id integer, 
post_id integer
);

insert into comments values 
(1, 103),
(2, 103),
(3, 103),
(4, 101),
(5, 102),
(6, 104),
(7, 105),
(8, 105),
(9, 105),
(10, 103);
create table post_read_count (
post_id integer, 
pcount integer
);

insert into post_read_count values 
(101, 12),
(102, 54),
(103, 76),
(104, 23),      
(105, 87);

---

查询#1

with cte_likes as (
select post_id, count(*) as total_likes
from likes
group by post_id
), 
cte_comments as (
select post_id, count(*) as total_comments
from comments
group by post_id
)
select p.post_id, 
p.title,
coalesce(l.total_likes, 0) as likes, 
coalesce(c.total_comments, 0) as comments, 
coalesce(prc.pcount, 0) as reads, 
coalesce(l.total_likes, 0) + coalesce(c.total_comments, 0) + coalesce(prc.pcount, 0) as score
from posts p
left join cte_likes l
on p.post_id = l.post_id
left join cte_comments c
on p.post_id = c.post_id
left join post_read_count prc
on p.post_id = prc.post_id;

点赞<1><2><1>54//tr>>2692

post_id	标题	评论	阅读	得分
101	某些标题	12	17
102	你好	57
103	先生，您好	0	4	76	80
104	再见	2	1	23
105	发生了什么	2	3	87

这个技巧是额外的联接将每个额外联接的结果相乘。如果我们仔细思考引擎是如何构建结果的，这会有所帮助。

假设你只有一篇帖子，但那篇帖子已经获得了3个赞和2条评论。在从posts到likes的第一次连接之后，在group by之前，到目前为止，结果集有3行：

post_id	like_id
1	1
1	2
1	3