为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。
一、rank()/dense_rank()over(partition by ...order by...)
现在客户有这样一个需求,查询每个部门工资最高的雇员的信息,相信有一定oracle应用知识的同学都能写出下面的SQL语句:
[sql]
view plain
copy
- 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) me -
where e.deptno = me.deptno -
and e.sal = me.sal;
[sql]
view plain
copy
- select
e.ename, e.job, e.sal, e.deptno -
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;
[sql]
view plain
copy
- select
e.ename, e.job, e.sal, e.deptno -
from (select e.ename, -
e.job, -
e.sal, -
e.deptno, -
dense_rank() over(partition by e.deptno order by e.sal desc) rank -
from scott.emp e) e -
where e.rank = 1;
over:
partition bye.deptno:
order by e.saldesc:
rank()/dense_rank():
整个语句的意思就是:在按部门划分的基础上,按工资从高到低对雇员进行分级,“级别”由从小到大的数字表示(最小值一定为1)。
那么rank()和dense_rank()有什么区别呢?
rank():
dense_rank():
小作业:查询部门最低工资的雇员信息。
二、min()/max()over(partition by ...)
现在我们已经查询得到了部门最高/最低工资,客户需求又来了,查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额。这个还是比较简单,在第一节的groupby语句的基础上进行修改如下:
[sql]
view plain
copy
- select
e.ename, -
e.job, -
e.sal, -
e.deptno, -
e.sal - me.min_sal diff_min_sal, -
me.max_sal - e.sal diff_max_sal -
from scott.emp e, -
(select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal -
from scott.emp e -
group by e.deptno) me -
where e.deptno = me.deptno -
order by e.deptno, e.sal;
[sql]
view plain
copy
- 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(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal -
from scott.emp e;
小作业:如果在本例中加上order by,会得到什么结果呢?
三、lead()/lag()over(partition by ... order by...)
中国人爱攀比,好面子,闻名世界。客户更是好这一口,在和最高/最低工资比较完之后还觉得不过瘾,这次就提出了一个比较变态的需求,计算个人工资与比自己高一位/低一位工资的差额。这个需求确实让我很是为难,在groupby语句中不知道应该怎么去实现。不过。。。。现在我们有了over(partition by ...),一切看起来是那么的简单。如下:
[sql]
view plain
copy
- 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(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal -
from scott.emp e;
lead(列名,n,m):
lag(列名,n,m):
下面再列举一些常用的方法在该语法中的应用(注:带orderby子句的方法说明在使用该方法的时候必须要带order by):
[sql]
view plain
copy
- 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 -
from scott.emp e;
重要提示:大家在读完本片文章之后可能会有点误解,就是OVER(PARTITION BY ..)比GROUPBY更好,实际并非如此,前者不可能替代后者,而且在执行效率上前者也没有后者高,只是前者提供了更多的功能而已,所以希望大家在使用中要根据需求情况进行选择。