我们在做项目中常遇到类似这样的统计需求 , 列出一些数据列表，最后来一个合计的功能，类似如下：
表结构基本如下：
实现这样的功能，一般通过如下四种方式实现：
在没有学习本章前，我一般是通过方式 a 或 b 的方式实现用户需求。
简单评价一下四种实现方式：
我们的项目一般很少会做数据库迁移(那个代价是非常昂贵的),如果在确定使用oracle数据库，熟悉其特性功能对开发还是非常有帮助的，可以考虑在开发时使用相关特性、提高开发效率。
oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
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;
查询结果：
数据记录集分组， 比较好理解，就不多说。
a 、要和查询对应的记录集排序一致，否则统计数据交叉比较很理解。
b 、如果查询条件表达式没有排序语句，返回记录集会按照 order-by-clause 排序
个人理解其为分析函数统计数据范围设定。
a、窗口使用前提：分析函数必须有order-by-clause语句
b、默认窗口范围：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
c、窗口有三种：range、row、specifying
只对日期和数字类型数据生效,只能按照一个字段排序,在对应的字段数据范围内进行搜索.说明范围和排序的升降有关
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;
查询结果：
row 窗口是设定分析函数的数据行数，使用该窗口基本没有限制
rows n preceding
即为该窗口数据包括本行前的 n 行以及本行共 (n+1) 行数据
实际上统计的函数都是由 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
数据一致。
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
查询结果：
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；
查询结果：
9. sum(expression) 计算组中表达式的累计和
行列转换
将如下表格的数据从行式
改为列式
可通过如下在行式 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 、在 oracle9i 中 pl/sql 分析器支持分析函数，在程序块中也可以使用（简单测试验证）
2 、使用函数时注意考虑 null 特殊情况，默认值最大，降序排列在最前列。
3 、分析函数使用时需要考虑排序、筛选的复杂度，大批量数据的过于复杂排序、筛选会导致系统资源繁忙。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。