计数不同列的最大值的问题

Question

i在亚马逊雅典娜(Amazon Athena(中有一个名为"级别"的表，其中包含称为"用户"，" latverstarted"one_answers" latverCount"的列。桌子看起来像： +------+---------------+--------------+ | user | levelstarted | startedcount | +------+---------------+--------------+ | A | 0050 | 2 | | A | 0051 | 1 | | A | 0052 | 3 | | B | 0030 | 1 | | B | 0031 | 2 | | B | 0032 | 5 | | C | 0010 | 6 | | C | 0011 | 3 | | C | 0012 | 3 | +------+---------------+--------------+

对于每个用户，我想找到最高水平的启动以及播放器启动了多少次。我希望能获得看起来像这样的结果：

+------+----------------+----------------+ | user | highestlevel | | startedcount | | +------+----------------+----------------+ | A | 0052 | 3 | | B | 0032 | 5 | | C | 0012 | 3 | +------+----------------+----------------+

寻找最高启动级别的工作正常：

SELECT 
 DISTINCT user as payer,
 MAX(levelstarted) as levelstarted
FROM "levels"
GROUP BY user, startedcount

但是，当我添加启动计数时，我会在结果中获得重复的用户：

SELECT 
 DISTINCT user as payer,
 MAX(levelstarted) as levelstarted,
 startedcount
FROM "levels"
GROUP BY user, levelcount

Answer 1

在athena/presto中您可以使用max_by函数找到与列的最大值相关的值：

SELECT
  user,
  MAX(levelstarted) AS highestlevel,
  MAX_BY(startedcount, levelstarted) AS startedcount
FROM (VALUES ('A', '0050', 2),
             ('A', '0051', 1),
             ('A', '0052', 3),
             ('B', '0030', 1),
             ('B', '0031', 2),
             ('B', '0032', 5),
             ('C', '0010', 6),
             ('C', '0011', 3),
             ('C', '0012', 3)
) AS v (user, levelstarted, startedcount)
GROUP BY user
ORDER BY user