PostgreSQL 计算 任意类型 字段之间的线性相关性-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

PostgreSQL 计算 任意类型 字段之间的线性相关性

简介:

PostgreSQL自带了计算numeric和numeric字段的线性相关性的聚合函数corr(numeric, numeric)。
例如:

postgres=# select corr(c1,c2) from (values (1,2),(2,1),(100,90),(13,13),(25,27) ) t(c1,c2);
       corr        
-------------------
 0.998528203831946
(1 row)

postgres=# \df+ corr
                                                                             List of functions
   Schema   | Name | Result data type |        Argument data types         | Type | Security | Volatility |  Owner   | Language |   Source code   |       Description       
------------+------+------------------+------------------------------------+------+----------+------------+----------+----------+-----------------+-------------------------
 pg_catalog | corr | double precision | double precision, double precision | agg  | invoker  | immutable  | postgres | internal | aggregate_dummy | correlation coefficient
(1 row)

如果要计算多元的线性相关性,可以使用madlib提供的linregr_train函数来统计。
http://doc.madlib.net/latest/group__grp__linreg.html
注意不管是一元回归还是多元回归,都需要提供数字类型,如果是文本是不支持的,如下:

postgres=# select corr(c1,c3) from (values (1,2,'test'),(2,1,'digoal'),(100,90,'hello'),(13,13,'china'),(25,27,'hangzhou') ) t(c1,c2,c3);
ERROR:  function corr(integer, text) does not exist
LINE 1: select corr(c1,c3) from (values (1,2,'test'),(2,1,'digoal'),...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

那么怎么处理呢?
PostgreSQL提供了强大的窗口功能,因为任意字段都可以排序,所以只要使用窗口输出字段排序后的rank()就可以代表它的位置从而计算相关性。
例如

postgres=# select 
c1,rank() over(order by c1) rc1, 
c2, rank() over(order by c2) rc2, 
c3, rank() over(order by c3) rc3 
from (values (1,2,'test'),(2,1,'digoal'),(100,90,'hello'),(13,13,'china'),(25,27,'hangzhou') ) 
t(c1,c2,c3) order by c1; 
 c1  | rc1 | c2 | rc2 |    c3    | rc3 
-----+-----+----+-----+----------+-----
   1 |   1 |  2 |   2 | test     |   5
   2 |   2 |  1 |   1 | digoal   |   2
  13 |   3 | 13 |   3 | china    |   1
  25 |   4 | 27 |   4 | hangzhou |   3
 100 |   5 | 90 |   5 | hello    |   4
(5 rows)

这个例子要计算c1,c3的相关性,c1是数字字段,但是c3是text。 corr函数不支持这么操作。
因此我使用上面这条带窗口的SQL,把text字段根据rank抽象为数值,正好和其他字段可以匹配相关性。
来看计算结果:

postgres=# select corr(c1,rc3), corr(rc1,rc3) from (
select c1,rank() over(order by c1) rc1, 
c2, rank() over(order by c2) rc2, 
c3, rank() over(order by c3) rc3 
from (values (1,2,'test'),(2,1,'digoal'),(100,90,'hello'),(13,13,'china'),(25,27,'hangzhou') ) t(c1,c2,c3) 
) t;
       corr        | corr 
-------------------+------
 0.283302495025433 | -0.1
(1 row)

建议采用corr(rc1,rc3)的结果,这个比较有代表性。 代表被评测列的线性相关性。

应用场景大家猜一猜。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接