Hive 报错 Expression not in GROUP BY key

https://blog.csdn.net/chinamcafee/article/details/53407505

collect_set() 函数解决

 

报错代码：

SELECT u.sex ,m.moviename,AVG(r.rate) as avgScore,count(r.rate) as total
from t_user as u
join t_rating as r on r.userid = u.userid 
join t_movie  as m on m.movieid = r.movieid 
where u.sex = 'F'
GROUP BY m.moviename 
HAVING total > 50
ORDER BY avgScore DESC LIMIT 10;

Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'sex'

 

解决代码：

SELECT collect_set(u.sex)[0] as sex,m.moviename,AVG(r.rate) as avgScore,count(r.rate) as total
from t_user as u
join t_rating as r on r.userid = u.userid 
join t_movie  as m on m.movieid = r.movieid 
where u.sex = 'F'
GROUP BY m.moviename 
HAVING total > 50
ORDER BY avgScore DESC LIMIT 10;

 

SELECT ‘F’ as sex,m.moviename,AVG(r.rate) as avgScore,count(r.rate) as total
from t_user as u
join t_rating as r on r.userid = u.userid 
join t_movie  as m on m.movieid = r.movieid 
where u.sex = 'F'
GROUP BY m.moviename 
HAVING total > 50
ORDER BY avgScore DESC LIMIT 10;