11.4.3 统计最热 Top20 视频的类别中每个类别包含 Top20 的视频的个数

思路:

• 找到观看数最高的 20 个视频, 降序排列.

• 炸裂出来类别

• 查看每个类别有多个 Top20 的视频

select 
    category_name,
    count(*) hot
from(
    select
        videoId,
        views,
        category
    from gulivideo_orc
    order by views desc
    limit 20
)t1
lateral view explode(category) temp as category_name
group by category_name
order by hot desc;

+----------------+------+
| category_name  | hot  |
+----------------+------+
| Entertainment  | 6    |
| Comedy         | 6    |
| Music          | 5    |
| People         | 2    |
|  Blogs         | 2    |
|  UNA           | 1    |
+----------------+------+
6 rows selected (67.996 seconds)