hive笔记 —— 解决where子句不能使用子查询的前提下，比较子查询结果（等于，大于，平均值）

解决 where 字段 >（select 字段 from 表名）的情况

拿网上的题目来举例子（想把题目出处贴出来的，发现网上各种网站都有这个题目，不确定来源是哪里）

员工信息表emp：
字段：员工id,员工名字,工作岗位,部门经理,受雇日期,薪水,奖金,部门编号
英文名：EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,BONUS,DEPTNO

表格创建好了，数据也加载好了，现在题目需求来了

需求：列出薪金比“SMITH”多的所有员工。

我一开始写sql语句写习惯了，下意识的就写的 where sal = (select sal from emp where)这种形式的sql语句。结果发现 hive不支持在where子句中使用子查询

所以如果想在hive中，需要用到 join on 表连接的形式来完成字段值的比较
但这里如果像下面这么写的话：

select e1.* from emp as e1 
left join (select empno,sal from emp where ename='SMITH') as e2 
on e1.empno = e2.empno 
where e1.sal>e2.sal;

就会发现虽然执行成功，但是结果为空，什么都不会输出
分析一下这个sql语句，e2表里面只返回了一条语句 SMITH的empno和sal，而e1表是emp表的全部数据。此时两表进行匹配，只有SMITH的那一行能匹配成功，剩下的行都没有匹配成功，虽然因为left join的原因，左表会打印全部，但效果就跟这张图一样

红线右边的就是最后一列，加上去 e2.sal 的值，发现只有SMITH的那一行是有值的800，其余行因为匹配失败，都为null。这样肯定没办法完成where子句里面的 e1.sal>e2.sal;

唯一匹配成功的一行，e1.sal 是等于 e2.sal 的，因此输出结果为空

这里正确的语句是

select e1.ename,e1.sal from
(select ename,sal,1 as eid1 from emp) as e1 
left join (select 1 as eid2,sal from emp where ename='SMITH') as e2
on e1.eid1 = e2.eid2 
where e1.sal > e2.sal;

结果为

再来分析一下这里的语句，就会发现，之前是因为 e1表和e2表的empno的值对应不上，所以只匹配了一行。那么现在不用 empnp作为key值连接两张表了，自己手动给两张表加上一个新的列，新的列的值都为1，然后两张表分别给这一列起一个别名，eid1 和 eid2，现在来看一下，这样连接的表长什么样子

ok，这样就可以完成 sal值的比较了

解决 where 字段 =（select 字段 from 表名）的情况

补正：hive在新的版本，也就是我现在在用的版本，已经支持了in 和 not in的使用

增加一张新表

求取在 New York上班的员工姓名

select ename from emp where deptno in ( select deptno from dept t1 where t1.deptaddr='NEW YORK');

注意： where in 子查询中的语句，里面的 from 语句的表后面必须跟上一个别名，然后用别名.字段名的形式完成筛选

像上面就是给 dept 起了一个别名 t1 ，然后 t1.deptaddr

成功得出结果

由于现版本已经可以使用 in 和 not in，所以下面的情况是在假设不能用的旧版本，看一下即可

原先在mysql中可以这样写

select * from emp as e1 where e1.sal in (select sal from emp where sal>2000) as e2;

到了hive里面就歇菜了，会报 ParseException 的错误

那么应该怎么写来替换 in 的写法呢，答案是使用 left semi join

select * from emp as e1 
left semi join emp as e2 
on (e1.empno=e2.empno and e2.sal>2000);

结果为

and子句可以有多个，

select * from emp as e1 
left semi join emp as e2 
on (e1.empno=e2.empno 
    and e2.sal>2000 
    and e2.ename='SMITH');

解决 where 字段 =（select avg(字段) from 表名 group by 字段）的情况

我是根据这篇博客学习后，才知道还有这种解决方法
hive sql系列（一）——找出所有科目成绩都大于某一学科平均成绩的学生

建议去源博客地址查看，我这里只是简单的写一下

数据如上图所示
字段含义从左到右依次为 uid学号，subject_id科目号，socre成绩

现在的需求是找出所有科目成绩都大于某一学科平均成绩的学生
因此思路应该是

1. 求处某一学科的平均成绩

select *,cast(avg(score) over(partition by subject_id) as int) as avg 
from scoretest;

列的含义依次是uid，subject_id，score，每个学科的平均值

这里的开窗函数 avg(score) over(partition by subject_id) 用来求每个学科的成绩平均值，这里外面还套上了一层cast（）函数，是要保证最后求出来的平均值是int类型。
如果不加上的话，结果长这个样子

2. 给数据的每一行的后面都加上学科平均值之后，下一步就应该是拿每个学生的成绩与学科的平均值进行比较

select t1.*,if(t1.score > t1.avg,0,1) as flag 
from (select *,avg(score) over(partition by subject_id) as avg from scoretest) as t1;

从第一步得出的表中提取 t1.* 全部列，然后还要再加上一个新的列，列名为flag

这个列的作用是通过hive 的 if 函数来判断

if 函数 ：语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
当条件 testCondition 为 TRUE 时，返回 valueTrue；否则返回 valueFalseOrNull

因此当t1.score > t1.avg，也就是学生的成绩大于学科平均成绩时，返回的结果为0

3. 按照 flag 来找到最后的结果，即学生的全部成绩都大于学科平均成绩的学生

select t2.uid from (select t1.*,if(t1.score > t1.avg,0,1) as flag 
from (select *,avg(score) over(partition by subject_id) as avg from scoretest) as t1) as t2
group by t2.uid
having sum(t2.flag)=0;

最后的 sum(t2.flag)=0; 如果某个学生的每一科成绩都大于学科平均成绩，那么，按照 t2.uid分组后，一个uid对应一个学生，一个组里面有一个学生的三个flag比较结果，如果sum求和后结果为0，说明flag的值都为0，也就是每一科的成绩都大于学科平均成绩。

最后的结果为