Oracle 分析函数（转载）
实际应用
我们在做项目中常遇到类似这样的统计需求 , 列出一些数据列表，最后来一个合计的功能，类似如下：
|
姓名 |
工资 |
|
SMITH |
800 |
|
ALLEN |
1600 |
|
WARD |
1250 |
|
JONES |
2975 |
|
MARTIN |
1250 |
|
BLAKE |
2850 |
|
CLARK |
2450 |
|
SCOTT |
3000 |
|
KING |
5000 |
|
TURNER |
1500 |
|
ADAMS |
1100 |
|
JAMES |
950 |
|
FORD |
3000 |
|
MILLER |
1300 |
|
合计 |
29025 |
表结构基本如下：
|
字段 |
数据类型 |
|
EMPNO |
NUMBER(4) |
|
ENAME |
VARCHAR2(10) |
|
JOB |
VARCHAR2(9) |
|
MGR |
NUMBER(4) |
|
HIREDATE |
DATE |
|
SAL |
NUMBER(7,2) |
|
COMM |
NUMBER(7,2) |
|
DEPTNO |
NUMBER(2) |
实现这样的功能，一般通过如下四种方式实现：
|
简称 |
实现方式 |
实现方法 |
|
a |
程序 |
取出符合条件的数据列表，在程序中遍历该数据列表统计合计数据，在列表中增加一行合计统计数据，最后页面展现信息 |
|
b |
oracle特性功能（临时表、包） |
创建临时表，创建包将查询数据以及统计的数据插入到临时表中，返回一个游标，程序遍历该游标，生成包含统计的数据列表 |
|
c |
标准sql |
通过sql union 联合 |
|
d |
oracle特性功能（分析函数） |
select nvl(ename,'total'),sum(sal) from emp group by rollup(ename); |
在没有学习本章前，我一般是通过方式 a 或 b 的方式实现用户需求。
简单评价一下四种实现方式：
|
实现方式 |
优点 |
缺点 |
|
a |
和数据库无关，这对一个在多个数据库的产品来说比较适用 |
实际上没有特别的缺点,就是多了一定的开发工作量 |
|
b |
主要体现在复杂的应用场景中，如果统计数据要和其他表关联查询，分析，采用a方式往往需要写大量的java代码并且多了很多次数据库交互， |
绑定了数据库，对应的数据库必须支持临时表功能，并且每次移值都需要重写对应数据库的过程代码, 实际开发的工作量比a方式可能更大 |
|
c |
比较简单，开发工作量比较小，是标准sql，一般数据库均支持 |
性能是相对比较差，如果数据量不大可以考虑 |
|
d |
相对于c，d性能更好，和a、b方式性能应该差距不大。 |
绑定oracle数据库 |
|
比较 |
方式c |
方式d |
|
分析 |
对emp表做了两次全表扫描 |
对emp表做一次全表扫描 |
|
测试数据 |
测试数据库中测试简单统计百万级数据表时间差异在2秒左右，还是比较大的 |
|
前提条件
我们的项目一般很少会做数据库迁移(那个代价是非常昂贵的),如果在确定使用oracle数据库，熟悉其特性功能对开发还是非常有帮助的，可以考虑在开发时使用相关特性、提高开发效率。
使用心得
oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
1.1基本语法
oracle分析函数的语法：
function_name(arg1,arg2,...)
over
(<partition-clause> <order-by-clause ><windowing clause>)
说明：
1. partition-clause 数据记录集分组
2. order-by-clause 数据记录集排序
3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合
例一 ：
通过avg分析函数实现查询每个人的工资，以及对应部门的平均工资，
select ename,sal, avg(sal) over ( partition by deptno order by deptno) from emp;
查询结果：
|
ENAME |
SAL |
AVG_DEPT |
|
CLARK |
2450 |
2916.6667 |
|
KING |
5000 |
2916.6667 |
|
MILLER |
1300 |
2916.6667 |
|
SMITH |
800 |
2175 |
|
ADAMS |
1100 |
2175 |
|
FORD |
3000 |
2175 |
|
SCOTT |
3000 |
2175 |
|
JONES |
2975 |
2175 |
|
ALLEN |
1600 |
1566.6667 |
|
BLAKE |
2850 |
1566.6667 |
|
MARTIN |
1250 |
1566.6667 |
|
JAMES |
950 |
1566.6667 |
|
TURNER |
1500 |
1566.6667 |
|
WARD |
1250 |
1566.6667 |
1.1.1 partition-clause
数据记录集分组， 比较好理解，就不多说。
1.1.2 order-by-clause
a 、要和查询对应的记录集排序一致，否则统计数据交叉比较很理解。
b 、如果查询条件表达式没有排序语句，返回记录集会按照 order-by-clause 排序
1.1.3 windowing-clause
个人理解其为分析函数统计数据范围设定。
a、窗口使用前提：分析函数必须有order-by-clause语句
b、默认窗口范围：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
c、窗口有三种：range、row、specifying
1.1.3.1 range窗口
只对日期和数字类型数据生效,只能按照一个字段排序,在对应的字段数据范围内进行搜索.说明范围和排序的升降有关
a、升序，查找[本行字段数据值-range值,本行数据值]数据集合
b、降序, 查找[本行数据值,本行字段数据值+range值,]数据集合
例二 ：
查询本人工资以及和本人工资差距在100内的员工个数（和自己相同工资的算高于自己）
select ename,sal,greater_num+lower_num
from
(select ename,sal,
count( ename) over ( order by sal desc range 100 preceding)-1
as greater_num ,
((count(ename) over ( order by sal asc range 100 preceding)-1) -
(count(ename) over ( order by sal asc range 0 preceding)-1))
as lower_num
from emp) a
order by sal asc;
查询结果：
|
ENAME |
SAL |
GREATER_NUM |
|
SMITH |
800 |
0 |
|
JAMES |
950 |
0 |
|
ADAMS |
1100 |
0 |
|
WARD |
1250 |
2 |
|
MARTIN |
1250 |
2 |
|
MILLER |
1300 |
2 |
|
TURNER |
1500 |
1 |
|
ALLEN |
1600 |
1 |
|
CLARK |
2450 |
0 |
|
BLAKE |
2850 |
0 |
|
JONES |
2975 |
2 |
|
SCOTT |
3000 |
2 |
|
FORD |
3000 |
2 |
|
KING |
5000 |
0 |
1.1.3.2 row 窗口
row 窗口是设定分析函数的数据行数，使用该窗口基本没有限制
rows n preceding
即为该窗口数据包括本行前的 n 行以及本行共 (n+1) 行数据
1.1.3.3 specifying 窗口
实际上统计的函数都是由 specifying 窗口设定， range 、 row 窗口实际是指定了分析的对象（字段、数据行），而具体的行数由 specifying 窗口设定，常用表达式如下：
unbounded preceding 从当前分区第一行开始，结束于处理的当前行
current row 从当前行开始 ( 并结束 )
numberic expression preceding 从当前行的数字表达式之前的行开始
numberic expression following 从当前行的数字表达式之前的行结束
在这边可以简化以前的前面的 sql, 查询本人工资以及和本人工资差距在100内的员工个数,sql如下：
select ename,sal,
count( ename) over ( orderby sal ascrangebetween100 preceding and 100 following)-1
as all_num
from emp
|
ENAME |
SAL |
GREATER_NUM |
|
SMITH |
800 |
0 |
|
JAMES |
950 |
0 |
|
ADAMS |
1100 |
0 |
|
WARD |
1250 |
2 |
|
MARTIN |
1250 |
2 |
|
MILLER |
1300 |
2 |
|
TURNER |
1500 |
1 |
|
ALLEN |
1600 |
1 |
|
CLARK |
2450 |
0 |
|
BLAKE |
2850 |
0 |
|
JONES |
2975 |
2 |
|
SCOTT |
3000 |
2 |
|
FORD |
3000 |
2 |
|
KING |
5000 |
0 |
数据一致。
1.2 常用分析函数
1. avg(distinct|all expression) 计算组内平均值， distinct 可去除组内重复数据
（参见 #例一 ）。
2. count(<distinct><*><expression>) 对组内数据进行计数 （参见 #例二 ）。
3. cume_dist() 计算一行在组中的相对位置，值的范围（ 0 ， 1 ]
4. dense_rank() 根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。
这个函数比较重要，
例三 ：
统计每个部门工资前三名的人员信息（重复人员也展现）
select ename,sal,deptno from
(select ename,sal,deptno,dense_rank() over (partitionby deptno orderby sal desc ) as seq_num
from emp
) a
where seq_num<=3
查询结果：
|
ENAME |
SAL |
deptno |
|
KING |
5000 |
10 |
|
CLARK |
2450 |
10 |
|
MILLER |
1300 |
10 |
|
SCOTT |
3000 |
20 |
|
FORD |
3000 |
20 |
|
JONES |
2975 |
20 |
|
ADAMS |
1100 |
20 |
|
BLAKE |
2850 |
30 |
|
ALLEN |
1600 |
30 |
|
TURNER |
1500 |
30 |
5. first_value(显示的表达式),last_value(显示的表达式) 返回组内第一个值，最后一个值。
例四 ：
查询每个部门工资高和最低的人
一般查询sql
select max(sal),min(sal),deptno from emp groupby deptno
但是无法查询对应人员名称，通过分析函数可以变通实现
select distinct deptno,
first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal) asfirst,
first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal desc) aslast
from emp;
要说明的last_value()并不类似于max函数，从分析函数语法解析知道，默认窗口下的last_value分析的是当前组的当前的以前数据行以及当前行，因此
first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal)
并不等同于
last_value(ename||' : ' ||sal) over (partitionby deptno orderby sal desc)
6. min(expression),max(expression) 返回组内最小，最大值
select distinct max(sal) over (partitionby deptno),min(sal) over(partitionby deptno),deptno from emp
该sql和
select max(sal),min(sal),deptno from emp groupby deptno有点类似
查看过两者的执行计划，采用分析函数多做了一次排序（在大数据量下没有做测试）。
7. rank() 和 dense_rank （）函数功能类似，但是有重复值时序号是跳号的。
8. row_number() 返回有序组中的一行的偏移量，也就是对应的序号。
例五 ：
显示每个的信息以及在工作在部门中的（从高到低）排名
select ename,sal ,deptno,row_number() over (partitionby deptno orderby sal desc) as sortno from emp；
查询结果：
|
ENAME |
SAL |
DEPTNO |
SORTNO |
|
KING |
5000 |
10 |
1 |
|
CLARK |
2450 |
10 |
2 |
|
MILLER |
1300 |
10 |
3 |
|
SCOTT |
3000 |
20 |
1 |
|
FORD |
3000 |
20 |
2 |
|
JONES |
2975 |
20 |
3 |
|
ADAMS |
1100 |
20 |
4 |
|
SMITH |
800 |
20 |
5 |
|
BLAKE |
2850 |
30 |
1 |
|
ALLEN |
1600 |
30 |
2 |
|
TURNER |
1500 |
30 |
3 |
|
WARD |
1250 |
30 |
4 |
|
MARTIN |
1250 |
30 |
5 |
|
JAMES |
950 |
30 |
6 |
9. sum(expression) 计算组中表达式的累计和
1.3 经典案例
行列转换
将如下表格的数据从行式
|
ENAME |
DEPTNO |
SORTNO |
|
KING |
10 |
1 |
|
CLARK |
10 |
2 |
|
MILLER |
10 |
3 |
|
SCOTT |
20 |
1 |
|
FORD |
20 |
1 |
|
JONES |
20 |
2 |
|
ADAMS |
20 |
3 |
|
BLAKE |
30 |
1 |
|
ALLEN |
30 |
2 |
|
TURNER |
30 |
3 |
改为列式
|
DEPTNO |
HIGHEST |
SEC_HIGHEST |
THIRD_HIGHEST |
|
10 |
KING |
CLARK |
MILLER |
|
20 |
FORD |
JONES |
ADAMS |
|
30 |
BLAKE |
ALLEN |
TURNER |
可通过如下在行式 sql 基础上生成， sql 如下：
select deptno, min(decode(seq_num,1,ename,null)) as highest,
min(decode(seq_num,2,ename,null)) as sec_highest,
min(decode(seq_num,3,ename,null)) as third_highest from
(select ename,deptno,seq_num from
(select ename,deptno,dense_rank() over (partitionby deptno orderby sal desc ) as seq_num
from emp) a
where seq_num<=3) b
groupby b.deptno
1.4 说明
1 、在 oracle9i 中 pl/sql 分析器支持分析函数，在程序块中也可以使用（简单测试验证）
2 、使用函数时注意考虑 null 特殊情况，默认值最大，降序排列在最前列。
3 、分析函数使用时需要考虑排序、筛选的复杂度，大批量数据的过于复杂排序、筛选会导致系统资源繁忙。
