在聚合查询中基于底层行的返回列。

Consider this table:

考虑一下这个表:

 comment_id | vote | user_id 
------------+------+---------
      79507 |    1 |  351501
      79507 |    2 |  533594
      79544 |    1 |  648703
      79544 |    1 |  533594
      79544 |    2 |  790789
      79545 |    1 |  351501

This aggregate query returns the sum of the vote column for every comment_id:

这个聚合查询返回每个comment_id的投票列的总和:

SELECT comment_id, SUM(vote_up) 
FROM votes 
GROUP BY comment_id 
ORDER BY comment_id;

 comment_id | sum 
------------+-----
      79507 |   3
      79544 |   4
      79545 |   1

However, I would like an additional column to be returned if any of the underlying grouped rows satisfy a condition. In this case, the voted column should be true if and only if any of the aggregated rows has a given user_id (user_id 351501 in this example):

但是，如果任何底层分组行满足条件，我希望返回一个附加列。在这种情况下，如果且仅当所有聚集的行中有一个给定的user_id (user_id 351501):

 comment_id | sum | voted
------------+-----+-------
      79507 |   3 |     t
      79544 |   4 |     f
      79545 |   1 |     t

I see a possible solution with by JOINing the table on itself, but that seems like a hack and seems highly inefficient. I came across window functions, not sure if they apply to this problem.

我看到了一种可能的解决方案，即加入表本身，但这似乎是一种技巧，而且似乎效率极低。我遇到了窗口函数，不确定它们是否适用于这个问题。

1 个解决方案

#1

Use the aggregate function bool_or():

使用聚合函数bool_or():

SELECT comment_id, SUM(vote_up) AS sume_vote
      ,bool_or(user_id = 351501) AS voted
FROM   votes
GROUP  BY 1
ORDER  BY 1;