11.4.2 统计视频类别热度 Top10

1. 目标:

   统计最热的10个视频类别

2. 思路:

   • 按照类别分组, 然后组内观看数统计就可以了.
   • 当前表结构一个视频对应多个类别, 类别的类型是 Array, 所以需要先炸开数据(列转行).
   • 排序后取 Top10

   select 
    category_name,
    sum(views) hot
   from(
    select 
        videoId,
        views,
        category_name 
    from gulivideo_orc
    lateral view explode(category) tmp as category_name) t1
   group by category_name
   order by hot
   limit 10;
   

   +----------------+-------------+
   | category_name  |     hot     |
   +----------------+-------------+
   | Music          | 2426199511  |
   | Entertainment  | 1644510629  |
   | Comedy         | 1603337065  |
   | Film           | 659449540   |
   |  Animation     | 659449540   |
   | Sports         | 647412772   |
   | Gadgets        | 505658305   |
   |  Games         | 505658305   |
   | People         | 425607955   |
   |  Blogs         | 425607955   |
   +----------------+-------------+
   10 rows selected (46.581 seconds)