Oracle语法之OVER（PARTITIONBY）及开窗函数

Oracle语法之OVER（PARTITIONBY）及开窗函数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 1
4 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)。