记录下刷的几个HQL题目

4.视频观看数TOP10

/*
select
	video_id,
	views
from video 
order by sumView desc
limit 10
PS
	如果是海量数据的情况下，可以通过sort  by  然后取出每个局部分区的第一

5.视频类别热度TOP10

类别下的总视频数
一个视频所属多种类别
/*
select
	count(t.c) sums
from(
    select
        c
    from video
    lateral view explode(category) temp as c
)t
group by t.c
order by sums desc
limit 10

6.视频观看数最高的前20所属类别下所包含TOP20视频的个数

/* 有点绕

select
	cate,
	count(video_id)
from(
    select
        video_id,
        cate
    from(
        select
            video_id,
            category,
            vides    			PS:子查询中,order by 后面的字段必须出现在select 里面,否则报错
        from video
        order by views desc
        limit 20
    )t lateral view explode(category) temp as cate
)tt
group by cate

7.统计视频观看数TOP50所属类别的排序

/*

select
	cate,
	count(t.video_id) sums,
	rank() over(order by sums desc)
from(
    select
        video_id,
        category
    from video
    order by views desc
    limit 50
)t lateral view explode(category)  tmep as cate
group by cate
order by count(t.video_id) desc

8.统计每个类别中视频热度TOP10,以Music为例

/*
select
	video_id,
	cate
from video lateral view explode(category) temp as cate
where cate='Music'
order by views desc
limit 10