Oracle OVER(PARTITION BY ..)
来源:互联网 发布:列车时刻表软件哪个好 编辑:程序博客网 时间:2024/05/16 09:30
为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。
一、rank()/dense_rank()over(partition by ...order by...)
现在客户有这样一个需求,查询每个部门工资最高的雇员的信息,相信有一定oracle应用知识的同学都能写出下面的SQL语句:[sql] view plaincopy
- select
e.ename, e.job, e.sal, e.deptno -
from scott.emp e, -
(select e.deptno, max(e.sal)sal fromscott.emp groupe by e.deptno) me -
where e.deptno = me.deptno -
and e.sal = me.sal;
[sql] view plaincopy
- select
e.ename, e.job, e.sal, e.deptno -
from ( selecte.ename, -
e.job, -
e.sal, -
e.deptno, -
rank() over(partition by e.deptno orderby e.sal desc)rank -
from scott.emp e) e -
where e.rank = 1;
[sql] view plaincopy
- select
e.ename, e.job, e.sal, e.deptno -
from ( selecte.ename, -
e.job, -
e.sal, -
e.deptno, -
dense_rank() over(partition by e.deptno orderby 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 plaincopy
- 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 plaincopy
- select
e.ename, -
e.job, -
e.sal, -
e.deptno, -
nvl(e.sal - min(e.sal) over(partition bye.deptno), 0) diff_min_sal, -
nvl(max(e.sal) over(partition bye.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 plaincopy
- select
e.ename, -
e.job, -
e.sal, -
e.deptno, -
lead(e.sal, 1, 0) over(partition by e.deptno orderby e.sal) lead_sal, -
lag(e.sal, 1, 0) over(partition by e.deptno orderby e.sal) lag_sal, -
nvl(lead(e.sal) over(partition by e.deptno orderby e.sal) - e.sal, -
0) diff_lead_sal, -
nvl(e.sal - lag(e.sal) over(partition by e.deptno orderby e.sal), 0) diff_lag_sal -
from scott.emp e;
lead(列名,n,m):
lag(列名,n,m):
下面再列举一些常用的方法在该语法中的应用(注:带orderby子句的方法说明在使用该方法的时候必须要带order by):
[sql] view plaincopy
- 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 bye.deptno) sum_sal, -
avg(e.sal) over(partition bye.deptno) avg_sal, -
count(e.sal) over(partition bye.deptno) count_num, -
row_number() over(partition by e.deptno orderby e.sal) row_num -
from scott.emp e;
重要提示:大家在读完本片文章之后可能会有点误解,就是OVER(PARTITION BY ..)比GROUPBY更好,实际并非如此,前者不可能替代后者,而且在执行效率上前者也没有后者高,只是前者提供了更多的功能而已,所以希望大家在使用中要根据需求情况进行选择。
0 0
- oracle over partition by
- Oracle OVER(PARTITION BY ..)
- Oracle over (partition by )
- Oracle 之 over partition by
- over(Partition by...) ORACLE函数
- ORACLE 的OVER PARTITION BY
- oracle over partition by用法
- Oracle over(partition by ...order by ...)
- Oracle 语法之 OVER (PARTITION BY ..)
- Oracle 分析函数 ---OVER(),row_number(),partition by
- oracle的分析函数over(Partition by...)
- Oracle 语法之 OVER (PARTITION BY ..)
- oracle over (partition by )用法
- Oracle高级查询之OVER (PARTITION BY ..)
- oracle over (partition by )用法
- Oracle高级查询之OVER (PARTITION BY ..)
- Oracle高级查询之OVER (PARTITION BY ..)
- Oracle高级查询之OVER (PARTITION BY ..)
- 搭建Maven私服
- MySQL数据库备份和还原的常用命令
- oracle视图总结
- 什么是java序列化,如何实现java序…
- Oracle 触发器
- Oracle OVER(PARTITION BY ..)
- Oracle SQL性能优化
- cocos2d-x 碰撞检测
- java实体类实现序列化的意义(转)
- java 拦截器、过滤器
- JAVA Web.xml 加载顺序
- Logger级别
- jdk outMemory内存溢出
- Oracle null的理解(转)