# hive练习 （员工表，部门表，与连续登录天数，开始日期，结束日期，交易金额总和，连续登录日期之间的间隔天数问题求解）

7369,SMITH,CLERK,7902,1980-12-17,800,null,20

10,ACCOUNTING,NEW YORK

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

``````select distinct t1.deptno ,t2.deptname
from(select deptno ,count(*) as num from emp group by deptno having num>=1 ) as t1
join dept as t2
on t1.deptno = t2.deptno;
``````

1. 列出薪金比“SMITH”多的所有员工。
之前的博客里面有详细解释
hive笔记 —— 解决where子句不能使用子查询的前提下，比较子查询结果（等于，大于，平均值）

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

``````select e1.ename,e2.ename from emp as e1 left join emp as e2 on e1.MGR = e2.empno;
``````

1. 列出受雇日期早于其直接上级的所有员工
``````select e1.ename,e1.hiredate,e2.ename,e2.hiredate
from emp as e1 left join emp as e2 on e1.MGR = e2.empno
where e1.hiredate < e2.hiredate;
``````

1. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
``````   select * from dept left join emp on dept.deptno=emp.deptno;
``````

1. 列出所有“CLERK”（办事员）的姓名及其部门名称。
`````` select ename,deptname from emp
left join dept on emp.deptno=dept.deptno
where emp.job='CLERK'
group by ename,deptname;
``````

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

``````select job from emp group by job having min(sal)>1500;
``````

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

``````with t1 as (select ename,sal,1 as eid1 from emp),
t2 as (select avg(sal) as avg_sal , 1 as eid2 from emp)
select ename from t1 left join t2
on t1.eid1 = t2.eid2
where t1.sal > t2.avg_sal;
``````

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

``````select * from emp
where job in (select job from emp as e where e.ename = 'SCOTT')
and ename != 'SCOTT';
``````

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

``````select ename,sal from emp
where sal in (select sal from emp as e where e.deptno=30)
and deptno != 30;
``````

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

``````with t1 as (select ename,sal,1 as eid1 from emp where deptno != 30),
t2 as (select max(sal) as max_sal,1 as eid2 from emp where deptno=30 group by deptno)
select t1.ename,t1.sal from t1 left join t2 on t1.eid1=t2.eid2 where t1.sal>t2.max_sal;
``````

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

``````select count(*) , round(avg(sal),1) , round(avg(datediff(current_date,hiredate)),1)
from emp group by deptno;
``````

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

``````select distinct ename,deptname,sal from emp left join dept on emp.deptno=dept.deptno;
``````

``````with t1 as (select ename,sal,deptno from emp),
t2 as (select distinct deptno,deptname from dept)
select ename,deptname,sal from t1 join t2 on t1.deptno=t2.deptno;
``````

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

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

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

``````select job,min(sal) from emp group by job;
``````

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

``````select deptno,min(sal) as min_sal from emp where job='MANAGER' group by deptno;
``````

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

``````select ename, (sal*12 + if(bonus is null,0,bonus)) as money from emp;
``````

1. 列出每个部门薪水前两名最高的人员名称以及薪水。
``````select ename,sal
from (select ename,sal,row_number() over (partition by deptno order by sal desc) as num from emp) as t
where t.num <=2;
``````

1. 列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天。
``````select ename,datediff('2018-12-12',hiredate) as d from emp;
``````

## 统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数

``````create table days (id int,daystr string,amount decimal(10,2));
``````
``````insert into days values
(1, "2019-02-08", 6214.23),
(1, "2019-02-08", 6247.32),
(1, "2019-02-09", 85.63),
(1, "2019-02-09", 967.36),
(1, "2019-02-10", 85.69),
(1, "2019-02-12", 769.85),
(1, "2019-02-13", 943.86),
(1, "2019-02-14", 538.42),
(1, "2019-02-15", 369.76),
(1, "2019-02-16", 369.76),
(1, "2019-02-18", 795.15),
(1, "2019-02-19", 715.65),
(1, "2019-02-21", 537.71),
(2, "2019-02-08", 6214.23),
(2, "2019-02-08", 6247.32),
(2, "2019-02-09", 85.63),
(2, "2019-02-09", 967.36),
(2, "2019-02-10", 85.69),
(2, "2019-02-12", 769.85),
(2, "2019-02-13", 943.86),
(2, "2019-02-14", 943.18),
(2, "2019-02-15", 369.76),
(2, "2019-02-18", 795.15),
(2, "2019-02-19", 715.65),
(2, "2019-02-21", 537.71),
(3, "2019-02-08", 6214.23),
(3, "2019-02-08", 6247.32),
(3, "2019-02-09", 85.63),
(3, "2019-02-09", 967.36),
(3, "2019-02-10", 85.69),
(3, "2019-02-12", 769.85),
(3, "2019-02-13", 943.86),
(3, "2019-02-14", 276.81),
(3, "2019-02-15", 369.76),
(3, "2019-02-16", 369.76),
(3, "2019-02-18", 795.15),
(3, "2019-02-19", 715.65),
(3, "2019-02-21", 537.71);
``````

``````select id,daystr,sum(amount) as money
from days
group by id,daystr;
``````

``````with t1 as (select id,daystr,sum(amount) as money from days group by id,daystr)
``````

``````with t1 as (select id,daystr,sum(amount) as money from days group by id,daystr)
select id,daystr,money,date_sub(daystr,row_number() over (partition by id)) as day from t1;
``````

``````with t1 as (select id,daystr,sum(amount) as money from days group by id,daystr),
t2 as (select id,daystr,money,date_sub(daystr,row_number() over (partition by id)) as day from t1)
``````

``````with t1 as (select id,daystr,sum(amount) as money from days group by id,daystr),
t2 as (select id,daystr,money,date_sub(daystr,row_number() over (partition by id)) as day from t1)
select id,
count(1) as count,
min(daystr) as startday,
max(daystr) as endday,
sum(money) as sum,
datediff(day , lag(day,1,day) over (partition by id order by day)) as intervalday
from t2
group by id,day;
``````

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

THE END