oracle最全函数大全(分析函数,聚合函数,转换函数,日期型函数,字符型函数,数值型函数,其他函数

oracle函数大全(分析函数,聚合函数,转换函数,日期型函数,字符型函数,数值型函数,其他函数)oracle函数大全...............................................................................................................1oracle分析函数--SQL*PLUS环境.....................................................................................1oracle10g函数大全--聚合函数..............................................................................19oracle10g函数大全--转换函数..............................................................................23oracle10g函数大全--日期型函数..........................................................................40oracle10g函数大全--字符型函数..........................................................................45oracle10g函数大全--数值型函数..........................................................................54oracle10g函数大全--其他函数..............................................................................57oracle分析函数--SQL*PLUS环境一、总体介绍1.1.分析函数如何工作语法FUNCTION_NAME(参数,…)OVER(PARTITIONBY表达式,…ORDERBY表达式ASCDESCNULLSFIRSTNULLSLASTWINDOWING子句)PARTITION子句ORDERBY子句WINDOWING子句缺省时相当于RANGEUNBOUNDEDPRECEDING1.值域窗(RANGEWINDOW)RANGENPRECEDING仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值–/+N)的所有行,因此与ORDERBY子句有关系。2.行窗(ROWWINDOW)ROWSNPRECEDING选定窗为当前行及之前N行。还可以加上BETWEENAND形式,例如RANGEBETWEENmPRECEDINGANDnFOLLOWING函数AVG(distinctalleXPr)一组或选定窗中表达式的平均值CORR(expr,expr)即COVAR_POP(exp1,exp2)/(STDDEV_POP(expr1)*STDDEV_POP(expr2)),两个表达式的互相关,-1(反相关)~1(正相关),0表示不相关COUNT(distinct*expr)计数COVAR_POP(expr,expr)总体协方差COVAR_SAMP(expr,expr)样本协方差CUME_DIST累积分布,即行在组中的相对位置,返回0~1DENSE_RANK行的相对排序(与ORDERBY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数FIRST_VALUE一个组的第一个值LAG(expr,offset,default)访问之前的行,OFFSET是缺省为1的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行)LAST_VALUE一个组的最后一个值LEAD(expr,offset,default)访问之后的行,OFFSET是缺省为1的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行)MAX(expr)最大值MIN(expr)最小值NTILE(expr)按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1~4的值,而不能等分则多出的部分在值最小的那组PERCENT_RANK类似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(expr1,expr2)/VAR_POP(expr2)REGR_INTERCEPT：返回回归线的y截距，等于AVG(expr1)-REGR_SLOPE(expr1,expr2)*AVG(expr2)REGR_COUNT：返回用于填充回归线的非空数字对的数目REGR_R2：返回回归线的决定系数，计算式为：IfVAR_POP(expr2)=0thenreturnNULLIfVAR_POP(expr1)=0andVAR_POP(expr2)!=0thenreturn1IfVAR_POP(expr1)0andVAR_POP(expr2!=0thenreturnPOWER(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_COUNT(expr1,expr2)*COVAR_POP(expr1,expr2)首先：创建表及接入测试数据createtablestudents(idnumber(15,0),areavarchar2(10),stu_typevarchar2(2),scorenumber(20,2));insertintostudentsvalues(1,'111','g',80);insertintostudentsvalues(1,'111','j',80);insertintostudentsvalues(1,'222','g',89);insertintostudentsvalues(1,'222','g',68);insertintostudentsvalues(2,'111','g',80);insertintostudentsvalues(2,'111','j',70);insertintostudentsvalues(2,'222','g',60);insertintostudentsvalues(2,'222','j',65);insertintostudentsvalues(3,'111','g',75);insertintostudentsvalues(3,'111','j',58);insertintostudentsvalues(3,'222','g',58);insertintostudentsvalues(3,'222','j',90);insertintostudentsvalues(4,'111','g',89);insertintostudentsvalues(4,'111','j',90);insertintostudentsvalues(4,'222','g',90);insertintostudentsvalues(4,'222','j',89);commit;二、具体应用：1、分组求和：1.2.GROUPBY子句1.2.1.GROUPINGSETSselectid,area,stu_type,sum(score)scorefromstudentsgroupbygroupingsets((id,area,stu_type),(id,area),id)orderbyid,area,stu_type;/*--------理解groupingsetsselecta,b,c,sum(d)fromtgroupbygroupingsets(a,b,c)等效于select*from(selecta,null,null,sum(d)fromtgroupbyaunionallselectnull,b,null,sum(d)fromtgroupbybunionallselectnull,null,c,sum(d)fromtgroupbyc)*/1.2.2.ROLLUPselectid,area,stu_type,sum(score)scorefromstudentsgroupbyrollup(id,area,stu_type)orderbyid,area,stu_type;1.2.3.rollupselecta,b,c,sum(d)fromtgroupbyrollup(a,b,c);等效于select*from(selecta,b,c,sum(d)fromtgroupbya,b,cunionallselecta,b,null,sum(d)fromtgroupbya,bunionallselecta,null,null,sum(d)fromtgroupbyaunionallselectnull,null,null,sum(d)fromt)*/1.2.4.CUBEselectid,area,stu_type,sum(score)scorefromstudentsgroupbycube(id,area,stu_type)orderbyid,area,stu_type;/*--------理解cubeselecta,b,c,sum(d)fromtgroupbycube(a,b,c)等效于selecta,b,c,sum(d)fromtgroupbygroupingsets((a,b,c),(a,b),(a),(b,c),(b),(a,c),(c),())*/1.2.5.GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/selectdecode(grouping(id),1,'allid',id)id,decode(grouping(area),1,'allarea',to_char(area))area,decode(grouping(stu_type),1,'all_stu_type',stu_type)stu_type,sum(score)scorefromstudentsgroupbycube(id,area,stu_type)orderbyid,area,stu_type;1.3.OVER()函数的使用1.3.1.统计名次1.3.1.1.DENSE_RANK()，允许并列名次、名次不间断，如122344456将score按ID分组排名：dense_rank()over(partitionbyidorderbyscoredesc)将score