Hive SQL练习之影评案例

目录
案例说明
数据下载
解析
o1、正确建表，导入数据（三张表，三份数据），并验证是否正确
o2、求被评分次数最多的10部电影，并给出评分次数（电影名，评分次数）
o3、分别求男性，女性当中评分最高的10部电影（性别，电影名，影评分）
o4、求movieid = 2116这部电影各年龄段（因为年龄就只有7个，就按这个7个分就好了）的平均影评（年龄段，影评分）
o5、求最喜欢看电影（影评次数最多）的那位女性评最高分的10部电影的平均影评分（观影者，电影名，影评分）
o6、求好片（评分>=4.0）最多的那个年份的最好看的10部电影
o7、求1997年上映的电影中，评分最高的10部Comedy类电影
o8、该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）
o9、各年评分最高的电影类型（年份，类型，影评分）
o10、每个地区最高评分的电影名，把结果存入HDFS（地区，电影名，影评分）
 
正文
回到顶部
案例说明
现有如此三份数据：
1、users.dat 数据格式为： 2::M::56::16::70072，
共有6040条数据
对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String
对应字段中文解释：用户id，性别，年龄，职业，邮政编码
2、movies.dat 数据格式为： 2::Jumanji (1995)::Adventure|Children’s|Fantasy，
共有3883条数据
对应字段为：MovieID BigInt, Title String, Genres String
对应字段中文解释：电影ID，电影名字，电影类型
3、ratings.dat 数据格式为： 1::1193::5::978300760，
共有1000209条数据
对应字段为：UserID BigInt, MovieID BigInt, Rating Double, Timestamped String
对应字段中文解释：用户ID，电影ID，评分，评分时间戳
题目要求
　　数据要求：
　　　　（1）写shell脚本清洗数据。（hive不支持解析多字节的分隔符，也就是说hive只能解析’:’, 不支持解析’::’，所以用普通方式建表来使用是行不通的，要求对数据做一次简单清洗）
　　　　（2）使用Hive能解析的方式进行
　　Hive要求：
　　　　（1）正确建表，导入数据（三张表，三份数据），并验证是否正确
　　　　（2）求被评分次数最多的10部电影，并给出评分次数（电影名，评分次数）
　　　　（3）分别求男性，女性当中评分最高的10部电影（性别，电影名，影评分）
　　　　（4）求movieid = 2116这部电影各年龄段（因为年龄就只有7个，就按这个7个分就好了）的平均影评（年龄段，影评分）
　　　　（5）求最喜欢看电影（影评次数最多）的那位女性评最高分的10部电影的平均影评分（观影者，电影名，影评分）
　　　　（6）求好片（评分>=4.0）最多的那个年份的最好看的10部电影
　　　　（7）求1997年上映的电影中，评分最高的10部Comedy类电影
　　　　（8）该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）
　　　　（9）各年评分最高的电影类型（年份，类型，影评分）
　　　　（10）每个地区最高评分的电影名，把结果存入HDFS（地区，电影名，影评分）

数据要求：
（1）写shell脚本清洗数据。（hive不支持解析多字节的分隔符，也就是说hive只能解析’:’, 不支持解析’::’，所以用普通方式建表来使用是行不通的，要求对数据做一次简单清洗）
（2）使用Hive能解析的方式进行
Hive要求：
（1）正确建表，导入数据（三张表，三份数据），并验证是否正确

（2）求被评分次数最多的10部电影，并给出评分次数（电影名，评分次数）
select
count(*) cout,title
from ratings a join movies b on a.movieid=b.movieid
group by a.movieid ,b.title
order by cout desc limit 10;
将ratings，movies 表连接，按电影id分组，并以每组的个数（每个电影出现的次数），从大到小排序（从小到大的倒序），取前十条。
（3）分别求男性，女性当中评分最高的10部电影（性别，电影名，影评分）
male:
select
c.Gender,b.title,avg(a.rating) avg_movie
from ratings a join movies b on a.movieid=b.movieid join users c on a.userid=c.userid
where c.Gender=“M” group by b.movieid,b.title,c.Gender order by avg_movie desc limit 10;

将三表连接，先where筛选出合格的行（男性数据），然后按movieid，title，Gender 分组，再按聚合函数avg得到的平均分进行从小至大排序，slelect选择性别，电影名，影评分并取别名。

select
c.Gender,b.title,avg(a.rating) avga_movie
from ratings a join movies b on a.movieid=b.movieid join users c on a.userid=c.userid
where c.Gender=“F” group by b.movieid,b.title,c.Gender
order by avga_movie desc limit 10;
与上面同理，将where筛选语句中的M换成F即可
（4）求movieid = 2116这部电影各年龄段（因为年龄就只有7个，就按这个7个分就好了）的平均影评（年龄段，影评分）
select
b.Age,avg(a.rating) avg
from ratings a join users b on a.userid=b.userid group by b.Age;
连接两表（用户表和影评表），按同年龄段分组，选择年龄段，影评分
（5）求最喜欢看电影（影评次数最多）的那位女性评最高分的10部电影的平均影评分（观影者，电影名，影评分）
先求最喜欢看电影的那位女性，再求10部电影的平均影评即可。

（6）求好片（评分>=4.0）最多的那个年份的最好看的10部电影
select b.Title from ratings a join movies b on a.movieid=b.movieid
where substr(b.Title,-5,4) year=
(select substr(b.Title,-5,4) year from ratings r where Rating>=4 group by substr(b.Title,-5,4),order by avg(a.Rating) desc limit 1)
order by a.Rating desc limit 10;
先将好片最多的年份查询出来，（嵌套子查询），然后查询该年份评分最高的十部电影即可。

（7）求1997年上映的电影中，评分最高的10部Comedy类电影
select b.Title from ratings a join movies b on a.movieid=b.movieid where substr(b.Title,-5,4)=’1997’ and a.Genres like’%Comedy%’ order by a.Rating desc limit 10;
直接取1997年的Comedy的元组（行），再按分数排序即可。
（8）该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）
select * from (select
c.title title,c.Genres,c.rating,row_number() over(distribute by c.Genres sort by c.rating desc) index
from (select a.title title,avg(b.rating) rating,a.movieid,a.Genres
from (select
movieid,title,leixing.lx Genres
from movies lateral view explode(split(Genres,"|"))leixing as lx) a join ratings b on a.movieid=b.movieid
group by a.Genres,a.movieid,a.title) c) d where d.index<=5;
（9）各年评分最高的电影类型（年份，类型，影评分）
select substr(b.Title,-5,4), b.Title a.Genres b.Ratings from ratings a join movies b on a.movieid=b.movieid where substr(b.Title,-5,4) in(
select max(b.Rating),year from movies b group by substr(b.Title,-5,4))
and a.Genres in(select year from movies b group by substr(b.Title,-5,4) year)
直先用子查询查出来所有年份和对应的最高影评分，然后外层查询在where 用in 筛选出需要的对应两个字段（年份，影评分），然后select选择需要的字段即可。

（10）每个地区最高评分的电影名，把结果存入HDFS（地区，电影名，影评分）
select * from (select d.Zipcode Zipcode,d.MovieID MovieID,d.Title Title,d.avg_rate avg_rate,row_number() over(distribute by d.Zipcode sort by d.avg_rate desc) no
from (select a.zipcode Zipcode,c.movieid MovieID,c.moviename Title,avg(b.rate) avg_rate from t_user a join t_rating b on a.userid=b.userid join t_movie c on b.movieid=c.movieid group by a.zipcode,c.movieid,c.moviename) d )e where e.no=1;