sql - 在 Postgres 中使用 max 和 group by 获取第二个属性

Question

我在 Postgres 中有下一个问题。

我有带评论的产品，每条评论都有投票。

所以这里有两个表，一个“投票”表和一个“评论”表:

表“投票”

 Column   |  Type   |                     KEYS                      
-----------+---------+----------------------------------------------------
 id        | integer |
 user_id   | integer | FK to users table
 review_id | integer | FK to reviews table

表“评论”

   Column   |            Type             |                      KEYS                       
------------+-----------------------------+--------------------------------------
 id         | integer                     |  
 product_id | integer                     |  FK to products
 user_id    | integer                     |  FK to users

我想获得每个产品得票最多的评论。换句话说，我需要为每个产品获取具有最大票数的 review_id。

这是我正在使用的查询:

SELECT max(votes_per_review.votes), reviews.product_id
     FROM (SELECT count(*) AS votes,review_id FROM votes GROUP BY review_id) AS votes_per_review, reviews
     WHERE votes_per_review.review_id = reviews.id
     GROUP BY reviews.product_id

我获得了每个产品最受欢迎评论的投票数和产品 ID，但我没有获得 review_id。

我知道您可以认为这个问题在 StackOverflow 中已得到解决，例如 here .但是我无法通过该帖子或尝试使用 OVER PARTITION 解决此问题。

如果问题已在 StackOverflow 中得到解决，在此先表示感谢和抱歉。

Answer 1

这应该返回使用 distinct on 的最多产品的评论:

select distinct on (r.product) rv.*
from (select r.product, r.review_id, count(*) as numvotes
      from reviews r join
           votes v
           on r.id = v.review_id
      group by r.product, r.review_id
     ) rv
order by r.product, numvotes desc;

这也可以使用窗口函数来完成:

select rv.*
from (select r.product, r.review_id, count(*) as numvotes,
             row_number() over (partition by r.product order by count(*) desc) as seqnum
      from reviews r join
           votes v
           on r.id = v.review_id
      group by r.product, r.review_id
     ) rv
where seqnum = 1;

这使用了 ANSI 标准功能，几乎所有数据库都应具备该功能。