oracle的分析函数over及开窗函数

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      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

 

二：开窗函数           

      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 

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)