与网站建设有关的课程和知识点,电子商务营销网站建设,安徽专业建网站,国家时事新闻Oracle 分析函数——数据分布函数及报表 函数CUME_DIST功能描述#xff1a;计算一行在组中的相对位置#xff0c; CUME_DIST 总是返回大于 0 、小于或等于 1 的数#xff0c;该数表示该行在 N 行中的位置。例如#xff0c;在一个 3 行的组中#xff0c;返回的累计分布值为…Oracle 分析函数——数据分布函数及报表 函数CUME_DIST功能描述计算一行在组中的相对位置 CUME_DIST 总是返回大于 0 、小于或等于 1 的数该数表示该行在 N 行中的位置。例如在一个 3 行的组中返回的累计分布值为 1/3 、 2/3 、 3/3SAMPLE 下例中计算每个部门的员工按薪水排序依次累积出现的分布百分比SELECTdepartment_id,first_name|| ||last_name employee_name,salary,CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_distFROM employeesNTILE功能描述将一个组分为 表达式 的散列表示例如如果表达式 4 则给组中的每一行分配一个数(从 1 到 4 )如果组中有 20 行则给前 5 行分配 1 给下 5 行分配 2 等等。如果组的基数不能由表达式值平均分开则对这些行进行分配时组中就没有任何 percentile 的行数比其它 percentile 的行数超过一行最低的 percentile 是那些拥有额外行的 percentile 。例如若表达式 4 行数 21 则 percentile1 的有 5 行 percentile2 的有 5 行等等。SAMPLE 下例中把 6 行数据分为 4 份SELECTdepartment_id,first_name|| ||last_name employee_name,salary,NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartileFROM employeesPERCENT_RANK功能描述和 CUME_DIST (累积分配)函数类似对于一个组中给定的行来说在计算那行的序号时先减 1 然后除以 n-1 ( n 为组中所有的行数)。该函数总是返回 0 1 (包括 1 )之间的数。SAMPLE 下例中如果 Khoo 的 salary 为 2900 则 pr 值为 0.6 因为 RANK 函数对于等值的返回序列值是一样的SELECTdepartment_id,first_name|| ||last_name employee_name,salary,PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS prFROM employeesORDER BY department_id,salary;PERCENTILE_DISC功能描述返回一个与输入的分布百分比值相对应的数据值分布百分比的计算方法见函数 CUME_DIST 如果没有正好对应的数据值就取大于该分布值的下一个值。注意本函数与 PERCENTILE_CONT 的区别在找不到对应的分布值时返回的替代值的计算方法不同SAMPLE 下例中 0.7 的分布值在部门 30 中没有对应的 Cume_Dist 值所以就取下一个分布值 0.83333333 所对应的 SALARY 来替代SELECTdepartment_id,first_name|| ||last_name employee_name,salary,PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary ) OVER (PARTITION BY department_id) Percentile_Disc,CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) Cume_DistFROM employeesPERCENTILE_CONT功能描述返回一个与输入的分布百分比值相对应的数据值分布百分比的计算方法见函数 PERCENT_RANK 如果没有正好对应的数据值就通过下面算法来得到值RN 1 (P*(N-1)) 其中 P 是输入的分布百分比值 N 是组内的行数CRN CEIL(RN) FRN FLOOR(RN)if (CRN FRN RN) then(value of expression from row at RN)else(CRN - RN) * (value of expression for row at FRN) (RN - FRN) * (value of expression for row at CRN)注意本函数与 PERCENTILE_DISC 的区别在找不到对应的分布值时返回的替代值的计算方法不同算法太复杂看不懂了 LSAMPLE 在下例中对于部门 60 的 Percentile_Cont 值计算如下P0.7 N5 RN 1 (P*(N-1)1(0.7*(5-1))3.8 CRN CEIL(3.8)4FRN FLOOR(3.8)3( 4 - 3.8 ) * 4800 (3.8 - 3) * 6000 5760SELECTdepartment_id,first_name|| ||last_name employee_name,salary,PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) Percentile_Disc,PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) Percentile_Cont,PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) Percent_RankFROM employees总案例SELECTdepartment_id,first_name|| ||last_name employee_name,salary,CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_dist, -- 数据分布百分比NTILE(4) OVER (PARTITION BY department_id ORDER BY salary) AS quartile, -- 数据分布以 NTILE 中的 exp 来计算PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pr, -- 数据分布百分比从 0 开始计PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary ) OVER (PARTITION BY department_id) Percentile_Disc, -- 输入的分布百分比值相对应的数据值PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) Percentile_Cont -- 表达式太复杂了 ...FROM employeesRATIO_TO_REPORT功能描述该函数计算 expression/(sum(expression)) 的值它给出相对于总数的百分比即当前行对 sum(expression) 的贡献。SAMPLE 下例计算每个员工的工资占该类员工总工资的百分比SELECTdepartment_id,first_name|| ||last_name employee_name,salary,RATIO_TO_REPORT(salary) OVER () AS rrFROM employeesWHERE job_id PU_CLERK;REGR_ (Linear Regression) Functions功能描述这些线性回归函数适合最小二乘法回归线有 9 个不同的回归函数可使用。REGR_SLOPE 返回斜率等于 COVAR_POP(expr1, expr2) / VAR_POP(expr2)REGR_INTERCEPT 返回回归线的 y 截距等于AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)REGR_COUNT 返回用于填充回归线的非空数字对的数目REGR_R2 返回回归线的决定系数计算式为If VAR_POP(expr2) 0 then return NULLIf VAR_POP(expr1) 0 and VAR_POP(expr2) ! 0 then return 1If VAR_POP(expr1) 0 and VAR_POP(expr2 ! 0 thenreturn POWER(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 返回值等于 REGR_COUNT(expr1, expr2) * VAR_POP(expr1)REGR_SXY: 返回值等于 REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)(下面的例子都是在 SH 用户下完成的)SAMPLE 1 下例计算 1998 年最后三个星期中两种产品( 260 和 270 )在周末的销售量中已×××数量和总数量的累积斜率和回归线的截距SELECT t.fiscal_month_number Month, t.day_number_in_month Day,REGR_SLOPE(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_SLOPE,REGR_INTERCEPT(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_ICPTFROM sales s, times tWHERE s.time_id t.time_idAND s.prod_id IN (270, 260)AND t.fiscal_year1998AND t.fiscal_week_number IN (50, 51, 52)AND t.day_number_in_week IN (6,7)ORDER BY t.fiscal_month_desc, t.day_number_in_month;SAMPLE 2 下例计算 1998 年 4 月每天的累积交易数量SELECT UNIQUE t.day_number_in_month,REGR_COUNT(s.amount_sold, s.quantity_sold)OVER (PARTITION BY t.fiscal_month_number ORDER BY t.day_number_in_month)Regr_CountFROM sales s, times tWHERE s.time_id t.time_idAND t.fiscal_year 1998 AND t.fiscal_month_number 4;SAMPLE 3 下例计算 1998 年每月销售量中已×××数量和总数量的累积回归线决定系数SELECT t.fiscal_month_number,REGR_R2(SUM(s.amount_sold), SUM(s.quantity_sold))OVER (ORDER BY t.fiscal_month_number) Regr_R2FROM sales s, times tWHERE s.time_id t.time_idAND t.fiscal_year 1998GROUP BY t.fiscal_month_numberORDER BY t.fiscal_month_number;SAMPLE 4 下例计算 1998 年 12 月最后两周产品 260 的销售量中已×××数量和总数量的累积平均值SELECT t.day_number_in_month,REGR_AVGY(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)Regr_AvgY,REGR_AVGX(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)Regr_AvgXFROM sales s, times tWHERE s.time_id t.time_idAND s.prod_id 260AND t.fiscal_month_desc 1998-12AND t.fiscal_week_number IN (51, 52)ORDER BY t.day_number_in_month;SAMPLE 5 下例计算产品 260 和 270 在 1998 年 2 月周末销售量中已×××数量和总数量的累积 REGR_SXY, REGR_SXX, and REGR_SYY 统计值SELECT t.day_number_in_month,REGR_SXY(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) Regr_sxy,REGR_SYY(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) Regr_syy,REGR_SXX(s.amount_sold, s.quantity_sold)OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) Regr_sxxFROM sales s, times tWHERE s.time_id t.time_idAND prod_id IN (270, 260)AND t.fiscal_month_desc 1998-02AND t.day_number_in_week IN (6,7)ORDER BY t.day_number_in_month;
