Oracle分析函数六——数据分布函数及报表函数

原创于2009年08月02日，2009年10月22日迁移至此。

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 ，则 percentile=1 的有 5 行， percentile=2 的有 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-1 （ n 为组中所有的行数）。该函数总是返回 0 ～ 1 （包括 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

 

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 值计算如下：

P=0.7 N=5 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_year=1998

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;