Hive什么时候可以用别名?

举个例子,求各部门工资和总工资的比例
1 首先求各部门工资以及总工资

select deptno,
       sum(sal) over (distribute by deptno) `d_dal`,
       sum(sal) over() `total_sal`
from tb_emp;

2 求比例
如下写法是错误的,此时还不能用别名

select deptno,
       sum(sal) over (distribute by deptno) `d_sal`,
       sum(sal) over() `total_sal`,
       d_sal/total_sal
from tb_emp;

想要用别名,必须嵌套一层,如下是可以的,因为相当于子查询保存起来,后面就可以用了

with a as (select deptno,
                  sum(sal) over (distribute by deptno) `d_sal`,
                  sum(sal) over ()                     `total_sal`
           from tb_emp)
select deptno,
       d_sal,
       total_sal,
       round(d_sal / total_sal, 2) `per`
from a
;

+------+-----+---------+----+
|deptno|d_sal|total_sal|per |
+------+-----+---------+----+
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|10    |8750 |29025    |0.3 |
|10    |8750 |29025    |0.3 |
|10    |8750 |29025    |0.3 |
|NULL  |NULL |29025    |NULL|
+------+-----+---------+----+

如果不想嵌套,可以如下,就是不能用别名,但是这样的话 sum(sal) over()会不会计算两次,影响效率呢?

测试一下,如下sql用时30 s 279 ms,上面用别名的耗时 33 s 612 ms,可以看出并不影响效率

select deptno,
        sum(sal) over (distribute by deptno) `d_sal`,
        sum(sal) over() `total_sal`,
        round((sum(sal) over (distribute by deptno))/(sum(sal) over()),2) `per`
from tb_emp;
;
+------+-----+---------+----+
|deptno|d_sal|total_sal|per |
+------+-----+---------+----+
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|30    |9400 |29025    |0.32|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|20    |10875|29025    |0.37|
|10    |8750 |29025    |0.3 |
|10    |8750 |29025    |0.3 |
|10    |8750 |29025    |0.3 |
|NULL  |NULL |29025    |NULL|
+------+-----+---------+----+

总结

• 此种情形要么别名+嵌套,要么不嵌套,但是也不能用别名!