oracle的分析函数over(Partition by...)
Sql代码
over(Partition by...) 一个超级牛皮的ORACLE特有函数。
最近工作中才接触到这个功能强大而灵活的函数。
oracle的分析函数over 及开窗函数
一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。
1 1：统计某商店的营业额。 2 date sale 3 1 20 4 2 15 5 3 14 6 4 18 7 5 30 8 规则：按天统计：每天都统计前面几天的总额 9 得到的结果： 10 DATE SALE SUM 11 ----- -------- ------ 12 1 20 20 --1天 13 2 15 35 --1天＋2天 14 3 14 49 --1天＋2天＋3天 15 4 18 67 . 16 5 30 97 . 17 18 2:统计各班成绩第一名的同学信息 19 NAME CLASS S 20 ----- ----- ---------------------- 21 fda 1 80 22 ffd 1 78 23 dss 1 95 24 cfe 2 74 25 gds 2 92 26 gf 3 99 27 ddd 3 99 28 adf 3 45 29 asdf 3 55 30 3dd 3 78 31 32 通过： 33 -- 34 select * from 35 ( 36 select name,class,s,rank()over(partition by class order by s desc) mm from t2 37 ) 38 where mm=1 39 -- 40 得到结果： 41 NAME CLASS S MM 42 ----- ----- ---------------------- ---------------------- 43 dss 1 95 1 44 gds 2 92 1 45 gf 3 99 1 46 ddd 3 99 1 47 48 注意： 49 1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果 50 2.rank()和dense_rank()的区别是： 51 --rank()是跳跃排序，有两个第二名时接下来就是第四名 52 --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名 53 54 55 3.分类统计 (并显示信息) 56 A B C 57 -- -- ---------------------- 58 m a 2 59 n a 3 60 m a 2 61 n b 2 62 n b 1 63 x b 3 64 x b 2 65 x b 4 66 h b 3 67 select a,c,sum(c)over(partition by a) from t2 68 得到结果： 69 A B C SUM(C)OVER(PARTITIONBYA) 70 -- -- ------- ------------------------ 71 h b 3 3 72 m a 2 4 73 m a 2 4 74 n a 3 6 75 n b 2 6 76 n b 1 6 77 x b 3 9 78 x b 2 9 79 x b 4 9 80 81 如果用sum，group by 则只能得到 82 A SUM(C) 83 -- ---------------------- 84 h 3 85 m 4 86 n 6 87 x 9 88 无法得到B列值 89 90 ＝＝＝＝＝ 91 select * from test 92 93 数据: 94 A B C 95 1 1 1 96 1 2 2 97 1 3 3 98 2 2 5 99 3 4 6 100 101 102 ---将B栏位值相同的对应的C 栏位值加总 103 select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum 104 from test 105 106 A B C C_SUM 107 1 1 1 1 108 1 2 2 7 109 2 2 5 7 110 1 3 3 3 111 3 4 6 6 112 113 ---如果不需要已某个栏位的值分割,那就要用 null 114 115 eg: 就是将C的栏位值summary 放在每行后面 116 117 select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum 118 from test 119 120 A B C C_SUM 121 1 1 1 17 122 1 2 2 17 123 1 3 3 17 124 2 2 5 17 125 3 4 6 17 126 127 求个人工资占部门工资的百分比 128 129 SQL> select * from salary; 130 131 NAME DEPT SAL 132 ---------- ---- ----- 133 a 10 2000 134 b 10 3000 135 c 10 5000 136 d 20 4000 137 138 SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary; 139 140 NAME DEPT SAL PERCENT 141 ---------- ---- ----- ---------- 142 a 10 2000 20 143 b 10 3000 30 144 c 10 5000 50 145 d 20 4000 100 146 147 二：开窗函数 148 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 149 1： 150 over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数 151 over（partition by deptno）按照部门分区 152 2： 153 over（order by salary range between 5 preceding and 5 following） 154 每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5 155 例如:对于以下列 156 aa 157 1 158 2 159 2 160 2 161 3 162 4 163 5 164 6 165 7 166 9 167 168 sum(aa)over（order by aa range between 2 preceding and 2 following） 169 得出的结果是 170 AA SUM 171 ---------------------- ------------------------------------------------------- 172 1 10 173 2 14 174 2 14 175 2 14 176 3 18 177 4 18 178 5 22 179 6 18 180 7 22 181 9 9 182 183 就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2 的和 184 对于aa=2来说 ，sum=1+2+2+2+3+4=14 ； 185 又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ； 186 187 3：其它： 188 over（order by salary rows between 2 preceding and 4 following） 189 每行对应的数据窗口是之前2行，之后4行 190 4：下面三条语句等效： 191 over（order by salary rows between unbounded preceding and unbounded following） 192 每行对应的数据窗口是从第一行到最后一行，等效： 193 over（order by salary range between unbounded preceding and unbounded following） 194 等效 195 over(partition by null) 196 197 常用的分析函数如下所列: 198 199 row_number() over(partition by ... order by ...) 200 rank() over(partition by ... order by ...) 201 dense_rank() over(partition by ... order by ...) 202 count() over(partition by ... order by ...) 203 max() over(partition by ... order by ...) 204 min() over(partition by ... order by ...) 205 sum() over(partition by ... order by ...) 206 avg() over(partition by ... order by ...) 207 first_value() over(partition by ... order by ...) 208 last_value() over(partition by ... order by ...) 209 lag() over(partition by ... order by ...) 210 lead() over(partition by ... order by ...) 211 212 示例 213 SQL> select type,qty from test; 214 215 TYPE QTY 216 ---------- ---------- 217 1 6 218 2 9 219 220 SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test; 221 222 TYPE QTY CNT2 223 ---------- ---------- ------------ 224 3 1/2 225 1 6 2/2 226 2 5 1/3 227 7 2/3 228 2 9 3/3 229 230 SQL> select * from test; 231 ---------- ------------------------------------------------- 232 1 11111 233 2 22222 234 3 33333 235 4 44444 236 237 SQL> select t.id,mc,to_char(b.rn)||'/'||t.id)e 238 2 from test t, 239 (select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ))L 240 4 where b.rn<=to_number(t.id) 241 order by id 242 243 ID MC TO_CHAR(B.RN)||'/'||T.ID 244 --------- -------------------------------------------------- --------------------------------------------------- 245 1 11111 1/1 246 2 22222 1/2 247 2 22222 2/2 248 3 33333 1/3 249 3 33333 2/3 250 3 33333 3/3 251 44444 1/4 44444 2/4 252 4 44444 3/4CNOUG4 44444 4/4 253 254 10 rows selected 255 256 *******************************************************************
2，rank over 说明
排序：
---rank()over(order by 列名 排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4
select scoreid, studentid,COURSENAME,totalexamscore ,
rank()over(order by TOTALEXAMSCORE desc)orderbyNum
from SCORECOURSE a ,COURSESCORE b
where a.SCORECOURSEID = b.SCORECOURSEID
---dense_rank()over(order by 列名 排序)的结果是连续的，如果有4个人，其中有3个是并列第1名， 那么最后的排序结果如：1 1 1 2
select scoreid, studentid,COURSENAME,totalexamscore ,
dense_rank()over(order by TOTALEXAMSCORE desc)orderbyNum
from SCORECOURSE a ,COURSESCORE b
where a.SCORECOURSEID = b.SCORECOURSEID
----rank () OVER (PARTITION BY 列名 ORDER BY 列名 排序)使用分区方式获取每门课程的最高分
SELECT *
FROM (SELECT scoreid, studentid, coursename, TOTALEXAMSCORE,
rank () OVER (PARTITION BY coursename ORDER BY TOTALEXAMSCORE DESC)orderbynum
FROM scorecourse a, coursescore b
WHERE a.scorecourseid = b.scorecourseid and studentID = 'xxxxx')
WHERE orderbynum < 2
-----使用over实现成绩求和
-----SUM (totalexamscore) OVER (ORDER BY studentid) sum1 实现的是连续求和，如第一个学生的总评成绩是30，则sum1就展示为30，到第二个学生成绩出现的时候，则会依次累加
-----SUM (totalexamscore) OVER () sum2 就相当于是单纯的求和，和直接使用sum是一致的
SELECT scoreid, studentid, totalexamscore,
SUM (totalexamscore) OVER (ORDER BY studentid) sum1,
SUM (totalexamscore) OVER () sum2
FROM coursescore
注：这个案例想了很久也没想到很直观的描述，还请大家亲自去测试一下吧！！
语法：
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)
1.顺序：asc|desc 名次与业务相关：
示例：求优秀学员，成绩：降序 迟到次数：升序
2.分区字段：根据什么字段进行分区。
问题：分区与分组有什么区别?
•分区只是将原始数据进行名次排列(记录数不变),
•分组是对原始数据进行聚合统计(记录数变少,每组返回一条)。
注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的
(如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。
所以建议将dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名 排序 nulls last)
这样只要排序字段为null,就会放在最后，而不会影响排序结果).