大数据训练营一期 0801 作业

题目一

sql

select u.Age, avg(r.Rate) from (select * from `hive_sql_test1`.`t_rating` where movieid = 2116) r join `hive_sql_test1`.`t_user` u on (r.userid = u.userid)

group by u.Age

题目二

sql

select m.moviename, qualifiedMovies.avgrate, qualifiedMovies.totalRateCount, qualifiedMovies.totalRateSum from

(

select movieid, avgrate, totalRateSum, totalRateCount from (

select t1.movieid, t1.avgrate, t1.totalRateSum, t1.totalRateCount from (

select r.movieid, avg(r.rate) as avgrate, count(r.rate) as totalRateCount, sum(r.rate) as totalRateSum from

`hive_sql_test1`.`t_rating` r join `hive_sql_test1`.`t_user` u on r.userid = u.userid and u.sex = 'M'

group by r.movieid having count(r.rate) > 50

) t1 sort by t1.totalRateSum desc limit 10) t2

) qualifiedMovies

join `hive_sql_test1`.`t_movie` m on qualifiedMovies.movieid = m.movieid