国外网站,北京微信网站开发报价,网页游戏网站斗地主,2017 如何做网站优化Oracle分析函数——数据分布函数及报表函数 CUME_DIST 功能描述#xff1a;计算一行在组中的相对位置#xff0c;CUME_DIST总是返回大于0、小于或等于1的数#xff0c;该数表示该行在N行中的位置。例如#xff0c;在一个3行的组中#xff0c;返回的累计分布值为1/3、2/3、…Oracle分析函数——数据分布函数及报表函数 CUME_DIST 功能描述计算一行在组中的相对位置CUME_DIST总是返回大于0、小于或等于1的数该数表示该行在N行中的位置。例如在一个3行的组中返回的累计分布值为1/3、2/3、3/3 SAMPLE下例中计算每个部门的员工按薪水排序依次累积出现的分布百分比 SELECT department_id, first_name|| ||last_name employee_name, salary, CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_dist FROM employees NTILE 功能描述将一个组分为表达式的散列表示例如如果表达式4则给组中的每一行分配一个数从1到4如果组中有20行则给前5行分配1给下5行分配2等等。如果组的基数不能由表达式值平均分开则对这些行进行分配时组中就没有任何percentile的行数比其它percentile的行数超过一行最低的percentile是那些拥有额外行的percentile。例如若表达式4行数21则percentile1的有5行percentile2的有5行等等。 SAMPLE下例中把6行数据分为4份 SELECT department_id, first_name|| ||last_name employee_name, salary, NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile FROM employees PERCENT_RANK 功能描述和CUME_DIST累积分配函数类似对于一个组中给定的行来说在计算那行的序号时先减1然后除以n-1n为组中所有的行数。该函数总是返回01包括1之间的数。 SAMPLE下例中如果Khoo的salary为2900则pr值为0.6因为RANK函数对于等值的返回序列值是一样的 SELECT department_id, first_name|| ||last_name employee_name, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pr FROM employees ORDER BY department_id,salary; PERCENTILE_DISC 功能描述返回一个与输入的分布百分比值相对应的数据值分布百分比的计算方法见函数CUME_DIST如果没有正好对应的数据值就取大于该分布值的下一个值。 注意本函数与PERCENTILE_CONT的区别在找不到对应的分布值时返回的替代值的计算方法不同 SAMPLE下例中0.7的分布值在部门30中没有对应的Cume_Dist值所以就取下一个分布值0.83333333所对应的SALARY来替代 SELECT department_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_Dist FROM employees!--[if !vml]--!--[endif]-- PERCENTILE_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的区别在找不到对应的分布值时返回的替代值的计算方法不同 算法太复杂看不懂了L SAMPLE在下例中对于部门60的Percentile_Cont值计算如下 P0.7 N5 RN 1 (P*(N-1)1(0.7*(5-1))3.8 CRN CEIL(3.8)4 FRN FLOOR(3.8)3 4 - 3.8* 4800 (3.8 - 3) * 6000 5760 SELECT department_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_Rank FROM employees 总案例 SELECT department_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 employees RATIO_TO_REPORT 功能描述该函数计算expression/(sum(expression))的值它给出相对于总数的百分比即当前行对sum(expression)的贡献。 SAMPLE下例计算每个员工的工资占该类员工总工资的百分比 SELECT department_id, first_name|| ||last_name employee_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr FROM employees WHERE 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 NULL If VAR_POP(expr1) 0 and VAR_POP(expr2) ! 0 then return 1 If VAR_POP(expr1) 0 and VAR_POP(expr2 ! 0 then return 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_ICPT FROM sales s, times t WHERE s.time_id t.time_id AND s.prod_id IN (270, 260) AND t.fiscal_year1998 AND 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_Count FROM sales s, times t WHERE s.time_id t.time_id AND 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_R2 FROM sales s, times t WHERE s.time_id t.time_id AND t.fiscal_year 1998 GROUP BY t.fiscal_month_number ORDER 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_AvgX FROM sales s, times t WHERE s.time_id t.time_id AND s.prod_id 260 AND t.fiscal_month_desc 1998-12 AND 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_sxx FROM sales s, times t WHERE s.time_id t.time_id AND prod_id IN (270, 260) AND t.fiscal_month_desc 1998-02 AND t.day_number_in_week IN (6,7) ORDER BY t.day_number_in_month;转载于:https://www.cnblogs.com/huozhicheng/archive/2010/09/03/2533172.html
