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

Linux操作系统 作者：蓝色妖姬09 时间：2013-11-18 14:08:32 0 删除 编辑
Oracle分析函数——数据分布函数及报表函数

CUME_DIST

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

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

SAMPLE：下例中如果Khoosalary2900，则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

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

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)

SAMPLE：在下例中，对于部门60Percentile_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

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

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年最后三个星期中两种产品（260270）在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距

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：下例计算19984月每天的累积交易数量

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：下例计算199812月最后两周产品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：下例计算产品26027019982月周末销售量中已开发票数量和总数量的累积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;

image044.jpg

image046.jpg

image048.jpg

image050.jpg

image052.jpg

image054.jpg

image056.jpg

• 博文量
8
• 访问量
7951