假如有一张直播间送礼记录的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 ;
关注 易学在线 公众号
每日更新大量优质技术文档
第一时间获知最新上架课程
与众多大数据猿分享与交流
DOIT.EDU 资料来自网络收集整理，如有侵权请告知
微信扫一扫 