PostgreSQL-从联接表中选择聚合列并使用它求和

Question

我想选择从三个表中聚合的字段，并为post输出三个字段，postId、calculatedRate和count，我尝试过，但无法使用

目前我有这个示例查询，只得到count和postId

select
mr.count,
mr.postId
from
(
select
"m"."postId" as postId,
count("m"."postId") as count
from
"Rating" r
left join "Messenger" m on
"r"."id" = "m"."ratingId"
group by
"m"."postId"
) mr
group by
mr.postId,
mr.count

信使(张贴和评级映射表(

ratingId

id	postId
1	1		4
2	1	5
3		2		6

我不确定我得到了你想要的，但如果你想要每个MESSENGER.ID的COUNT和CALCULATEDRATE，那么下面的方法应该可以实现

SELECT 
MESSENGER.ID,
POST.ID POSTID, 
COUNT(*) NR_RATINGS,
(RATING.A + RATING.B)/2 CALCULATEDRATE
FROM 
RATING JOIN MESSENGER ON RATING.ID = MESSENGER.RATINGID
JOIN POST ON MESSENGER.POSTID=POST.ID

编辑：如果你想要邮寄平均值，你可以通过以下实现

SELECT 
POST.ID POSTID, 
COUNT(*) NR_RATINGS,
avg((RATING.A + RATING.B)/2) CALCULATEDRATE
FROM 
RATING JOIN MESSENGER ON RATING.ID = MESSENGER.RATINGID
JOIN POST ON MESSENGER.POSTID=POST.ID
GROUP BY POST.ID