sql - 选择 SUM 和具有最大值的列

Question

我寻找最好或最简单的方法 SELECT type, user_with_max_value, SUM(value) GROUP BY type .表看起来相似

type  | user | value
type1 | 1    | 100
type1 | 2    | 200
type2 | 1    | 50
type2 | 2    | 10

结果看起来:

type1 | 2 | 300
type2 | 1 | 60

Answer 1

使用窗口函数:

select type, max(case when seqnum = 1 then user end), sum(value)
from (select t.*,
             row_number() over (partition by type order by value desc) as seqnum
      from t
     ) t
where seqnum = 1;

某些数据库具有用于返回第一个值的聚合函数的功能。一种没有使用标准 SQL 的子查询的方法是:

select distinct type,
       first_value(user) over (partition by type order by value desc) as user,
       sum(value) over (partition by type)
from t;