文章目录
原始数据:
(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,cate
from (
select videoid,views,category
from video_orc
order by views desc
)t1
lateral view explode(t1.category) t2 as cate
) t3
group by cate