oracle完整函数全套汇编(分析函数聚合函数转换函数日期型函数字符型函数数值型函数其他函数).doc
《oracle完整函数全套汇编(分析函数聚合函数转换函数日期型函数字符型函数数值型函数其他函数).doc》由会员分享,可在线阅读,更多相关《oracle完整函数全套汇编(分析函数聚合函数转换函数日期型函数字符型函数数值型函数其他函数).doc(101页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、-!oracle函数大全(分析函数,聚合函数,转换函数,日期型函数,字符型函数,数值型函数,其他函数)oracle函数大全1oracle分析函数-SQL*PLUS环境1oracle 10g函数大全-聚合函数19oracle 10g函数大全-转换函数23oracle 10g函数大全-日期型函数40oracle 10g函数大全-字符型函数45oracle 10g函数大全-数值型函数55oracle 10g函数大全-其他函数58oracle分析函数-SQL*PLUS环境一、总体介绍1.1. 分析函数如何工作语法 FUNCTION_NAME(,) OVER ( ORDER BY 表达式 ) PARTI
2、TION子句 ORDER BY子句 WINDOWING子句 缺省时相当于RANGE UNBOUNDED PRECEDING 1. 值域窗(RANGE WINDOW) RANGE N PRECEDING 仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值 /+ N)的所有行,因此与ORDER BY子句有关系。 2. 行窗(ROW WINDOW) ROWS N PRECEDING 选定窗为当前行及之前N行。 还可以加上BETWEEN AND 形式,例如RANGE BETWEEN m PRECEDING AND n FOLLOWING 函数 AVG( eXP
3、r) 一组或选定窗中表达式的平均值 CORR(expr, expr) 即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2),两个表达式的互相关,-1(反相关) 1(正相关),0表示不相关 COUNT( ) 计数 COVAR_POP(expr, expr) 总体协方差 COVAR_SAMP(expr, expr) 样本协方差 CUME_DIST 累积分布,即行在组中的相对位置,返回0 1 DENSE_RANK 行的相对排序(与ORDER BY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数 FIRST_VAL
4、UE 一个组的第一个值 LAG(expr, , ) 访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行) LAST_VALUE 一个组的最后一个值 LEAD(expr, , ) 访问之后的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行) MAX(expr) 最大值 MIN(expr) 最小值 NTILE(expr) 按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1 4的值,而不能等分则多出的部分在值最小的那组 PERCENT_RANK
5、 类似CUME_DIST,1/(行的序数 - 1) RANK 相对序数,答应并列,并空出随后序号 RATIO_TO_REPORT(expr) 表达式值 / SUM(表达式值) ROW_NUMBER 排序的组中行的偏移 STDDEV(expr) 标准差 STDDEV_POP(expr) 总体标准差 STDDEV_SAMP(expr) 样本标准差 SUM(expr) 合计 VAR_POP(expr) 总体方差 VAR_SAMP(expr) 样本方差 VARIANCE(expr) 方差 REGR_ xxxx(expr, expr) 线性回归函数 REGR_SLOPE:返回斜率,等于COVAR_POP
6、(expr1, expr2) / VAR_POP(expr2)REGR_INTERCEPT:返回回归线的y截距,等于AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)REGR_COUNT:返回用于填充回归线的非空数字对的数目REGR_R2:返回回归线的决定系数,计算式为:If VAR_POP(expr2) = 0 then return NULLIf VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 then return 1If VAR_POP(expr1) 0 and VAR_POP(expr2 != 0
7、 then return POWER(CORR(expr1,expr),2)REGR_AVGX:计算回归线的自变量(expr2)的平均值,去掉了空对(expr1, expr2)后,等于AVG(expr2)REGR_AVGY:计算回归线的应变量(expr1)的平均值,去掉了空对(expr1, expr2)后,等于AVG(expr1)REGR_SXX: 返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)REGR_SYY: 返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)REGR_SXY: 返回值等于REGR_CO
8、UNT(expr1, expr2) * COVAR_POP(expr1, expr2)首先:创建表及接入测试数据create table students(id number(15,0),area varchar2(10),stu_type varchar2(2),score number(20,2);insert into students values(1, 111, g, 80 );insert into students values(1, 111, j, 80 );insert into students values(1, 222, g, 89 );insert into stud
9、ents values(1, 222, g, 68 );insert into students values(2, 111, g, 80 );insert into students values(2, 111, j, 70 );insert into students values(2, 222, g, 60 );insert into students values(2, 222, j, 65 );insert into students values(3, 111, g, 75 );insert into students values(3, 111, j, 58 );insert i
10、nto students values(3, 222, g, 58 );insert into students values(3, 222, j, 90 );insert into students values(4, 111, g, 89 );insert into students values(4, 111, j, 90 );insert into students values(4, 222, g, 90 );insert into students values(4, 222, j, 89 );commit;二、具体应用:1、分组求和:1.2. GROUP BY子句 1.2.1.
11、GROUPING SETSselect id,area,stu_type,sum(score) score from studentsgroup by grouping sets(id,area,stu_type),(id,area),id)order by id,area,stu_type;/*-理解grouping setsselect a, b, c, sum( d ) from tgroup by grouping sets ( a, b, c )等效于select * from (select a, null, null, sum( d ) from t group by aunio
12、n allselect null, b, null, sum( d ) from t group by b union allselect null, null, c, sum( d ) from t group by c )*/1.2.2. ROLLUPselect id,area,stu_type,sum(score) score from studentsgroup by rollup(id,area,stu_type)order by id,area,stu_type;1.2.3. rollupselect a, b, c, sum( d )from tgroup by rollup(
13、a, b, c);等效于select * from (select a, b, c, sum( d ) from t group by a, b, c union allselect a, b, null, sum( d ) from t group by a, bunion allselect a, null, null, sum( d ) from t group by aunion allselect null, null, null, sum( d ) from t)*/1.2.4. CUBEselect id,area,stu_type,sum(score) score from s
14、tudentsgroup by cube(id,area,stu_type)order by id,area,stu_type;/*-理解cubeselect a, b, c, sum( d ) from tgroup by cube( a, b, c)等效于select a, b, c, sum( d ) from tgroup by grouping sets( ( a, b, c ), ( a, b ), ( a ), ( b, c ), ( b ), ( a, c ), ( c ), () )*/1.2.5. GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会
15、出现null,如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/select decode(grouping(id),1,all id,id) id,decode(grouping(area),1,all area,to_char(area) area,decode(grouping(stu_type),1,all_stu_type,stu_type) stu_type,sum(score) scorefrom studentsgroup by cube(id,area,stu_type)order by id,area,stu_type; 1.3. OVER()
16、函数的使用1.3.1. 统计名次1.3.1.1. DENSE_RANK(),允许并列名次、名次不间断,如122344456将score按ID分组排名:dense_rank() over(partition by id order by score desc)将score不分组排名:dense_rank() over(order by score desc)select id,area,score,dense_rank() over(partition by id order by score desc) 分组id排序,dense_rank() over(order by score desc)
17、 不分组排序from students order by id,area;1.3.1.2. ROW_NUMBER(),不允许并列名次、相同值名次不重复,结果如123456将score按ID分组排名:row_number() over(partition by id order by score desc)将score不分组排名:row_number() over(order by score desc)select id,area,score,row_number() over(partition by id order by score desc) 分组id排序,row_number() o
18、ver(order by score desc) 不分组排序from students order by id,area;1.3.1.3. rank(),允许并列名次、复制名次自动空缺,结果如12245558将score按ID分组排名:rank() over(partition by id order by score desc)将score不分组排名:rank() over(order by score desc)select id,area,score,rank() over(partition by id order by score desc) 分组id排序,rank() over(o
19、rder by score desc) 不分组排序from students order by id,area;1.3.1.4. cume_dist(),名次分析-最大排名/总个数 函数:cume_dist() over(order by id)select id,area,score,cume_dist() over(order by id) a, -按ID最大排名/总个数 cume_dist() over(partition by id order by score desc) b, -ID分组中,scroe最大排名值/本组总个数row_number() over (order by id
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 完整 完全 函数 全套 汇编 分析 聚合 转换 日期 字符 数值 其他
链接地址:https://www.deliwenku.com/p-3009506.html
限制150内