hive -e "select a.EMP_ID,
count(distinct c.SERIAL_NBR) as NUM_CURRENT_EMP,
count(distinct c.SERIAL_NBR)/count(distinct a.SERIAL_NBR) as DISTINCT_EMP
from ORDERS_COMBINED_EMPLOYEES as a
inner join ORDERS_EMPLOYEE_STATS as b
on a.CPP_ID = b.CPP_ID
left join ( select SERIAL_NBR, MIN(TRAN_DT) as TRAN_DT
from EMP_TXNS
group by SERIAL_NBR
) c
on c.SERIAL_NBR = a.SERIAL_NBR
where c.TRAN_DT > a.LAST_TXN_DT
group by a.EMP_ID
having (
(NUM_CURRENT_EMP >= 25 and DISTINCT_EMP > 0.01)
) ; " > EMPLOYEE_ORDERS.txt
收到错误消息,
"FAILED: SemanticException [Error 10025]: Line 15:31 Expression not in GROUP BY key '0.01'".
当我在HAVING
子句中仅以一个条件运行与NUM_CURRENT_EMP >= 25
相同的查询时,查询运行良好,没有任何问题。在我要插入结果的表中,NUM_CURRENT_EMP
是int
类型,DISTINCT_EMP
是float
。伤了我的头。任何帮助表示赞赏。
最佳答案
如果用定义别名的表达式替换having
中的别名,会发生什么?
having count(distinct c.SERIAL_NBR) >= 25 and
count(distinct c.SERIAL_NBR)/count(distinct a.SERIAL_NBR) > 0.01
关于sql - HIVE:GROUP BY key 错误,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29424673/