oracle的分析函数over及开窗关键字range

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 testA B C C_SUM 1 1 1 1 1 2 2 7 2 2 5 7 1 3 3 3 3 4 6 6 ---如果不需要已某个栏位的值分割,那就要用 nulleg: 就是将C的栏位值summary 放在每行后面select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sumfrom testA B C C_SUM 1 1 1 17 1 2 2 17 1 3 3 17 2 2 5 17 3 4 6 17

 

求个人工资占部门工资的百分比

二：开窗函数                 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 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) 相关资源：谷歌安装器(如果Go安装器无法打卡可以使用这个)