Oracle SQL高级查询，分析函数FUNC over (partition by col [,order by col ])

/*
常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
*/

--每个部门工资最高的雇员的信息
select e.ename,e.job,e.sal,e.deptno from scott.emp e,(select e.deptno,max(e.sal) sal from scott.emp e group by e.deptno) maxe 
where e.deptno=maxe.deptno and e.sal=maxe.sal;

/*--over:  在什么条件之上。
partition by e.deptno:  按部门编号划分（分区）。
order by e.sal desc:  按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
rank()/dense_rank():  分级*/
--rank():  跳跃排序，如果有两个第一级时，接下来就是第三级。
--dense_rank():  连续排序，如果有两个第一级时，接下来仍然是第二级。
select e.ename,e.job,e.sal,e.deptno,rank from 
(select e.ename,e.job,e.sal,e.deptno,rank() over(partition by e.deptno order by e.sal desc) rank from scott.emp e) e 
where e.rank=1;


--查询部门最低工资的雇员信息
select e.ename,e.job,e.sal,e.deptno from (select e.ename,e.job,e.sal,e.deptno,  min(e.sal) over(partition by e.deptno order by e.sal) min from scott.emp e) e
where e.sal=min;

--查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额
select e.ename,e.job,e.sal,e.deptno,
nvl(e.sal-min(e.sal) over(partition by e.deptno),0) diff_min_sal,
nvl(e.sal-max(e.sal) over(partition by e.deptno order by e.sal) ,0) diff_max_sal from scott.emp e;

--max(col) over (partition by col order by col) http://stackoverflow.com/questions/10320435/partition-by-clause-is-giving-different-results-due-to-order-by-clause
select e.ename,e.sal,lead(e.sal,2) over (partition by e.deptno order by e.sal) from scott.emp e;
select e.ename,e.sal,e.deptno,max(e.sal) over(partition by e.deptno order by e.ename desc) max_sal from scott.emp e;
select e.ename,e.sal,max(e.sal) over(partition by e.deptno order by e.sal desc) max_sal from scott.emp e;
/*

lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。

lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

*/
--计算个人工资与比自己高一位/低一位工资的差额

select e.ename,e.job,e.sal,e.deptno,
lead(e.sal,1,0) over(partition by e.deptno order by e.sal ) lead_sal,
lag(e.sal,1,0) over(partition by e.deptno order by e.sal) lag_sal,
nvl(lead(e.sal) over (partition by e.deptno order by e.sal) -e.sal ,0) diff_lead_sal,
nvl(lag(e.sal) over (partition by e.deptno order by e.sal) -e.sal,0) diff_lag_sal from scott.emp e;




select e.ename,e.job,e.sal,e.deptno,
first_value(e.sal) over (partition by e.deptno) first_sal,
last_value(e.sal) over(partition by e.deptno) last_sal,
sum(e.sal) over(partition by e.deptno) sum_sal,
avg(e.sal) over (partition by e.deptno) avg_sal,
count(e.sal) over(partition by e.deptno) count_num,
row_number() over(partition by e.deptno order by e.sal) row_num --row_number() 必须使用order by parameter 要根据此参数排序编号
from scott.emp e;