实战"/>
hive:电影数据实战
文章目录
- (1)视频观看数top10
- (2)视频数top10的种类
- (3)观看数top20视频所属类别,以及这些类别中每个类别包含Top20视频的个数
- (4)统计视频观看数Top50所关联视频的所属类别排序
- (5)每类视频观看数top10的视频
- (6)上传视频最多的top10用户,以及他们各自views top20的视频
- (8)上传视频最多的top10用户,以及他们各自上传的视频中有哪些是views top20
原始数据:
(1)视频观看数top10
select videoid,views from video_orc
order by views desc
limit 10;
(2)视频数top10的种类
- 炸开每个视频的category,并与原始videoid连接
--t1
select v.videoid,cate_view.cate
from video_orc v
lateral view
explode(category) cate_view as cate;
(下图只显示了部分结果)
- 统计每种类别下的视频个数
select cate,count(videoid) as n
from t1
group by cate
order by n
limit 10;
- 完整版:
select cate,count(*) total
from (
select v.videoid,cate_view.cate
from video_orc v
lateral view
explode(category) cate_view as cate
) tbl
group by tbl.cate
order by total desc
limit 10;
(3)观看数top20视频所属类别,以及这些类别中每个类别包含Top20视频的个数
- 观看数前20的视频
--t1
select videoid,views,category
from video_orc
order by views desc
limit 20;
- 炸开category
--t2
select videoid,views,cate
from t1
lateral view explode(t1.category) cate_view as cate;
- 完整版:
select videoid,cate
from (
select videoid,views,category
from video_orc
order by views desc
limit 20
) t1
lateral view explode(t1.category) cate_view as cate;
(下图只显示了部分结果)
- 按类别分类,并统计每类中有多少个视频
select cate,count(videoid) n
from t2
group by cate
order by n desc;
- 完整版:
select cate,count(*) as n
from( select videoid,views,catefrom (select vi
更多推荐
hive:电影数据实战
发布评论