oracle 分析函数

为了选用正确的函数，你必须周详地了解目前Oracle SQL中用到的一系列集聚函数。Oracle8和Oracle9都支持的函数包括： 　　　　

AVG 　

CORR 　　

COUNT 　　

COVAR_POP 　　

COVAR_SAMP 　　

MAX 　　

MIN 　　

REGR_ 　　

STDDEV 　　

STDDEV_POP 　　

STDDEV_SAMP 　　

SUM 　　

VAR_POP 　　

VAR_SAMP 　　

VARIANCE 　　　　　

几乎所有的集聚函数都将忽略那些NULL列。不过有一个例外，那就是COUNT(*)，这个函数将计算所有的纪录而不管是否存在NULL列。在一般集聚函数所有数据都为NULL时，集聚函数的返回值为NULL，不过COUNT(col)的返回值为零。　　　　

MIN函数和MAX函数　　

MIN函数和MAX函数根据排序后返回最小值和最大值。你应该明白那些函数都是基于对他们的数据类型的排序结果。为了避免排序错误，日期和数字都必须定义为和其对应的数据类型。例如，MAX(TO_CHAR(date))会把1月排在2月之前。

STDDEV，

STDDEV_POP，

STDDEV_SAMP和VARIANCE，

VAR_POP，

VAR_SAMP 　　　　

标准差和方差是衡量数据扩散范围的标准。如果一列中的数据都相同的话，那么标准差和方差就为零。在实际情况中，标准差描述的是反映平均值的一个锥形曲线。对于现实世界人口，有68%的数据应在一个标准差之内，96%的数据应在2个标准差之内。

据统计表明：人口的样本方差并不能非常好的反映整体人口的方差，这个事实就决定了存在3个标准差函数和3个方差函数。通过以下的SQL脚本来看看他的作用：　　　　

drop table agg; 　　

create table agg(n integer); 　　

begin 　　　　

for i in 0..1000 loop 　　　　　　

insert into agg values(i); 　　　　

end loop; 　　

end; 　　

/ 　　

show errors; 　　

select

’Population’ d,

count(n),

avg(n),

stddev(n),

stddev_pop(n),

stddev_samp(n) 　　　

from agg 　　

union 　　

select

’Sample’,

count(n),

avg(n),

stddev(n),

stddev_pop(n),

stddev_samp(n) 　　　

from agg where mod(n,2) = 0 　　

union 　　

select

’Sample’,

count(n),

avg(n),

stddev(n),

stddev_pop(n),

stddev_samp(n) 　　　

from agg where mod(n,10) = 0 　　

union 　　

select

’Sample’,

count(n),

avg(n),

stddev(n),

stddev_pop(n),

stddev_samp(n) 　　　

from agg where mod(n,100) = 0; 　　

得到的结果是　　 　　

虽然这些数据是人为计算得到的，并没有在此锥形曲线上，不过这个例子反映了_SAMP和_POP的差别。从这个例子看出，样本的STDDEV_POP(N)列的数据比样本的STDDE(N)列数据更接近实际的STDDECV(N)列数据。　　　

因此，当你应用STDDEV函数时，认真考虑你处理的是什么数据是非常重要的。如果你想得到一些数据的真实标准差，那么你就应该用STDDEV_SAMP。如果你只是处理一些小型的抽样样本，只希望得到人口标准差的近似值的话，那么你应该用STDDEV_POP。　　　　

VAR_SAMP就是STDDEV_SAMP的平方，相似的VAR_POP就是STDDEV_POP的平方。这两个函数并不常用，原因是他们的成员都是带平方的。　　　　

COVAR_POP 和 COVAR_SAMP 　　　　　　　

协方差和标准差相似，不过协方差能衡量一列数据对其他列的影响。如果存在一列是不可改动的（意味着次列和其他列无所有关系），那么协方差就为零，表示位COVER_SAMP(n，0)。　　　　如果一列的变化和另一列有关，那么协方差值就会变大。COVER_POP适用于计算的人口样本方差的近似值，而COVER_SAMP则应该用于计算数据的实际协方差。

CORR 　　

相关性是用于衡量相关系数的。他将两列的相关程度规格化为-1和1之间的一个数值。如果是1或-1的话，就说明他们是完整的线性相关。表示位CORR(n，n)。　　　　

如果相关性为零，那么意味着一个变量对另一个变量将没有影响。如果两列根本就没有改动，那就意味着这列的标准差为零，也就是说对其计算相关性是没有意义的，所以此时CORR的返回值为空。举例来说，CORR(n,1)的返回值将为空。

REGR_ 　　

假设已知两数据相关，回溯函数能通过已知数据画一条能反映他们的发展趋势的曲线。并能通过这条线来在已知数据的基础上估计得到新的数据。事实上，由于得到的结果一条线而不是数字,所有的结果都是通过图中的线描述出来的，所以说回溯函数是一系列和众不同的函数。在图中，我们认为第一表达式为“Y轴”，而第二表达式为“X轴”。　　　　

回溯函数是我们能进行一些初步估计。估计近似值的公式为：

pred1 = (pred2 * regr_slope(expr1,expr2)) + regr_intercept(expr1,expr2) 　　

pred2 = (pred1 - regr_intercept(expr1,expr2)) / regr_slope(expr1,expr2) 　　回溯函数需求有两个数型参数。如果你要对日期数据进行操作，那么你必须先将这些数据转换成线行数类型。在Oracle SQL中，最佳的办法就是就这些日期间去一个参照日期，从而得到对应的不同的天数（天数的类型符合需求了）。

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; Month Day CUM_SLOPE CUM_ICPT

---------- ---------- ---------- ----------

12 12 -68 1872 12 12 -68 1872 12 13 -20.244898 1254.36735 12 13 -20.244898 1254.36735 12 19 -18.826087 1287 12 20 62.4561404 125.28655 12 20 62.4561404 125.28655 12 20 62.4561404 125.28655 12 20 62.4561404 125.28655 12 26 67.2658228 58.9712313 12 26 67.2658228 58.9712313 12 27 37.5245541 284.958221 12 27 37.5245541 284.958221 12 27 37.5245541 284.958221

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; DAY_NUMBER_IN_MONTH Regr_Count

------------------- ----------

1 825 2 1650 3 2475 4 3300 . . .

26 21450 30 22200

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;

FISCAL_MONTH_NUMBER Regr_R2

------------------- ----------

1 2 1 3 .927372984 4 .807019972 5 .932745567 6 .94682861 7 .965342011 8 .955768075 9 .959542618 10 .938618575 11 .880931415 12 .882769189

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; DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX

------------------- ---------- ----------

14 882 24.5 14 882 24.5 15 801 22.25 15 801 22.25 16 777.6 21.6 18 642.857143 17.8571429 18 642.857143 17.8571429 20 589.5 16.375 21 544 15.1111111 22 592.363636 16.4545455 22 592.363636 16.4545455 24 553.846154 15.3846154 24 553.846154 15.3846154 26 522 14.5 27 578.4 16.0666667

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;

DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx

------------------- ---------- ---------- ----------

1 18870.4 2116198.4 258.4 1 18870.4 2116198.4 258.4 1 18870.4 2116198.4 258.4 1 18870.4 2116198.4 258.4 7 18870.4 2116198.4 258.4 8 18870.4 2116198.4 258.4 14 18870.4 2116198.4 258.4 15 18870.4 2116198.4 258.4 21 18870.4 2116198.4 258.4 22 18870.4 2116198.4 258.4

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 <50

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31491/viewspace-598179/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31491/viewspace-598179/