以下代碼均經過測試,可直接運行Oracle分析函數、多維函數和Model函數簡要說明,主要針對BI報表統計,不一定很全面,但對BI應用場景做了少許說明--創建一張銷售數量表,數據趨勢是遞增的CREATETABLEComputerSalesASSELECT120+TRUNC(rn/12)+ROUND(DBMS_RANDOM.VALUE(1,10))SalesNumberFROM(SELECTlevel,ROWNUMrnFROMDUALCONNECTBYROWNUM<=120);一下面用于比較NULL值和非NULL值的統計,可以看出NULL值情況下的COUNT是存在問題的,所以建議數據庫系統中最好不要使用NULL值列SELECTCOUNT(*),COUNT(a.SalesNumber),COUNT(DISTINCTa.SalesNumber),SUM(a.SalesNumber),AVG(a.SalesNumber),MAX(a.SalesNumber),MIN(a.SalesNumber)FROMComputerSalesA;DELETEFROMComputerSalesWHERESalesNumberISNULL;COMMIT;INSERTINTOComputerSalesVALUES(NULL);COMMIT;INSERTINTOComputerSalesVALUES(NULL);COMMIT;SELECTCOUNT(*),COUNT(a.SalesNumber),COUNT(DISTINCTa.SalesNumber),AVG(a.SalesNumber),MAX(a.SalesNumber),MIN(a.SalesNumber)FROMComputerSalesA;SELECTtrunc(dbms_random.value(1,101)),DELETEFROMComputerSalesWHERESalesNumberISNULL;COMMIT;--創建增加了日期字段的表CREATETABLEComputerSalesBAKASSELECTSalesNumber,TRUNC(SYSDate)+MOD(A.DateSEQ-1,10)SalesDateFROM(SELECTSalesNumber,ROW_NUMBER()OVER(ORDERBYROWID)DateSEQFROMComputerSales)A;DROPTABLEComputerSales;RENAMEComputerSalesBAKTOComputerSales;一下面是兩種創建方式,構招Area列和日期列CREATETABLEComputerSalesBAKASSELECTSalesNumber,TRUNC(SYSDATE)+MOD(A.DateSEQ-1,24)SalesDate,CASEWHENTRUNC((DateSEQ-1)/24)=1THEN'華南地區WHENTRUNC((DateSEQ-1)/24)=2THEN'華北地區'WHENTRUNC((DateSEQ-1)/24)=3THEN'東北地區'WHENTRUNC((DateSEQ-1)/24)=4THEN'華東地區'ELSE'其他地區'ENDFROM(SELECTSalesNumber,ROW_NUMBER()OVER(ORDERBYROWID)DateSEQFROMComputerSales)A;DROPTABLEComputerSales;RENAMEComputerSalesBAKTOComputerSales;--該例可構造SalesDate和Area的重復數據CREATETABLEComputerSalesBAKASSELECTSalesNumber,TRUNC(SYSDATE)+MOD(A.DateSEQ-1,10)SalesDate,CASEWHENAreaSEQ=1THEN'華南地區'WHENAreaSEQ=2THEN'華北地區'WHENAreaSEQ=3THEN'東北地區WHENAreaSEQ=4THEN'華東地區'ELSE'其他地區'ENDFROM(SELECTSalesNumber,ROW_NUMBER()OVER(ORDERBYROWID)DateSEQ,ROUND(dbms_random.VALUE(1,5))AreaSEQFROMComputerSales)A;DROPTABLEComputerSales;RENAMEComputerSalesBAKTOComputerSales;--移動平均值,累計求和,當前窗口平均值,當前窗口求和,以及窗口函數和排序函數的作用域SELECTArea,SalesDate,SalesNumber,MIN(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)ASmin_Area_SalesDate,MAX(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)ASmax_Area_SalesDate,AVG(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)ASavg_Area_SalesDate,SUM(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)ASsum_Area_SalesDate,COUNT(*)OVER(PARTITIONBYAreaORDERBYSalesDate)AScount_Area,MIN(SalesNumber)OVER(PARTITIONBYArea)ASmin_Area,MAX(SalesNumber)OVER(PARTITIONBYArea)ASmax_Area,AVG(SalesNumber)OVER(PARTITIONBYArea)ASavg_Area,SUM(SalesNumber)OVER(PARTITIONBYArea)ASsum_Area,COUNT(*)OVER(PARTITIONBYArea)AScount_AreaFROMComputerSales-觀察Rank、Dense_Rank,Row_number,CoUnt的區別-Rank跳號，Dense_Rank不跳號，Row_number唯一,Count按統計數計也跳號如果PARTITIONBY和orderby的字段是唯一的話,則這四個函數沒什么區別SELECTArea,SalesDate,SalesNumber,RANK()OVER(PARTITIONBYAreaorderbySalesNumber)ASRank_Area_SalesNumber,DENSE_RANK()OVER(PARTITIONBYAreaorderbySalesNumber)ASDenseRank_Area_SalesNumber,ROW_NUMBER()OVER(PARTITIONBYAreaorderbySalesNumber)ASRownumber_Area_SalesNumber,COUNT(*)OVER(PARTITIONBYAreaorderbySalesNumber)ASCountAll_Area_SalesNumber,COUNT(SalesNumber)OVER(PARTITIONBYAreaorderbySalesNumber)ASCount_Area_SalesNumberFROMComputerSales一觀察Lag和Lead的異同，以及Lag參數之間的異同一缺省情況下Lag取前一行的值,Lead取后一行的值--Lag、lead的第一個參數決定了取行的位置,第二個參數為取不到值時的缺省值SELECTArea,SalesDate,SalesNumber,LAG(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)ASLag_Area_SalesNumber,LEAD(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)ASLead_Area_SalesNumber,LAG(SalesNumber,1)OVER(PARTITIONBYAreaorderbySalesDate)ASLag1_Area_SalesNumber,LAG(SalesNumber,2)OVER(PARTITIONBYAreaorderbySalesDate)ASLag2_Area_SalesNumber,LEAD(SalesNumber,1)OVER(PARTITIONBYAreaorderbySalesDate)ASLead1_Area_SalesNumber,LEAD(SalesNumber,2)OVER(PARTITIONBYAreaorderbySalesDate)ASLead2_Area_SalesNumber,LAG(SalesNumber,1,0)OVER(PARTITIONBYAreaorderbySalesDate)ASLag10_Area_SalesNumber,LAG(SalesNumber,2,1)OVER(PARTITIONBYAreaorderbySalesDate)ASLag21_Area_SalesNumber,LEAD(SalesNumber,1,0)OVER(PARTITIONBYAreaorderbySalesDate)ASLead10_Area_SalesNumber,LEAD(SalesNumber,2,1)OVER(PARTITIONBYAreaorderbySalesDate)ASLead21_Area_SalesNumberFROMComputerSales--觀察First_Value和Last_Value的不同一如果取同一個同組中最大值最小值對應的某歹列,使用FIRST_VALUE,按照升降序排列即可--LAST_VALUE有些像兩次分組所求的最后一行SELECTArea,SalesDate,SalesNumber,FIRST_VALUE(SalesDate)OVER(PARTITIONBYAreaorderbySalesNumber)ASFirstValue_Area,FIRST_VALUE(SalesDate)OVER(PARTITIONBYAreaorderbySalesNumberDESC)ASFirstValue_Area_Desc,LAST_VALUE(SalesDate)OVER(PARTITIONBYAreaorderbySalesNumber)ASLastValue_Area,LAST_VALUE(SalesDate)OVER(PARTITIONBYAreaorderbySalesNumberDESC)ASLastValue_Area_DescFROMComputerSales一與上面不同的是,KEEP需要和DENSE_RANKFIRST|DENSE_RANKLAST配合使用，且取的是相同Area中按SalesNumber排序所獲得最大或最小的值,而上面只是取第一行或最后一行SELECTArea,SalesDate,SalesNumber,DENSE_RANK()OVER(PARTITIONBYAreaORDERBYSalesNumber)DENSE_RANK,MIN(SalesDate)KEEP(DENSE_RANKFIRSTORDERBYSalesNumber)OVER(PARTITIONBYArea)min_first,MIN(SalesDate)KEEP(DENSE_RANKLASTORDERBYSalesNumber)OVER(PARTITIONBYArea)min_last,MAX(SalesDate)KEEP(DENSE_RANKFIRSTORDERBYSalesNumber)OVER(PARTITIONBYArea)max_first,MAX(SalesDate)KEEP(DENSE_RANKLASTORDERBYSalesNumber)OVER(PARTITIONBYArea)max_lastFROMComputerSales--CUME_DIST和PERCENT_RANK差不多，都是累計計算比例，只不過計算基準不同,CUME_DIST更符合一般的做法--NTILE把數據平分為若干份,更適合用來計算四分位上的值--RATIO_TO_REPORT,則是求當前值在分區中的比例，且不能與ORDERBY合起來使用--PERCENTILE_DISC和PERCENTILE_CONT,則是給定的比例參數所對應的值，一般使用PERCENTILE_DISC即可SELECTArea,SalesDate,SalesNumber,ROUND(CUME_DIST()OVER(PARTITIONBYAreaORDERBYSalesNumber),2)cume_dist,ROUND(PERCENT_RANK()OVER(PARTITIONBYAreaORDERBYSalesNumber),2)PERCENT_RANK,ROUND(RATIO_TO_REPORT(SalesNumber)OVER(PARTITIONBYArea),2)RATIO_TO_REPORT,NTILE(4)OVER(PARTITIONBYAreaORDERBYSalesNumber)NTILE,PERCENTILE_DISC(0.7)WITHINGROUP(ORDERBYSalesNumber)OVER(PARTITIONBYArea)PERCENTILE_DISC,PERCENTILE_CONT(0.7)WITHINGROUP(ORDERBYSalesNumber)OVER(PARTITIONBYArea)PERCENTILE_CONTFROMComputerSales--增加了一列叫銷售額,可以進行相關數理統計CREATETABLEComputerSalesBAKASSELECTSalesNumber,ROUND(SalesNumber*10+5*DBMS_RANDOM.VALUE(1,10))SalesValue,TRUNC(SYSDate)+MOD(A.DateSEQ-1,24)SalesDate,CASEWHENTRUNC((DateSEQ-1)/24)=1THEN'華南地區'WHENTRUNC((DateSEQ-1)/24)=2THEN'華北地區'WHENTRUNC((DateSEQ-1)/24)=3THEN'東北地區'WHENTRUNC((DateSEQ-1)/24)=4THEN'華東地區'ELSE'其他地區'ENDAreaFROM(SELECTSalesNumber,ROW_NUMBER()OVER(ORDERBYROWID)DateSEQFROMComputerSales)A;DROPTABLEComputerSales;RENAMEComputerSalesBAKTOComputerSales;SELECT*FROMComputerSales;--其他統計,對數理分析有研究的同學可以嘗試一下其經濟學含義SELECTArea,SalesDate,SalesValue,SalesNumber,REGR_SLOPE(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBYSalesDate)"斜率",REGR_INTERCEPT(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBYSalesDate)"截距",REGR_R2(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBYSalesDate)"回歸線決定系數",REGR_AVGX(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBYSalesDate)"回歸線自變量平均值",REGR_AVGY(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBYSalesDate)"回歸線