postgresql - 如何显示一列相对于另一列的最大值？

Question

我有这样一个数据集:

    story_name     | users  | age | reading_counts
-------------------+--------+-----+----------------
 Humpty Dumpty     | Elaine |   5 |             10
 Wheels on the Bus | Simon  |   3 |             15
 Dr.Seuss          | Simon  |   3 |             12
 asd               | Simon  |   3 |             10
 dsf               | Simon  |   3 |              6
 Dr.Seuss          | Elaine |   5 |              3
 asd               | Elaine |   5 |              7
(7 rows)

我希望能够编写一个查询来显示每个唯一用户的最大阅读计数。所以像这样:

        story_name     | users  | reading_counts
-------------------+--------+----------------
 Humpty Dumpty     | Elaine |             10
 Wheels on the Bus | Simon  |             15

---

到目前为止我有这个查询:

SELECT story_name, users, reading_counts
FROM story
WHERE reading_counts IN (SELECT MAX(reading_counts) FROM story GROUP BY users);

我得到了这个结果:

        story_name     | users  | reading_counts
-------------------+--------+----------------
 Humpty Dumpty     | Elaine |             10
 Wheels on the Bus | Simon  |             15
 asd               | Simon  |             10
(3 rows)

Answer 1

您可以在子查询中使用窗口函数 rank() 为具有相同用户的记录组中的每条记录分配排名，按阅读计数降序排序，然后过滤顶部记录在外部查询的每个组中:

select story_name, users, reading_counts
from (
    select 
        t.*,
        rank() over(partition by users order by reading_counts desc) rn 
    from mytable t
) x
where rn = 1

注意:如果给定用户存在最高关系(即具有相同最大阅读计数的多条记录)，它们将全部返回。

另一种可能提供更好性能的解决方案是使用相关子查询来进行过滤，如下所示:

select story_name, users, reading_counts
from mytable t
where reading_counts = (
    select max(reading_counts) from mytable t1 where t1.users = t.users
)

此查询将利用 users 上的索引。