统计视频观看数Top10 select videoId, views from gulivideo_orc order by views desc limit 10;
统计视频类别热度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;
统计出视频观看数最高的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;
统计视频观看数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;
统计每个类别中的视频热度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;
统计每个类别中视频流量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;
统计上传视频最多的用户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 ;
统计每个类别视频观看数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;