时间戳

首页 > 解决方案 > 对分组内的行进行排名（2 列）

sql - 对分组内的行进行排名（2 列）

问题描述

我写了一个查询来获取这样的数据（附图），user_id 和 topic_id 被分组并计算分数的平均值。我想对每个用户的分数进行排名，因此每个用户的最高 topic_id 排名为 1。我们如何编写查询来对他们的分数进行排名，因为我需要为每个用户选择第一行。

表看起来像这样 -

USER_ID        TOPIC_ID    SCORE  
------------- ------------- ------ 
 b33e3100a7be  829e4b89c318  85     
 b33e3100a7be  b19b6f2b2975  82     
 b33e3100a7be  e305c970701c  81.6   
 b33e3100a7be  6c6fac161e65  81.6   
 7379ce6bc5a9  6c6fac161e65  54.6   
 7379ce6bc5a9  e305c970701c  54.6   
 7379ce6bc5a9  b19b6f2b2975  51.6

并希望输出看起来像这样。

 USER_ID        TOPIC_ID    SCORE  RANK
------------- ------------- ------ -----
 b33e3100a7be  829e4b89c318  85     1
 b33e3100a7be  b19b6f2b2975  82     2
 b33e3100a7be  e305c970701c  81.6   3
 b33e3100a7be  6c6fac161e65  81.6   3
 7379ce6bc5a9  6c6fac161e65  54.6   1
 7379ce6bc5a9  e305c970701c  54.6   1
 7379ce6bc5a9  b19b6f2b2975  54.6   2

在此处输入图像描述

标签： sqlpostgresqlsubquerysql-order-bygreatest-n-per-group

解决方案

您可以使用窗口函数来获得所有最高分：

select t.*
from (select t.*,
             rank() over (partition by user_id order by score desc) as seqnum
      from t
     ) t
where seqnum = 1;

在这种情况下rank()，如果有平局，则返回所有最高主题。 row_number()任意返回一个。

您还可以使用相关子查询：

select t.*
from t
where t.score = (select max(t2.score)
                 from t t2
                 where t2.user_id = t.user_id
                );

请注意，这两种方法都可以合并到聚合查询中。无需保存中间结果即可获得您想要的结果。

推荐阅读