比如,如下是会报错的,year这个别名此时还是不能用的
select deptno `dept`,
year(hiredate) `year`,
sum(sal)
from tb_emp
group by deptno,year;
org.apache.hadoop.hive.ql.parse.SemanticException:Line 5:16 Invalid
table alias or column reference ‘year’: (possible column names are:
empno, ename, job, mgr, hiredate, sal, comm, deptno)
如下这种不用别名是可以的,同时虽然出现了两次year(hiredate),但是并不会增加运行耗时
select deptno `dept`,
year(hiredate) `year`,
sum(sal)
from tb_emp
group by deptno, year(hiredate);
+----+----+----+
|dept|year|c2 |
+----+----+----+
|NULL|NULL|NULL|
|10 |1981|7450|
|10 |1982|1300|
|20 |1980|800 |
|20 |1981|5975|
|20 |1987|4100|
|30 |1981|9400|
+----+----+----+
原因
在hive中,group by的执行顺序在select之前,所以…
总结
- 要么嵌套要么不要使用列别名
参考
group by 和where 条件后面不能用刚设置的别名。_weixin_34245169的博客-CSDN博客
https://blog.csdn.net/weixin_34245169/article/details/93737105