Oracle analytic function-corr

皮尔逊相关系数的数学公式请参考WIKI上关于Pearson correlation coefficient的内容。

Pearson相关系数是用协方差除以两个变量的标准差得到的,虽然协方差能反映两个随机变量的相关程度(协方差大于0的时候表示两者正相关,小于0的时候表示两者负相关),但是协方差值的大小并不能很好地度量两个随机变量的关联程度,例如,现在二维空间中分布着一些数据,我们想知道数据点坐标X轴和Y轴的相关程度,如果X与Y的相关程度较小但是数据分布的比较离散,这样会导致求出的协方差值较大,用这个值来度量相关程度是不合理的。为了更好的度量两个随机变量的相关程度,引入了Pearson相关系数,其在协方差的基础上除以了两个随机变量的标准差,容易得出,pearson是一个介于-1和1之间的值,当两个变量的线性关系增强时,相关系数趋于1或-1;当一个变量增大,另一个变量也增大时,表明它们之间是正相关的,相关系数大于0;如果一个变量增大,另一个变量却减小,表明它们之间是负相关的,相关系数小于0;如果相关系数等于0,表明它们之间不存在线性相关关系。

上述的描述是比较难懂的,简单一点的描述是
cos[a,b] = a * b / |a|*|b| 皮尔逊系数就是cos计算之前两个向量都先搞个标准化,计算a,b两个向量的夹角的cos值。

当两个变量的标准差都不为零时,相关系数才有定义,皮尔逊相关系数适用于:
(1)、两个变量之间是线性关系,都是连续数据。
(2)、两个变量的总体是正态分布,或接近正态的单峰分布。
(3)、两个变量的观测值是成对的,每对观测值之间相互独立。

CORR函数一对表达式的相关系数,计算的理论基于皮尔逊相关系数,排除掉expr1或expr2为null的数值,基于下面的计算公式计算
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
函数返回值为NUMBER类型。如果函数被用于空集则函数返回NULL。

聚合功能
SQL> SELECT weight_class, CORR(list_price, min_price) "Correlation"
  2    FROM product_information
  3    GROUP BY weight_class
  4    ORDER BY weight_class, "Correlation";

WEIGHT_CLASS Correlation
------------ -----------
           1  .999149795
           2  .999022941
           3  .998484472
           4  .999359909
           5  .999536087

随着对参与运算的两个数据样本点之间的数值相差越来越大,相关系数不断变小,最终为负数,不相关。

分析功能
计算员工在公司时间和员工职位薪水之间的相关性。
SQL> SELECT employee_id,
  2         job_id,
  3         TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH) "Yrs-Mns",
  4         salary,
  5         CORR(SYSDATE - hire_date, salary) OVER(PARTITION BY job_id) AS "Correlation"
  6    FROM employees
  7   WHERE department_id in (50,80)
  8   ORDER BY job_id, employee_id;

EMPLOYEE_ID JOB_ID               Yrs-Mns            SALARY Correlation
----------- -------------------- -------------- ---------- -----------
        145 SA_MAN               +12-08              14000  .912385598
        146 SA_MAN               +12-05              13500  .912385598
        147 SA_MAN               +12-03              12000  .912385598
        148 SA_MAN               +09-08              11000  .912385598
        149 SA_MAN               +09-04              10500  .912385598
        150 SA_REP               +12-04              10000   .80436755
        151 SA_REP               +12-02               9500   .80436755
        152 SA_REP               +11-10               9000   .80436755
        153 SA_REP               +11-02               8000   .80436755
        154 SA_REP               +10-06               7500   .80436755
        155 SA_REP               +09-06               7000   .80436755
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

One Response to Oracle analytic function-corr

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s