-
转载地址:http://www.2cto.com/database/201306/217319.html
-
-
Oracle的over分组排序讲解oracle的分析函数over 及开窗函数eg: 相关解析:表t_pi_part字段 id code namevalue 1 222 avalue 2 222 bvalue 3 333 c给code相同的part code 添加行标,根据id 排序select p.* ,row_number()over(partition by p.code order order by a.id desc) as row_index from t_pi_part p;一:分析函数overOracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。下面通过几个例子来说明其应用。1:统计某商店的营业额。date sale1 202 153 144 185 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 80ffd 1 78dss 1 95cfe 2 74gds 2 92gf 3 99ddd 3 99adf 3 45asdf 3 553dd 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 1gds 2 92 1gf 3 99 1ddd 3 99 1注意:1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果2.rank()和dense_rank()的区别是:--rank()是跳跃排序,有两个第二名时接下来就是第四名--dense_rank()l是连续排序,有两个第二名时仍然跟着第三名3.分类统计 (并显示信息)A B C-- -- ----------------------m a 2n a 3m a 2n b 2n b 1x b 3x b 2x b 4h b 3select a,c,sum(c)over(partition by a) from t2得到结果:A B C SUM(C)OVER(PARTITIONBYA)-- -- ------- ------------------------h b 3 3m a 2 4m a 2 4n a 3 6n b 2 6n b 1 6x b 3 9x b 2 9x b 4 9如果用sum,group by 则只能得到A SUM(C)-- ----------------------h 3m 4n 6x 9无法得到B列值=====select * from test数据:A B C1 1 11 2 21 3 32 2 53 4 6---将B栏位值相同的对应的C 栏位值加总select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sumfrom testA B C C_SUM1 1 1 11 2 2 72 2 5 71 3 3 33 4 6 6---如果不需要已某个栏位的值分割,那就要用 nulleg: 就是将C的栏位值summary 放在每行后面select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sumfrom testA B C C_SUM1 1 1 171 2 2 171 3 3 172 2 5 173 4 6 17求个人工资占部门工资的百分比SQL> select * from salary;NAME DEPT SAL---------- ---- -----a 10 2000b 10 3000c 10 5000d 20 4000SQL> 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例如:对于以下列aa1222345679sum(aa)over(order by aa range between 2 preceding and 2 following)得出的结果是AA SUM---------------------- -------------------------------------------------------1 102 142 142 143 184 185 226 187 229 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)
Oracle的over分组排序讲解
最新推荐文章于 2022-02-26 11:10:16 发布