oracle高级分析函数使用实例

oracle高级分析函数使用实例

2014年11月26日10:26:55

∙标签：

∙oracle

∙1744

ORACLE的分析函数，发现大家写SQL的时候有些功能写的比较麻烦或者不知道复杂的功能怎么通过SQL实现，ORACLE自带的分析函数有很多相应的功能：

它是Oracle分析函数专门针对类似于"经营总额"、"找出一组中的百分之多少" 或"计算排名前几位"等问题设计的。

分析函数运行效率高，使用方便。

分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行。

语法：

(,,...)

over(

)

其中：

1 over是关键字，用于标识分析函数。

2 是指定的分析函数的名字。Oracle分析函数很多。

3 为参数，分析函数可以选取0-3个参数。

4 分区子句的格式为：

partition by[,value_expr]...

关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N

组。这里的"分区partition"和"组group"

都是同义词。

5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]

其中：

(1)asc|desc:指定了排列顺序。

(2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。

6窗口子句windowing-clause

给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，

可用该子句让分析函数计算出它的值。

格式：

{rows|range}

{between

{unbounded preceding|current row |{preceding|following} }and

{unbounded preceding|current row |{preceding|following} }|{unbounded preceding|current row |{preceding|following }}

(1)rows|range:此关键字定义了一个window。

(2)between...and...:为窗品指一个起点和终点。

(3)unbounded preceding：指明窗口是从分区(partition)的第一行开始。

(4)current row:指明窗口是从当前行开始。

create table emp(

deptno varchar2(20),--部门编码

ename varchar2(20),--人名

sal number(10));--工资

insert into emp values('10','andy1',2000);

insert into emp values('10','andy2',3000);

insert into emp values('10','andy3',2000);

insert into emp values('20','leno1',4000);

insert into emp values('20','leno2',8000);

insert into emp values('20','leno3',6000);

insert into emp values('30','jack1',5000);

insert into emp values('30','jack2',6000);

insert into emp values('30','jack3',7000);

1 连续求和

select deptno,ename,sal,sum(sal) over(order by ename) 连续求和from emp;

DEPTNO ENAME SAL 连续求和

-------------------- -------------------- ----------- ----------

10 andy1 2000 2000

10 andy2 3000 5000

10 andy3 2000 7000

30 jack1 5000 12000

30 jack2 6000 18000

30 jack3 7000 25000

20 leno1 4000 29000

20 leno2 8000 37000

20 leno3 6000 43000

2 不连续求和

select deptno,ename,sal,sum(sal) over() 不连续求和from emp; DEPTNO ENAME SAL 不连续求和

-------------------- -------------------- ----------- ----------

10 andy1 2000 43000

10 andy2 3000 43000

10 andy3 2000 43000

20 leno1 4000 43000

20 leno2 8000 43000

20 leno3 6000 43000

30 jack1 5000 43000

30 jack2 6000 43000

30 jack3 7000 43000