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 nameclasssrank（）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 acsum（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
如果用sumgroup by 则只能得到
A SUM（C）
h
m
n
x
无法得到B列值
=====
select * from test
数据
A B C
将B栏位值相同的对应的C 栏位值加总
select abc SUM（C） OVER （PARTITION BY B） C_Sum
from test
A B C C_SUM
如果不需要已某个栏位的值分割那就要用 null
eg: 就是将C的栏位值summary 放在每行后面
select abc 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 namedeptsalsal*/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 between preceding and following）
每行对应的数据窗口是之前行幅度值不超过之后行幅度值不超过
例如对于以下列
aa
sum（aa）over（order by aa range between preceding and following）
得出的结果是
AA SUM
对于aa=来说 sum=+++++= ;
又如 对于aa= <=aa<=+ 只有一个数所以sum= ;
:其它
over（order by salary rows between preceding and following）
每行对应的数据窗口是之前行之后行
:下面三条语句等效
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行等效
over（order by salary range between 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 typeqty from test;
TYPE QTY
SQL> select typeqtyto_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 tidmcto_char（brn）||/||tid）e
from test t
（select rownum rn from （select max（to_number（id）） mid from test） connect by rownum <=mid ））L
where brn<=to_number（tid）
order by id
ID MC TO_CHAR（BRN）||/||TID
/
/
/
/
/
/
/ /
/CNOUG /
rows selected
*******************************************************************
关于partition by
这些都是分析函数好像是以后才有的 row_number（）和rownum差不多功能更强一点（可以在各个分组内从开时排序） rank（）是跳跃排序有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank（）l是连续排序有两个第二名时仍然跟着第三名相比之下row_number是没有重复值的 lag（argargarg） arg是从其他行返回的表达式 arg是希望检索的当前行分区的偏移量是一个正的偏移量时一个往回检索以前的行的数目 arg是在arg表示的数目超出了分组的范围时返回的值
select deptnorow_number（） over（partition by deptno order by sal） from emp order by deptno;
select deptnorank（） over （partition by deptno order by sal） from emp order by deptno;
select deptnodense_rank（） over（partition by deptno order by sal） from emp order by deptno;
select deptnoenamesallag（enamenull） over（partition by deptno order by ename） from emp ord er by deptno;
select deptnoenamesallag（enameexample） over（partition by deptno order by ename） from em p
order by deptno;
select deptno salsum（sal） over（partition by deptno） from emp;每行记录后都有总计值 select deptno sum（sal） from emp group by deptno;
求每个部门的平均工资以及每个人与所在部门的工资差额
select deptnoenamesal
round（avg（sal） over（partition by deptno）） as dept_avg_sal
round（salavg（sal） over（partition by deptno）） as dept_sal_diff
from emp;
