Oracle row_number()分析函数，over（）开窗函数

      在做系统选厂时，需要按比价单（com_no）、件号（pn）分组进行比价，选出厂家报价折算金额（convert_usd）最低，折算金额相同的情况下，选择交货期（delivery_day）最短的记录 。之前虽然想到了group by order by 之类的，但都不好分组进行内部排序，求助了同事后，同事帮写的SQL实现语句如下：

 select *

  from (   select row_number() over(partition by vcd.com_no, vcd.pn order by vcd.convert_usd, vcd.delivery_day) r_num,

               vcd.com_no,

               vcd.pn,

               vcd.vender,

               vcd.convert_usd,

               vcd.delivery_day

               from VENDER_COMPARISON_DETAIL vcd where vcd.com_no = '0107')   // 对询价单号为‘0107’中的每个件进行选厂

 where r_num = 1;

            Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行

            举个例子 :统计各班成绩排名

         name  class s                         

           ----- -----   ---------------------- 

           aa    1      80                     

           bb    1      78                     

           cc    1      95                     

           dd    2      74                     

           ee    2      92                     

           gg    3      99                     

           hh    3      99                     

           pp    3      45                     

    通过：                                                                        

    select name,class,s,.rank() over(partition by class order by s desc) mm from t2

   得到结果：

   name  class s                    mm

    ----- ----- ---------------------- ---------------------- 

    cc      1      95                   1          

    aa      1      80                   2         

    bb      1      78                   3    

    ee      2      92                   1

    dd      2      74                   2                      

    gg      3      99                   1                      

    hh      3      99                   1         

    pp      3      45                   3 

       row_number() over(partition by COL1 order by COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的). 

   1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果：

   name  class s                    mm

    ----- ----- ---------------------- ---------------------- 

    gg       3      99                   1                      

    hh       3      99                   2         

    pp       3      45                   3 

    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）按照部门分区