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優(yōu)點：最容易想到缺點：多次訪問表或索引分析函數方法分析SQL

:邏輯讀423，COST：693優(yōu)點：減少表或索引的訪問次數，邏輯讀和COST

是常規(guī)方法的一半，SQL簡單缺點：需要排序操作進一步優(yōu)化建立owner,trunc(created,’dd’)desc復合索引作用對于原始SQL子查詢可以快速掃描，分析函數消除排序優(yōu)化后原始SQL邏輯讀/COST：111/171,分析函數:6/767構建合適索引，消除排序，是一種重要的SQL優(yōu)化手段------------------------------------------------------------|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需要排序的分析函數，會消耗一定的資源，當然大多可以優(yōu)化，對復雜的行間計算、累計值、移動平均等還是首選分析函數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窗口的話，必須要求排序鍵唯一，否則結果可能不穩(wěn)定。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解決當前行與前后行之間的關系中統(tǒng)計分析函數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弱按照各種分析函數使用頻率以及業(yè)務特點，將分析函數分為排名分析函數、聚合分析函數、行比較分析函數、統(tǒng)計分析函數、行連接分析函數、其他分析函數（基本都是數學統(tǒng)計函數，很少使用）。很多分析函數，同時也可以作為普通的組函數使用，有的分析函數，比如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之后已經做了優(yōu)化。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關鍵字就是起到一個語義的作用：說明按照指定的排序規(guī)則找到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統(tǒng)計分析函數

統(tǒng)計分析函數最常用的是ratio_to_report?？梢杂衱indow子句。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分析函數專題案例專題案例說明連續(xù)數問題用分析函數解決經典連續(xù)數問題填充缺失行問題使用LAST_VALUE分析函數解決填充行問題模擬SQL*PLUSBREAK使用LAG模擬BREAKCOLUMN重復行問題用分析函數解決重復行問題行列轉換問題使用排名分析函數/LEAD解決行列轉換金額攤派問題使用ROW_NUMBER分析函數解決經典金額均分問題自定義分析函數自定義一個連乘分析函數連續(xù)數問題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