Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開窗函數(shù)

Oracle的分析函數(shù)over 及開窗函數(shù)

一：分析函數(shù)over
Oracle從8.1.6開始提供分析函數(shù)，分析函數(shù)用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之處是
對(duì)于每個(gè)組返回多行，而聚合函數(shù)對(duì)于每個(gè)組只返回一行。
下面通過幾個(gè)例子來(lái)說(shuō)明其應(yīng)用。
1：統(tǒng)計(jì)某商店的營(yíng)業(yè)額。
date sale
1 20
2 15
3 14
-
 

4 18
5 30
規(guī)則：按天統(tǒng)計(jì)：每天都統(tǒng)計(jì)前面幾天的總額
得到的結(jié)果：
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:統(tǒng)計(jì)各班成績(jī)第一名的同學(xué)信息
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
--
得到結(jié)果：
NAME CLASS S MM
----- ----- ---------------------- ----------------------
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1

注意：
1.在求第一名成績(jī)的時(shí)候，不能用row_number()，因?yàn)槿绻嘤袃蓚�(gè)并列第一，row_number()只返回一個(gè)結(jié)果
2.rank()和dense_rank()的區(qū)別是：
--rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來(lái)就是第四名
--dense_rank()l是連續(xù)排序，有兩個(gè)第二名時(shí)仍然跟著第三名

3.分類統(tǒng)計(jì) (并顯示信息)
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
得到結(jié)果：
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
無(wú)法得到B列值

＝＝＝＝＝
select * from test

數(shù)據(jù):
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6

---將B欄位值相同的對(duì)應(yīng)的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

---如果不需要已某個(gè)欄位的值分割,那就要用 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

求個(gè)人工資占部門工資的百分比

二：開窗函數(shù)
開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，舉例如下：
1：
over（order by salary） 按照salary排序進(jìn)行累計(jì)，order by是個(gè)默認(rèn)的開窗函數(shù)
over（partition by deptno）按照部門分區(qū)
2：
over（order by salary range between 5 preceding and 5 following）
每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過5，之后行幅度值不超過5
例如:對(duì)于以下列
aa
1
2
2
2
3
4
5
6
7
9

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

就是說(shuō)，對(duì)于aa=5的一行 ，sum為 5-1<=aa<=5+2 的和
對(duì)于aa=2來(lái)說(shuō) ，sum=1+2+2+2+3+4=14 ；
又如 對(duì)于aa=9 ，9-1<=aa<=9+2 只有9一個(gè)數(shù)，所以sum=9 ；

3：其它：
over（order by salary rows between 2 preceding and 4 following）
每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前2行，之后4行
4：下面三條語(yǔ)句等效：
over（order by salary rows between unbounded preceding and unbounded following）
每行對(duì)應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
等效
over(partition by null)

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]