hive练习2

练习：
一：将下列数据加载hive表。

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

create table emp(
    EMPNO int
    ,ENAME string
    ,JOB string
    ,MGR int
    ,HIREDATE string
    ,SAL int
    ,BONUS int
    ,DEPTNO int
) 
row format delimited
fields terminated by ',';




7369,SMITH,CLERK,7902,1980-12-17,800,null,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,null,20,
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10
7788,SCOTT,ANALYST,7566,1987-04-19,3000,null,20
7839,KING,PRESIDENT,null,1981-11-17,5000,null,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23,1100,null,20
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-03,3000,null,20
7934,MILLER,CLERK,7782,1982-01-23,1300,null,10


1600 1250 2850 1500 950
部门信息表dept：
字段：部门编号,部门名称,部门地点
英文名：DEPTNO,DEPTNAME,DEPTADDR

create table dept(
    DEPTNO int
    ,DEPTNAME string
    ,DEPTADDR string
) 
row format delimited
fields terminated by ',';


10,ACCOUNTING,NEW YORK
10,ACCOUNTING,shanghai
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON


二：使用HQL完成下面需求：
1． 列出至少有一个员工的所有部门。 

2． 列出薪金比“SMITH”多的所有员工。 

3． 列出所有员工的姓名及其直接上级的姓名。 

4． 列出受雇日期早于其直接上级的所有员工。 

5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 

6． 列出所有“CLERK”（办事员）的姓名及其部门名称。

7． 列出最低薪金大于1500的各种工作。

8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

9． 列出薪金高于公司平均薪金的所有员工。 

10．列出与“SCOTT”从事相同工作的所有员工。

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

14．列出所有员工的姓名、部门名称和工资。 

15．列出所有部门的详细信息和部门人数。 

16．列出各种工作的最低工资。 

17．列出各个部门的MANAGER（经理）的最低薪金。

18．列出所有员工的年工资,按年薪从低到高排序。

19. 列出每个部门薪水前两名最高的人员名称以及薪水。

20. 列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天。

1． 列出至少有一个员工的所有部门。 

-- 先求得每个部门的员工数
select  DEPTNO
        ,count(EMPNO) as emp_cnt
from emp
group by DEPTNO

第一种方式：用having去筛选至少有一个员工
select  t1.deptno
        ,t1.DEPTNAME
        ,t1.DEPTADDR
        ,t2.emp_cnt
from dept t1
left join(
    select  DEPTNO
            ,count(EMPNO) as emp_cnt
    from emp
    group by DEPTNO
    having emp_cnt>=1
) t2 on t1.deptno = t2.deptno;

第二种方式：使用where（子查询）
select  t1.deptno
        ,t1.DEPTNAME
        ,t1.DEPTADDR
        ,tt2.emp_cnt
from dept t1
left join(
    select  t2.DEPTNO
            ,t2.emp_cnt
    from(
        select  DEPTNO
                ,count(EMPNO) as emp_cnt
        from emp
        group by DEPTNO
    ) t2 where t2.emp_cnt>=1
) tt2 on t1.deptno = tt2.deptno;

2． 列出薪金比“SMITH”多的所有员工。 
# 在MySQL中支持，但在Hive中where条件不支持（不等式）子查询
select  EMPNO
        ,ENAME
        ,SAL
from emp t1 
where t1.SAL > (select  SAL from emp where ENAME = 'SMITH') t2


select  1 as tmp_id
        ,SAL 
from emp where ENAME = 'SMITH'

select  t1.empno
        ,t1.ENAME
        ,t1.SAL
from(
    select  1 as tmp_id
            ,EMPNO
            ,ENAME
            ,SAL
    from emp
    where ENAME!='SMITH'
) t1 left join(
    select  1 as tmp_id
            ,SAL 
    from emp where ENAME = 'SMITH'
) t2 on t1.tmp_id = t2.tmp_id
where t1.SAL > t2.SAL;

3． 列出所有员工的姓名及其直接上级的姓名。 
select  t1.ENAME
        ,t2.ENAME as mgr_name
from emp t1
left join emp t2
on t1.MGR = t2.empno;

4． 列出受雇日期早于其直接上级的所有员工。 
select  t1.ENAME
        ,t2.ENAME as mgr_name
from emp t1
left join emp t2
on t1.MGR = t2.empno
where t1.HIREDATE<t2.HIREDATE;


5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 
select  t1.deptno
        ,t1.deptname
        ,t2.EMPNO
        ,t2.ENAME
from (
    select  distinct
            deptno
            ,deptname
    from dept
) t1
left join emp t2
on t1.DEPTNO = t2.deptno;


6． 列出所有“CLERK”（办事员）的姓名及其部门名称。

select  t1.ENAME
        ,t2.deptname
from(
    select  ENAME
            ,deptno
    from emp
    where job = "CLERK"
) t1 left join (
    select  distinct
            deptno
            ,deptname
    from dept
) t2 on t1.deptno = t2.deptno;


7． 列出最低薪金大于1500的各种工作。
select  job
        ,min(SAL) as min_job
from emp
group by job
having min_job > 1500;


8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select  ENAME
from emp
where deptno in (
    select  distinct
            deptno
    from dept
    where deptname = "SALES"
);

9． 列出薪金高于公司平均薪金的所有员工。 
select  t1.ENAME
        ,t1.SAL
        ,t2.avg_sal
from(
    select  1 as tmp_id
            ,SAL
            ,ENAME
    from emp
) t1 left join(
    select  1 as tmp_id
            ,avg(SAL) as avg_sal
    from emp
) t2 on t1.tmp_id = t2.tmp_id
where t1.SAL>t2.avg_sal;

10．列出与“SCOTT”从事相同工作的所有员工。
select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select  t1.ENAME
        ,t1.SAL
from emp t1
where t1.deptno != '30' 
and t1.SAL in(
    select  SAL
    from emp
    where deptno = '30');

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
select  t1.ENAME
        ,t1.SAL
from(
    select  1 as tmp_id
            ,ENAME
            ,SAL
    from emp
    where deptno != '30'
) t1 left join(
    select  1 as tmp_id
            ,max(SAL) as max_sal
    from emp
    where deptno = '30'
) t2 on t1.tmp_id = t2.tmp_id
where t1.SAL>t2.max_sal;

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select  deptno
        ,count(empno) emp_cnt
        ,round(avg(SAL),1) as avg_sal
        ,round(avg(datediff(current_date(),HIREDATE)),1) as avg_days
from emp
group by deptno

14．列出所有员工的姓名、部门名称和工资。 
select  t1.ENAME
        ,t2.deptname
        ,t1.SAL
from emp t1
left join (
    select  distinct
            deptno
            ,deptname
    from dept
) t2 on t1.deptno = t2.deptno;

15．列出所有部门的详细信息和部门人数。 

select  t1.deptno
        ,t1.deptname
        ,t1.deptaddr
        ,t2.emp_cnt
from dept t1
left join(
    select  deptno
            ,count(empno) as emp_cnt
    from emp
    group by deptno
) t2 on t1.deptno = t2.deptno;


16．列出各种工作的最低工资。 
select  job
        ,min(sal) as min_sal
from emp
group by job;


17．列出各个部门的MANAGER（经理）的最低薪金。
select  deptno
        ,min(SAL) as min_sal
from emp
group by deptno,job
having job = "MANAGER";

select  t1.deptno
        ,min(t1.SAL) as min_sal
from (
    select  deptno
            ,ENAME
            ,SAL
    from emp
    where job = "MANAGER"
) t1 group by t1.deptno;


18．列出所有员工的年工资,按年薪从低到高排序。
# 年工资 = sal*12 + BONUS
# 两个字段在做 加法的时候 如果有一个为null 那么最终结果为null
select  ENAME
        ,SAL*12 + if(BONUS is null,0,BONUS) as year_sal
from emp
order by year_sal;

19. 列出每个部门薪水前两名最高的人员名称以及薪水。
select  t1.ENAME
        ,t1.SAL
        ,t1.rn
from(
    select  ENAME
            ,SAL
            ,row_number() over(partition by deptno order by SAL desc) as rn
    from emp
) t1 where t1.rn<=2;

20. 列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天。
select  ENAME
        ,datediff('2018-12-12',HIREDATE)
from emp;