ORACLE分析函数.ppt

OracleAnalyticFunctionsInPractice,Bydingjun1232013.06,主要内容,甲：我有个SQL，你能帮我用分析函数改写下吗？乙：相关列有索引吗？甲：owner有索引，选择性不错，我想用分析函数改写看看？乙：哦，知道了，这是典型的top-n查询。,SELECTowner,object_typeFROMdemo2WHEREowner=DINGJUN123ANDtrunc(created,dd)=(SELECTMAX(trunc(created,dd)FROMdemo2WHEREowner=DINGJUN123),SELECTowner,object_typeFROM(SELECTowner,object_type,dense_rank()over(ORDERBYtrunc(created,dd)DESC)rnFROMdemo2WHEREowner=DINGJUN123)WHERErn=1,分析函数作用,欢迎进入今天的分析函数学习之旅！,子查询方法,总行数：667827返回9行原始SQL:逻辑读848，COST：1103优点：最容易想到缺点：多次访问表或索引,分析函数方法,分析SQL:逻辑读423，COST：693优点：减少表或索引的访问次数，逻辑读和COST是常规方法的一半，SQL简单缺点：需要排序操作,进一步优化,建立owner,trunc(created,dd)desc复合索引作用对于原始SQL子查询可以快速扫描，分析函数消除排序优化后原始SQL逻辑读/COST：111/171,分析函数:6/767,构建合适索引，消除排序，是一种重要的SQL优化手段,-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|202|*1|TABLEACCESSBYINDEXROWID|DEMO2|202|*2|INDEXRANGESCAN|IDX_DEMO2|20237|3|SORTAGGREGATE|1|4|TABLEACCESSBYINDEXROWID|DEMO2|20237|*5|INDEXRANGESCAN|IDX_DEMO2|20237|PredicateInformation(identifiedbyoperationid):-1-filter(TRUNC(INTERNAL_FUNCTION(CREATED),fmdd)=(SELECTMAX(TRUNC(INTERNAL_FUNCTION(CREATED),fmdd)FROMDEMO2DEMO2WHEREOWNER=DINGJUN123)2-access(OWNER=DINGJUN123)5-access(OWNER=DINGJUN123),-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|20237|*1|VIEW|20237|*2|WINDOWSORTPUSHEDRANK|20237|3|TABLEACCESSBYINDEXROWID|DEMO2|20237|*4|INDEXRANGESCAN|IDX_DEMO2|20237|-PredicateInformation(identifiedbyoperationid):-1-filter(RN=1)2-filter(DENSE_RANK()OVER(ORDERBYTRUNC(INTERNAL_FUNCTION(CREATED),fmdd)DESC)=1)4-access(OWNER=DINGJUN123),分析函数作用,分析函数的作用总结,-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|213|1|TABLEACCESSBYINDEXROWID|DEMO2|213|*2|INDEXRANGESCAN|IDX_DEMO2|1|3|SORTAGGREGATE|1|*4|INDEXRANGESCAN|IDX_DEMO2|21334|-,SELECTowner,object_typeFROMdemo2WHEREowner=DINGJUN123ANDtrunc(created,dd)=(SELECTMAX(trunc(created,dd)FROMdemo2WHEREowner=DINGJUN123),1.减少表或索引的访问次数,SELECTowner,object_typeFROM(SELECTowner,object_type,dense_rank()over(ORDERBYtrunc(created,dd)DESC)rnFROMdemo2WHEREowner=DINGJUN123)WHERErn=1,-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|21334|*1|VIEW|21334|*2|WINDOWNOSORTSTOPKEY|21334|3|TABLEACCESSBYINDEXROWID|DEMO2|21334|*4|INDEXRANGESCAN|IDX_DEMO2|21334|-,2.实现复杂的行间计算，复杂聚合等,SELECTempno,sal,deptno,SUM(sal)over(PARTITIONBYdeptnoORDERBYempno)sum_currentFROMemp,EMPNOSALDEPTNOSUM_CURRENT-778224501024507839500010745079341300108750。,很多分析函数要求排序,SELECTa.ID,a.sal,a.extFROMt1a,(SELECTID,MAX(sal)max_salFROMt1GROUPBYID)bWHEREa.sal=b.max_salANDa.ID=b.ID,SELECTID,sal,extFROM(SELECTID,sal,ext,rank()over(PARTITIONBYIDORDERBYsalDESC)rnFROMt1)WHERErn=1,-|SELECTSTATEMENT|1|65|HASHJOIN|1|65|35M|VIEW|990K|24M|HASHGROUPBY|990K|24M|TABLEACCESSFULL|T1|990K|24M|TABLEACCESSFULL|T1|990K|36M|-Elapsed:00:00:01.49,-|SELECTSTATEMENT|990K|49M|VIEW|990K|49M|WINDOWSORTPUSHEDRANK|990K|36M|49M|TABLEACCESSFULL|T1|990K|36M|-Elapsed:00:00:04.38,CREATETABLEt1ASSELECTmod(LEVEL,1000)ID,LEVEL+1000sal,MOD(LEVEL,10)extFROMdualCONNECTBYLEVEL1000000,需要排序的分析函数，会消耗一定的资源，当然大多可以优化，对复杂的行间计算、累计值、移动平均等还是首选分析函数,ID很多重复，inlineview分组减少连接数量,分析函数缺点,分析函数语法-图1,分析函数语法-图2,PARTITIONBYORDERBY,ROWSVsRANGE,AnalyticFunction,UNBOUNDEDPRECEDINGFOLLOWINGCURRENTROW,分析函数语法之关系,文档注意点和限制,分析函数语法之partitionby,通过partitionby子句，将相同的行聚合到一起成为一组，之后当前行的分析函数计算就是在这行对应的partition里。每个分析函数都可以使用partitionby子句。每行在对应的窗口内，应用分析函数，然后计算得到当前行对应的分析函数值。partitionby子句可以没有，如果也没有orderby子句，那么表示当前行对应的窗口范围是所有行。,1,1,2,2,3,SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptno)sum_dept,SUM(sal)over()sum_allFROMemp,DEPTNOEMPNOSALSUM_DEPTSUM_ALL-107782245087503202510793413008750320251078395000875032025207902300010875320252075662975108753202520787611001087532025207369800108753202520778830001087532025,分析函数语法之orderby,orderby当前行默认窗口是当前行所属的partition第1行到当前行（根据orderby顺序指定），无orderby就是对应所属partition所有行。orderby默认是range窗口，对应逻辑窗口，保证分析函数值的唯一性，但是对排名分析函数特殊,因为排名函数不能带window。orderby如果有多个排序键且是range窗口，则必须要求对应的窗口是当前partition所有行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行,1,2,3,SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptnoORDERBYsal)dept_current,SUM(sal)over(PARTITIONBYdeptnoORDERBYsalRANGEBETWEENunboundedprecedingANDCURRENTROW)dept_current1FROMemp,DEPTNOEMPNOSALDEPT_CURRENTDEPT_CURRENT1-1079341300130013001077822450375037501078395000875087502073698008008002078761100190019002075662975487548752077883000108751087520790230001087510875307900950950950307654125034503450307521125034503450307844150049504950307499160065506550307698285094009400,逻辑当前行,分析函数语法之window,WITHtAS(SELECT(CASEWHENLEVELIN(1,2)THEN1WHENLEVELIN(4,5)THEN6ELSELEVELEND)IDFROMdualCONNECTBYLEVEL10)-默认排序,逻辑当前行SELECTid,SUM(ID)over(ORDERBYID)default_sum,-逻辑行上限到当前行，此当前行是逻辑当前行，和上面一样SUM(ID)over(ORDERBYIDRANGEBETWEENunboundedprecedingANDCURRENTROW)range_unbound_sum,-物理当前行，按排序后的行位置计算SUM(ID)over(ORDERBYIDROWSBETWEENunboundedprecedingANDCURRENTROW)rows_unbound_sum,-下面两条与上面的比较，换成了对窗口有一定的限制，同样分为-逻辑行和物理行SUM(ID)over(ORDERBYIDRANGEBETWEEN1precedingAND2following)range_sum,SUM(ID)over(ORDERBYIDROWSBETWEEN1precedingAND2following)rows_sumFROMt;,有显式window子句，必须有orderby。有的分析函数不能有显式window,如row_number,rank,dense_rank等window可以指定是逻辑窗口还是物理窗口，逻辑行用range,物理行用rowswindow窗口滑动的方向应该总是从上到下，特别注意range的计算方式和orderby是升序还是降序有关。,1,2,3,IDDEFAULT_SUMRANGE_UNBOUND_SUMROWS_UNBOUND_SUMRANGE_SUMROWS_SUM-12215512225113555316623231133216232317332562323233327730303042308383838242494747471717,语法综合之ROWS,rows窗口是物理窗口，也就是排序后，按排序结果的行号对应确定窗口，指定rows窗口的话，必须要求排序键唯一，否则结果可能不稳定。,SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptnoORDERBYsalROWSBETWEENunboundedprecedingANDCURRENTROW)dept_currentFROMemp,DEPTNOEMPNOSALDEPT_CURRENT-107934130013001077822450375010783950008750207369800800207876110019002075662975487520778830007875207902300010875,rows窗口既然是物理窗口，那么它的orderby是允许带多个排序键的。后面讲解range与rows不同。rows窗口滑动顺序总是向下。它的含义就是当前行之前xx行为起点，当前行之后xx行为终点（其他类似）。,SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptnoORDERBYsalDESCROWSBETWEEN1precedingAND2preceding)sum_salFROMemp,DEPTNOEMPNOSALSUM_SAL-107839500010778224501079341300207788300020790230002075662975207876110020736980030769828503074991600307844150030765412503075211250307900950,终点起点,换成1follwing,语法综合之RANGE,range窗口是默认窗口，它不管排序键是否是唯一，都能保证诸如聚合分析函数值的唯一（排名分析函数？必须唯一），range窗口反应的是行与行之间的逻辑关系（当前行的排序键的value加或减逻辑偏移量得到当前行对应的逻辑窗口的范围）。range+orderby带多个排序键，必须要求对应的窗口是当前partition所有行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行。(Why？)，升序range窗口preceding含义是比当前行小xx值，following含义是比当前行大xx值，降序range窗口preceding含义是比当前行大xx值，following含义是比当前行小xx值。,SELECTdepartment_id,employee_id,salary,SUM(salary)over(PARTITIONBYdepartment_idORDERBYsalaryRANGEBETWEEN100precedingAND500following)sum_salFROMhr.employees;,DEPARTMENT_IDEMPLOYEE_IDSALARYSUM_SAL-102004400440020202600060002020113000130003011925001080030118260013900301172800880030116290088003011531003100301141100011000,换成rows含义变化,语法综合之RANGE,SELECTdepartment_id,employee_id,salary,SUM(salary)over(PARTITIONBYdepartment_idORDERBYsalaryDESCRANGEBETWEEN100precedingAND500following)sum_salFROMhr.employees;,DEPARTMENT_IDEMPLOYEE_IDSALARYSUM_SAL-1020044004400202011300013000202026000600030114110001100030115310011400301162900108003011728001080030118260051003011925005100402036500650050121820024100501208000159005012279001590050123650065005012458005800,因为salarydesc,所以100preceding含义是比当前行的salary大100，以employee_id=117为例，也就是起点是2800+100=2900,终点是比当前行小500，2800-500=2300。那么employee_id=117行分析函数计算的窗口范围是employee_idbetween116and119。,语法综合之窗口范围,当前行对应的分析函数结果是根据当前行对应的窗口（逻辑OR物理）范围计算的。理解窗口的含义对分析函数的使用至关重要。窗口只有起点的，起点必须是CURRENTROW之前（包含CURRENTROW）。窗口内数据移动顺序必须是按照排序顺序，从上到下，否则要么报错，要么结果有问题。,表1-只指定起点的,表2-有起点和终点,分析函数分类,按照各种分析函数使用频率以及业务特点，将分析函数分为排名分析函数、聚合分析函数、行比较分析函数、统计分析函数、行连接分析函数、其他分析函数（基本都是数学统计函数，很少使用）。很多分析函数，同时也可以作为普通的组函数使用，有的分析函数，比如FIRST,LAST、LISTAGG，很多时候，主要用它的普通分组函数功能，而不是它的分析函数功能。,排名分析函数row_number,dense_rank,rank,排名分析函数有row_number,dense_rank,rank,first,last,first_value,last_value等，其中row_number,dense_rank,rank,first,last都需要orderby,first_value和last_value可以不指定。,SELECTempno,sal,deptno,row_number()over(PARTITIONBYdeptnoORDERBYsal)row_rn,rank()over(PARTITIONBYdeptnoORDERBYsal)rank_rn,dense_rank()over(PARTITIONBYdeptnoORDERBYsal)dense_rnFROMemp,EMPNOSALDEPTNOROW_RNRANK_RNDENSE_RN-79341300101117782245010222783950001033373698002011178761100202227566297520333790230002044477883000205447900950301117521125030222765412503032278441500304437845150030543784615003064374991600307747698285030885,排名分析函数row_number,dense_rank,rank新特性,在9i以及之前的版本中使用row_number做分页，会有一定的问题，它没有做到谓词推进，10g之后已经做了优化。,SELECTempno,ename,job,mgr,deptnoFROM(SELECTempno,ename,job,mgr,deptnoFROMscott.empORDERBYempno)WHEREROWNUM10;,-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|9|468|2(0)|00:00:01|*1|COUNTSTOPKEY|2|VIEW|9|468|2(0)|00:00:01|3|TABLEACCESSBYINDEXROWID|EMP|12|300|2(0)|00:00:01|4|INDEXFULLSCAN|PK_EMP|9|1(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-1-filter(ROWNUM10)Statistics-1recursivecalls0dbblockgets4consistentgets,SELECTempno,ename,job,mgr,deptnoFROM(SELECTa.*,row_number()over(ORDERBYempno)rnFROMscott.empa)WHERErn10;,-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|12|780|2(0)|00:00:01|*1|VIEW|12|780|2(0)|00:00:01|*2|WINDOWNOSORTSTOPKEY|12|300|2(0)|00:00:01|3|TABLEACCESSBYINDEXROWID|EMP|12|300|2(0)|00:00:01|4|INDEXFULLSCAN|PK_EMP|12|1(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-1-filter(RNSELECTdepartment_id,first_name2FROMhr.employees3WHEREdepartment_idselect*fromtab;AB-A1A2A3A4A5A6A7,SQLsetnullnullSQLSELECTa,b,LEAD(b)OVER(PARTITIONBYaORDERBYb)c,2LEAD(b,2)OVER(PARTITIONBYaORDERBYb)d,3LEAD(b,3)OVER(PARTITIONBYaORDERBYb)e,4LEAD(b,4)OVER(PARTITIONBYaORDERBYb)f5FROMtab;ABCDEF-A12345A23456A34567A4567nullA567nullnullA67nullnullnullA7nullnullnullnull,行列转换问题2,SQLSELECTINDEX_NAME,COLUMN_NAME2FROMuser_ind_columns3WHEREINDEX_NAMELIKE%PK4ANDrownumselect*fromdemo7_2;IDPERSONS-1322,平均分派问题，如何将金额平均分摊，并且小数也分摊掉，避免误差。,SELECTID,persons,(CASEWHENrn=tm.rn)t),IDPERSONSJEAMOUNT-1333.341001333.331001333.33100222550222550,自定义分析函数,自定义分析函数就是需要实现ORACLE规定的对象的接口方法，然后自定义函数使用这个对象即可(红色为必须实现的，其他可选)。返回值：ODCIConst.Success成功，ODCIConst.Error失败。,自定义分析函数,-1.定义对象CREATEORREPLACETYPETYPE_MULTI_AGGASOBJECT(resultsNUMBER,STATICFUNCTIONODCIAGGREGATEINITIALIZE(sctxINOUTTYPE_MULTI_AGG)RETURNNUMBER,MEMBERFUNCTIONODCIAGGREGATEITERATE(SELFINOUTTYPE_MULTI_AGG,VALUEINNUMBER)RETURNNUMBER,MEMBERFUNCTIONODCIAGGREGATETERMINATE(SELFINTYPE_MULTI_AGG,returnValueOUTNUMBER,flagsINNUMBER)RETURNNUMBER,MEMBERFUNCTIONODCIAGGREGATEMERGE(SELFINOUTTYPE_MULTI_AGG,ctx2INTYPE_MULTI_AGG)RETURNNUMBER);/-2.实现对象类型方法CREATEORREPLACETYPEBODYTYPE_MULTI_AGGISSTATICFUNCTIONODCIAGGREGATEINITIALIZE(sctxINOUTTYPE_MULTI_AGG)RETURNNU