ORACLE分析函數(shù)資料

OracleAnalyticFunctionsInPracticeBydingjun1232013.06主要內(nèi)容甲：我有個SQL，你能幫我用分析函數(shù)改寫下嗎？

乙：相關(guān)列有索引嗎？甲：owner有索引，選擇性不錯，我想用分析函數(shù)改寫看看？乙：哦，知道了，這是典型的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分析函數(shù)作用歡迎進(jìn)入今天的分析函數(shù)學(xué)習(xí)之旅！4子查詢方法總行數(shù)：667827返回9行原始SQL

:邏輯讀848，COST：1103優(yōu)點：最容易想到缺點：多次訪問表或索引分析函數(shù)方法分析SQL

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

是常規(guī)方法的一半，SQL簡單缺點：需要排序操作進(jìn)一步優(yōu)化建立owner,trunc(created,’dd’)desc復(fù)合索引作用對于原始SQL子查詢可以快速掃描，分析函數(shù)消除排序優(yōu)化后原始SQL邏輯讀/COST：111/171,分析函數(shù):6/767構(gòu)建合適索引，消除排序，是一種重要的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')分析函數(shù)作用分析函數(shù)的作用總結(jié)---------------------------------------------------------|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.減少表或索引的訪問次數(shù)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.實現(xiàn)復(fù)雜的行間計算，復(fù)雜聚合等SELECTempno,sal,deptno,SUM(sal)over(PARTITIONBYdeptnoORDERBYempno)sum_currentFROMempEMPNOSALDEPTNOSUM_CURRENT-------------------------------------778224501024507839500010745079341300108750。。。很多分析函數(shù)要求排序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需要排序的分析函數(shù)，會消耗一定的資源，當(dāng)然大多可以優(yōu)化，對復(fù)雜的行間計算、累計值、移動平均等還是首選分析函數(shù)ID很多重復(fù)，inlineview分組減少連接數(shù)量分析函數(shù)缺點分析函數(shù)語法-圖1分析函數(shù)語法-圖2PARTITIONBYORDERBYROWSVsRANGEAnalyticFunctionUNBOUNDEDPRECEDINGFOLLOWINGCURRENTROW分析函數(shù)語法之關(guān)系文檔注意點和限制分析函數(shù)語法之partitionby

通過partitionby子句，將相同的行聚合到一起成為一組，之后當(dāng)前行的分析函數(shù)計算就是在這行對應(yīng)的partition里。每個分析函數(shù)都可以使用partition

by子句。

每行在對應(yīng)的窗口內(nèi)，應(yīng)用分析函數(shù)，然后計算得到當(dāng)前行對應(yīng)的分析函數(shù)值。partitionby子句可以沒有，如果也沒有orderby子句，那么表示當(dāng)前行對應(yīng)的窗口范圍是所有行。11223SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptno)sum_dept,SUM(sal)over()sum_allFROMempDEPTNOEMPNOSALSUM_DEPTSUM_ALL-------------------------------------------------107782245087503202510793413008750320251078395000875032025207902300010875320252075662975108753202520787611001087532025207369800108753202520778830001087532025分析函數(shù)語法之orderbyorderby當(dāng)前行默認(rèn)窗口是當(dāng)前行所屬的partition第1行到當(dāng)前行（根據(jù)orderby順序指定），無orderby就是對應(yīng)所屬partition所有行。orderby默認(rèn)是range窗口，對應(yīng)邏輯窗口，保證分析函數(shù)值的唯一性，但是對排名分析函數(shù)特殊,因為排名函數(shù)不能帶window。orderby如果有多個排序鍵且是range窗口，則必須要求對應(yīng)的窗口是當(dāng)前partition所有行、第1行到當(dāng)前行、當(dāng)前行到當(dāng)前partition最后一行或當(dāng)前行到當(dāng)前行123SELECTdeptno,empno,sal,

SUM(sal)over(PARTITIONBYdeptnoORDERBYsal)dept_current,

SUM(sal)over(PARTITIONBYdeptnoORDERBYsalRANGEBETWEENunboundedprecedingANDCURRENTROW)dept_current1FROMempDEPTNOEMPNOSALDEPT_CURRENTDEPT_CURRENT1---------------------------------------------------1079341300130013001077822450375037501078395000875087502073698008008002078761100190019002075662975487548752077883000108751087520790230001087510875307900950950950307654125034503450307521125034503450307844150049504950307499160065506550307698285094009400邏輯當(dāng)前行分析函數(shù)語法之windowWITHtAS(SELECT

(CASE

WHEN

LEVEL

IN

(1,2)

THEN1

WHEN

LEVEL

IN

(4,5)

THEN6

ELSE

LEVEL

END)

ID

FROMdualCONNECT

BY

LEVEL<10)--默認(rèn)排序,邏輯當(dāng)前行SELECT

id,SUM(ID)over(ORDER

BY

ID)default_sum,--邏輯行上限到當(dāng)前行，此當(dāng)前行是邏輯當(dāng)前行，和上面一樣

SUM(ID)over(ORDER

BY

ID

RANGE

BETWEENunboundedpreceding

AND

CURRENT

ROW)range_unbound_sum,--物理當(dāng)前行，按排序后的行位置計算

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。有的分析函數(shù)不能有顯式window,如row_number,rank,dense_rank等window可以指定是邏輯窗口還是物理窗口，邏輯行用range,物理行用rowswindow窗口滑動的方向應(yīng)該總是從上到下，特別注意range的計算方式和orderby是升序還是降序有關(guān)。123IDDEFAULT_SUMRANGE_UNBOUND_SUMROWS_UNBOUND_SUMRANGE_SUMROWS_SUM-------------------------------------------------------------------1221551222511355531662323113321623231733256232323332773030304230838383824249474747171713語法綜合之ROWS

rows窗口是物理窗口，也就是排序后，按排序結(jié)果的行號對應(yīng)確定窗口，指定rows窗口的話，必須要求排序鍵唯一，否則結(jié)果可能不穩(wěn)定。SELECTdeptno,empno,sal,

SUM(sal)over(PARTITIONBYdeptno

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

rows窗口既然是物理窗口，那么它的orderby是允許帶多個排序鍵的。后面講解range與rows不同。rows窗口滑動順序總是向下。它的含義就是當(dāng)前行之前xx行為起點，當(dāng)前行之后xx行為終點（其他類似）。SELECTdeptno,empno,sal,

SUM(sal)over(PARTITIONBYdeptno

ORDERBYsalDESCROWSBETWEEN1precedingAND2preceding)sum_sal

FROMempDEPTNOEMPNOSALSUM_SAL------------------------------------107839500010778224501079341300207788300020790230002075662975207876110020736980030769828503074991600307844150030765412503075211250307900950終點起點換成1follwing14語法綜合之RANGErange窗口是默認(rèn)窗口，它不管排序鍵是否是唯一，都能保證諸如聚合分析函數(shù)值的唯一（排名分析函數(shù)？必須唯一），range窗口反應(yīng)的是行與行之間的邏輯關(guān)系（當(dāng)前行的排序鍵的value加或減邏輯偏移量得到當(dāng)前行對應(yīng)的邏輯窗口的范圍）。range+orderby帶多個排序鍵，必須要求對應(yīng)的窗口是當(dāng)前partition所有行、第1行到當(dāng)前行、當(dāng)前行到當(dāng)前partition最后一行或當(dāng)前行到當(dāng)前行。(Why？)，升序range窗口preceding含義是比當(dāng)前行小xx值，following含義是比當(dāng)前行大xx值，降序range窗口preceding含義是比當(dāng)前行大xx值，following含義是比當(dāng)前行小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含義是比當(dāng)前行的salary大100，以employee_id=117為例，也就是起點是2800+100=2900,終點是比當(dāng)前行小500，2800-500=2300。那么employee_id=117行分析函數(shù)計算的窗口范圍是employee_idbetween116and119。16語法綜合之窗口范圍當(dāng)前行對應(yīng)的分析函數(shù)結(jié)果是根據(jù)當(dāng)前行對應(yīng)的窗口（邏輯OR物理）范圍計算的。理解窗口的含義對分析函數(shù)的使用至關(guān)重要。窗口只有起點的，起點必須是CURRENTROW之前（包含CURRENTROW）。窗口內(nèi)數(shù)據(jù)移動順序必須是按照排序順序，從上到下，否則要么報錯，要么結(jié)果有問題。示例說明ROWS/RANGEUNBOUNDEDPRECEDING終點CURRENTROW，相當(dāng)于BETWEEN起點BTEWEENCURRENTROWROWS/RANGECURRENTROW相當(dāng)于BETWEENCURRENTROWANDCURRENTROWROWS/RANGEvalue_exprPRECEDING相當(dāng)于BETWEENvalue_exprPRECEDINGANDCURRENTROWvalue_exprFOLLOWING報錯,無效窗口表1-只指定起點的表2-有起點和終點示例說明ROWS/RANGEBETWEEN

UNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING和無ORDERBY一樣，等價于沒有寫ORDERBy和WINDOW子句ROWS/RANGEBETWEENCURRENTROWANDvalue_exprFOLLOWING當(dāng)前行到對應(yīng)value_exp偏移量的行ROWS/RANGEBETWEENvalue_exprFOLLOWINGANDvalue_expr1FOLLOWING起點是FOLLOWING,終點必須是FOLLOWINGROWS/RANGEBETWEENvalue_exprPRECEDINGANDvalue_expr1FOLLOWING終點是PRECEDING,起點必須是PRECEDINGROWS/RANGEBETWEENCURRENTROWANDvalue_exprPRECEDING報錯,無效窗口17分析函數(shù)分類分類內(nèi)容說明使用頻率排名分析函數(shù)ROW_NUMBER、RANK、DENSE_RANK、FIRST、LAST、LAST_VALUE,FIRST_VALUEFIRST,LAST不應(yīng)該算做函數(shù)，它主要與聚合函數(shù)聯(lián)合使用強聚合分析函數(shù)AVG、COUNT、MAX,MIN,SUM強行比較分析函數(shù)LEAD、LAG解決當(dāng)前行與前后行之間的關(guān)系中統(tǒng)計分析函數(shù)RATIO_TO_REPORT解決報表占比問題中行連接分析函數(shù)LISTAGG11g新特性中其他分析函數(shù)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弱按照各種分析函數(shù)使用頻率以及業(yè)務(wù)特點，將分析函數(shù)分為排名分析函數(shù)、聚合分析函數(shù)、行比較分析函數(shù)、統(tǒng)計分析函數(shù)、行連接分析函數(shù)、其他分析函數(shù)（基本都是數(shù)學(xué)統(tǒng)計函數(shù)，很少使用）。很多分析函數(shù)，同時也可以作為普通的組函數(shù)使用，有的分析函數(shù)，比如FIRST,LAST、LISTAGG，很多時候，主要用它的普通分組函數(shù)功能，而不是它的分析函數(shù)功能。排名分析函數(shù)row_number,dense_rank,rank

排名分析函數(shù)有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排名分析函數(shù)row_number,dense_rank,rank新特性

在9i以及之前的版本中使用row_number做分頁，會有一定的問題，它沒有做到謂詞推進(jìn)，10g之后已經(jīng)做了優(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排名分析函數(shù)first、last

FIRST/LAST可以作為組函數(shù)和分析函數(shù)，組函數(shù)不帶OVER,分析函數(shù)帶OVER,它必須結(jié)合KEEP關(guān)鍵字，KEEP關(guān)鍵字就是起到一個語義的作用：說明按照指定的排序規(guī)則找到TOP1或BOTTOM1，因為是DENSE_RANK,所以TOP1和BOTTOM1是個集合，然后外層用組函數(shù)對TOP1或BOTTOM1的行再處理獲得唯一結(jié)果。因此它的作用就是對某些列排名，對其他列計算。排名分析函數(shù)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排名分析函數(shù)first_value、last_value

FIRST_VALUE/LAST_VALUE只能用作分析函數(shù)，它返回的是按照指定排序，得到對應(yīng)窗口集合的第1個值或最后一個值。它特別有用的是可以忽略NULL,常用于填補缺失數(shù)據(jù)。23排名分析函數(shù)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聚集分析函數(shù)

聚集分析函數(shù)有sum,max,min,avg,count等，很多組函數(shù)同時可以作為分析函數(shù)使用。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行比較分析函數(shù)

行比較分析函數(shù)有LEAD/LAG。無window子句，分別得到當(dāng)前行物理偏移n行的值，默認(rè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)計分析函數(shù)

統(tǒng)計分析函數(shù)最常用的是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行連接分析函數(shù)

11g引入了一個新的函數(shù)LISTAGG，它可以作為組函數(shù)和分析函數(shù)，主要作用就是按照指定分組或者指定窗口范圍，并且按照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分析函數(shù)專題案例專題案例說明連續(xù)數(shù)問題用分析函數(shù)解決經(jīng)典連續(xù)數(shù)問題填充缺失行問題使用LAST_VALUE分析函數(shù)解決填充行問題模擬SQL*PLUSBREAK使用LAG模擬BREAKCOLUMN重復(fù)行問題用分析函數(shù)解決重復(fù)行問題行列轉(zhuǎn)換問題使用排名分析函數(shù)/LEAD解決行列轉(zhuǎn)換金額攤派問題使用ROW_NUMBER分析函數(shù)解決經(jīng)典金額均分問題自定義分析函數(shù)自定義一個連乘分析函數(shù)連續(xù)數(shù)問題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