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)
Copyright © 尚硅谷大数据 2019 all right reserved,powered by Gitbook
该文件最后修订时间: 2018-11-20 18:14:21

results matching ""

    No results matching ""