sql - 如何使用分组依据从表中查找最大值

Question

我有下面的表(播放器)列

playerId  score  teamId  

此表包含所有球队的所有球员信息。 PlayerID 是主列。每个团队包括多名球员，因此 teamId 上有很多重复值。分数是每个玩家的分数。

我想写一个 hive-sql 来查询每支球队的最高得分球员。下面是我试过的查询:

select max(score) score, teamId from player group by teamId

这个查询工作正常，但它只显示 teamId 和最高分数。我也想查询playerId。如果我在选择列中添加 playerId，我会遇到以下错误:

org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:32 Invalid column reference 'playerId'

好像只能从group by中获取column。如何编写查询来查找 playerId？

Answer 1

在 Hive 中，您应该使用窗口函数执行此操作:

select p.score, p.teamId
from (select p.*,
             row_number() over (partition by teamId order by score desc) as seqnum
      from player p
     ) p
where seqnum = 1;

进行单独的聚合和连接是表达此类逻辑的“旧”方式。 SQL 在过去几十年中变得更加强大。