Hive练习之影评案例分析（二）

（3）求movieid = 2116这部电影各年龄段的平均影评（年龄段，影评分）

分析：t_user和t_rating表进行联合查询，用movieid=2116作为过滤条件，用年龄段作为分组条件

create table question3 as 
select a.age as age, avg(b.rate) as avgrate 
from t_user a join t_rating b on a.userid=b.userid 
where b.movieid=2116 
group by a.age;

结果：

（4）求最喜欢看电影（影评次数最多）的那位女性评最高分的10部电影的平均影评分（观影者，电影名，影评分）

分析：

     1）首先求出最喜欢看电影的女性

select a.userid, count(a.userid) as total 
from t_rating a join t_user b on a.userid = b.userid 
where b.sex="F" 
group by a.userid 
order by total desc 
limit 1;

     结果：

2）求出id为1150用户评分最高的10部电影，以看过的电影的影评分rate作为排序条件进行排序，求出评分最高的10部电影。

create table question4_1 as 
select a.movieid as movieid, a.rate as rate  
from t_rating a 
where a.userid=1150 
order by rate desc 
limit 10;

结果：

  3）求出2）中10部电影的平均影评分

create table question4_2 as 
select b.movieid as movieid, c.moviename as moviename, avg(b.rate) as avgrate 
from question4_1 a 
join t_rating b on a.movieid=b.movieid 
join t_movie c on b.movieid=c.movieid 
group by b.movieid,c.moviename;

结果：

（5）求好片（评分>=4.0）最多的那个年份的最好看的10部电影

分析：

   1）需要将t_rating和t_movie表进行联合查询，将电影名当中的上映年份截取出来，保存到临时表question5_1中

create table question5_1 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_rating b on a.movieid=b.movieid 
group by a.movieid, a.moviename;

结果：

2）从question5_1按照年份进行分组条件，按照评分>=4.0作为where过滤条件，按照count(years)作为排序条件进行查询

select years, count(years) as total 
from question5_1 a 
where avgrate >= 4.0 
group by years 
order by total desc 
limit 1;

结果：

3）从question5_1按照years=1998作为where过滤条件，按照评分作为排序条件进行查询

create table question5_3 as
select a.moviename as name, a.avgrate as rate 
from question5_1 a 
where a.years=1998 
order by rate desc 
limit 10;

结果：

（6）求1997年上映的电影中，评分最高的10部Comedy类电影

分析：因为需要年份，所有先将第5题中求出question5_1表和t_movie表进行联合查询，生成临时表question6_1;然后从question6_1按照电影类型中是否包含Comedy和按上映年份作为where过滤条件，按照评分作为排序条件进行查询。

create table question6_1 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 question5_1 b on a.movieid=b.movieid;

create table question6_2 as 
select t.id as id, t.name as name, t.rate as rate 
from question6_1 t 
where t.years=1997 and instr(lcase(t.type),'comedy') >0 
order by rate desc
limit 10;

（7）该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）

分析：需要电影类型，所有需要将question6_1中的type字段进行裂变，将结果保存到question7_1中;然后按照Type进行分组求TopN（需要使用行号来实现）。

create table question7_1 as 
select a.id as id, a.name as name, a.years as years, a.rate as rate, tv.type as type 
from question6_1 a 
lateral view explode(split(a.type,"\\|")) tv as type;

create table question7_2 as 
select id,name,years,rate,type,row_number() over(distribute by type sort by rate desc ) as num
from question7_1;

（8）各年评分最高的电影类型（年份，类型，影评分）

分析：需要按照电影类型和上映年份进行分组，按照影评分进行排序，将结果保存到question8_1中；然后按照years分组求TopN（与上一题类似）。

create table question8_1 as 
select a.years as years, a.type as type, avg(a.rate) as rate 
from question7_1 a 
group by a.years,a.type 
order by rate desc;

create table question8_2 as 
select years,type,rate,row_number() over (distribute by years sort by rate desc) as num
from question8_1;

（9）每个地区最高评分的电影名，把结果存入HDFS（地区，电影名，影评分）

分析：首先需要把三张表进行联合查询，取出电影id、电影名称、影评分、地区，将结果保存到question9_1表中；然后一九分组求TopN。

create table question9_1 as
select c.movieid, c.moviename, avg(b.rate) as avgrate, a.zipcode
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;

create table question9_2 as
select movieid,moviename,avgrate,zipcode, row_number() over (distribute by zipcode sort by avgrate desc) as num 
from question9_1; 

insert overwrite directory "/movie/" select * from question9_2 where num=1;