oracle over partition by用法

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

一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。 
下面通过几个例子来说明其应用。

1：统计某商店的营业额。

date sale
1 20
2 15
3 14
4 18
5 30
规则：按天统计：每天都统计前面几天的总额
得到的结果：
DATE SALE SUM
----- -------- ------
1 20 20 --1天 
2 15 35 --1天＋2天 
3 14 49 --1天＋2天＋3天 
4 18 67 . 
5 30 97 .

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

NAME CLASS S 
----- ----- ---------------------- 
fda 1 80 
ffd 1 78 
dss 1 95 
cfe 2 74 
gds 2 92 
gf 3 99 
ddd 3 99 
adf 3 45 
asdf 3 55 
3dd 3 78 

通过： 
--
select * from 
( 
select name,class,s,rank()over(partition by class order by s desc) mm from t2
) 
where mm=1 
--
得到结果：
NAME CLASS S MM 
----- ----- ---------------------- ---------------------- 
dss 1 95 1 
gds 2 92 1 
gf 3 99 1 
ddd 3 99 1 

注意：
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果 
2.rank()和dense_rank()的区别是：
--rank()是跳跃排序，有两个第二名时接下来就是第四名
--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名


3.分类统计 (并显示信息)

A B C 
-- -- ---------------------- 
m a 2 
n a 3 
m a 2 
n b 2 
n b 1 
x b 3 
x b 2 
x b 4 
h b 3

select a,c,sum(c)over(partition by a) from t2 
得到结果：
A B C SUM(C)OVER(PARTITIONBYA) 
-- -- ------- ------------------------ 
h b 3 3 
m a 2 4 
m a 2 4 
n a 3 6 
n b 2 6 
n b 1 6 
x b 3 9 
x b 2 9 
x b 4 9 

如果用sum，group by 则只能得到
A SUM(C) 
-- ---------------------- 
h 3 
m 4 
n 6 
x 9 
无法得到B列值 

＝＝＝＝＝

select * from test

数据:
A B C 
1 1 1 
1 2 2 
1 3 3 
2 2 5 
3 4 6 


---将B栏位值相同的对应的C 栏位值加总
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test

A B C C_SUM 
1 1 1 1 
1 2 2 7 
2 2 5 7 
1 3 3 3 
3 4 6 6 



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

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

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

A B C C_SUM 
1 1 1 17 
1 2 2 17 
1 3 3 17 
2 2 5 17 
3 4 6 17

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

SQL> select * from salary;

NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000

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

NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100

二：开窗函数 
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 
1： 
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
2：
over（order by salary range between 5 preceding and 5 following）
每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
例如:对于以下列
aa
1
2
2
2
3
4
5
6
7
9

sum(aa)over（order by aa range between 2 preceding and 2 following）
得出的结果是
AA SUM
---------------------- ------------------------------------------------------- 
1 10 
2 14 
2 14 
2 14 
3 18 
4 18 
5 22 
6 18 
7 22 
9 9 

就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2 的和
对于aa=2来说 ，sum=1+2+2+2+3+4=14 ；
又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ；

3：其它：
over（order by salary rows between 2 preceding and 4 following）
每行对应的数据窗口是之前2行，之后4行 
4：下面三条语句等效： 
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)

[@more@]