获取具有最高平均等级的两个记录的对

Question

我想计算每条记录的平均等级，然后得到平均等级最高的两条记录，每个类别。

我的等级表如下所示：

userid | recordid | grade
123    | 1        | 8
123    | 2        | 1
123    | 3        | 3
123    | 4        | 6
121    | 1        | 3
121    | 2        | 7
121    | 3        | 1
121    | 4        | 8
124    | 1        | 6
124    | 2        | 8
124    | 3        | 9
124    | 4        | 5

和记录表,如下所示：

id | userid | name | category | year
1  | 101    | Foo  | FooCat   | 2021
2  | 102    | Bar  | FooCat   | 2021
3  | 103    | Foos | BarCat   | 2021
4  | 104    | Bars | BarCat   | 2021

结果将如下所示：

id | name | category | grade_avg
4  | Bars | BarCat   | 6.3
1  | Foo  | FooCat   | 5.7
2  | Bar  | FooCat   | 5.3
3  | Foos | BarCat   | 4.3

我当前正在使用此SQL查询：

SELECT S.id, S.name, S.category, AVG(IB.grade) AS grade_avg
FROM (SELECT id, name, category, year, row_number() over (partition by category) as r FROM records) S
INNER JOIN grades IB ON IB.recordid = S.id
WHERE S.r < 3 AND S.year=2021
GROUP BY IB.recordid
ORDER BY grade_avg DESC

分组确实工作得很好，但是分区时的等级不是按平均等级排序的，所以您只得到每个类别的前2个记录。

要得到我想要达到的结果，最好的查询是什么？

Answer 1

如果我没有理解错的话，您可以使用row_number()枚举行，得到等级最高的两行，然后聚合：

select name, category, avg(grade)
from (select g.*, r.name, r.category,
             row_number() over (partition by g.recordid order by g.grade desc) as seqnum
      from grades g join
           records r
           on g.recordid = r.id
      where r.year = 2021
     ) g
where seqnum <= 2
group by name, category;

这是一个dB<>小提琴。

编辑：

根据您的结果，您需要的只是整体平均值，而不是前两行的平均值。这就简单多了：

select r.name, r.category, avg(grade)
from grades g join
     records r
     on g.recordid = r.id
where r.year = 2021
group by r.name, r.category