数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

110个oracle常用函数总结(8)


发布日期:2020年01月06日
 
110个oracle常用函数总结(8)

RATIO_TO_REPORT功能描述该函数计算expression/(sum(expression))的值它给出相对于总数的百分比即当前行对sum(expression)的贡献

SAMPLE下例计算每个员工的工资占该类员工总工资的百分比

SELECT last_name salary RATIO_TO_REPORT(salary) OVER () AS rr

FROM employees

WHERE job_id = PU_CLERK;

LAST_NAME SALARY RR

Khoo Baida Tobias Himuro Colmenares

REGR_ (Linear Regression) Functions功能描述这些线性回归函数适合最小二乘法回归线个不同的回归函数可使用

REGR_SLOPE返回斜率等于COVAR_POP(expr expr) / VAR_POP(expr) REGR_INTERCEPT返回回归线的y截距等于AVG(expr) REGR_SLOPE(expr expr) * AVG(expr)

REGR_COUNT返回用于填充回归线的非空数字对的数目

REGR_R返回回归线的决定系数计算式为

If VAR_POP(expr) = then return NULL

If VAR_POP(expr) = and VAR_POP(expr) != then return If VAR_POP(expr) > and VAR_POP(expr != then return POWER(CORR(exprexpr)) REGR_AVGX计算回归线的自变量(expr)的平均值去掉了空对(expr expr)后等于AVG(expr) REGR_AVGY计算回归线的应变量(expr)的平均值去掉了空对(expr expr)后等于AVG(expr) REGR_SXX 返回值等于REGR_COUNT(expr expr) * VAR_POP(expr) REGR_SYY 返回值等于REGR_COUNT(expr expr) * VAR_POP(expr) REGR_SXY: 返回值等于REGR_COUNT(expr expr) * COVAR_POP(expr expr)

(下面的例子都是在SH用户下完成的)

SAMPLE 下例计算年最后三个星期中两种产品()在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距

SELECT tfiscal_month_number Month tday_number_in_month Day REGR_SLOPE(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month) AS CUM_SLOPE REGR_INTERCEPT(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month) AS CUM_ICPT FROM sales s times t WHERE stime_id = ttime_id AND sprod_id IN ( ) AND tfiscal_year= AND tfiscal_week_number IN ( ) AND tday_number_in_week IN () ORDER BY tfiscal_month_desc tday_number_in_month;

Month Day CUM_SLOPE CUM_ICPT

SAMPLE 下例计算月每天的累积交易数量

SELECT UNIQUE tday_number_in_month REGR_COUNT(samount_sold squantity_sold) OVER (PARTITION BY tfiscal_month_number ORDER BY tday_number_in_month)

Regr_Count

FROM sales s times t WHERE stime_id = ttime_id AND tfiscal_year = AND tfiscal_month_number = ;

DAY_NUMBER_IN_MONTH Regr_Count

SAMPLE 下例计算年每月销售量中已开发票数量和总数量的累积回归线决定系数

SELECT tfiscal_month_number REGR_R(SUM(samount_sold) SUM(squantity_sold)) OVER (ORDER BY tfiscal_month_number) Regr_R FROM sales s times t WHERE stime_id = ttime_id AND tfiscal_year = GROUP BY tfiscal_month_number ORDER BY tfiscal_month_number;

FISCAL_MONTH_NUMBER Regr_R

SAMPLE 下例计算月最后两周产品的销售量中已开发票数量和总数量的累积平均值

SELECT tday_number_in_month REGR_AVGY(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month) Regr_AvgY REGR_AVGX(samount_sold squantity_sold) OVER (ORDER BY tfiscal_month_desc tday_number_in_month)

Regr_AvgX

FROM sales s times t WHERE stime_id = ttime_id AND sprod_id = AND tfiscal_month_desc = AND tfiscal_week_number IN ( ) ORDER BY tday_number_in_month;

DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX

SAMPLE 下例计算产品月周末销售量中已开发票数量和总数量的累积REGR_SXY REGR_SXX and REGR_SYY统计值

SELECT tday_number_in_month REGR_SXY(samount_sold squantity_sold) OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_sxy REGR_SYY(samount_sold squantity_sold) OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_syy REGR_SXX(samount_sold squantity_sold) OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_sxx FROM sales s times t WHERE stime_id = ttime_id AND prod_id IN ( ) AND tfiscal_month_desc = AND tday_number_in_week IN () ORDER BY tday_number_in_month;

DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx

ROW_NUMBER功能描述返回有序组中一行的偏移量从而可用于按特定标准排序的行号

SAMPLE下例返回每个员工再在每个部门中按员工号排序后的顺序号

SELECT department_id last_name employee_id ROW_NUMBER()

OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id

FROM employees

WHERE department_id < ;

DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID

Whalen

Hartstein

Fay

Raphaely

Khoo

Baida

Tobias

Himuro

Colmenares

Mavris

STDDEV功能描述计算当前行关于组的标准偏离(Standard Deviation)

SAMPLE下例返回部门按雇佣日期排序的薪水值的累积标准偏离

SELECT last_name hire_datesalary

STDDEV(salary) OVER (ORDER BY hire_date) StdDev

FROM employees

WHERE department_id = ;

LAST_NAME HIRE_DATE SALARY StdDev

Raphaely

Khoo Tobias Baida Himuro Colmenares

STDDEV_POP功能描述该函数计算总体标准偏离并返回总体变量的平方根其返回值与VAR_POP函数的平方根相同(Standard Deviation-Population)

SAMPLE下例返回部门的薪水值的总体标准偏差

SELECT department_id last_name salary

STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std

FROM employees

WHERE department_id in ();

DEPARTMENT_ID LAST_NAME SALARY POP_STD

Hartstein

Fay

Raphaely Khoo Baida Colmenares Himuro Tobias Hunold Ernst Austin Pataballa Lorentz

STDDEV_SAMP功能描述 该函数计算累积样本标准偏离并返回总体变量的平方根其返回值与VAR_POP函数的平方根相同(Standard Deviation-Sample)

SAMPLE下例返回部门的薪水值的样本标准偏差

SELECT department_id last_name hire_date salary

STDDEV_SAMP(salary) OVER

(PARTITION BY department_id ORDER BY hire_date

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev

FROM employees

WHERE department_id in ();

DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV

Hartstein

Fay

Raphaely

Khoo Tobias Baida Himuro Colmenares

Hunold

Ernst Austin Pataballa Lorentz

SUM功能描述该函数计算组中表达式的累积和

SAMPLE下例计算同一经理下员工的薪水累积值

SELECT manager_id last_name salary

SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary

RANGE UNBOUNDED PRECEDING) l_csum

FROM employees

WHERE manager_id in ();

MANAGER_ID LAST_NAME SALARY L_CSUM

Whalen

Mavris

Baer

Greenberg

Higgins

Lorentz

Austin

Pataballa

Ernst

Popp

Sciarra

Urman

Chen

Faviet

VAR_POP功能描述(Variance Population)该函数返回非空集合的总体变量(忽略null)VAR_POP进行如下计算

(SUM(expr) SUM(expr) / COUNT(expr)) / COUNT(expr)

SAMPLE下例计算年每月销售的累积总体和样本变量(本例在SH用户下运行)

SELECT tcalendar_month_desc VAR_POP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Pop VAR_SAMP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Samp FROM sales s times t WHERE stime_id = ttime_id AND tcalendar_year = GROUP BY tcalendar_month_desc;

CALENDAR Var_Pop Var_Samp

E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+

VAR_SAMP功能描述(Variance Sample)该函数返回非空集合的样本变量(忽略null)VAR_POP进行如下计算

(SUM(expr*expr)SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr))

SAMPLE下例计算年每月销售的累积总体和样本变量

SELECT tcalendar_month_desc VAR_POP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Pop VAR_SAMP(SUM(samount_sold)) OVER (ORDER BY tcalendar_month_desc) Var_Samp FROM sales s times t WHERE stime_id = ttime_id AND tcalendar_year = GROUP BY tcalendar_month_desc;

CALENDAR Var_Pop Var_Samp

E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+

VARIANCE功能描述该函数返回表达式的变量Oracle计算该变量如下如果表达式中行数为则返回如果表达式中行数大于则返回VAR_SAMP

SAMPLE下例返回部门按雇佣日期排序的薪水值的累积变化

SELECT last_name salary VARIANCE(salary)

OVER (ORDER BY hire_date) Variance

FROM employees

WHERE department_id = ;

LAST_NAME SALARY Variance

Raphaely

Khoo

Tobias Baida

Himuro

Colmenares

上一篇:使用Oracle的外部表查询警告日志文件

下一篇:学会使用Oracle9i带有tablespace的空间管理管理工具