无极限的解决方案

Question

我有一个这样的表（来自LeetCode）：

requester_id	Accepter_id	Accept_date
1	2	2016/06/03
1	3	2016/06/08
2	3	2016/06/08
3	4	2016/06/09

找到拥有最多朋友的人以及最多的好友数量。

预期输出：

id	num
3	3

我编写了这个查询来获取答案，它有效：

select a.f as "id", count(a.f) as "num"
from (
    select requester_id as f
    from requestaccepted
    union all
    select accepter_id as f
    from requestaccepted ) a
group by a.f
order by count(a.f) desc
limit 1;

我并不 100% 相信使用 limit 1 是最好的解决方案。

替代/更好的选择应该是什么？

Answer 1

ORDER BY 之后的 LIMIT 1 就可以获得单个获胜者：

SELECT a.id, count(*) AS num
FROM  (
   SELECT requester_id AS id FROM requestaccepted
   UNION ALL
   SELECT accepter_id  AS id FROM requestaccepted
   ) a
GROUP  BY a.id
ORDER  BY num DESC
LIMIT  1;

如果有多个获胜者，您将获得任意选择。您可以添加其他 ORDER BY 表达式来获得确定性选择。

LIMIT / FETCH FIRST （真的吗？），窗口函数 row_number() 是一个（更昂贵！）替代方案：

SELECT id, num
FROM  (
   SELECT a.id, count(*) AS num
        , row_number() OVER (ORDER BY count(*) DESC) AS rn
   FROM  (
      SELECT requester_id AS id FROM requestaccepted
      UNION ALL
      SELECT accepter_id  AS id FROM requestaccepted
      ) a
   GROUP BY a.id
   ) sub
WHERE  rn = 1;

如果您必须避免 所有获胜的 ID，只需添加 WITH TIES。必须使用标准 SQL 语法 FETCH FIRST 1 ROWS 而不是 Postgres 快捷方式 LIMIT 1 来添加该子句。

SELECT a.id, count(*) AS num
FROM  (
   SELECT requester_id AS id FROM requestaccepted
   UNION ALL
   SELECT accepter_id  AS id FROM requestaccepted
   ) a
GROUP  BY a.id
ORDER  BY count(*) DESC
FETCH  FIRST 1 ROWS WITH TIES;

无需额外的 ORDER BY 表达式来解决关系。

如果您必须避免 LIMIT / FETCH FIRST（真的吗？），窗口函数 rank() 是一个（更昂贵！）替代方案：

SELECT id, num
FROM  (
   SELECT a.id, count(*) AS num
       , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM  (
      SELECT requester_id AS id FROM requestaccepted
      UNION ALL
      SELECT accepter_id  AS id FROM requestaccepted
      ) a
   GROUP  BY a.id
   ) sub
WHERE  rnk = 1
ORDER  BY id; -- optional

db>>小提琴此处 - 带有扩展测试用例以显示平局

请参阅：

• 获取具有最高值且有联系的顶行
• <一个href="https://stackoverflow.com/questions/9629953/postgresql-equivalent-for-top-n-with-ties-limit-with-ties/9630562#9630562">PostgreSQL 等价于 TOP n WITH TIES: LIMIT "有关系”？

Answer 2

由于您只需要数量最多的朋友，因此您可以直接获取接受的 id 的数量，并且最多可以使用。

select  id ,max(num) from
(
select accepter_id id ,count(*) num from requestaccepted
group by accepter_id 
) a

Answer 3

另一种有趣的方法是使用 Join Lateral 与 Limit

架构并插入语句：

 create table requestaccepted(requester_id int, accepter_id int, accept_date date);
 insert into requestaccepted values(1,  2,  '2016/06/03');
 insert into requestaccepted values(1,  3,  '2016/06/08');
 insert into requestaccepted values(2,  3,  '2016/06/08');
 insert into requestaccepted values(3,  4,  '2016/06/09');

Output:

 SELECT id,count(*)num
 FROM requestaccepted r
 JOIN LATERAL (VALUES(r.requester_id),(r.accepter_id)) s(id) ON TRUE
 group by id
 order by num desc
 Limit 1 

Output:

id	num
3	3

db<>fiddle 此处