hive20道查询例题

hive20道查询例题

网上很多数据库例题都有的两张表：emp和dept 表

员工信息表emp：
字段：员工id,员工名字,工作岗位,部门经理,受雇日期,薪水,奖金,部门编号

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

建表语句： 

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 ',';

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

导入数据后查询 

hive> select * from emp

为了方便观看，我给上面加了每列的字段名称 

hive> select * from dept;

 

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

//使用having
hive> select distinct(dept.deptname) 
from 
(select deptno from emp group by deptno having count(*)>=1) as a
join 
dept 
on a.deptno=dept.deptno;

这里为什么要写第二种方法，因为hive的语法和mysql有的地方不太相同

所以部分mysql写的没有问题的语句，hive中需要转换方法写， 通常就是多表连接和多写点二次查询，比较麻烦

//方式2:二次查询
select distinct(dept.deptname) 
from (select deptno from 
(select deptno,count(*) as num from emp group by deptno) as a 
where num>=1) as b 
join dept 
on b.deptno=dept.deptno;

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

mysql写法：

select ename from emp where sal>(select sal from emp where name='SMITH');

但是hive不支持where后面跟子查询 ：in可以跟子查询，但是<,>,=不支持

select e.ENAME 
      ,e.SAL 
from 
(select ENAME 
       ,SAL 
       ,1 as cid 
from emp)e 
left join 
(select SAL 
        ,1 as cid 
from emp 
where ENAME='SMITH')
s on e.cid=s.cid 
where e.SAL>s.SAL;

下面来解析一下，首先根据题目中说找出比SMITH工资高的人，

那么我就先查询出SMITH工资是多少，

然后 把所有人的工资和这个值比较，hive不支持不等值连接，where后面子查询。

所有我们考虑给这2个查询表每行都打上一个标记作为一个新的字段，这样就可以根据这个新的字段进行表连接，然后我在这个查询基础上通过where筛选出工资比SMITH工资多的

      

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

和mysql一样的写法，利用表的自连接，将第一张表的领导标号与第二张表的员工标号相同作为连接条件

select a.ename,b.ename from emp a
join 
emp b
on a.mgr=b.empno

 

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

还是利用表的自连接，再通过where筛选出hiredate比领导早的

select a.ename from emp a 
join emp b
on a.mgr=b.empno
where a.hiredate 
 
 
 5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 
 
做法与mysql相同，利用左连接，显示左边的全部信息，右边为空的会使用null填充
 
select b.DEPTNAME,a.* from dept b
left join  emp a
on a.deptno=b.deptno;
 
上面的部门表中出现2个相同的部门名称，但是地理位置不同，这里会出现重复的人名 
 
 
 
6.列出所有“CLERK”（办事员）的姓名及其部门名称。
 
//注意distinct去重，要放在最前面，不能有字段在distinct前面
select distinct b.deptname,a.ename from emp a
join 
dept b
on a.deptno=b.deptno
where a.job='CLERK';
 
 
 
 7． 列出最低薪金大于1500的各种工作。
 
//使用having
select job from emp group by job having min(sal)>1500 ;
 
//使用where
select job from (select job,min(sal) as minsal from emp group by job) as tmp
where minsal>1500;
 
 
 
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
 
题目我有点看不懂，假定不知道销售部门的编号，我看了解析说： sal的部门编号不能直接用，需要自己查询出对应的部门编号
 
//通常都是这么写，但是题目要求我们不知道部门编号 
select a. ename
from
emp  a 
join
dept b
on a.deptno=b.deptno
where b.Deptname='SALES';
 
//所以下面的语句需要改动，我们过滤出部门编号
select deptno from
dept
where deptname='SALES'

//再从筛选出的部门编号里匹配出对应的人员名字，
//我使用的是hive1.2.1版本，可以使用where后面跟上in子查询语句
//但是>,<,=还是不支持的
select ename from emp where 
deptno in
(
select deptno from
dept
where deptname='SALES'
);
 
 
 
9． 列出薪金高于公司平均薪金的所有员工
 
//mysql中的写法
select * from emp where sal>(select avg(sal) from emp) as a;
 
//hive中不支持where子查询，所以还是老样子手动打上一个标签，然后连接，再通过where做筛选
select a.* from  
(select *,1 as cli from emp) a
join
(select avg(sal) as sal,1 as cli from emp)  b
on a.cli=b.cli
where a.sal>b.sal;

 
 
 
 10．列出与“SCOTT”从事相同工作的所有员工。
 
//mysql中的答案
select ename from emp 
where job in 
(select job from emp where ename='SMITH');
 
在hive中运行上面的按理说应该是通过的，但是报错了
 
FAILED: SemanticException [Error 10249]: Line 1:70 
Unsupported SubQuery expression 'ENAME': SubQuery cannot use the table alias: emp;
this is also an alias in the Outer Query and SubQuery contains a unqualified column reference
 
翻译内容： 
 
第3:27行不支持的子查询表达式“ename”：
 
子查询不能使用表别名：emp；这也是外部查询中的别名，子查询包含非限定列引用
 
//所以子查询和外面的查询字段不能重复，必须起别名 
select ename from emp 
where job in 
(select job from emp e where e.ename='SMITH')
and ename<>'SMITH';
 
 
 
 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
 
select ename,sal from emp where deptno=30;
 
 
 
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
 
//mysql写法，显然在hive不能用，有子查询，并且是>,<,=的类型
select ename,sal from emp where sal>(select sal from emp e where e.deptno=30)
 
//hive写法 
select a.ename,a.sal from  
(select *,1 as cli from emp) a
join 
(select max(sal) as max,1 as cli from emp where deptno=30) b
on a.cli=b.cli
where a.deptno<>30 and a.sal>b.max;
 
 
 
 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
 
hive支持的获取当前时间  ：
 
CURRENT_TIMESTAMP();    精确到 年，月，日，时，分，秒，毫秒
 CURRENT_DATE();                精确到 年，月，日
 
 求为企业的服务期限，那么就是时间相减datediff(current_date(),hiredate)
 
select count(*)
      ,avg(sal) 
      ,avg(datediff(current_date(),hiredate))
from emp group by deptno;
 
 
结果看起来有点长，保留2位小数
 
select count(*)
      ,round(avg(sal),2) 
      ,round(avg(datediff(current_date(),hiredate)),2)
from emp group by deptno;
 
 
 
14．列出所有员工的姓名、部门名称和工资。
 
select ename
      ,deptname
      ,sal 
from emp a
join dept b
on a.deptno=b.deptno;
 
 
 
 
 15．列出所有部门的详细信息和部门人数。 
 
select a.deptno
      ,a.deptname
      ,a.deptaddr
      ,b.num
from dept a
left join
(select count(*) as num,deptno from emp  group by deptno) b
on a.deptno=b.deptno;
    
 
 
下面分析一下为什么这么做 
 
select t.deptname.t.deptno,count(*) 
from (select deptname
      ,b.deptno
      ,b.DEPTADDR
from dept b
left join emp a
on a.deptno=b.deptno) t
group by deptno,deptaddr;
 
 

 16．列出各种工作的最低工资
 
select job,min(sal) from emp group by job;
 
 
 
17．列出各个部门的MANAGER（经理）的最低薪金
 
where后面跟in子查询，mysql和hive都可以使用
 
select deptno,
       min(sal) 
from emp 
where empno in (select mgr from emp) 
group by deptno;
 
 

 18．列出所有员工的年工资,按年薪从低到高排序
 
//此处nvl函数是帮助转化为null的值，如果是空，就转换为后面设置的大小
select ename
    ,(sal*12+nvl(bonus,0)) as money
from emp order by money;
 
 
 
19. 列出每个部门薪水前两名最高的人员名称以及薪水。
 
//准备工作：先开一个窗口，将部门的排名给打上去
select ename
      ,sal
      ,row_number() over(partition by deptno order by sal) rn
      ,deptno
from emp;
 
 
 然后我们通过where筛选出排名小于等于2的：
 
select t.ename
      ,t.deptno
      ,t.sal
from
(
select ename
      ,sal
      ,row_number() over(partition by deptno order by sal) rn
      ,deptno
from emp
) t
where t.rn<=2;
 
 
 
 20. 列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天
 
select ename
      ,datediff('2018-12-12',hiredate)as days
from emp ;
 
 
 
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5618596.html