oracle中over 函数,oracle的分析函数over(Partition by...)

oracle的分析函数over(Partition by...)以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容，让我们赶快一起来看一下吧！

Sql代码

over(Partition by…) 一个超级牛皮的ORACLE特有函数

最近工作中才接触到这个功能强大而灵活的函数

oracle的分析函数over 及开窗函数

一 分析函数over

Oracle从 开始提供分析函数 分析函数用于计算基于组的某种聚合值 它和聚合函数的不同之处是对于每个组返回多行 而聚合函数对于每个组只返回一行

下面通过几个例子来说明其应用

:统计某商店的营业额

date       sale

规则 按天统计 每天都统计前面几天的总额

得到的结果

DATE   SALE       SUM

天

天+ 天

天+ 天+ 天

:统计各班成绩第一名的同学信息

NAME   CLASS S

fda

ffd

dss

cfe

gds

gf

ddd

adf

asdf

dd

通过

select * from

(

select name class s rank()over(partition by class order by s desc) mm from t

)

where mm=

得到结果

NAME   CLASS S                       MM

dss

gds

gf

ddd

注意

在求第一名成绩的时候 不能用row_number() 因为如果同班有两个并列第一 row_number()只返回一个结果

rank()和dense_rank()的区别是

rank()是跳跃排序 有两个第二名时接下来就是第四名

dense_rank()l是连续排序 有两个第二名时仍然跟着第三名

分类统计 (并显示信息)

A   B   C

m   a

n   a

m   a

n   b

n   b

x   b

x   b

x   b

h   b

select a c sum(c)over(partition by a) from t

得到结果

A   B   C        SUM(C)OVER(PARTITIONBYA)

h   b

m   a

m   a

n   a

n   b

n   b

x   b

x   b

x   b

如果用sum group by 则只能得到

A   SUM(C)

h

m

n

x

无法得到B列值

=====

select * from test

数据

A B C

将B栏位值相同的对应的C 栏位值加总

select a b c SUM(C) OVER (PARTITION BY B) C_Sum

from test

A B C C_SUM

如果不需要已某个栏位的值分割 那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a b c SUM(C) OVER (PARTITION BY null) C_Sum

from test

A B C C_SUM

求个人工资占部门工资的百分比

SQL> select * from salary;

NAME DEPT SAL

a

b

c

d

SQL> select name dept sal sal* /sum(sal) over(partition by dept) percent from salary;

NAME DEPT SAL PERCENT

a

b

c

d

二 开窗函数

开窗函数指定了分析函数工作的数据窗口大小 这个数据窗口大小可能会随着行的变化而变化 举例如下

:

over(order by salary) 按照salary排序进行累计 order by是个默认的开窗函数

over(partition by deptno)按照部门分区

:

over(order by salary range beeen preceding and following)

每行对应的数据窗口是之前行幅度值不超过 之后行幅度值不超过

例如 对于以下列

aa

sum(aa)over(order by aa range beeen preceding and following)

得出的结果是

AA                       SUM

对于aa= 来说 sum= + + + + + =      ;

又如 对于aa= <=aa<= + 只有 一个数 所以sum=     ;

:其它

over(order by salary rows beeen preceding and following)

每行对应的数据窗口是之前 行 之后 行

:下面三条语句等效

over(order by salary rows beeen unbounded preceding and unbounded following)

每行对应的数据窗口是从第一行到最后一行 等效

over(order by salary range beeen unbounded preceding and unbounded following)

等效

over(partition by null)

常用的分析函数如下所列

row_number() over(partition by … order by …)

rank() over(partition by … order by …)

dense_rank() over(partition by … order by …)

count() over(partition by … order by …)

max() over(partition by … order by …)

min() over(partition by … order by …)

sum() over(partition by … order by …)

avg() over(partition by … order by …)

first_value() over(partition by … order by …)

last_value() over(partition by … order by …)

lag() over(partition by … order by …)

lead() over(partition by … order by …)

示例

SQL> select type qty from test;

TYPE QTY

SQL> select type qty to_char(row_number() over(partition by type order by qty))|| / ||to_char(count(*) over(partition by type)) as cnt from test;

TYPE QTY CNT

/

/

/

/

/

SQL> select * from test;

SQL> select t id mc to_char(b rn)|| / ||t id)e

from test t

(select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ))L

where b rn<=to_number(t id)

order by id

ID MC TO_CHAR(B RN)|| / ||T ID

/

/

/

/

/

/

/ /

/ CNOUG /

rows selected

*******************************************************************

关于partition by

这些都是分析函数 好像是 以后才有的 row_number()和rownum差不多 功能更强一点(可以在各个分组内从 开时排序) rank()是跳跃排序 有两个第二名时接下来就是第四名(同样是在各个分组内) dense_rank()l是连续排序 有两个第二名时仍然跟着第三名 相比之下row_number是没有重复值的 lag(arg arg arg ) arg 是从其他行返回的表达式 arg 是希望检索的当前行分区的偏移量 是一个正的偏移量 时一个往回检索以前的行的数目 arg 是在arg 表示的数目超出了分组的范围时返回的值

select deptno row_number() over(partition by deptno order by sal) from emp order by deptno;

select deptno rank() over (partition by deptno order by sal) from emp order by deptno;

select deptno dense_rank() over(partition by deptno order by sal) from emp order by deptno;

select deptno ename sal lag(ename null) over(partition by deptno order by ename) from emp ord er by deptno;

select deptno ename sal lag(ename example ) over(partition by deptno order by ename) from em p

order by deptno;

select deptno sal sum(sal) over(partition by deptno) from emp; 每行记录后都有总计值  select deptno sum(sal) from emp group by deptno;

求每个部门的平均工资以及每个人与所在部门的工资差额

select deptno ename sal

round(avg(sal) over(partition by deptno)) as dept_avg_sal

round(sal avg(sal) over(partition by deptno)) as dept_sal_diff lishixinzhi/Article/program/Oracle/201311/18056

分页：123