OVER (PARTITION BY ..) 及开窗函数

一：分析函数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            .
       SQL: select DATE, SALE, SUM(SALE) OVER (ORDERY BY DATE) FROM TABLE;
　　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)