select item_id,buyer_id from(
select item_id,buyer_id,dense_rank() over(order by tot_amt desc) as ranking1,
dense_rank() over(partition by item_id order by amt desc) as ranking2 from (
select item_id, buyer_id,amt,sum(amt) over (partition by item_id order by amt ) as tot_amt
from A
where dt>=201807 and dt <=201809 and seller_id= 123) as a ) as b
where b.ranking1 = 1 and b.ranking2=1;
我的方法是采用窗口函数，先计算每个item_id的总金额tot_amt，然后用dense_rank()分别对tot_amt、amt排序，再根据条件ranking=1，选择 item_id,buyer_id
ps：采用rank()函数是考虑到可能有销售总额相同的 item_id以及最高购买金额相同的buyer_id，若直接采用limit1来选择最大值可能导致选择的数据不全
