hadoop - 通过子查询或联接配置Hive SQL平均表

Question

我有两个表:

电影

movieid int                                         
title   string  

等级

userid    int                                       
movieid   int                                       
rating    float                                     
timestamp string 

我想得到这样的结果

movietitle ratingavg
--------------------   
movie 1     4.8
movie 2      8

这是我的查询:

select avg(r.rating), m.title 
from ratings r 
join movies m on r.movieId = m.movieId;

还有这个

select m.title, (select avg(r.rating) from ratings r) 
from movies m 
join ratings r on r.movieId = m.movieId;

但我收到一个错误:

FAILED: SemanticException [Error 10025]: Line 1:21 Expression not in GROUP BY key 'title'

Answer 1

SELECT avg(r.rating)
    ,m.title
FROM ratings r
JOIN movies m ON r.movieId = m.movieId
GROUP BY m.title;