第11章综合案例2影评大数据分析
实验目的及要求
（1）现有电影、影评和用户信息3个数据文件，将对其进行大数据分析。
实验系统环境及版本
- Linux Ubuntu 20.04
- JDK1.8
- Hadoop3.1.0
- MySQL8.0.28
- Hive3.1.2
实验任务
- 评分次数最多的10部电影；
- 性别当中评分最高的10部电影；
- 一部电影各年龄段的平均影评；
- 评分最高的10部电影的平均评分；
- 好片最多年份的最好看电影Top10；
- 评分最高的10部Comedy类电影；
- 各种类型电影中评价最高的5部电影。
实验内容及步骤
创建一个数据仓库movie
hive> create database movie;
hive> use movie;
创建t_user表及导入数据
hive> create table t_user(userid bigint,sex string,age int,occupation string,zipcode string) row format delimited fields terminated by ‘\t’;
hive> load data local inpath ‘/opt/datas/user.txt’ into table t_user;
创建t_movie表及导入数据
hive> create table t_movie(movieid bigint,moviename string,movietype string) row format delimited fields terminated by ‘\t’;
hive> load data local inpath ‘/opt/datas/movie.txt’ into table t_movie;
创建t_ratings表及导入数据
hive> create table t_ratings(
> userid bigint,
> movieid bigint,
> rate double,
> times string)
> row format serde ‘org.apache.hadoop.hive.serde2.RegexSerDe’
> stored as textfile;
hive> load data local inpath “/opt/datas/ratings.dat” into table t_ratings;
基本信息查询
查询表t_user的记录总数：
hive> select count( *) from t_user;
查询表t_movie的记录总数：
hive> select count( *) from t_movie;
查询表t_rating的记录总数：
hive> select count( *) from t_ratings;
查看3个表文件的数据大小：
hdfs dfs -du -h /user/hive/warehouse/movie.db/t_movie
hdfs dfs -du -h /user/hive/warehouse/movie.db/t_user
hdfs dfs -du -h /user/hive/warehouse/movie.db/t_ratings
1.评分次数最多的10部电影
统计评分次数最多的10部电影，并给出评分次数（电影名、评分次数）。
按照电影名进行分组统计，求出每部电影的评分次数并按照评分次数降序排序，保存在表answer2中：
hive> create table answer2 as
> select a.moviename as moviename,count(a.moviename) as total from t_movie a join t_ratings b on a.movieid=b.movieid
> group by a.moviename order by total desc limit 10;
查询表answer2：
hive> select * from answer2;
2.性别当中评分最高的10部电影
统计男性、女性当中评分最高的10部电影（性别、电影名、影评分）。
（1）创建表answer3_F，保存女性当中评分最高的10部电影（性别、电影名、影评分），分组条件为评论次数大于或等于50次：
create table answer3_F as
select “F” as sex, c.moviename as name, avg(a.rate) as avgrate, count(c.moviename) as total
from t_ratings a join t_user b on a.userid=b.userid
join t_movie c on a.movieid=c.movieid
where b.sex=“F”
group by c.moviename
having total >= 50
order by avgrate desc
limit 10;
（2）查询表answer3_F：
select sex,name,round(avgrate,2),total from answer3_F;
（3）创建表answer3_M，保存男性当中评分最高的10部电影（性别、电影名、影评分），要求评论次数大于或等于50次：
create table answer3_M as
select “M” as sex, c.moviename as name, avg(a.rate) as avgrate, count(c.moviename) as total
from t_ratings a join t_user b on a.userid=b.userid
join t_movie c on a.movieid=c.movieid
where b.sex=“M”
group by c.moviename
having total >= 50
order by avgrate desc
limit 10;
（4）查询表answer3_M：
select sex,name,round(avgrate,2),total from answer3_M;
2.一部电影各年龄段的平均影评
统计movieid = 2116这部电影各年龄段的平均影评（年龄段、影评分）。
（1）对t_user和t_ratings表进行联合查询，用movieid=2116作为过滤条件，用年龄段作为分组条件，查询结果保存在表answer4中：
create table answer4 as
select a.age as age, avg(b.rate) as avgrate
from t_user a join t_ratings b on a.userid=b.userid
where b.movieid=2116
group by a.age;
（2）查询表answer4：
select age,round(avgrate,2) from answer4;
3.评分最高的10部电影的平均影评分
统计最喜欢看电影（影评次数最多）的那位女性评分最高的10部电影的平均影评分（观影者、电影名、影评分）。
（1）查询最喜欢看电影的那位女性，查询的字段分别为t_user.sex（性别）和count t_ratings.userid（观影次数）：
select a.userid, count(a.userid) as total
from t_ratings a join t_user b on a.userid = b.userid
where b.sex=“F”
group by a.userid
order by total desc
limit 10;
（2）根据上述（1）中查询的女性userid作为Where过滤条件，以看过的电影的影评分rate作为排序条件进行排序，统计出评分最高的10部电影，并将查询结果保存在表answer5_B中：
create table answer5_B as
select a.movieid as movieid, a.rate as rate
from t_ratings a
where a.userid=1150
order by rate desc
limit 10;
（3）查询表answer5_B：
select * from answer5_B;
（4）统计上述（3）中10部电影的平均影评分，需要查询的字段分别为answer5_B.movieid（电影的ID）和t_ratings.rate（影评分），并将查询结果保存在表answer5_C中：
create table answer5_C as
select b.movieid as movieid, c.moviename as moviename, avg(b.rate) as avgrate
from answer5_B a join t_ratings b on a.movieid=b.movieid
join t_movie c on b.movieid=c.movieid
group by b.movieid,c.moviename;
（5）查询表answer5_C：
select movieid,moviename,round(avgrate,2) from answer5_C;
4.好片最多年份的最好看电影Top10
年份的最好看电影Top10。
（1）将t_rating和t_movie表进行联合查询，截取电影名中的上映年份，并将查询结果保存至表answer6_A：
create table answer6_A as select
a.movieid as movieid, a.moviename as moviename,
substr(a.moviename,-5,4) as years, avg(b.rate) as avgrate
from t_movie a join t_ratings b on a.movieid=b.movieid
group by a.movieid, a.moviename;
（2）查询表answer6_A：
select * from answer6_A;
（3）按照年份将answer6_A分组，评分≥4.0作为过滤条件，按照count(years)作为排序条件进行查询：
select years, count(years) as total
from answer6_A a
where avgrate >= 4.0
group by years
order by total desc
limit 10;
（4）按照years=1998作为Where过滤条件，按照评分作为排序条件进行查询，并保存至表answer6_C：
create table answer6_C as
select a.moviename as name, a.avgrate as rate
from answer6_A a
where a.years=1998
order by rate desc
limit 10;
（5）查询表answer6_C：
select name,round(rate,2) from answer6_C;
5.评分最高的10部Comedy类电影
统计1997年上映的电影中评分最高的10部Comedy类电影。
（1）将answer6_A表和t_movie表进行联合查询，保存至表answer7_A：
create table answer7_A as
select b.movieid as id, b.moviename as name, b.years as years, b.avgrate as rate, a.movietype as type
from t_movie a join answer6_A b on a.movieid=b.movieid;
（2）表answer7_A按照电影类型中是否包含Comedy和按照评分≥4.0作为Where过滤条件，按照评分作为排序条件进行查询，将结果保存到表answer7_B中。其中，instr函数返回字符串str中子字符串substr第一次出现的位置，在SQL中第一字符的位置是1，如果str不含substr，则返回0。lcase函数把字段的值转换为小写。
create table answer7_B as
select t.id as id, t.name as name, t.rate as rate
from answer7_A t
where t.years=1997 and instr(lcase(t.type),‘comedy’) >0
order by rate desc
limit 10;
（3）查询表answer7_B：
select id,name,round(rate,2) from answer7_B;
6.各种类型电影中评价最高的5部电影
统计各种类型电影中评价最高的5部电影（类型、电影名、平均影评分）。
（1）将表answer7_A中的type字段进行裂变，将结果保存到表answer8_A中。其中，Lateral View用于和Split、Explode等函数一起使用，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合。Lateral View首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，Lateral View再把结果组合，产生一个支持别名表的虚拟表。
create table answer8_A as
select a.id as id, a.name as name, a.years as years, a.rate as rate, tv.type as type
from answer7_A a
lateral view explode(split(a.type,“\\|”)) tv as type;
（2）查询表answer8_A：
select * from answer8_A limit 10 ;
（3）按照type分组，添加一列记录每组的顺序，将结果保存到表answer8_B中：
create table answer8_B as
select id,name,years,rate,type,row_number() over(distribute by type sort by rate desc ) as num
from answer8_A;
（4）查询表answer8_B：
select * from answer8_B limit 10;
（5）从表answer8_B中取出num列序号≤5：
select a.id, a.name, a.years, round(a.rate,2), a.type, a.num from answer8_B a where a.num <= 5 limit 10;
