2025年Hadoop-练习利用hive进行数据查询

科技前沿 • 2025-02-18 20:50 • 阅读 62

大家好，我是讯享网，很高兴认识大家。

1 准备测试数据
问题1：求各个部门的总工资
问题2：求各个部门的人数和平均工资
问题3：求每个部门最早进入公司的员工姓名
问题4：求各个城市的员工的总工资
问题5：列出工资比上司高的员工姓名及其工资
问题6：列出工资比公司平均工资要高的员工姓名及其工资
问题7：列出名字以J开头的员工姓名及其所属部门名称
问题8：列出工资最高的头三名员工姓名及其工资
问题9：将全体员工按照总收入（工资+提成）从高到低排列

问题10：求任何两名员工信息传递所需要经过的中间节点数

1 准备测试数据

创建两张表emp dept

create table dept(id int,name string,city string) row format delimited fields terminated by ',';

讯享网

讯享网create table emp( mpno int, name string, job string, mgr int, hiredate string, sal double, comm double, deptid int) row format delimited fields terminated by ',';

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

7369,SMITH,CLERK,7902,1981-6-9,800,,20
7499,ALLEN,SALESMAN,7698,1981-11-17,1600,300,30
7521,WARD,SALESMAN,7698,1982-1-23,1250,500,30
7566,JONES,MANAGER,7839,1980-12-17,2975,,20
7654,MARTIN,SALESMAN,7698,1981-4-2,1250,1400,30
7698,BLAKE,MANAGER,7839,1987-4-19,2850,,30
7782,CLARK,MANAGER,7839,1987-5-23,2450,,10
7839,KING,PRESIDENT,,1980-12-3,5000,,10
7844,TURNER,SALESMAN,7698,1983-11-22,1500,0,30
7900,JAMES,CLERK,7698,1984-3-11,950,,30
7902,FORD,ANALYST,7566,1987-10-09,3000,,20
7934,MILLER,CLERK,7782,1981-2-13,1300,,10

load data local inpath '/home/hadoop/dept.txt' into table dept;

讯享网load data local inpath '/home/hadoop/emp.txt' into table emp;

问题1：求各个部门的总工资

select d.name , e.sumsal from dept d join (select deptid ,sum(sal) sumsal from emp group by deptid) e on e.deptid=d.id;

讯享网

问题2：求各个部门的人数和平均工资

讯享网select d.name , e.avg ,e.count from dept d join (select deptid ,avg(sal) avg,count(*) count from emp group by deptid) e on e.deptid=d.id ;

问题3：求每个部门最早进入公司的员工姓名

select e.name , r.minhiredate from emp e join (select deptid, min(to_date(hiredate)) as minhiredate from emp group by deptid) r on r.deptid=e.deptid and to_date(e.hiredate)=r.minhiredate;

问题4：求各个城市的员工的总工资

讯享网select d.city , e.sumsal from dept d join (select deptid ,sum(sal) sumsal from emp group by deptid) e on e.deptid=d.id ;

问题5：列出工资比上司高的员工姓名及其工资

select e.name ,e.sal from emp e , (select sal ,mpno from emp) r where r.mpno=e.mgr and e.sal >r.sal;

问题6：列出工资比公司平均工资要高的员工姓名及其工资

讯享网select e.name ,e.sal from emp e , (select avg(sal) avg from emp) r where e.sal>r.avg;

问题7：列出名字以J开头的员工姓名及其所属部门名称

select d.name , r.name from dept d join (select name , deptid from emp where name like 'J%') r where r.deptid=d.id

问题8：列出工资最高的头三名员工姓名及其工资

讯享网select name,sal from emp order by sal desc limit 3

问题9：将全体员工按照总收入（工资+提成）从高到低排列

coalesce函数也可以实现判空的功能 coalesce(属性名,参数1,参数2)

 select name,(sal+nvl(comm,0.0)) as salary from emp order by salary desc;

2025年Hadoop-练习 利用hive进行数据查询