寻找每个赛季得分最高的主队

jtjikinw 于 2021-05-27 发布在 Hadoop

关注(0)|答案(1)|浏览(340)

下面是我的Hive查询，试图找出每个赛季得分最高的主队。

select t1.season , max(t1.TOTAL_Goals) as Highest_Score
  from
 (select season, home_team_id, sum(home_goals) TOTAL_Goals
    from game_kpark
   group by season, home_team_id
 ) as t1
 group by t1.season

上述代码的结果如下表所示

t1.season   highest_score

20122013    118
20132014    179
20142015    174
20152016    173
20162017    204
20172018    185

如果我包括 t1.home_team_id 之后 SELECT 以及 GROUP BY 最后，它返回每个赛季所有球队的总得分，而不是最高得分。
如何正确地编写查询以查看每个赛季得分最高的相应球队？

hadoop Hive greatest-n-per-group hiveql

来源：https://stackoverflow.com/questions/54838315/finding-the-highest-scoring-home-team-for-every-season

1条答案

按热度按时间

dgiusagp1#

使用 rank() 分析函数：

select s.season, s.home_team_id, s.TOTAL_Goals
from
(
select s.season, s.home_team_id, s.TOTAL_Goals, 
       rank() over(partition by season order by s.TOTAL_Goals desc) as rnk
  from
 (--team season totals
  select season, home_team_id, sum(home_goals) TOTAL_Goals 
    from game_kpark
   group by season, home_team_id
 ) s
) s
where rnk=1; --filter teams with highest rank per season

赞(0）回复(0）举报 2021-05-27

我来回答

寻找每个赛季得分最高的主队

1条答案

相关问题

热门标签

最新问答