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，美国是第 1 名，因为它的所有游戏记录总和。

对以下评论的要求：

原始数据如下所示：

+----+---------+--------------+---------+------------+--+
| 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 |  |
+----+---------+--------------+---------+------------+--+

标签： sqlsql-servergroup-bygreatest-n-per-group

Answer 1

您只能使用窗口函数执行此操作：

select 
    id, 
    country,
    game,
    game_records records,
    date max_date,
    dense_rank() over(order by country_records desc) rnk
from (
    select 
        t.*,
        rank() over(partition by id, country, game order by date desc) rn,
        sum(records) over(partition by id, country, game) as game_records,
        sum(records) over(partition by country) country_records
    from mytable t
) t
where rn = 1
order by rnk, records

内部查询(id, country, game)按降序排列具有相同记录的记录date，并计算同一 (id, country, game)分区和由所有国家/地区记录组成的分区的窗口总和。

然后，外部查询过滤每个第一个分区中的顶部记录（这为我们提供了最大日期），并按每个国家/地区的总记录进行排名。

DB Fiddle上的这个演示与您的示例数据返回：

编号 | 国家 | 游戏 | 记录 | 最大日期 | rnk
-: | :-------- | :----------- | ------: | :----------------- | :--
 2 | 美国| 哇 | 10 | 01/01/2019 00:00:00 | 1  
 2 | 美国| 瓦库 | 15 | 01/01/2019 00:00:00 | 1  
 2 | 美国| 皇室战争 | 30 | 01/01/2019 00:00:00 | 1  
 2 | 德国| 家企业 多福斯 | 9 | 01/01/2019 00:00:00 | 2