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

以下代码均经过测试,可直接运行

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

不一定很全面，但对BI应用场景做了少许说明

一创建一张销售数量表,数据趋势是递增的

CREATETABLEComputerSalesAS

SELECT

120+TRUNC(rn/12)+R0UND(DBMS_RAND0M.VALUE(1,10))SalesNumber

FROM

(

SELECTlevel,ROWNUMrn

FROMDUAL

CONNECTBYROWNUMCI20

)；

一下面用于比较NULL值和非NULL值的统计，可以看出NULL值情况下的

COUNT是存在问题的，所以建议数据库系统中最好不要使用NLLL值列

SELECT

COUNT(*),

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;

SELECT

COUNT(*),

COUNT(a.SalesNumber),

COUNT(DISTINCTa.SalesNumber),

SUM(a.SalesNumber),

AVG(a.Sa1esNumbcr),

MAX(a.SalesNumber),

MIN(a.SalesNumber)

FROMComputerSalcsA;

SELECTtrunc(dbms_random.valued,101)),

DELETEFROMComputerSalesWHERESalesNumberISNULL;

COMMIT;

一创建增加了日期字段的表

CREATETABLEComputerSalesBAKAS

SELECTSalesNumber,TRUNC(SYSDate)+MOD(A.DateSEQ-l,10)SalesDate

FROM(SELECTSalesNumber,ROWNUMBER()OVER(ORDERBYROWID)DateSEQ

FROMComputerSales)A;

DROPTABLEComputerSales;

RENAMEComputerSalesBAKTOComputerSales;

一下面是两种创建方式，构招Area列和日期列

CREATETABLEComputerSalesBAKAS

SELECTSalesNumber,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'其他地区'

END

FROM(SELECTSalesNumber,ROW_NUMBER()OVER(ORDERBYROWID)DateSEQ

FROMComputerSales)A;

DROPTABLEComputerSales;

RENAMEComputerSalesBAKTOComputerSales;

一该例可构造SalcsDatc和Area的重复数据

CREATETABLEComputerSalesBAKAS

SELECTSalesNumber,

TRUNC(SYSDATE)+MOD(A.DateSEQ-1,10)SalesDate,

CASEWHENAreaSEQ=lTHEN'华南地区'

WHENAreaSEQ=2THEN'华北地区'

WHENAreaSEQ=3THEN'东北地区

WHENAreaSEQ=4THEN'华东地区'

ELSE'其他地区'

END

FROM(SELECTSalesNumber,ROWNUMBER()OVER(ORDERBYROWTD)

DateSEQ,ROUND(clbms_random.VALUE(1,5))AreaSE2FROMComputerSales)A;

DROPTABLEComputerSales;

RENAMEComputerSalesBAKTOComputerSales;

一移动平均值，累计求和，当前窗口平均值，当前窗口求和，以及窗口函数和排

序函数的作用域

SELECT

Area,SalesDate,SalesNumbcr,

MIN(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)AS

minAreaSdlesDale,

MAX(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)AS

max_Area_SalesDate,

AVG(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)AS

avg_Area_SalesDate,

SUM(SalcsNumbcr)OVER(PARTITIONBYAreaorderbySalesDate)AS

sum_Area_SalesDate,

COUNT(*)OVER(PARTITIONBYAreaORDERBYSalesDate)AScount_Area,

MIN(SalesNumber)OVER(PARTITIONBYArea)ASmin_Area,

MAX(SalesNumber)OVER(PARTITIONBYArea)ASmaxArea,

AVG(SalesNumber)OVER(PARTITIONBYArea)ASavg_Area,

SUM(SalesNumber)OVER(PARTITIONBYArea)ASsum_Area,

COUNT(*)OVER(PARTITIONBYArea)AScount_Area

FROMComputerSales

一观察Rank,DenseRank,Rownumber,Count的区别

--Rank跳号,Dense.Rank不跳号，Rownumber唯一,Count按统计数计也跳号

―如果PARTITIONBY和orderby的字段是唯一的话,则这四个函数没什么

区别

SELECT

Area,SalesDate,SalesNumber,

RANKOOVER(PARTITIONBYAreaorderbySalesNumber)AS

RankAreaSa1esNumber,

DENSERANKOOVER(PARTITIONBYAreaorderbySalesNumber)AS

DenseRankAreaSalesNumber,ROWNUMBER()OVER(PARTITIONBYAreaorder

bySalesNumber)ASRownumberAreaSalesNumber,

COUNT(*)OVER(PARTITIONBYAreaorderbySalesNumber)AS

CountAll_Area_SalesNumber,

COUNT(SalesNumber)OVER(PARTITIONBYAreaorderbySalesNumber)AS

Count_Area_SalesNumber

FROMComputerSales

一观察Lag和Lead的异同，以及Lag参数之间的异同

一缺省情况下Lag取前一行的值,Lead取后一行的值

-Lag.lead的第一个参数决定了取行的位置，第二个参数为取不到值时的缺

省值

SELECT

Area,SalesDate,SalesNumber,

LAG(SalesNumber)OVER(PARTITIONBYAreaorderbySalcsDate)AS

Lag_Area_SalesNumber,

LEAD(SalesNumber)OVER(PARTITIONBYAreaorderbySalesDate)AS

LeadAreaSalesNumber,

LAG(SalesNumber,1)OVER(PARTITIONBYAreaorderbySalesDate)AS

LaglAreaSalesNumber,

LAG(SalesNumber,2)OVER(PARTITIONBYAreaorderbySalesDate)AS

Lag2_Area_SalesNumber,

LEAD(SalesNumber,1)OVER(PARTITIONBYAreaorderbySalesDate)AS

LeadlAreaSalesNumbcr,LEAD(SalesNumber,2)OVER(PARTITIONBYArea

orderbySalesDcite)ASLead2Area_SalesNumber,LAG(SalesNumber,1,0)OVER

(PARTITIONBYAreaorderbySalesDate)ASLag10_Area_Sa1esNumber,

LAG(SalesNumber,2,1)OVER(PARTITIONBYAreaorderbySalesDate)AS

Lag21_Area_SalesNumber,LEAD(SalesNumber,1,0)OVER(PARTITIONBYArea

orderbySalesDate)ASLead10Area_Sa1esNumber,

LEAD(SalesNumber,2,1)OVER(PARTITIONBYAreaorderbySalesDate)

ASLead21_Area_SalesNumber

EROMComputerSales

一观察First_Value和LastJValue的不同

一如果取同一个同组中最大值最小值对应的某列，使用FIRST_VALUE,按照升

降序排列即可

-LAST_VAI,UE有些像两次分组所求的最后一行

SELECT

Area,SalesDate,SalesNumber,

FTRST_VALUE(SalesDcite)OVER(PARTITIONBYAreaorderbySalesNumber)

ASFirstValue_Area,

FIRSTVALUE(SalesDate)OVER(PARTITION3YAreaorderbySalesNumber

DESC)ASFirstValueArea.Desc,

LAST_VALUE(SalesDate)OVER(PARTITIONBYAreaorderbySalesNumber)

ASLastValue_Area,

LAST_VALUE(SalesDate)OVER(PARTITIONBYAreaorderbySalesNumber

DESC)ASLastValue_Area_Desc

FROMComputerSalcs

一与上面不同的是,KEEP需要和DENSE_RANKFIRST|DENSE_RANKLAST配合

使用,且取的是相同Area中按SalesNumber排序所获得最大或最小的值，而上面只

是取第一行或最后一行

SELECTArea,SalesDate,SalesNumber,

DENSERANK()OVER(PARTITIONBYAreaORDERBYSalesNumber)

DENSE_RANK,

MTN(SalesDate)KEEP(DENSE_RANKFIRSTORDERBYSalesNumber)

OVER(PARTITIONBYArea)min_first,

MIN(SalesDate)KEEP(DENSE,RANKLASTORDERBYSalesNumber)

OVER(PARTITIONBYArea)min」ast,

MAX(SalesDate)KEEP(DENSE_RANKFIRSTORDERBYSalesNumber)

OVER(PARTITIONBYArea)max_first,

M^X(SalesDate)KEEP(DENSE_RANKLASTORDERBYSalesNumber)

OVER(PARTITIONBYArea)max_last

FROMComputerSales

-CUME_DIST和PERCENT_RANK差不多，都是累计计算比例，只不过计算基准不

同,CUME_DIST更符合一般的做法

-NTILE把数据平分为若干份，更适合用来计算四分位上的值

一RATIO_TO_REPORT,则是求当前值在分区中的比例，且不能与ORDERBY合起

来使用

—PERCENTILEDISC和PERCENTILECONT,则是给定的比例参数所对应的值，一-

般使用PERCENTILE_DISC即可

SELECTArea,SalesDate,SalesNumber,

ROUND(CUME_D1ST()OVER(PARTITIONBYAreaORDERBYSalesNumber),2)

cume_dist,

ROUND(PERCENT_RANK()OVER(PARTITIONBYAreaORDERBYSalesNumber),2)

PERCENTRANK,ROUND(RATIOTOREPORT(SalesNumber)OVER(PARTITIONBY

Area),2)RATIOTOREPORT,

NTILE(4)OVER(PARTITIONBYAreaORDERBYSalesNumber)NTILE,

PERCENTILE_DISC(O.7)WITHINGROUP(ORDERBYSalesNumber)

OVER(PARTITIONBYArea)PERCENTILEDISC,

PERCENTILE_CONT(O.7)WITHINGROUP(ORDERBYSalesNumber)

OVER(PARTITIONBYArea)PERCENTILE_CONT

FROMComputerSales

一增加了一列叫销售额,可以进行相关数理统计

CREATETABLEComputerSalesBAKAS

SELECTSalesNumber,

ROUND(SalcsNumbcr*10+5*DBMSRANDOM.VALUE(1,10))SalcsValue,

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'其他地区’

ENDArea

FROM(SELECTSalesNumber,ROWNUMBER0OVER(ORDERBYROWID)DateSEQ

FROMComputerSales)A;

DROPTABLEComputerSales;

RENAMEComputerSalesBAKTOComputerSales;

SELECT*FROMComputerSales;

—其他统计•，对数理分析有研究的同学可以尝式一下其经济学含义

SELECTArea,SalesDate,SalesValue,SalesNumber,

REGR^SLOPE(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBY

SalesDate)〃斜率〃，

REGILINTERCEPT(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDER

BYSalesDate)〃截距〃，REGR_K2(SalesValue,SalesNumber)OVER(PARTITIONBY

AreaORDERBYSalesDate)〃回归线决定系数〃,

REGR^AVGX(SalesValue,SalesNumber)OVER(PARTITIONBYAreaORDERBY

SalesDate)〃回归线自变量平均值〃，

REG