资源描述:
《oracle9i分析函数》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、``ORACLE9I分析函数[2006-9-309:26:00
2、By:hewy0526]RATIO_TO_REPORT功能描述:该函数计算expression/(sum(expression))的值,它给出相对于总数的百分比,即当前行对sum(expression)的贡献。SAMPLE:下例计算每个员工的工资占该类员工总工资的百分比SELECTlast_name,salary,RATIO_TO_REPORT(salary)OVER()ASrr
FROMemployeesWHEREjob_id='PU_CL
3、ERK';LAST_NAMESALARYRR
---------------------------------------------
Khoo3100.223021583
Baida2900.208633094
Tobias2800.201438849
Himuro2600.18705036
Colmenares2500.179856115REGR_(LinearRegression)Functions功能描述:这些线性回归函数适合最小二乘法回归线,有9个不同的回归函数可使用。REGR_SLOPE:返
4、回斜率,等于COVAR_POP(expr1,expr2)/VAR_POP(expr2)
REGR_INTERCEPT:返回回归线的y截距,等于AVG(expr1)-REGR_SLOPE(expr1,expr2)*AVG(expr2)REGR_COUNT:返回用于填充回归线的非空数字对的数目REGR_R2:返回回归线的决定系数,计算式为:IfVAR_POP(expr2)=0thenreturnNULLIfVAR_POP(expr1)=0andVAR_POP(expr2)!=0thenreturn1
IfVA
5、R_POP(expr1)>0andVAR_POP(expr2!=0then
returnPOWER(CORR(expr1,expr),2)REGR_AVGX:计算回归线的自变量(expr2)的平均值,去掉了空对(expr1,expr2)后,等于AVG(expr2)REGR_AVGY:计算回归线的应变量(expr1)的平均值,去掉了空对(expr1,expr2)后,等于AVG(expr1)REGR_SXX:返回值等于REGR_COUNT(expr1,expr2)*VAR_POP(expr2)REGR_SYY
6、:返回值等于REGR_COUNT(expr1,expr2)*VAR_POP(expr1)REGR_SXY:返回值等于REGR_COUNT(expr1,expr2)*COVAR_POP(expr1,expr2)`````(下面的例子都是在SH用户下完成的)SAMPLE1:下例计算1998年最后三个星期中两种产品(260和270)在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距SELECTt.fiscal_month_number"Month",t.day_number_in_month"Day"
7、,
REGR_SLOPE(s.amount_sold,s.quantity_sold)
OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)AS
CUM_SLOPE,REGR_INTERCEPT(s.amount_sold,s.quantity_sold)
OVER(ORDERBYt.fiscal_month_desc,t.day_number_in_month)AS
CUM_ICPTFROMsaless,timestWHEREs.time_id=t
8、.time_idANDs.prod_idIN(270,260)ANDt.fiscal_year=1998ANDt.fiscal_week_numberIN(50,51,52)
ANDt.day_number_in_weekIN(6,7)ORDERBYt.fiscal_month_desc,t.day_number_in_month;MonthDayCUM_SLOPECUM_ICPT
----------------------------------------
1212-6818721212-68187
9、21213-20.2448981254.36735
1213-20.2448981254.36735
1219-18.8260871287
122062.4561404125.28655
122062.4561404125.28655
122062.4561404125.28655
122062.4561404125.28655
122667.265822858.9712313
122667.265822858.9712313