实际应用
我们在做项目中常遇到类似这样的统计需求 , 列出一些数据列表,最后来一个合计的功能,类似如下:
姓名 | 工资 |
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 开始,有重复值时序号不跳号。
这个函数比较重要,
例三:
统计每个部门工资前三名的人员信息(重复人员也展现)
selectename,sal,deptno from
(select ename,sal,deptno,dense_rank() over (partitionby deptno orderby sal desc ) as seq_num
from emp
) a
whereseq_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
selectmax(sal),min(sal),deptno from emp groupby deptno
但是无法查询对应人员名称,通过分析函数可以变通实现
selectdistinct 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和
selectmax(sal),min(sal),deptno from emp groupby deptno有点类似
查看过两者的执行计划,采用分析函数多做了一次排序(在大数据量下没有做测试)。
7. rank() 和 dense_rank ()函数功能类似,但是有重复值时序号是跳号的。
8. row_number() 返回有序组中的一行的偏移量,也就是对应的序号。
例五:
显示每个的信息以及在工作在部门中的(从高到低)排名
selectename,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 如下:
selectdeptno, 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 、分析函数使用时需要考虑排序、筛选的复杂度,大批量数据的过于复杂排序、筛选会导致系统资源繁忙。