11.4.5 统计每个类别中的视频热度 Top10

1. 炸裂类别
select
    views,
    videoId,
    category_name
from gulivideo_orc
lateral view explode(category) tmp as category_name;   // t1

2. 对每个类别的视频的view排名

select
    videoId,
    views,
    category_name,
    rank() over(partition by category_name order by views) rank
from t1; // t2

3. 只取 rank <= 10 的行
select
    *
from t2
where rank <= 10;


合并:

select
    *
from(
    select
        videoId,
        views,
        category_name,
        rank() over(partition by category_name order by views desc) rank
    from(
        select
            views,
            videoId,
            category_name
        from gulivideo_orc
        lateral view explode(category) tmp as category_name   
    ) t1
) t2
where rank <= 10;