Oracle函数-分析函数1.doc.pdf
《Oracle函数-分析函数1.doc.pdf》由会员分享,可在线阅读,更多相关《Oracle函数-分析函数1.doc.pdf(13页珍藏版)》请在三一文库上搜索。
1、OVER(PARTITION BY)函数用法 2010年10月26日 OVER(PARTITION BY)函数介绍 开窗函数 Oracle从8.1.6开始提供分析函数 , 分析函数用于计算基于组的某种聚合值,它和聚合函数的不同 之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。 开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化, 举 例如下: 1 : over 后的写法: over ( order by salary )按照salary排序进行累计,order by是个默认的开窗函数 over ( partition by deptno )按照部门分
2、区 over ( partition by deptno order by salary) 2:开窗的窗口范围: over ( order by salary range between 5 preceding and 5 following ): 窗口范围为当前行数据幅度减5加5后的范围内的。 举例: sum(s)over(order by s range between 2 preceding and 2 following)表示力口2 或2 的 范围 内的求和 select name,classes, sum(s)over(order by s range between 2 prece
3、ding and 2 following) mm from t2 adf 3 45 45 -45加2减2即43到47,但是s在这个范围内只有45 asdf 3 55 55 cfe 2 74 74 3dd 3 78 158 -78在76到80范围内有78,80 , 求和得158 fda 1 80 158 gds 2 92 92 ffd 1 95 190 dss 1 95 ddd 3 99 gf 3 99 over ( order by salaryrows between 5 preceding and 5 举例: 一sum(s)over(order by s rows between 2 pr
4、eceding and 2 following)表示在上下两彳之 间的范围内 select name,classs, sum(s)over(order by s rows bet ween 2 preceding and 2 following) mm from t2 over ( order by salary range between unbounded preceding and unbounded following ) 或者 over ( order by salary rows between unbounded preceding and unbounded following
5、 ): 窗口不 做限制 3、与 over 函数结合的几个函数介绍 row_number()over() rank()over()和 dense_rank()over () 函数的使用 adf 3 45 174 asdf 3 55 252 cfe 2 74 332 3dd 3 78 379 (45+55+74=174) (45+55+74+78=252 ) (74+55+45+78+80=332 ) (78+74+55+80+92=379 ) 190 198 198 following ) 口范围为当前行前后各移动5行。 fd a 80 gdsff d 9 2 94 6 sid s d f d
6、d g 9 9 9 下面以班级成绩表t2来说明其应用 t2表信息如下: cfe 2 74 dss 1 95 ffd 1 95 fda 1 80 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 ; 得到的结果是: dss 1 95 1 ffd 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 注意: 1
7、 . 在求第一名成绩的时候, 不能用row_number(),因为如果同班有两个并列第一,row_n umber() 只返回一个结果;select * from ( select name,class,s,row_number()over(partition by class order by s desc) mm from t2 ) where mm=1 ; 1 95 1 -95有两名但是只显示一个 2 92 1 3 99 1 -99有两名但也只显乔个 2.rank()和dense_rank()可以将所有的都查找出来:如上可以看到采用rank可以将并列第一名的 都查找出来 ; rank()和
8、dense_rank()x另!: rank()是跳跃排序 , 有两个第二名时接下来就是第四名; select name,class,s,rank()over(partition by class order by s desc) mm from t2 dss 1 95 1 ffd 1 95 1 fda 1 80 3 一直接就跳到了第二 gds 2 92 1 cfe 2 74 2 gf 3 99 1 ddd 3 99 1 3dd 3 78 3 asdf 3 55 4 adf 3 45 5 dense_rank()l是连续排序 , 有两个第二名时仍然跟着第三名鬻ct name,clasps,den
9、se_rank()over(partition by class order by s desc) ffd 1 95 1 TO a 1 80 gds 2 92 cfe 2 74 gf 3 99 ddd 3 99 3dd 3 78 asdf 3 55 adf 3 45 2一连续排序(仍为2) 1 2 1 1 2 3 4 first_value() over()和 last_value() over()的使用 据班级进行分数求和 dss 1 95 190 一由于两个95都是第 ffd 1 95 190 fda 1 80 270 一第名加上第 _名的 gds 2 92 92 cfe 2 74 166
10、 gf 3 99 198 ddd 3 99 198 3dd 3 78 276 asdf 3 55 331 adf 3 45 376 sum()over () 的使用 select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 一根 名,所以累加时是两个第一名的相加 mm from t2 5) with a as (select 1 id, faf name from dual union select 2 id/b f name from dual union select 3 id/c* nam
11、e from dual union select 4 id,9 name from dual union select 5 id/e f name from dual ) select id,name,lag(id313,f)over(order by name) from a; Iead() over() 函数用法 ( 取出后N行数据 ) lead(expresstion55) with a as (select 1 id/a f name from dual union select 2 id/b f name from dual union select 3 id/c f name fr
12、om dual ? 1 2 3016-1PRAN-SW 2-LL-9 3 06-B 3016-IPRAN-SW2-LL-9306-B ? 1 2 union select 4 id/d f name from dual union select 5 id/e 1 name from dual ) select id,name,lead(id,1 , f, )over(order by name) from a; ratio_to_report(a)函数用法Ratio_to_report()括号中就是分子, over()括号中就是分母 with a as (select 1 a from dua
13、l union all select 1 a from dual union all select 1 a from dual union all select 2 a from dual union all select 3 a from dual union all select 4 a from dual union all select 4 a from dual union all select 5 a from dual ) select a,ratio_to_report(a)over(partition by a) b from a order by a; with a as
14、(select 1 a from dual union all select 1 a from dual union all select 1 a from dual union all select 2 a from dual union all select 3 a from dual union all select 4 a from dual union all select 4 a from dual union all select 5 a from dual ) select a, ratio_to_report(a)over() b from a 分母缺省就是整个占比 orde
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 函数 分析 doc
链接地址:https://www.31doc.com/p-5616186.html