Hive报错Expression Not In Group By Key解决方法

Hive报错Expression Not In Group By Key解决方法

SQL例如以下会报错：

select sum(time) as time, roadCoding, upstreamOrDownstream  
from historicalroaddata where ...

报以下roadcoding、upstreamOrDownstream的错误

FAILED: SemanticException [Error 10025]: Line 1:12 expression not in GROUP BY key ‘roadcoding′

解决方法：使用collect_set()函数包围非group by字段collect_set(roadcoding)[0]

select sum(time) as time,collect_set(roadcoding)[0],  collect_set(upstreamOrDownstream)[0]
from historicalroaddata where...

问题原因：

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

1.Hive不允许直接访问非group by字段；
2.对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
3.使用数字下标，可以直接访问数组中的元素；

MySQL：

select dept.name,count(*) num from empt
join dept on empt.deptno=dept.deptno 
where empt.sal<=51
group by empt.deptno 
order by num desc limit 1

hiveSQL:

select collect_set(dept.dname)[0],count(*) num from empt
join dept on empt.deptno=dept.deptno 
where empt.sal<=51
group by empt.deptno 
order by num desc limit 1