select b.*, distinct c.buyer_id, max(gmv_by_buyer)over(partition by c.buyer_id) as 最豪买家 from (select distinct b.item_id , max(gmv_by_item)over(partition by b.item_id)as 最热销单品 from (select item_id, sum(amp)over(partition by item_id ) as gmv_by_item from A where month(dt) in (7,8,9) and seller_id = '123' )bb )b join (select item_id, buyer_id , sum(amp)over(partition by buyer_id ) as gmv_by_buyer from A where month(dt) in (7,8,9) and seller_id = '123' )c on b.item_id=c.item_id