Oracle 分析函数

    oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
1.1基本语法
oracle分析函数的语法：
function_name(arg1,arg2,...)
over
()
      说明：
1. partition-clause 数据记录集分组
2. order-by-clause   数据记录集排序
3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合
例一 ：
通过avg分析函数实现查询每个人的工资，以及对应部门的平均工资，
SQL> select ename,sal,deptno,round(avg(sal) over ( partition by deptno order by deptno),2) as avg_sl from emp;

ENAME             SAL     DEPTNO    AVG_SAL
---------- ---------- ---------- ----------
CLARK            2450         10    2916.67
KING             5000         10    2916.67
MILLER           1300         10    2916.67
SMITH             800         20       2175
ADAMS            1100         20       2175
FORD             3000         20       2175
SCOTT            3000         20       2175
JONES            2975         20       2175
ALLEN            1600         30    1566.67
BLAKE            2850         30    1566.67
MARTIN           1250         30    1566.67
JAMES             950         30    1566.67
TURNER           1500         30    1566.67
WARD             1250         30    1566.67

 

1.1.1 partition-clause
数据记录集分组， 就好比group by把列col中相同值分成了一组，上面例子里按deptno分了组。
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如下：  
SQL> select ename,sal,count(ename) over (order by sal range between 100 preceding and 100 following)-1 as all_row
  2  from emp;

ENAME             SAL    ALL_ROW
---------- ---------- ----------
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 可去除组内重复数据（参见 例一）。
 用distinct的例子：
SQL> select n1,v1,avg(distinct n1) over(partition by v1) as avg_n1
  2  from t2;
运行后可发现结果与例一的结果是不一样(必须找个表里面有重复数据的表才看的出来不同）
2.  count() 对组内数据进行计数 （参见 例二）。
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 （）函数功能类似，但是有重复值时序号是跳号的。 
比如上dense_rank()查出来是10条记录，但是用rank()查出来是9条记录，其中 ADAMS 1100 20

此条记录是没有的
8. row_number()   返回有序组中的一行的偏移量，也就是对应的序号。
例五 ：
    显示每个的信息以及在工作在部门中的（从高到低）排名  
SQL> select ename,sal,deptno,row_number() over (partition by deptno order by sal desc) as sorts
  2  from emp;

ENAME             SAL     DEPTNO      SORTS
---------- ---------- ---------- ----------
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

已选择14行。

 分页语句里使用row_number() over：
SQL>select ename,sal from (
       select ename,sal,row_number() over (order by sal desc) as r
    ) where r<=10 and r>=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 、分析函数使用时需要考虑排序、筛选的复杂度，大批量数据的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-615714/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20542911/viewspace-615714/