http://zonghl8006.blog.163.com/blog/static/4528311520083995931317/

  over(Partition by...) 一个超级牛皮的ORACLE特有函数。

天天都用ORACLE，用了快2年了。最近才接触到这个功能强大而灵活的函数。真实惭愧啊！

oracle的分析函数over 及开窗函数一：分析函数overOracle从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_Sumfrom 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_Sumfrom 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 2000b 10 3000c 10 5000d 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 20b 10 3000 30c 10 5000 50d 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 select t.id,mc,to_char(b.rn)||'/'||t.id)e2 from test t, (select rownum rn from (select max(to_number(id)) mid from test) connect by rownum