Hive中group by 提示Expression Not In Group By Key的解决办法

现象

hive中group by的时候

select stu.s_name,sum(sc.s_score) from score sc left join student stu on stu.s_id = sc.s_id group by sc.s_id;

会提示：

Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 's_name' (state=42000,code=10025)

问题原因

在 Group by 子句中，Select 查询的列，要么需要是 Group by 中的列，要么得是用聚合函数（比如 sum、count 等）加工过的列。不支持直接引用非 Group by 的列。这一点和 MySQL 有所区别。

解决方法

1、不关心s_name的值，且有多个s_name，那么语句改成

select collect_set(stu.s_name)[0],sum(sc.s_score) from score sc left join student stu on stu.s_id = sc.s_id group by sc.s_id;

2、如果每个s_name的值不同且关心s_name的值，那么可以改成

select stu.s_name,sum(sc.s_score) from score sc left join student stu on stu.s_id = sc.s_id group by sc.s_id,stu.s_name;