hadoop - 运行 Hive 查询时获取 ParseException

Question

我试图找出工资低于平均工资的员 worker 数。

我是 hive 的新手并且有点挣扎，有人可以解释我的陈述有什么问题并帮助我吗？

我的陈述-

SELECT COUNT(*) FROM(SELECT wage, AVG(wage) AS avgWage FROM emp_wages) WHERE wage < avgWage;

错误-

ParseException line 1:82 cannot recognize input near 'where' 'wage' '<' in subquery source


感谢任何帮助!

Answer 1

语法错误。派生表应该有别名。

SELECT COUNT(*) 
FROM (SELECT wage, AVG(wage) AS avgWage FROM emp_wages group by wage)  t --alias needed here
WHERE wage < avgWage;

明智的查询，它需要改变。

select count(*)
from (SELECT wage, AVG(wage) over() AS avgWage 
      FROM emp_wages
     ) t
where wage < avgWage