管理信息化ORACLE总结归纳Oracle常用函数的总结

管理信息化ORACLE总结归纳Oracle常用函数的总结SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL>selectascii(A)A,ascii(a)a,ascii(0)zero,ascii()spacefromdual;AAZEROSPACE------------------------------------22.CHR给出整数,返回对应的字符;SQL>selectchr(54740)zhao,chr(65)chr65fromdual;ZHC---赵A3.CONCAT连接两个字符串;SQL>selectconcat(010-,8)||转23高乾竞电话fromdual;高乾竞电话----------------010-8转234.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL>selectinitcap(smith)uppfromdual;UPP-----Smith5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1被搜索的字符串C2希望搜索的字符串I搜索的开始位置,默认为1J出现的位置,默认为1SQL>selectinstr(oracletraning,ra,1,2)instringfromdual;INSTRING---------96.LENGTH返回字符串的长度;SQL>selectname,length(name),addr,length(addr),sal,length(to_char(sal))from_tst;NAMELENGTH(NAME)ADDRLENGTH(ADDR)SALLENGTH(TO_CHAR(SAL))---------------------------------------------------------------------------高乾竞3北京市海锭区69999.9977.LOWER返回字符串,并将所有的字符小写SQL>selectlower(AaBbCcDd)AaBbCcDdfromdual;AABBCCDD--------aabbccdd8.UPPER返回字符串,并将所有的字符大写SQL>selectupper(AaBbCcDd)upperfromdual;UPPER--------AABBCCDD9.RPAD和LPAD(粘贴字符)RPAD在列的右边粘贴字符LPAD在列的左边粘贴字符SQL>selectlpad(rpad(gao,10,*),17,*)fromdual;

LPAD(RPAD(GAO,1-----------------*******gao*******不够字符则用*来填满10.LTRIM和RTRIMLTRIM删除左边出现的字符串RTRIM删除右边出现的字符串SQL>selectltrim(rtrim(gaoqianjing,),)fromdual;LTRIM(RTRIM(

-------------gaoqianjing11.SUBSTR(string,start,count)取子字符串,从start开始,取count个SQL>selectsubstr(,3,8)fromdual;SUBSTR(--------812.REPLACE(string,s1,s2)string希望被替换的字符或变量s1被替换的字符串s2要替换的字符串SQL>selectreplace(heloveyou,he,i)fromdual;REPLACE(H----------iloveyou13.SOUNDEX返回一个与给定的字符串读音相同的字符串

SQL>createtabletable1(xmvarchar(8));

SQL>insertintotable1values(weather);

SQL>insertintotable1values(wether);

SQL>insertintotable1values(gao);

SQL>selectxmfromtable1wheresoundex(xm)=soundex(weather);

XM--------weatherwether14.TRIM(sfromstring)LEADING剪掉前面的字符TRAILING剪掉后面的字符如果不指定,默认为空格符15.ABS返回指定值的绝对值SQL>selectabs(100),abs(-100)fromdual;ABS(100)ABS(-100)------------------10010016.ACOS给出反余弦的值SQL>selectacos(-1)fromdual;ACOS(-1)---------3.17.ASIN给出反正弦的值SQL>selectasin(0.5)fromdual;ASIN(0.5)---------.818.ATAN返回一个数字的反正切值SQL>selectatan(1)fromdual;ATAN(1)---------.619.CEIL返回大于或等于给出数字的最小整数

SQL>selectceil(3.)fromdual;

CEIL(3.)---------------420.COS返回一个给定数字的余弦SQL>selectcos(-3.)fromdual;COS(-3.)----------------121.COSH返回一个数字反余弦值SQL>selectcosh(20)fromdual;COSH(20)---------9822.EXP返回一个数字e的n次方根SQL>selectexp(2),exp(1)fromdual;EXP(2)EXP(1)------------------7.2.23.FLOOR对给定的数字取整数SQL>selectfloor(2345.67)fromdual;FLOOR(2345.67)--------------234524.LN返回一个数字的对数值SQL>selectln(1),ln(2),ln(2.)fromdual;LN(1)LN(2)LN(2.)-------------------------------0.8.925.LOG(n1,n2)返回一个以n1为底n2的对数SQL>selectlog(2,1),log(2,4)fromdual;LOG(2,1)LOG(2,4)------------------0226.MOD(n1,n2)返回一个n1除以n2的余数SQL>selectmod(10,3),mod(3,3),mod(2,3)fromdual;MOD(10,3)MOD(3,3)MOD(2,3)---------------------------

10227.POWER返回n1的n2次方根SQL>selectpower(2,10),power(3,3)fromdual;POWER(2,10)POWER(3,3)---------------------10242728.ROUND和TRUNC按照指定的精度进行舍入SQL>selectround(55.5),round(-55.4),trunc(55.5),trunc(-55.5)fromdual;ROUND(55.5)ROUND(-55.4)TRUNC(55.5)TRUNC(-55.5)----------------------------------------------56-5555-5529.SIGN取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQL>selectsign(123),sign(-100),sign(0)fromdual;SIGN(123)SIGN(-100)SIGN(0)----------------------------1-1030.SIN返回一个数字的正弦值SQL>selectsin(1.57079)fromdual;SIN(1.57079)------------

131.SIGH返回双曲正弦的值SQL>selectsin(20),sinh(20)fromdual;SIN(20)SINH(20)------------------.32.SQRT返回数字n的根SQL>selectsqrt(64),sqrt(10)fromdual;SQRT(64)SQRT(10)------------------83.33.TAN返回数字的正切值SQL>selecttan(20),tan(10)fromdual;TAN(20)TAN(10)------------------2..334.TANH返回数字n的双曲正切值SQL>selecttanh(20),tan(20)fromdual;TANH(20)TAN(20)------------------12.35.TRUNC按照指定的精度截取一个数SQL>selecttrunc(124.1666,-2)trunc1,trunc(124.16666,2)fromdual;TRUNC1TRUNC(124.16666,2)---------------------------100124.1636.ADD_MONTHS增加或减去月份SQL>selectto_char(add_months(to_date(199912,yyyymm),2),yyyymm)fromdual;TO_CHA

------200002SQL>selectto_char(add_months(to_date(199912,yyyymm),-2),yyyymm)fromdual;TO_CHA------19991037.LAST_DAY返回日期的最后一天SQL>selectto_char(sysdate,.dd),to_char((sysdate)+1,.dd)fromdual;

TO_CHAR(SYTO_CHAR((S--------------------2004.05.092004.05.10SQL>selectlast_day(sysdate)fromdual;LAST_DAY(S----------31-5月-0438.MONTHS_BETWEEN(date2,date1)给出date2-date1的月份SQL>selectmonths_between(19-12月-1999,19-3月-1999)mon_betweenfromdual;MON_BETWEEN-----------9SQL>selectmonths_between(to_date(2000.05.20,.dd),to_date(2005.05.20,.dd))mon_betwfromdual;MON_BETW----------6039.NEW_TIME(date,this,that)给出在this时区=other时区的日期和时间SQL>selectto_char(sysdate,.ddhh24:mi:ss)bj_time,to_char(new_time

2(sysdate,PDT,GMT),.ddhh24:mi:ss)los_anglesfromdual;

BJ_TIMELOS_ANGLES--------------------------------------2004.05.0911:05:322004.05.0918:05:3240.NEXT_DAY(date,day)给出日期date和星期x之后计算下一个星期的日期SQL>selectnext_day('18-5月-2001','星期五')next_dayfromdual;NEXT_DAY----------25-5月-0141.SYSDATE用来得到系统的当前日期SQL>selectto_char(sysdate,dd-mm-yyyyday)fromdual;

TO_CHAR(SYSDATE,-----------------09-05-2004星期日trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒SQL>selectto_char(trunc(sysdate,hh),.ddhh24:mi:ss)hh,2to_char(trunc(sysdate,mi),.ddhh24:mi:ss)hhmmfromdual;HHHHMM--------------------------------------2004.05.0911:00:002004.05.0911:17:0042.CHARTOROWID将字符数据类型转换为ROWID类型SQL>selectrowid,rowidtochar(rowid),enamefrom;ROWIDROWIDTOCHAR(ROWID)ENAME----------------------------------------------AAAAfKAACAAAAEqAAAAAAAfKAACAAAAEqAAASMITHAAAAfKAACAAAAEqAABAAAAfKAACAAAAEqAABALLENAAAAfKAACAAAAEqAACAAAAfKAACAAAAEqAACWARDAAAAfKAACAAAAEqAADAAAAfKAACAAAAEqAADJONES43.CONVERT(c,dset,sset)将源字符串sset从一个语言字符集转换到另一个目的dset字符集SQL>selectconvert(strutz,we8hp,f7dec)"conversion"fromdual;conver------strutz44.HEXTORAW将一个十六进制构成的字符串转换为二进制45.RAWTOHEXT将一个二进制构成的字符串转换为十六进制46.ROWIDTOCHAR将ROWID数据类型转换为字符类型47.TO_CHAR(date,format)SQL>selectto_char(sysdate,yyyyddhh24:mi:ss)fromdual;TO_CHAR(SYSDATE,YY-------------------

1:14:4148.TO_DATE(string,format)将字符串转化为ORACLE中的一个日期49.TO_MULTI_BYTE将字符串中的单字节字符转化为多字节字符SQL>selectto_multi_byte(高)fromdual;TO--高50.TO_NUMBER将给出的字符转换为数字SQL>selectto_number(1999)yearfromdual;YEAR---------199951.BFILENAME(dir,file)指定一个外部二进制文件SQL>insertintofile_tb1values(bfilename(lob_dir1,image1.gif));52.CONVERT(x,desc,source)将x字段或变量的源source转换为descSQL>selectsid,serial#,username,decode(mand,20,none,32,insert,43,5select,66,update,77,delete,88,drop,9other)cmdfromv$sessionwheretype!=background;SIDSERIAL#USERNAMECMD------------------------------------------------------11none21none31none41none51none61none71275none81275none920GAOselect1040GAOnone53.DUMP(s,fmt,start,length)DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值SQL>colglobal_namefora30SQL>coldump_stringfora50SQL>setlin200SQL>selectglobal_name,dump(global_name,1017,8,5)dump_stringfromglobal_name;GLOBAL_NAMEDUMP_STRING--------------------------------------------------------------------------------Typ=1Len=12CharacterSet=ZHS16GBK:W,O,R,L,D54.EMPTY_BLOB()和EMPTY_CLOB()这两个函数都是用来对大数据类型字段进行初始化操作的函数55.GREATEST返回一组表达式中的最大值,即比较字符的编码大小.SQL>selectgreatest(AA,AB,AC)fromdual;GR--ACSQL>selectgreatest(啊,安,天)fromdual;GR--天56.LEAST返回一组表达式中的最小值SQL>selectleast(啊,安,天)fromdual;LE--啊57.UID返回标识当前用户的唯一整数SQL>showuserUSER为"GAO"SQL>selectusername,user_idfromdba_userswhereuser_id=uid;USERNAMEUSER_ID---------------------------------------GAO2558.USER返回当前用户的名字SQL>selectuserfromdual;USER------------------------------GAO59.USEREVN返回当前用户环境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZEISDBA查看当前用户是否是DBA如果是则返回trueSQL>selectuserenv(isdba)fromdual;USEREN------FALSESQL>selectuserenv(isdba)fromdual;USEREN

------TRUESESSION返回会话标志SQL>selectuserenv(sessionid)fromdual;USERENV(SESSIONID)--------------------152ENTRYID返回会话人口标志SQL>selectuserenv(entryid)fromdual;USERENV(ENTRYID)------------------0INSTANCE返回当前INSTANCE的标志SQL>selectuserenv(instance)fromdual;USERENV(INSTANCE)-------------------1LANGUAGE返回当前环境变量SQL>selectuserenv(language)fromdual;USERENV(LANGUAGE)----------------------------------------------------SIMPLIFIEDCHINESE_16GBKLANG返回当前环境的语言的缩写SQL>selectuserenv(lang)fromdual;USERENV(LANG)----------------------------------------------------ZHSTERMINAL返回用户的终端或机器的标志SQL>selectuserenv(terminal)fromdual;USERENV(TERMINA----------------GAOVSIZE(X)返回X的大小(字节)数SQL>selectvsize(user),userfromdual;VSIZE(USER)USER-----------------------------------------

6SYSTEM60.AVG(DISTINCT|ALL)all表示对所有的值求平均值,distinct只对不同的值求平均值SQLWKS>createtabletable3(xmvarchar(8),salnumber(7,2));语句已处理。SQLWKS>insertintotable3values(gao,1111.11);SQLWKS>insertintotable3values(gao,1111.11);SQLWKS>insertintotable3values(zhu,5555.55);SQLWKS>mit;SQL>selectavg(distinctsal)from3;AVG(DISTINCTSAL)----------------3333.33SQL>selectavg(allsal)from3;AVG(ALLSAL)-----------2592.5961.MAX(DISTINCT|ALL)求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次SQL>selectmax(distinctsal)from;MAX(DISTINCTSAL)----------------500062.MIN(DISTINCT|ALL)求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次SQL>selectmin(allsal)from3;MIN(ALLSAL)-----------1111.1163.STDDEV(distinct|all)求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差SQL>selectstddev(sal)from;STDDEV(SAL)-----------1182.5032SQL>selectstddev(distinctsal)from;STDDEV(DISTINCTSAL)-------------------1229.95164.VARIANCE(DISTINCT|ALL)求协方差SQL>selectvariance(sal)from;VARIANCE(SAL)-------------.965.GROUPBY主要用来对一组数进行统计SQL>selectdeptno,count(*),sum(sal)fromgroupbydeptno;DEPTNOCOUNT(*)SUM(SAL)---------------------------566.HAVING对分组统计再加限制条件SQL>selectdeptno,count(*),sum(sal)fromgroupbydeptnohavingcount(*)>=5;DEPTNOCOUNT(*)SUM(SAL)---------------------------5SQL>selectdeptno,count(*),sum(sal)fromhavingcount(*)>=5groupbydeptno;DEPTNOCOUNT(*)SUM(SAL)---------------------------567.ORDERBY用于对查询到的结果进行排序输出SQL>selectdeptno,ename,salfromorderbydeptno,saldesc;DEPTNOENAMESAL----------------------------10KING500010CLARK245010MILLER130020SCOTT300020FORD300020JONES297520ADAMS110020SMITH80030BLAKE285030ALLEN160030TURNER150030WARD125030MARTIN125030JAMES95068.pl/sql中的case语句select(casewhenDUMMY='X'then0else1end)asflagfromdual;case的第1种用法：casecolwhen'a'then1when'b'then2else0end这种用法跟decode一样没什么区别

case的第2种用法：casewhenscore<60then'd'whenscore>=60andscore<70then'c'whenscore>=70andscore<80then'b'else'a'end69.NVL(expr1,expr2)NVL(expr1,expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致NVL2(expr1,expr2,expr3)->expr1不为NULLexpr2NULLexpr3expr2和

expr3类型不同的话，expr3会转换为expr2的类型NULLIF(expr1,expr2)->相等返回NULL，不等返回expr1Oracle分析函数参考手册=============================================作者:xsb([url].net)[/url]发表于:2006.03.0112:22分类:DW&BI出处：.net419/33028---------------------------------------------------------------Oracle从8.1.6的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。常用的分析函数如下所列:row_number()over(partitionby...orderby...)rank()over(partitionby...orderby...)dense_rank()over(partitionby...orderby...)count()over(partitionby...orderby...)max()over(partitionby...orderby...)min()over(partitionby...orderby...)sum()over(partitionby...orderby...)avg()over(partitionby...orderby...)first_value()over(partitionby...orderby...)last_value()over(partitionby...orderby...)lag()over(partitionby...orderby...)lead()over(partitionby...orderby...)下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOMEschema/human_resources/hr_来创建。除本文内容外，你还可参考：ROLLUP与CUBE[url].net419/29159[/url]分析函数使用例子介绍：[url].net419/44634[/url]本文如果未指明，缺省是在HR用户下运行例子。开窗函数的的理解：化，举例如下：over（orderbysalary）按照salary排序进行累计，orderby是个默认的开窗函数over（partitionbydeptno）按照部门分区over（orderbysalaryrangebetween50precedingand150following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150over（orderbysalaryrowsbetween50precedingand150following）每行对应的数据窗口是之前50行，之后150行over（orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing）每行对应的数据窗口是从第一行到最后一行，等效：over（orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing）主要参考资料：《expertone-on-one》TomKyte《Oracle9iSQLReference》第6章ohwww2007-3-1209:19续70。AVG功能描述：用于计算一个组和数据窗口内表达式的平均值。SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；SELECTmanager_id,last_name,hire_date,salary,AVG(salary)OVER(PARTITIONBYmanager_idORDERBYhire_dateROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASc_mavgFROMemployees;MANAGER_IDLAST_NAMEHIRE_DATESALARYC_MAVG

----------------------------------------------------------------

100Kochhar21-SEP-0100DeHaan13-JAN-0100Raphaely07-DEC-6.6667100Kaufling01-MAY-.3333100Hartstein17-FEB-.33333100Weiss18-JUL-.6667100Russell01-OCT-3.333371。CORR功能描述：返回一对表达式的相关系数，它是如下的缩写：COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度上一个变量的值可由其它的值进行预测。通过返回一个-1~1之间的一个数,相关系数给出了关联的强度，0表示不相关。SAMPLE1998年月销售收入和月单位销售的关系的累积系数（本例在SH用户下运行）SELECTt.calendar_month_number,CORR(SUM(s.amount_sold),SUM(s.quantity_sold))OVER(ORDERBYt.calendar_month_number)asCUM_CORRFROMsaless,timestWHEREs.time_id=t.time_idANDcalendar_year=1998GROUPBYt.calendar_month_numberORDERBYt.calendar_month_number;CALENDAR_MONTH_NUMBERCUM_CORR-------------------------------1213.824.755.046.287.038.999.5610.111.6512.6272。COVAR_POP功能描述：返回一对表达式的总体协方差。SAMPLE：下例CUM_COVP返回定价和最小产品价格的累积总体协方差SELECTproduct_id,supplier_id,COVAR_POP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVP,COVAR_SAMP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVSFROMproduct_informationp

WHEREcategory_id=29ORDERBYproduct_id,supplier_id;PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS-----------------------------------------473.252946.5702.54.16667926.252568.33333591.41989.25512.51815475.21.97619..73。COVAR_SAMP功能描述：返回一对表达式的样本协方差SAMPLE：下例CUM_COVS返回定价和最小产品价格的累积样本协方差SELECTproduct_id,supplier_id,COVAR_POP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVP,COVAR_SAMP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVSFROMproduct_informationpWHEREcategory_id=29ORDERBYproduct_id,supplier_id;PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS-----------------------------------------473.252946.5702.54.16667926.252568.33333591.41989.25512.51815475.21.9761974。COUNT功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水例如，Philtanker的薪水22002200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数SELECTlast_name,salary,COUNT(*)OVER()AScnt1,COUNT(*)OVER(ORDERBYsalary)AScnt2,COUNT(*)OVER(ORDERBYsalaryRANGEBETWEEN50PRECEDINGAND150FOLLOWING)AScnt3FROMemployees;LAST_NAMESALARYCNT1CNT2CNT3-----------------------------------------------------------------Olson13Markle32Philtanker32Landry58Gee58ColmenaresPatel..75。CUME_DIST功能描述：计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。例如，在一个3行的组中，返回的累计分布值为1/3、2/3、3/3SAMPLE：下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比SELECTjob_id,last_name,salary,CUME_DIST()OVER(PARTITIONBYjob_idORDERBYsalary)AScume_distFROMemployeesWHEREjob_idLIKE'PU%';JOB_IDLAST_NAMESALARYCUME_DIST-------------------------------------------------------PU_CLERKColmenares2500.2PU_CLERKHimuro2600.4PU_CLERKTobias2800.6PU_CLERKBaida2900.8PU_CLERKKhoo31001PU_MANRaphaely11000176。DENSE_RANKORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相ORDERBY序列从1ORDERBY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）SELECTd.department_id,e.last_name,e.salary,DENSE_RANK()

OVER(PARTITIONBYe.department_idORDERBYe.salary)asdrank

FROMemployeese,departmentsdWHEREe.department_id=d.department_id

ANDd.department_idIN('60','90');

DEPARTMENT_IDLAST_NAMESALARYDRANK

----------------------------------------------------------

60Lorentz4200160Austin4800260Pataballa4800260Ernst6000360Hunold9000490Kochhar17000190DeHaan17000190King24000277。FIRST功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录SAMPLEDENSE_RANKmission_pctFIRST取出佣金

最低的对应的所有行，然后前面的MAXLAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值SELECTlast_name,department_id,salary,MIN(salary)KEEP(DENSE_RANKFIRSTORDERBYmission_pct)OVER(PARTITIONBYdepartment_id)"Worst",MAX(salary)KEEP(DENSE_RANKLASTORDERBYmission_pct)OVER(PARTITIONBYdepartment_id)"Best"FROMemployeesWHEREdepartment_idin(20,80)ORDERBYdepartment_id,salary;LAST_NAMEDEPARTMENT_IDSALARYWorstBest--------------------------------------------------------------------FayHartstein

KumarBandaJohnson

AndeLeeTuvault

Sewall

Marvins

Bates

...78。FIRST_VALUE功能描述：返回组中数据窗口的第一个值。SAMPLE的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字SELECTdepartment_id,last_name,salary,FIRST_VALUE(last_name)OVER(PARTITIONBYdepartment_idORDERBYsalaryASC)ASlowest_salFROMemployeesWHEREdepartment_idin(20,30);DEPARTMENT_IDLAST_NAMESALARYLOWEST_SAL--------------------------------------------------------------20Fay6000Fay20Hartstein13000Fay30Colmenares2500Colmenares30Himuro2600Colmenares30Tobias2800Colmenares30Baida2900Colmenares30Khoo3100Colmenares30Raphaely11000Colmenares79。LAG以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEADSAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值SELECTlast_name,hire_date,salary,LAG(salary,1,0)OVER(ORDERBYhire_date)ASprev_salFROMemployeesWHEREjob_id='PU_CLERK';LAST_NAMEHIRE_DATESALARYPREV_SAL

-------------------------------------------------------

Khoo18-5月-Tobias24-7月-100Baida24-12月-800Himuro15-11月-900Colmenares10-8月-60080。LAST功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金mission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAXLAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值SELECTlast_name,department_id,salary,MIN(salary)KEEP(DENSE_RANKFIRSTORDERBYmission_pct)OVER(PARTITIONBYdepartment_id)"Worst",MAX(salary)KEEP(DENSE_RANKLASTORDERBYmission_pct)OVER(PARTITIONBYdepartment_id)"Best"FROMemployeesWHEREdepartment_idin(20,80)ORDERBYdepartment_id,salary;LAST_NAMEDEPARTMENT_IDSALARYWorstBest--------------------------------------------------------------------FayHartsteinKumarBandaJohnsonAndeLeeTuvaultSewallMarvinsBates.81。LAST_VALUE功能描述：返回组中数据窗口的最后一个值。SAMPLE水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字SELECTdepartment_id,last_name,salary,LAST_VALUE(last_name)OVER(PARTITIONBYdepartment_idORDERBYsalary)AShighest_salFROMemployeesWHEREdepartment_idin(20,30);DEPARTMENT_IDLAST_NAMESALARYHIGHEST_SAL------------------------------------------------------------20Fay6000Fay20Hartstein13000Hartstein30Colmenares2500Colmenares30Himuro2600Himuro30Tobias2800Tobias30Baida2900Baida30Khoo3100Khoo30Raphaely11000Raphaely82。LEAD功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）SAMPLE：下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值SELECTlast_name,hire_date,LEAD(hire_date,1)OVER(ORDERBYhire_date)AS"NextHired"FROMemployeesWHEREdepartment_id=30;LAST_NAMEHIRE_DATENextHired-------------------------------------------Raphaely07-DEC-9418-MAY-95Khoo18-MAY-9524-JUL-97Tobias24-JUL-9724-DEC-97Baida24-DEC-9715-NOV-98Himuro15-NOV-9810-AUG-99Colmenares10-AUG-9983。MAX功能描述：在一个组中的数据窗口中查找表达式的最大值。

SAMPLE：下面例子中dept_max返回当前行所在部门的最大薪水值

SELECTdepartment_id,last_name,salary,MAX(salary)OVER(PARTITIONBYdepartment_id)ASdept_max

FROMemployeesWHEREdepartment_idin(10,20,30);DEPARTMENT_IDLAST_NAMESALARYDEPT_MAX----------------------------------------------------------10Whalen020Hartstein00020Fay0030Raphaely00030Khoo0030Baida0030Tobias0030Himuro0030Colmenares0084。MIN功能描述：在一个组中的数据窗口中查找表达式的最小值。SAMPLE：下面例子中dept_min返回当前行所在部门的最小薪水值SELECTdepartment_id,last_name,salary,MIN(salary)OVER(PARTITIONBYdepartment_id)ASdept_minFROMemployeesWHEREdepartment_idin(10,20,30);DEPARTMENT_IDLAST_NAMESALARYDEPT_MIN----------------------------------------------------------10Whalen020Hartstein0020Fay030Raphaely0030Khoo030Baida030Tobias030Himuro030Colmenares085。NTILE"表达式"的散列表示，例如，如果表达式=4，则给组中的每一行分

配一个数（从1到420行，则给前5行分配1，给下5行分配2等等。如果

组的基数不能由表达式值平均分开，则对这些行进行分配时，组中就没有任何percentile

的行数比其它percentile的行数超过一行，最低的percentile是那些拥有额外行的

percentile=4=21percentile=1的有5percentile=2的有5行等等。SAMPLE：下例中把6行数据分为4份SELECTlast_name,salary,NTILE(4)OVER(ORDERBYsalaryDESC)ASquartileFROMemployeesWHEREdepartment_id=100;LAST_NAMESALARYQUARTILE---------------------------------------------Greenberg120001

Faviet90001Chen82002Urman78002Sciarra77003Popp6900486。PERCENT_RANK功能描述：和CUME_DIST（累积分配）函数类似，对于一个组中给定的行来说，在计算那行的序号时，先减1，然后除以n-1（n为组中所有的行数）。该函数总是返回0～1（包括1）之间的数。SAMPLEKhoo的salary为2900，则pr值为0.6，因为RANK函数对于等值的返回序列值是一样的SELECTdepartment_id,last_name,salary,PERCENT_RANK()OVER(PARTITIONBYdepartment_idORDERBYsalary)ASprFROMemployeesWHEREdepartment_id<50ORDERBYdepartment_id,salary;DEPARTMENT_IDLAST_NAMESALARYPR----------------------------------------------------------

10Whalen4400020Fay6000020Hartstein13000130Colmenares2500030Himuro26000.230Tobias28000.430Baida29000.630Khoo31000.830Raphaely11000140Mavris6500087。PERCENTILE_CONT功能描述：返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数PERCENT_RANK，如果没有正好对应的数据值，就通过下面算法来得到值：RN=1+(P*(N-1))其中P是输入的分布百分比值，N是组内的行数CRN=CEIL(RN)FRN=FLOOR(RN)if(CRN=FRN=RN)then(valueofexpressionfromrowatRN)else(CRN-RN)*(valueofexpressionforrowatFRN)+(RN-FRN)*(valueofexpressionforrowatCRN)注意：本函数与PERCENTILE_DISC的区别在找不到对应的分布值时返回的替代值的计算方法不同SAMPLE：在下例中，对于部门60的Percentile_Cont值计算如下：P=0.7N=5RN=1+(P*(N-1)=1+(0.7*(5-1))=3.8CRN=CEIL(3.8)=4FRN=FLOOR(3.8)=3（4-3.8）*4800+(3.8-3)*6000=5760SELECTlast_name,salary,department_id,PERCENTILE_CONT(0.7)WITHINGROUP(ORDERBYsalary)OVER(PARTITIONBYdepartment_id)"Percentile_Cont",PERCENT_RANK()OVER(PARTITIONBYdepartment_idORDERBYsalary)"Percent_Rank"FROMemployeesWHEREdepartment_idIN(30,60);LAST_NAMESALARYDEPARTMENT_IDPercentile_ContPercent_Rank---------------------------------------------------------------------------ColmenaresHimuro.2Tobias.4Baida.6Khoo.8Raphaely1LorentzAustin.25Pataballa.25Ernst.75Hunold88。PERCENTILE_DISCCUME_DIST，如果没有正好对应的数据值，就取大于该分布值的下一个值。PERCENTILE_CONT的区别在找不到对应的分布值时返回的替代值的计算方法不同SAMPLE：下例中0.7的分布值在部门30中没有对应的Cume_Dist值，所以就取下一个分布值0.3所对应的SALARY来替代SELECTlast_name,salary,department_id,PERCENTILE_DISC(0.7)WITHINGROUP(ORDERBYsalary)OVER(PARTITIONBYdepartment_id)"Percentile_Disc",CUME_DIST()OVER(PARTITIONBYdepartment_idORDERBYsalary)"Cume_Dist"FROMemployeesWHEREdepartment_idin(30,60);LAST_NAMESALARYDEPARTMENT_IDPercentile_DiscCume_Dist-------------------------------------------------------------------------Colmenares100.67Himuro100.33Tobias100.5Baida100.67Khoo100.33Raphaely1Lorentz000.2Austin000.6Pataballa000.6Ernst000.8Hunold89。RANKORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相

对位置。组内的数据按ORDERBY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1ORDERBY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）SELECTd.department_id,e.last_name,e.salary,RANK()OVER(PARTITIONBYe.department_idORDERBYe.salary)asdrank

FROMemployeese,departmentsdWHEREe.department_id=d.department_idANDd.department_idIN('60','90');DEPARTMENT_IDLAST_NAMESALARYDRANK----------------------------------------------------------60Lorentz4200160Austin4800260Pataballa4800260Ernst6000460Hunold9000590Kochhar17000190DeHaan17000190King24000390。RATIO_TO_REPORT功能描述：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。SAMPLE：下例计算每个员工的工资占该类员工总工资的百分比

SELECTlast_name,salary,RATIO_TO_REPORT(salary)OVER()ASrr

FROMemployeesWHEREjob_id='PU_CLERK';LAST_NAMESALARYRR---------------------------------------------

Khoo3100.83Baida2900.94Tobias2800.49Himuro2600.6Colmenares2500.1591。REGR_(LinearRegression)Functions功能描述：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用。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)（下面的例子都是在SH用户下完成的）SAMPLE1：下例计算1998年最后三个星期中两种产品（260和270）在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距SELECTt.fiscal_month_number"Month",t.day_number_in_month"Day",REGR_SLOPE(s.amount_sold,s.quantity_sold)OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)ASCUM_SLOPE,REGR_INTERCEPT(s.amount_sold,s.quantity_sold)OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)ASCUM_ICPTFROMsaless,timestWHEREs.time_id=t.time_idANDd_idIN(270,260)ANDt.fiscal_year=1998ANDt.fiscal_week_numberIN(50,51,52)ANDt.day_number_in_weekIN(6,7)ORDERBYt.fiscal_month_desc,t.day_number_in_month;MonthDayCUM_SLOPECUM_ICPT----------------------------------------1212-6818721212-6818721213-20.254.367351213-20.254.367351219-18.287122062.125.28655122062.125.28655122062.125.28655122062.125.28655122667.58.122667.58.122737.284.958221122737.284.958221122737.284.958221SAMPLE2：下例计算1998年4月每天的累积交易数量SELECTUNIQUEt.day_number_in_month,REGR_COUNT(s.amount_sold,s.quantity_sold)OVER(PARTITIONBYt.fiscal_month_numberORDERBYt.day_number_in_month)"Regr_Count"FROMsaless,timestWHEREs.time_id=t.time_idANDt.fiscal_year=1998ANDt.fiscal_month_number=4;DAY_NUMBER_IN_MONTHRegr_Count-----------------------------1825216503247543300.SAMPLE3：下例计算1998年每月销售量中已开发票数量和总数量的累积回归线决定系数SELECTt.fiscal_month_number,REGR_R2(SUM(s.amount_sold),SUM(s.quantity_sold))OVER(ORDERBYt.fiscal_month_number)"Regr_R2"FROMsaless,timestWHEREs.time_id=t.time_idANDt.fiscal_year=1998GROUPBYt.fiscal_month_numberORDERBYt.fiscal_month_number;FISCAL_MONTH_NUMBERRegr_R2-----------------------------1213.844.725.659.1810.7511.1512.89SAMPLE4：下例计算1998年12月最后两周产品260的销售量中已开发票数量和总数量的累积平均值SELECTt.day_number_in_month,REGR_AVGY(s.amount_sold,s.quantity_sold)OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)"Regr_AvgY",REGR_AVGX(s.amount_sold,s.quantity_sold)

OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)

"Regr_AvgX"FROMsaless,timestWHEREs.time_id=t.time_idANDd_id=260ANDt.fiscal_month_desc='1998-12'ANDt.fiscal_week_numberIN(51,52)ORDERBYt.day_number_in_month;DAY_NUMBER_IN_MONTHRegr_AvgYRegr_AvgX---------------------------------------.516777.621.618642.7.18642.7.20589.516.375.22592.6.22592.6.24553.5.24553.5..527578.416.SAMPLE5260和270在1998年2月周末销售量中已开发票数量和总数量的累

积REGR_SXY,REGR_SXX,andREGR_SYY统计值SELECTt.day_number_in_month,REGR_SXY(s.amount_sold,s.quantity_sold)OVER(ORDERBYt.fiscal_year,t.fiscal_month_desc)"Regr_sxy",

REGR_SYY(s.amount_sold,s.quantity_sold)OVER(ORDERBYt.fiscal_year,t.fiscal_month_desc)"Regr_syy",

REGR_SXX(s.amount_sold,s.quantity_sold)OVER(ORDERBYt.fiscal_year,t.fiscal_month_desc)"Regr_sxx"

FROMsaless,timestWHEREs.time_id=t.time_idANDprod_idIN(270,260)ANDt.fiscal_month_desc='1998-02'ANDt.day_number_in_weekIN(6,7)ORDERBYt.day_number_in_month;DAY_NUMBER_IN_MONTHRegr_sxyRegr_syyRegr_sxx-------------------------------------------------118870.8.4258.4118870.8.4258.4