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

万望 · 发表于 2020-3-19 23:04:16

本帖最后由万望于 2016-4-19 23:04 编辑

oracle的分析函数over(Partition by...) 一个超级牛皮的ORACLE特有函数。
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