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