分析函数学习三：1.带空值的排列、2.查询前几或倒数几位、3.First/Last排名查询、4.按层次查询

--准备测试数据

create table emp_1 as select * from emp
select * from emp_1 for update    --编辑一个带不重复的同部门job，让sal为空

一、带空值的排列
根据各部门及工种排序，列出该部门各工种的工资和，各部门的工资和，以及所有排名

select deptno,
       job,
       sum(sal) job_sal,
       sum(sum(sal)) over(partition by deptno) deptno_sal,
       rank() over(partition by deptno order by sum(sal) desc) rank
  from emp_1
 group by deptno,job;

为空的默认排名第一，如何调整,在desc后面加上 nulls last (注意是nulls 而不是null)

select deptno,
       job,
       sum(sal) job_sal,
       sum(sum(sal)) over(partition by deptno) deptno_sal,
       rank() over(partition by deptno order by sum(sal)  desc nulls last) rank
  from emp_1
 group by deptno,job

二、Top/Bottom N查询即前几名或者倒数几名的查询需求

 按部门工种分组，找出薪资和排名前两位的

select * from (
select deptno,
       job,
       sum(sal) job_sal,
       rank() over(order by sum(sal)  desc nulls last) rank
  from emp_1
 group by deptno,job) where rank<3

按部门工种分组，找出各部门工种薪资和排名前两位的

select * from (
select deptno,
       job,
       sum(sal) job_sal,
       sum(sum(sal)) over(partition by deptno) deptno_sal,
       rank() over(partition by deptno order by sum(sal)  desc nulls last) rank
  from emp_1
 group by deptno,job) where rank<3

三、First/Last排名查询
想象一下下面的情形：按工种找出工资总和最多、最少的job。
按照前面我们学到的知识，这个至少需要2个查询。第一个查询按照job总额降序排列以期拿到第一名，
第二个查询按照job总额升序排列以期拿到最后一名。是不是很烦？
因为Rank函数只告诉我们排名的结果，却无法自动替我们从中筛选结果。
Oracle为我们在排列函数之外提供了两个额外的函数：first、last函数，专门用来解决这种问题

select min(job) keep(dense_rank first order by sum(sal) desc) first,
       min(job) keep(dense_rank last order by sum(sal) desc) last
  from emp_1
 group by job;

①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能换成rank吗？

1：min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。不能去掉，会导致语句无法执行。
2：keep是干什么用的？从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。
告诉Oracle只保留符合keep条件的记录。
3：dense_rank是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。
4:不能

四、按层次查询

按部门工种分组，找出工资和前1/3的
Ntil函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，
那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。
假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。

select deptno,
       job,
       sum(sal) job_sal,
       ntile(3) over(order by sum(sal)  desc nulls last) tile
  from emp_1
 group by deptno,job;