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_EMPint类型,DISTINCT_EMPfloat。伤了我的头。
任何帮助表示赞赏。

最佳答案

如果用定义别名的表达式替换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/

10-16 21:27