ORACLE分析函数资料

OracleAnalyticFunctionsInPracticeBydingjun1232013.06主要内容甲：我有个SQL，你能帮我用分析函数改写下吗？

乙：相关列有索引吗？甲：owner有索引，选择性不错，我想用分析函数改写看看？乙：哦，知道了，这是典型的top-n查询。

SELECT

owner,object_type

FROMdemo2

WHEREowner='DINGJUN123'

AND

trunc(created,'dd')=(SELECT

MAX(trunc(created,'dd'))

FROMdemo2

WHEREowner='DINGJUN123')SELECTowner,object_typeFROM

(SELECTowner,object_type,

dense_rank()over(ORDER

BY

trunc(created,'dd')DESC)rn

FROMdemo2

WHEREowner='DINGJUN123'

)

WHERErn=1分析函数作用欢迎进入今天的分析函数学习之旅！4子查询方法总行数：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'))FROM"DEMO2""DEMO2"WHERE"OWNER"='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_type

FROMdemo2

WHEREowner='DINGJUN123'

AND

trunc(created,'dd')=(SELECT

MAX(trunc(created,'dd'))

FROMdemo2

WHEREowner='DINGJUN123')1.减少表或索引的访问次数SELECTowner,object_typeFROM

(SELECTowner,object_type,

dense_rank()over(ORDER

BYtrunc(created,'dd')DESC)rn

FROMdemo2

WHEREowner='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_currentFROMempEMPNOSALDEPTNOSUM_CURRENT-------------------------------------778224501024507839500010745079341300108750。。。很多分析函数要求排序SELECTa.ID,a.sal,a.extFROMt1a,(SELECTID,MAX(sal)max_salFROMt1GROUPBYID)bWHEREa.sal=b.max_salANDa.ID=b.IDSELECTID,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.38CREATETABLEt1ASSELECTmod(LEVEL,1000)ID,LEVEL+1000sal,MOD(LEVEL,10)extFROMdualCONNECTBYLEVEL<1000000需要排序的分析函数，会消耗一定的资源，当然大多可以优化，对复杂的行间计算、累计值、移动平均等还是首选分析函数ID很多重复，inlineview分组减少连接数量分析函数缺点分析函数语法-图1分析函数语法-图2PARTITIONBYORDERBYROWSVsRANGEAnalyticFunctionUNBOUNDEDPRECEDINGFOLLOWINGCURRENTROW分析函数语法之关系文档注意点和限制分析函数语法之partitionby

通过partitionby子句，将相同的行聚合到一起成为一组，之后当前行的分析函数计算就是在这行对应的partition里。每个分析函数都可以使用partition

by子句。

每行在对应的窗口内，应用分析函数，然后计算得到当前行对应的分析函数值。partitionby子句可以没有，如果也没有orderby子句，那么表示当前行对应的窗口范围是所有行。11223SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptno)sum_dept,SUM(sal)over()sum_allFROMempDEPTNOEMPNOSALSUM_DEPTSUM_ALL-------------------------------------------------107782245087503202510793413008750320251078395000875032025207902300010875320252075662975108753202520787611001087532025207369800108753202520778830001087532025分析函数语法之orderbyorderby当前行默认窗口是当前行所属的partition第1行到当前行（根据orderby顺序指定），无orderby就是对应所属partition所有行。orderby默认是range窗口，对应逻辑窗口，保证分析函数值的唯一性，但是对排名分析函数特殊,因为排名函数不能带window。orderby如果有多个排序键且是range窗口，则必须要求对应的窗口是当前partition所有行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行123SELECTdeptno,empno,sal,

SUM(sal)over(PARTITIONBYdeptnoORDERBYsal)dept_current,

SUM(sal)over(PARTITIONBYdeptnoORDERBYsalRANGEBETWEENunboundedprecedingANDCURRENTROW)dept_current1FROMempDEPTNOEMPNOSALDEPT_CURRENTDEPT_CURRENT1---------------------------------------------------1079341300130013001077822450375037501078395000875087502073698008008002078761100190019002075662975487548752077883000108751087520790230001087510875307900950950950307654125034503450307521125034503450307844150049504950307499160065506550307698285094009400逻辑当前行分析函数语法之windowWITHtAS(SELECT

(CASE

WHEN

LEVEL

IN

(1,2)

THEN1

WHEN

LEVEL

IN

(4,5)

THEN6

ELSE

LEVEL

END)

ID

FROMdualCONNECT

BY

LEVEL<10)--默认排序,逻辑当前行SELECT

id,SUM(ID)over(ORDER

BY

ID)default_sum,--逻辑行上限到当前行，此当前行是逻辑当前行，和上面一样

SUM(ID)over(ORDER

BY

ID

RANGE

BETWEENunboundedpreceding

AND

CURRENT

ROW)range_unbound_sum,--物理当前行，按排序后的行位置计算

SUM(ID)over(ORDER

BY

ID

ROWS

BETWEENunboundedpreceding

AND

CURRENT

ROW)rows_unbound_sum,--下面两条与上面的比较，换成了对窗口有一定的限制，同样分为--逻辑行和物理行

SUM(ID)over(ORDER

BY

ID

RANGE

BETWEEN1precedingAND2following)range_sum,

SUM(ID)over(ORDER

BY

ID

ROWS

BETWEEN1precedingAND2following)rows_sumFROMt;

有显式window子句，必须有orderby。有的分析函数不能有显式window,如row_number,rank,dense_rank等window可以指定是逻辑窗口还是物理窗口，逻辑行用range,物理行用rowswindow窗口滑动的方向应该总是从上到下，特别注意range的计算方式和orderby是升序还是降序有关。123IDDEFAULT_SUMRANGE_UNBOUND_SUMROWS_UNBOUND_SUMRANGE_SUMROWS_SUM-------------------------------------------------------------------1221551222511355531662323113321623231733256232323332773030304230838383824249474747171713语法综合之ROWS

rows窗口是物理窗口，也就是排序后，按排序结果的行号对应确定窗口，指定rows窗口的话，必须要求排序键唯一，否则结果可能不稳定。SELECTdeptno,empno,sal,

SUM(sal)over(PARTITIONBYdeptno

ORDERBYsalROWSBETWEENunboundedprecedingANDCURRENTROW)dept_currentFROMempDEPTNOEMPNOSALDEPT_CURRENT---------------------------------------107934130013001077822450375010783950008750207369800800207876110019002075662975487520778830007875207902300010875

rows窗口既然是物理窗口，那么它的orderby是允许带多个排序键的。后面讲解range与rows不同。rows窗口滑动顺序总是向下。它的含义就是当前行之前xx行为起点，当前行之后xx行为终点（其他类似）。SELECTdeptno,empno,sal,

SUM(sal)over(PARTITIONBYdeptno

ORDERBYsalDESCROWSBETWEEN1precedingAND2preceding)sum_sal

FROMempDEPTNOEMPNOSALSUM_SAL------------------------------------107839500010778224501079341300207788300020790230002075662975207876110020736980030769828503074991600307844150030765412503075211250307900950终点起点换成1follwing14语法综合之RANGErange窗口是默认窗口，它不管排序键是否是唯一，都能保证诸如聚合分析函数值的唯一（排名分析函数？必须唯一），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含义变化15语法综合之RANGESELECTdepartment_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。16语法综合之窗口范围当前行对应的分析函数结果是根据当前行对应的窗口（逻辑OR物理）范围计算的。理解窗口的含义对分析函数的使用至关重要。窗口只有起点的，起点必须是CURRENTROW之前（包含CURRENTROW）。窗口内数据移动顺序必须是按照排序顺序，从上到下，否则要么报错，要么结果有问题。示例说明ROWS/RANGEUNBOUNDEDPRECEDING终点CURRENTROW，相当于BETWEEN起点BTEWEENCURRENTROWROWS/RANGECURRENTROW相当于BETWEENCURRENTROWANDCURRENTROWROWS/RANGEvalue_exprPRECEDING相当于BETWEENvalue_exprPRECEDINGANDCURRENTROWvalue_exprFOLLOWING报错,无效窗口表1-只指定起点的表2-有起点和终点示例说明ROWS/RANGEBETWEEN

UNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING和无ORDERBY一样，等价于没有写ORDERBy和WINDOW子句ROWS/RANGEBETWEENCURRENTROWANDvalue_exprFOLLOWING当前行到对应value_exp偏移量的行ROWS/RANGEBETWEENvalue_exprFOLLOWINGANDvalue_expr1FOLLOWING起点是FOLLOWING,终点必须是FOLLOWINGROWS/RANGEBETWEENvalue_exprPRECEDINGANDvalue_expr1FOLLOWING终点是PRECEDING,起点必须是PRECEDINGROWS/RANGEBETWEENCURRENTROWANDvalue_exprPRECEDING报错,无效窗口17分析函数分类分类内容说明使用频率排名分析函数ROW_NUMBER、RANK、DENSE_RANK、FIRST、LAST、LAST_VALUE,FIRST_VALUEFIRST,LAST不应该算做函数，它主要与聚合函数联合使用强聚合分析函数AVG、COUNT、MAX,MIN,SUM强行比较分析函数LEAD、LAG解决当前行与前后行之间的关系中统计分析函数RATIO_TO_REPORT解决报表占比问题中行连接分析函数LISTAGG11g新特性中其他分析函数CORR、CORVAR_POP、CORVAR_SAMP、CUME_DIST、NTH_VALUE、NTILE、PERCENT_RANK、PERCENTILE_CONT、PERCENTILE_DISC、REGR_(LinearRegression)Functions、STDDEV、STDDEV_POP、STDDEV_SAMP、VAR_POP,VAR_SAMP、VARIANCE弱按照各种分析函数使用频率以及业务特点，将分析函数分为排名分析函数、聚合分析函数、行比较分析函数、统计分析函数、行连接分析函数、其他分析函数（基本都是数学统计函数，很少使用）。很多分析函数，同时也可以作为普通的组函数使用，有的分析函数，比如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_rnFROMempEMPNOSALDEPTNOROW_RNRANK_RNDENSE_RN--------------------------------------------------------7934130010111778224501022278395000103337369800201117876110020222756629752033379023000204447788300020544790095030111752112503022276541250303227844150030443784515003054378461500306437499160030774769828503088519排名分析函数row_number,dense_rank,rank新特性

在9i以及之前的版本中使用row_number做分页，会有一定的问题，它没有做到谓词推进，10g之后已经做了优化。SELECTempno,ename,job,mgr,deptnoFROM(SELECTempno,ename,job,mgr,deptnoFROMscott.empORDERBYempno)WHEREROWNUM<10;----------------------------------------------------------------------------------------|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(ROWNUM<10)Statistics----------------------------------------------------------1recursivecalls0dbblockgets

4consistentgetsSELECTempno,ename,job,mgr,deptnoFROM(SELECTa.*,row_number()over(ORDERBYempno)rnFROMscott.empa)WHERErn<10;----------------------------------------------------------------------------------------|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("RN"<10)2-filter(ROW_NUMBER()OVER(ORDERBY"EMPNO")<10)Statistics----------------------------------------------------------0recursivecalls0dbblockgets4consistentgets排名分析函数first、last

FIRST/LAST可以作为组函数和分析函数，组函数不带OVER,分析函数带OVER,它必须结合KEEP关键字，KEEP关键字就是起到一个语义的作用：说明按照指定的排序规则找到TOP1或BOTTOM1，因为是DENSE_RANK,所以TOP1和BOTTOM1是个集合，然后外层用组函数对TOP1或BOTTOM1的行再处理获得唯一结果。因此它的作用就是对某些列排名，对其他列计算。排名分析函数first、lastSELECTyy,mm,NAME,s_qty,avg_price,(SELECTt.inventFROMsaletWHEREt.yy=s.yyANDt.mm=s.mmANDt.dd=s.mdd)last_inventFROM

(SELECTyy,mm,NAME,SUM(qty)s_qty,AVG(price)avg_price,MAX(dd)mddFROMsaleGROUP

BYyy,mm,NAME)sORDER

BYyy,mm,NAMESELECTyy,mm,NAME,SUM(qty)s_qty,AVG(price)avg_price,MAX(invent)

KEEP

(dense_rankLAST

ORDER

BYdd)last_inventFROMsaleGROUP

BYyy,mm,NAME22排名分析函数first_value、last_value

FIRST_VALUE/LAST_VALUE只能用作分析函数，它返回的是按照指定排序，得到对应窗口集合的第1个值或最后一个值。它特别有用的是可以忽略NULL,常用于填补缺失数据。23排名分析函数first_value、last_valueSELECTdeptno,sal,ename,last_value(ename)over(PARTITIONBYdeptnoORDERBYsal)last_enameFROMscott.emp;DEPTNOSALENAMELAST_ENAME------------------------------------101300MILLERMILLER102450CLARKCLARK105000KINGKING20800SMITHSMITH202975JONESJONES203000FORDFORD30950JAMESJAMES301250MARTINWARD301250WARDWARD301500TURNERTURNER301600ALLENALLEN302850BLAKEBLAKESELECTdeptno,sal,ename,last_value(ename)over(PARTITIONBYdeptnoORDERBYsalROWSunboundedpreceding)last_enameFROMscott.emp;DEPTNOSALENAMELAST_ENAME-------------------------------------101300MILLERMILLER102450CLARKCLARK105000KINGKING20800SMITHSMITH202975JONESJONES203000FORDFORD30950JAMESJAMES301250WARDWARD301250MARTINMARTIN301500TURNERTURNER301600ALLENALLEN302850BLAKEBLAKE聚集分析函数

聚集分析函数有sum,max,min,avg,count等，很多组函数同时可以作为分析函数使用。SELECTename,hiredate,sal,

SUM(sal)OVER(ORDERBYhiredate

RANGENUMTOYMINTERVAL(2,'month')PRECEDING)ASt_sal

FROMscott.empENAMEHIREDATESALT_SAL-------------------------------------------------SMITH1980-12-1700:00:00800800ALLEN1981-02-2000:00:0016001600WARD1981-02-2200:00:0012502850JONES1981-04-0200:00:0029755825BLAKE1981-05-0100:00:0028505825CLARK1981-06-0900:00:0024505300TURNER1981-09-0800:00:0015001500MARTIN1981-09-2800:00:0012502750KING1981-11-1700:00:0050006250JAMES1981-12-0300:00:009508950FORD1981-12-0300:00:0030008950MILLER1982-01-2300:00:0013005250SCOTT1987-04-1900:00:0030003000ADAMS1987-05-2300:00:0011004100distinct限制不能有orderby行比较分析函数

行比较分析函数有LEAD/LAG。无window子句，分别得到当前行物理偏移n行的值，默认偏移1，比较特殊，这里没有逻辑窗口的概念。SELECTempno,hiredate,lead(hiredate,1)over(ORDER

BYhiredate)lead_hiredate,lag(hiredate,1)over(ORDER

BYhiredate)

lag_hiredateFROMscott.empEMPNOHIREDATELEAD_HIREDALAG_HIREDA---------------------------------------73691980-12-171981-02-2074991981-02-201981-02-221980-12-1775211981-02-221981-04-021981-02-2075661981-04-021981-05-011981-02-2276981981-05-011981-06-091981-04-0277821981-06-091981-09-081981-05-0178441981-09-081981-09-281981-06-0976541981-09-281981-11-171981-09-0878391981-11-171981-12-031981-09-2879001981-12-031981-12-031981-11-1779021981-12-031982-01-231981-12-0379341982-01-231987-04-191981-12-0377881987-04-191987-05-231982-01-2378761987-05-231987-04-19统计分析函数

统计分析函数最常用的是ratio_to_report。可以有window子句。SELECT

department_id,sum(salary)dept_sum,SUM(SUM(salary))over()all_sum,round(SUM(salary)/(SUM(SUM(salary))over()),2)*100||'%'ratioFROMhr.employeesGROUP

BYdepartment_idORDER

BY1SELECTdepartment_id,sum(salary)dept_sum,SUM(SUM(salary))over()all_sum,round(ratio_to_report(SUM(salary))over(),2)*100||'%'

ratioFROMhr.employeesGROUP

BYdepartment_idORDER

BY1DEPARTMENT_IDDEPT_SUMALL_SUMRATIO----------------------------------------1044006914161%20190006914163%30249006914164%4065006914161%5015640069141623%60288006914164%70100006914161%8030450069141644%90580006914168%100516086914167%110203086914163%70006914161%27行连接分析函数

11g引入了一个新的函数LISTAGG，它可以作为组函数和分析函数，主要作用就是按照指定分组或者指定窗口范围，并且按照orderby的行顺序，将列按照指定连接符连成一串。SELECTdeptno,listagg(ename,'||')withinGROUP(ORDERBYempno)list_enameFROMscott.empGROUPBYdeptno;

DEPTNOLIST_ENAME----------------------------------------------10CLARK||KING||MILLER20SMITH||JONES||FORD30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMESSELECTdeptno,listagg(ename,'||')withinGROUP(ORDERBYempno)over(PARTITIONBYdeptno)list_enameFROMscott.emp;

DEPTNOLIST_ENAME-------------------------------------------------10CLARK||KING||MILLER10CLARK||KING||MILLER10CLARK||KING||MILLER20SMITH||JONES||FORD20SMITH||JONES||FORD20SMITH||JONES||FORD30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMES30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMES30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMES30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMES30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMES30ALLEN||WARD||MARTIN||BLAKE||TURNER||JAMES28分析函数专题案例专题案例说明连续数问题用分析函数解决经典连续数问题填充缺失行问题使用LAST_VALUE分析函数解决填充行问题模拟SQL*PLUSBREAK使用LAG模拟BREAKCOLUMN重复行问题用分析函数解决重复行问题行列转换问题使用排名分析函数/LEAD解决行列转换金额摊派问题使用ROW_NUMBER分析函数解决经典金额均分问题自定义分析函数自定义一个连乘分析函数连续数问题selectid,num,valfromtest_tab;

IDNUMVAL------------------------------

1

1

50

1

2

100

1

3

150

1

5

250

2

1

100

2

3

400

3

1

100

3