Oracle 分析函数

Oracle 分析函数

技术   2009-04-29 11:24   阅读53   评论0  

字号： 大大  中中  小小

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分析函数实现查询每个人的工资，以及对应部门的平均工资，

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(<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 （）函数功能类似，但是有重复值时序号是跳号的。  

比如上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 、分析函数使用时需要考虑排序、筛选的复杂度，大批量数据的过于复杂排序、筛选会导致系统资源繁忙。