sql - 在执行其他类型的聚合时获得最高类别

Question

故事:

我正在尝试按国家/地区和游戏获取记录总和和最大日期，以及根据记录总和对顶级国家/地区进行排名的另一列:

select id, country, game, sum(records) as records, max(date) as max_date
from table
group by id, country, game

是国家排名栏给我带来了麻烦。这是我尝试过的:

ROW_NUMBER() OVER(PARTITION BY id, country ORDER BY SUM(records) DESC) as rn

它所做的只是按国家/地区对每一行分区进行排名，这正是我所期望的。

目标

有没有办法在一个或两个子查询中实现我想要的？

这是所需的输出

+----+---------+--------------+---------+------------+------+
| id | country |     game     | records |  max_date  | rank |
+----+---------+--------------+---------+------------+------+
|  2 | usa     | wow          |      10 | 2019-01-01 |    1 |
|  2 | usa     | wakfu        |      15 | 2019-01-01 |    1 |
|  2 | usa     | clash royale |      30 | 2019-01-01 |    1 |
|  2 | germany | dofus        |       9 | 2019-01-01 |    2 |
+----+---------+--------------+---------+------------+------+

对于 ID #2，由于所有游戏的记录总和，美国国家排名第一。

以下评论请求:

原始数据如下所示:

+----+---------+--------------+---------+------------+--+
| id | country |     game     | records |  max_date  |  |
+----+---------+--------------+---------+------------+--+
|  2 | usa     | wow          |       2 | 2018-12-01 |  |
|  2 | usa     | wow          |       5 | 2018-12-05 |  |
|  2 | usa     | wow          |       1 | 2018-12-10 |  |
|  2 | usa     | wow          |       2 | 2019-01-01 |  |
|  2 | usa     | wakfu        |      10 | 2018-12-10 |  |
|  2 | usa     | wakfu        |       5 | 2019-01-01 |  |
|  2 | usa     | clash royale |      30 | 2019-01-01 |  |
|  2 | germany | dofus        |       2 | 2018-05-01 |  |
|  2 | germany | dofus        |       4 | 2018-07-01 |  |
|  2 | germany | dofus        |       3 | 2019-01-01 |  |
+----+---------+--------------+---------+------------+--+

Answer 1

您可以在聚合查询的基础上进行构建。此版本产生的排名类似于 row_number() ，所以关系会得到不同的值:

select id, country, game, records, max_date,
       dense_rank() over (order by country_sum desc, country) as ranking
from (select id, country, game, sum(records) as records, max(date) as max_date,
             sum(sum(records)) over (partition by country) as country_sum
      from mytable
      group by id, country, game
     ) cg;

Here是一个db<> fiddle 。