前端之家

  1. 首页
  2. 问答

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

• 问答

故事：

我正在尝试按国家和游戏获取记录总数和最大日期，以及另一个根据记录总数对排名最高的国家进行排名的列：

select id,country,game,sum(records) as records,max(date) as max_date
from table
group by id,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 |  |
+----+---------+--------------+---------+------------+--+
weiwei398 回答：在执行其他类型的聚合时获得最高类别

您只能使用窗口功能来做到这一点：

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,game order by date desc) rn,sum(records) over(partition by id,game) as game_records,sum(records) over(partition by country) country_records
    from mytable t
) t
where rn = 1
order by rnk,records

该内部查询通过对(id,game)进行降序对具有相同date的记录进行排名，并为相同的(id,game)分区和计算由以下组成的分区的窗口总和所有国家/地区记录。

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

此 demo on DB Fiddle 及其示例数据将返回：

id | country | game         | records | max_date            | rnk
-: | :------ | :----------- | ------: | :------------------ | :--
 2 | usa     | wow          |      10 | 01/01/2019 00:00:00 | 1  
 2 | usa     | wakfu        |      15 | 01/01/2019 00:00:00 | 1  
 2 | usa     | clash royale |      30 | 01/01/2019 00:00:00 | 1  
 2 | germany | dofus        |       9 | 01/01/2019 00:00:00 | 2
,

您可以基于您的聚合查询。此版本产生的排名类似于row_number()，因此关系将获得不同的值：

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

Here是db 小提琴。

greatest-n-per-groupgroup-bysqlsql-server
本文链接：https://www.f2er.com/3163484.html

大家都在问