linux - Hive 获取超过平均值 10000 的行

Question

我正在尝试使用 Hive 显示表中超出每个唯一工作角色 (ROLE) 的平均工资 (AnnualSalary) 超过 10000 的行。我尝试使用下面的代码，但不起作用，并在底部出现错误

hive> SELECT ROLE, AVG(AnnualSalary) From Salaries WHERE AnnualSalary > AVG(AnnualSalary) +10000   GROUP BY ROLE;

FAILED: SemanticException [Error 10128]: Line 1:66 Not yet supported place for UDAF 'AVG'

Answer 1

Having Clause

SELECT ROLE, AVG(AnnualSalary) 
FROM Salaries 
GROUP BY ROLE
HAVING AnnualSalary > (AVG(AnnualSalary) + 10000);