Oracle分析函数、多维函数和Model函数简要说明,主要针对BI报表统计

以下代码均经过测试,可直接运行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)"回归线