专注大数据培训
我们一直在领跑

最新面试题集锦(15)

假如有一张直播间送礼记录的Hive表,查询每个直播间送礼金额最多的3个用户及其金额数:
default.dwd_trade_user_consume_order
列名 类型 备注
user_id varchar
room_id bigint
scores bigint
op_time bigint 10位时间戳
数据示例.
user_id room_id scores
A001 11110 100
A002 11111 80
A003 11111 10
… … …

select
room ,
uid ,
sum_cost
from
(select
room ,
uid,
sum_cost ,
row_number() over(partition by room , uid order by sum_cost desc) rn
from
(select
room ,
uid ,
sum(cost) sum_cost
from
tb
group by room , uid)t)t2
where rn <=3 ;

欢迎分享,转载有奖:多易教育 » 最新面试题集锦(15)