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;
复制代码

 

posted @   坤坤无敌  阅读(165)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
点击右上角即可分享
微信分享提示