Oracle 分析函数
------
本人《
expert one-to-one 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 联合
select 'total',sum(sal) from emp union select ename,sal from emp
|
|
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方式性能应该差距不大。
相对于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
、分析函数使用时需要考虑排序、筛选的复杂度，大批量数据的过于复杂排序、筛选会导致系统资源繁忙。
附注：表emp为oracle安装用户scott下的表