Oracle语法之OVER(PARTITION BY)及开窗函数【转载】
Oracle语法之OVER(PARTITION BY)及开窗函数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 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 例如:对于以下列 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)
页:
[1]