谷粒影音Hive需求分析

@HIve_guiliVideo

1. 统计视频观看数Top10
   select videoId, views from gulivideo_orc order by views desc limit 10;
2. 统计视频类别热度Top10

SELECT   hot, 
         total_view 
FROM     ( 
                  SELECT   hot, 
                           Count(*) total_view 
                  FROM     ( 
                                  SELECT views, 
                                         hot 
                                  FROM   gulivideo_orc lateral view explode (category) category_t as hot)t1
                  GROUP BY hot)t2 
ORDER BY total_view DESC limit 10;

3. 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

SELECT
    category_name,
    count( category_name ) AS top
FROM
    (
    SELECT
        category_name,
        views 
    FROM
        ( SELECT videoId, views, category FROM gulivideo_orc ORDER BY views DESC LIMIT 20 ) t1 lateral VIEW explode ( category ) category_t AS category_name 
    ) t2 
GROUP BY
    category_name 
ORDER BY
    top  DESC;

4. 统计视频观看数Top50所关联视频的所属类别Rank

SELECT
    category_name,
    hot 
FROM
    (
    SELECT
        category_name,
        count( category_name ) AS hot 
    FROM
        (
        SELECT
            category_name 
        FROM
            (
            SELECT
                videoId,
                category 
            FROM
                (
                SELECT
                    views,
                    relatedId_name 
                FROM
                    ( SELECT views, relatedId FROM gulivideo_orc ORDER BY views DESC LIMIT 50 ) t1 lateral VIEW explode ( relatedId ) relatedId_t AS relatedId_name 
                ) t2
                JOIN gulivideo_orc t3 ON t2.relatedId_name = t3.videoId
            ) t4 lateral VIEW explode ( category ) category_t AS category_name 
        ) t5 
    GROUP BY
        category_name 
    ) t6 
ORDER BY
    hot DESC;

5. 统计每个类别中的视频热度Top10，以Music为例

SELECT
    videoId,
    views,
    category_name 
FROM
    ( SELECT category_name, views, videoId FROM gulivideo_orc lateral VIEW explode ( category ) t_catetory AS category_name ) t1 
WHERE
    category_name = 'Music' 
ORDER BY
    views DESC
LIMIT 10;

6. 统计每个类别中视频流量Top10，以Music为例

SELECT
    videoId,
    ratings,
    category_name 
FROM
    ( SELECT category_name, ratings, videoId FROM gulivideo_orc lateral VIEW explode ( category ) t_catetory AS category_name ) t1 
WHERE
    category_name = 'Music' 
ORDER BY
    ratings DESC
LIMIT 10;

7. 统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频

SELECT
    t1.uploader,
    t2.views,
    t2.videoId 
FROM
    ( SELECT uploader, videos FROM gulivideo_user_orc ORDER BY videos DESC LIMIT 10 ) t1
    JOIN gulivideo_orc t2 ON t1.uploader = t2.uploader 
ORDER BY
    views DESC ;

8. 统计每个类别视频观看数Top10

SELECT
category_name,
views,
num 
FROM
    (
    SELECT
        category_name,
        views,ROW_NUMBER () over ( PARTITION BY category_name ORDER BY views DESC ) num 
    FROM
        gulivideo_orc lateral VIEW explode ( category ) category_t AS category_name 
    ) t1 
WHERE
    num <= 10;